fbpx

Stellar & EROAD

Case study

EROAD is on a mission to make our roads safer
and more productive. Launched to make New
Zealand’s Road User Charging system more
efficient and paperless, they have since grown
into a full telematics suite of products, serving
essential services across New Zealand, Australia
and the United States. Based in Auckland, New
Zealand, they design and manufacture in-vehicle
hardware, operate secure payment and
merchant gateways and offer web-based value-added
services.

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.

https://www.eroad.co.nz


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.

“Stellar was engaged to provide analysis of our various sources of data and provide us with a robust, future-proof data warehouse schema design that would be easy to work with and understandable enough to be able to offer our integrated customers greater insights into their data. They not only did a very professional job of this but were able to do so with very little assistance needed from us.”

Jeremy Peaks, Software Development Manager, EROAD

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.

 

Download Case Study as a PDF

CLICK HERE