Stellar & Eroad

EROAD modernises road charging and compliance for road transport by replacing paper-based systems with easy-to-use electronic systems. EROAD is the largest provider of road user charges (RUC) compliance in New Zealand, and a leading provider of health and safety, compliance and fleet management solutions.

The EROAD MyEROAD platform is the primary interface for Fleet Managers to manage their vehicles and drivers. MyEROAD provides both real-time information on vehicle activity and driver behaviour, and analytic reporting across a range of subjects, including fuel usage, vehicle idling, exceptional events and fuel emissions.

The AWS-based EROAD data processing platform receives streaming feeds from vehicles via the Ehubo telematics units, including GPS and sensor information. The telematics data is cleansed and consolidated into useable data for operational and analytical use.

The challenge

EROAD is migrating their customer-facing, embedded reporting solution to Microsoft Power BI using Snowflake as the supporting database platform. They were looking to optimise the performance of the reports to ensure that customers could access information as quickly as possible while minimising Snowflake operating costs.

Stellar was engaged to design an optimised model for Snowflake and the Power BI report designs and data models, with a focus on reducing the Snowflake compute required to process the report data and report responsiveness for multiple concurrent users.

The solution

Stellar undertook an analysis of the Power BI reports and their associated data models to identify optimisations that could be applied, focussing on the expected user experience and outcomes as a goal.

Snowflake data model

As part of the analysis process, Stellar identified that the Power BI reports had been built against a data model that contained data stored in operational structures. The data model used was closely aligned with the operational source data, which contained many data attributes and high volumes of transactional data. Stellar determined that the use of an analytical reporting model would both simplify the design and reduce the data volumes involved, by aggregating data to the level required to support the reporting needs, without losing value for the customers.

Based on the existing reporting requirements and data points, Stellar designed and documented a dimensional model that allowed for simplification and aggregation of data within Snowflake whilst supporting reporting data needs.

Power BI model optimisation

The existing Power BI report models combined multiple data access methodologies, including importing data into the Power BI model and directly accessing data from the Snowflake database. Stellar worked with the report developers to migrate the model to a full direct access model to leverage the power of the Snowflake database. Further optimisations were applied to the model to reduce the volume of data being returned to the report and speeding up performance by improving join logic and reducing model complexity.

Business benefits

Implementing an analytical data model means that reports run far quicker across much smaller data sets, which when combined with the effective result set caching, provides much greater performance for end-users and reduces the Snowflake compute costs for Power BI reporting.