A leading wholesale distributor of building and industrial products in the United States has been working to improve their reporting on financial-related items. In an industry where margins are small and payment terms can differ for the company as well as its customers, understanding exactly when revenues and costs are expected to come in is critical to the bottom line.
BlueGranite worked to architect an Azure Data Warehouse that would initially focus on Accounts Receivable, Accounts Payable, Inventory, and Purchase Orders but would also be built in such a way that it could scale to accommodate additional use cases in the future.
BlueGranite initially deployed its Data Lake Hydrator, which allowed for data to be moved from source systems to an Azure Data Lake, enabling eventual transformation of data for reporting purposes. The team was able to deploy the underlying architecture, connect to the company’s source systems, and begin to land data in the Data Lake in less than a week – a process that previously might have taken almost a month.
Data Transformation Choices:
Once the Data Lake Hydrator was deployed, the company needed to choose the method for which they would do data transformation into the data warehouse. BlueGranite set up demonstrations of several different options: Mapping Data Flows (a low-code option), stored procedures in Synapse (a SQL based option), and using Azure DataBricks for transformation (a PySpark and SQL based option). Over the course of less than a week, the BlueGranite team set up demonstrations using the company’s own data that was now located in the Data Lake, and gave the company all it needed to consider the best path forward. Ultimately for this company, they wanted more access to the codebase than the low-code mapping data flows would offer, they had no experience using PySpark, and they had a lot of previous experience using stored procedures to transform their data. They were also excited to use Azure Synapse for their Cloud Data Warehouse and ultimately settled on using stored procedures in a dedicated SQL pool within Synapse.
From there, BlueGranite got to work understanding the use case and working hand-in-hand with the IT team to build the data transformations needed to load data into the Data Warehouse.
Future Acquisitions:
The company had just gone through a several year stretch where they acquired another company and then needed to integrate back-end data systems. Knowing that this was a likelihood in the future, the IT team asked that BlueGranite set up processes to better integrate data from multiple sources into the final Data Warehouse tables. BlueGranite created a framework to handle data coming from any system that would all translate into final metrics and dimensions related to Accounts Receivable, Accounts Payable, Purchase Orders, and Inventory. This work will save the IT team time in the future so they can better respond to needs, while also allowing for historic financial data to be tracked consistently, allowing for better reporting.
An overview of the tools used in this solution are below:
| Technology | Business Outcome |
|
|
Setting up resources and moving data from source systems used to be challenging and would take days or weeks. With BlueGranite’s Data Lake Hydrator, it took hours to move data from several source systems, enabling more comprehensive data transformation. |
|
|
The Data Lake centralized data from the company’s previous on-prem data warehouse, current financial and operational databases, as well as some databases holding data from companies that had been acquired. In the future, the company also expects to ingest data from 3rd party sources to give even better context to their data. |
|
|
Synapse set this company up with a scalable data warehouse solution that could be managed using SQL. They can monitor their data pipelines, scale their warehouse, and also dynamically shift the compute on data transformations to ensure their processes are cost-effective. |
|
|
The company was already using Power BI for some of its work, but needed help establishing dashboards based on the data in the new data warehouse, while also ensuring the dashboards were auto-refreshed every time new data landed in the data warehouse. |
Data Lake Hydrator
