- A private equity firm needed a way to consolidate data, standardize KPI’s and analyze data across their portfolio of machinery dealerships.
- They turned to Aptitive to build out a scalable modern data architecture and analytics platform that would enable them to track key metrics such as cash flow, inventory turns, sales efficiency and machine obsolescence from each dealership and as well as the portfolio as a whole.
- As a result our client is able to run financial, operation, and sales reports. They can make better decisions based on up-to-date and accurate information from all existing dealerships in their portfolio from easy to use Power BI dashboards.
Just the Headlines
Short on time? Here are the key facts.
As a private equity firm started to acquire construction machinery dealerships across the United States, they needed a way to consolidate data from each location, standardize KPI’s, and analyze data in a user friendly business intelligence (BI) tool.
They turned to Aptitive, a Data, Analytics, and Data Science Consulting firm, to build out a scalable modern data architecture and analytics platform that would enable them to track key metrics such as cash flow, inventory turns, sales efficiency and machine obsolescence data across their dealerships.
- Driving operating performance improvements through key management metrics
- Creating a unifying set of standard metrics and a playbook for future dealership acquisitions
- Increasing cash flow predictability, financial performance, and operator productivity
- Building easy-to-access analytics solutions with the ability for users to build self-service reports and dashboards
- Provide a highly scalable cloud-based solution that can easily absorb new dealerships to the platform as they are acquired
Private Equity and Equipment Distribution
Azure (Data Factory, Blob, Functions, Key Store)
Snowflake (Data Vault, Star-Schema model)
Step 1: Design the solution and build a road map
Our team led with a quick strategy phase to align on the right data and technical platform design that would meet immediate needs and also scale for the anticipated long-term growth of the organization. Working closely with the executives we assessed their current reporting and metrics environment, designed a new solution, and estimated and planned a phased-project to quickly deliver business value.
Step 2: Data Ingestion to Centralize Data from Multiple Sources
The first critical step for this project was to centralize data from multiple source systems throughout the network of dealerships. The scope included core dealership management systems, GL, sales and inventory data.
To make this happen, our team set up custom data pipelines for ingesting data to a cloud-based Snowflake data warehouse using Azure Data Factory, Azure Functions, Blob Storage, and custom python/SQL scripting.
Step 3: Building a Scalable Data Model
The next step was to organize, standardize, and structure the data to ensure that data was consistent and ready for analytics.
A data vault model was used in the semantic layer for its ability to manage data from multiple source systems as well as frequently added or changed data. Additionally, the data vault model is beneficial for:
- Tracking change history (not tracked in the original source)
- Providing simple method for adding new data sources with unique business logic
- Integrating disparate data using simple module SQL scripts (for easier future support)
- Allowing for easy linkage between areas of analytics (eg, connecting GL to Sales or Sales to Inventory)
Step 4: Deploy standard dashboards and analytic tools
The final step was to create interactive, user-friendly reports and dashboards in Power BI. This enabled the private equity partners to easily visualize the KPI’s they needed to track performance at both the individual dealerships and across all dealerships as a whole.
These Power BI Dashboard provided:
- Shareable, innovative reports to showcase business value across a variety of business domains
- Reporting to help with day-to-day operations and decision making by department managers to increase efficiency
- Easier report development and interaction with data to answer to ad hoc business questions
- A solution that allows the client to build their own reports using metrics based on a data dictionary that standardizes KPIs across the full enterprise
As a result of this data architecture and analytics project, our private equity firm client is able to visualize data from all existing dealerships in their portfolio from easy to use Power BI dashboards. They can run financial, operation, and sales reports and make better decisions based on up-to-date and accurate information.
Success by the Numbers:
- Increased the speed to insight from approximately 1 month to daily for key KPIs
- Enabled over 200 users to access the data they needed to run reports directly without having to rely on IT
- Significantly reduced the number of report requests to IT, freeing them up to focus on more strategic initiatives
- Created over 100 standardized KPI’s to ensure consistency and report accuracy across all dealerships through an enterprise data dictionary