Organizations, large and small, often have vast data resources but struggle with linking them together to provide meaningful insights that spur intelligent business decision making. Couple that issue with another big one: many organizations lack a strategic plan for their data infrastructure. Over time, companies can suffer from the complexities and compromises of a system that has been adjusted reactively instead of proactively. This can lead to delayed business needs and costly changes among different applications, technologies and platforms.

When organizations decide to begin the data warehouse migration process, it must be done strategically. Avoiding bottlenecks in the process requires an understanding of the relative dependency of each data source within the data ecosystem. While individual stakeholders, applications and reports provide insights into the use of enterprise data, there are also abstract, latent connections and dependencies within the data itself which reveal themselves through mathematical analysis. These hidden relationships are important to designing, prioritizing and ordering the myriad of tasks required for the transition to a new data warehouse.

The Problem

After years of ad-hoc solutions that seemingly put band-aids over numerous issues, leaders within one Indiana electric utility company had become frustrated by their legacy data environment. The scale and complexity of the company’s data resources became an issue both for the internal IT team, which was spending significant time manually producing reports from the legacy system, and executive leaders, who wanted the ability to quickly access up-to-date reports.

The Solution

In 2021, company leaders decided it was time for a change; they wanted a new data warehouse system. But if they were going to spend the money to do it, they wanted it done right. That’s why they began working with Data317, a data product company. Data317 would perform an analysis of their existing data resources and make recommendations for their new, modern data warehouse system. The analysis would untangle years worth of compounding complexities within the system, provide new extract transform load (ETL) procedures that would copy data from all sources into the new source, refactor database tables, and migrate existing reports.

Adding to the complexity of the project was the fact that the company did not have a global data dictionary, or comprehensive documentation of their existing architecture, to support these efforts. Essentially, Data317 would be diving into the system without a map. The process included:

• Utilizing Data317's methods to measure resource utilization
• Deploying a custom SQL parser to extract important contextual metadata from the hundreds of available files
• Applying advanced network analysis techniques to analyze the nearly 8,000 dependency relationships between all tables and reports within the existing environment
• Leveraging AI-based network planning and optimization techniques to unwind all the report and table dependency relationships in order to find the most efficient migration task execution plan while observing task precedence constraints and key recommendations

Although the majority of existing reports were not frequently referenced, the algorithm could also learn from the data reference patterns to support predicted future reporting needs, therefore reducing the time required for analysts and users to produce new business intelligence reports.

The Results

Using both cutting-edge, AI-powered techniques and industry best practices, Data317 developed a data warehouse migration strategy that is helping its client achieve its goals of building a comprehensive reporting framework while also enabling future development. The resulting plan encompassed data architecture, data lifecycle management and ETL operations.

The data warehouse migration plan reduced the risks of the migration to a new platform by identifying and prioritizing the critical dependencies and tasks necessary. By adopting best practices recommended by Data317, the company can avoid the constraints and issues they faced in the past and move forward with a data warehouse migration that will result in a system that provides increased transparency, speed of reporting and a more responsive organization, serving its customers with a methodical and data-driven approach.