- A private equity firm needed a way to consolidate data, standardize KPIs 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 KPIs, 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
- Providing 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: Designing the solution and building 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: Centralizing data from multiple sources with data ingestion
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 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 (e.g., connecting GL to Sales or Sales to Inventory)
Step 4: Deploying 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 KPIs they needed to track performance at both the individual dealerships and across all dealerships as a whole.
These Power BI dashboards 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 modern data architecture and analytics project, our private equity firm client is able to visualize data from all existing dealerships in their portfolio with easy to use Power BI dashboards. They can run financial, operations, 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 more than 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 more than 100 standardized KPIs to ensure consistency and report accuracy across all dealerships through an enterprise data dictionary