From Chaos to Clarity:

Streamlining Data Environments

‍

Problem

An industrial distribution company pursued a growth strategy through aggressive acquisition. With each acquisition, challenges arose in reconciling the diverse data structures. Over two decades of acquisitions, the data environment had become so vast and intricate that obtaining a list of the company's products involved navigating through thousands of lines of SQL and intricate processes. The growing complexity of the data environment resulted in a decline in the quality of amalgamated data. This, in turn, caused issues across various company operations, leading to a loss of customers and profits. Challenges included inaccurate data, miscommunication on processes, and increased stress among teams.

Solution

We determined that in order to clean and centralize the data environment a new architecture had to be implemented. Using the Kimball approach to data warehousing, we focused on what data brought value to the business. We spoke with stakeholders across the company to properly define the meaning of each data point and documented business processes. Using these reconciled and agreed upon definitions each data point was dimensionally modeled. Fact tables were built on top of the dimensional tables and ETL was implemented to keep the warehouse up to date and to handle any slowly changing dimensions. After completing these steps and allowing ample time for QA, we began rewriting crucial reports based on the newly created warehouse tables. The internal team was able to focus on the busy day to day needs of the business as we worked alongside them to transform the data into the new reporting structures.

‍

Tech Stack:

SQL, SSIS, SSRS, Microsoft Azure

Outcome

Employees were shocked as the time needed to produce reports was dramatically reduced with trusted accuracy of the data. The data we provided finally matched third-party results and employees were starting to trust the data in their reports again. Productivity went up, orders were moving faster, time to customer increased, and overall end to end efficiency dramatically increased. With a simplified data environment, we were able to provide more advanced analytics in our reports. All these factors led to a reduction in issues across many of the company’s operations, which ultimately led to happier customers and employees.

‍