Manufacturing / Modern Data Platform

Distributer Enables Granular Financial Insights with Transformed Data Infrastructure

SB_Manufacturing

Overview

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.

The Challenges

This company previously worked with BlueGranite to build an on-premises data warehouse and was ready to upgrade to a cloud-based data warehouse, but needed help. Additionally, the company worked internally to set key measures and key performance indicators (KPI) they were interested in tracking and needed a consistent way to ingest, store, transform, and report on these metrics. Up until now, much of the reporting was done manually by exporting data from source systems that were then used to build reports in Excel. This had the potential to cause version-control confusion, as well as inaccurate reporting that was challenging to distribute. Finally, the company has had some leadership transition over the last couple of years, bringing an expectation of new demands for data-driven efforts that would need an underlying infrastructure to support.

The Solution

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

Data Lake Hydrator

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.


Azure Data Lake Storage

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.


Azure Synapse

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.


Power BI

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.

The Results

This project enabled the company to better understand Azure data services, specifically how data could be ingested from nearly any source system, stored in a Data Lake, transformed and live in a Synapse Data Warehouse, and reported on in Power BI. Building out an infrastructure like the one BlueGranite created for this company has saved the IT team time, and has allowed for them to think more strategically about how to make data available to the company as a whole, rather than rushing to help manually update reports. Going forward, the IT team is thinking more about data governance, data access policies, and potential AI and ML use cases.

The company now has access to granular levels of information about exactly when payments are due or can be expected, where their inventory is at a moments notice, and how individual company branches are operating, all leading to better overall company management.

Interested in learning more about how BlueGranite can help accelerate your modern analytics solution? Contact us today.

INFORM STRATEGIC & TACTICAL BUSINESS DECISIONS

Kickstart Your Digital Transformation With BlueGranite's Business Intelligence Solutions

LEARN MORE