Delivering a data analytics, reporting and migration strategy

Client

A leading appliance and electronics retailer, selling the best of appliances and televisions to over a million satisfied customers, providing high-quality products at competitive prices and standing as an independent elite retailer.

Background

Following their migration from a legacy ERP system (Everest) to Microsoft Dynamics, a UK appliance and electronics retailer sought to transform their reporting capabilities by transitioning to Power BI. The company needed to maintain access to historical sales data from Everest whilst incorporating new data from Dynamics, which was being synchronised every 15 minutes to an Azure Data Lakehouse via Azure Data Factory.

The challenge was to develop a scalable reporting solution that would unify these disparate data sources whilst empowering staff with self-service access to insights and improving overall data visibility across the organisation.

Solution

Audacia conducted a comprehensive analysis of the data architecture and reporting requirements, developing three potential approaches to implementing Power BI reporting within their existing infrastructure. After careful evaluation, we recommended implementing a robust data warehouse solution using Azure Synapse alongside their existing Azure Data Lake.

The solution architecture began with a one-time migration of historical sales data from Everest to the Azure Data Lake, transforming it to match the structure of the current Dynamics system. This data was then processed into a well-structured data warehouse using Fact and Dimension tables, optimising it for reporting performance.

We implemented an automated process to maintain data currency, ensuring the Azure Synapse Data Warehouse stayed synchronised with the 15-minute delta updates from Dynamics. This approach enabled Power BI to connect to both the Data Lake and Data Warehouse, allowing reports to be generated from the most suitable data source based on specific requirements.

The architecture was designed with scalability in mind, utilising Azure Synapse's capabilities to handle growing data volumes and increasingly complex queries without performance degradation. The implementation of a proper data warehouse structure laid the groundwork for future advanced analytics capabilities, including potential machine learning applications.

Results

The data analytics project delivered clear strategic recommendations:

The strategic recommendations provide a clear path forward for transforming data capabilities and establishing a scalable foundation for future analytics and reporting initiatives.