Manufacturing and retail company
This well-known client is an international retailer based in the U.S. They produce powerful, long-lasting batteries, including AA batteries, rechargeable batteries, coin button batteries and more.
Overview
THE CHALLENGE
This manufacturing company struggled to consolidate sales metrics across a variety of markets.
THE SOLUTION
Ollion pulled our client’s raw data into Snowflake and wrote SQL scripts to recreate the data transformation in their existing Excel workbooks. We then appropriately formatted the data for use in a Power BI report.
THE OUTCOME
The documentation of the transformation process and SQL script gave this client's BI team a better understanding of how raw sales data became consolidated market share information.
The challenge
An international manufacturing company was struggling to consolidate sales metrics across a variety of markets. The process for generating reports was unclear to business intelligence (BI) teams and stakeholders, and it seemed that much of the work was done manually by one person. Transformations of the data were done in Excel, across multiple sheets and workbooks for each market, and then pasted into a final market share document for stakeholders to analyze. This process left our client’s teams with no ability to drill further into market-level metrics and no way to understand what transformations had been done.
The company asked Ollion to explore these Excel workbooks, understand the data transformation process and recreate it in Snowflake for use in a Power BI report.
Making an impact
To start, the Ollion development team pulled the raw sales data into a data warehouse using Snowflake. From there, we wrote SQL scripts to recreate the transformation of the data we were seeing in the Excel workbooks. This was a combination of rewriting Excel formulas and appropriately formatting the data for analytical purposes.
We also created a dedicated currency conversion table to more quickly and more accurately convert sales metrics. The entire transformation process was recreated in Snowflake, only using one SQL script.
The business benefits
By recreating the transformation logic in Snowflake, Ollion eliminated the need for multiple Excel workbooks and sheets per market, as well as a tedious manual process that needed to be done several times a year. We reduced the time it took for reports to be generated; getting more accurate data into the hands of our client’s stakeholders enabled them to make decisions more quickly.
Streamlining data transformation for effortless insights
The documentation of the transformation process and SQL script gave this manufacturer’s BI team a better understanding of how raw sales data became consolidated market share information. A week’s worth of work by several people is now done in a few minutes, without the need for copy/pasting data from one workbook to another.
The greatest impact is turning a manual task into an automated and documented process. If the employees completing that task were to leave the company, our client was at risk of losing the knowledge of how their international market share data was being calculated. Now, the entire process is documented in Snowflake, and all that is needed to maintain their system is an experienced data engineer.
