The Challenge

In the early part of 2020, they rolled out a top-tier management system to 450 trucks in their fleet, resulting in an explosion of data. In the past, they relied on hand-coded driver score cards and performance reports to keep track of productivity and profitability. But, the new influx of data had pushed their manual reporting processes beyond their limits.

Since they own over 450 trucks, understanding their profitability margins at the lowest possible granularity could help them stay ahead of depreciation. Limitations of their homegrown analytics processes made it almost impossible to have a clear picture of key profit metrics that would allow them to continue to consistently increase profitability.

Their unwavering focus on driver safety and driver satisfaction meant they needed to identify the key attributes that resulted in higher productivity and profitability among their drivers. This would allow them to optimize their incentives, bonus structure, hiring, and training practices in a way that would reliably increase their profitability, without sacrificing the expenses that made them one of the most fuel efficient and desirable transportation companies to work and drive for.

As with many organizations, understanding basic metrics – like number of drivers on the road each day – were a challenge. Additionally, even understanding the definition of driver profitability was not initially clear. Through trial and error, they developed various perspectives and several hard-coded approaches, but there was no agreement on the correct way to calculate it. Also, all of the manual reporting they were doing consisted of complex queries running against their operational systems, creating performance issues in reporting and their critical business systems.

The Solution

The challenges mentioned above sent their CIO on a mission to find a solution. One that would allow them to harness their data and increase their competitive advantage even further, leveraging a modern analytics approach.

Without an off-the-shelf solution that fit their needs, the CIO came across two eBook’s that BlueGranite authored titled Data Lakes in a Modern Data Architecture and Successful Self Service BI and the approaches seemed to resonate with the future-state they were striving for.

After leading a brief 4-hour Architectural Design workshop with the COO, CIO, CFO, and several key IT stakeholders, BlueGranite was able to draw up a solution architecture leveraging Azure Data Services powered by BlueGranite’s Data Lake Hydration Framework.

Within the first week of the engagement, BlueGranite stood up the Data Lake Hydrator Framework in the client’s Azure environment and successfully landed most of the data required for the modern solution into Azure.

To host the Data Warehouse, BlueGranite used Azure Synapse Analytics. This decision came in part because of the ability to scale data storage and workloads, as well as the potential to use it in concert with Machine Learning tools in the future. Most importantly, the SQL functionality was familiar to the client’s work process already.

BlueGranite’s data science team was able to start making use of the data that landed in Azure right away to build out various experiments in Azure Machine Learning for both supervised and unsupervised learning. The models took into account everything about the drivers including their certifications, tenure, location/domicile, who their manager was, how many trips they had taken, and how many miles they had driven -  to name a few, in order to derive the leading indicators that consistently led to higher profitability among truck drivers.

Outside of the advanced analytics, BlueGranite delivered powerful business intelligence through Power BI, giving everyone from the executives to the driver managers, and drivers themselves, access to different metrics around driver profitability, down to an hour-by-hour granularity.

The client was able to clearly identity which routes were most profitable and uncover key insights like “when a driver start’s a route on a Tuesday, it typically leads to higher profitability”.

Technical Solution

A simplified version of the Modern Data Architecture used for the client’s driver profitability solution is illustrated below:

The solution centered around BlueGranite’s Data Lake Hydrator framework, which is a meta data driven ETL framework that makes heavy use of Azure Data Factory, Azure Data Lake Storage, and Azure SQL DB for standing up and configuring an end-to-end ingestion framework that can start bringing in floods of data within hours of starting a project.

The architects at BlueGranite modeled the Data Warehouse star schema and implemented it in the recently released Azure Synapse Analytics service to take advantage of the scalability and unified analytics environment.

Tabular Models in Power BI were then modeled off the Data Warehouse structure within Azure Synapse Analytics, which made it easy for the analysts and executives that had been used to reports in Excel to start taking advantage of the new reports immediately in Power BI, due to the low learning curve between the tools. The executives were quickly interested in the number of other types of insights they could see from the data through use of Power BI.

Process Solutions

Due to the advancements of the client’s data architecture, they were able to speed up several business decisions including the hiring of a full-time data scientist to continue to increase the ROI they see out of their modern analytics platform.

Through the use of Power BI and the Data Warehouse serving as one source of truth that everyone could agree on, the client has seen a rapid increase of self-service capabilities. These included unlocking a multitude of new perspectives of their business and the ability to optimize their incentive structures and hiring strategies to ensure they are attracting and continually grooming the best drivers in the industry.

While the approach took advantage of some brand new technology in Azure, the clients existing IT team members were able to effectively administer and evolve the solution from day 1 in production, since the modern tools provided familiar SQL interfaces. Also, BlueGranite’s consultants and the client’s IT team members worked collaboratively throughout the engagement to ensure successful knowledge transfer and on-the-project training.

The BlueGranite team delivered in-depth modeling and design checkpoints along the way, ensuring the client’s stakeholders agreed and were up to speed with the evolution of the solution throughout the entire engagement.

The Results

Due to the rapid deployment resulting in shortened development cycles and the economies of scale of Microsoft’s Platform as a Service cloud technology, the client has a clear path to a strong return on investment from the solution.

The use of BlueGranite’s Data Lake Hydrator framework cut down development time by months and will continue to reduce the cost of administration, support, and continued development for years to come.

But, the most significant benefit from the solution is the ability for the client to see a 360 degree view of the drivers, ensuring they can continue to roll out programs that keep them at the top of the list of Best Transportation Companies to work for, hopefully for the next 75 years.