Data Strategy Platform Solutions - On-Premise to Azure Consultancy Services

Use Case – On-Premise BI to Azure Migration

Our client is highly data driven, however processes for handling and manipulating data are very manual and potentially error prone and time intensive.

The objective for this solution was to Overhaul and markedly improve on the existing On-Premise SQL server stack reporting and business intelligence function. Data was to be integrated into a central Data Warehouse from data sources such as SAP and various on-premise and cloud hosted Transport, Logistics, CRM, Quality Control and Production systems, and a single authoratative source of truth built to enable a business wide view through through rich and engaging visualisations.

Business Requirement

• Deliver insightful and engaging reports and dashboards

• Build a central, authoritative, repository of data

• Consolidate unstructured and semi-structured data into a Data Warehouse

• Develop a Semantic Model

• Automate cleansing, shaping and movement of data

• Build a scalable solution

• Take advantage of current technology Cloud offerings

• Lower or remove On-Premise licensing costs

The PTR Solution

The solution implemented was built around Microsoft Azure SQL Database, Azure Analysis Services, Azure AD, Azure Data Factory, Azure Data Lake and Power BI.

• Azure Synapse Analytics to host a newly designed Data Warehouse integrating data from across the business

• Azure AD to service User Authentication & Authorisation to Power BI, Azure Synapse Analytics and Azure Analysis Services

• Blended On-Premise and Azure ETL and ELT tools

• Azure Data Lake Storage Gen 2 to provide a single repository for source data

• Azure Data Factory to facilitate ELT pipelines to extract data from the Data Lake, load data into Staging tables in the Data Warehouse, transform data by manipulating it into a desired tabular structure for consumption

• Power BI Premium for Shared Datasets and a Self-Service Business Intelligence tool set

The Benefits

• Newly designed Data Warehouse fit for purpose

• Central Tabular Model providing unique and complex business calculations, measures and KPIs, and a single authoritative source of truth

• Single sign on across the whole Data Platform enabling highly granular User Access Control to reports, source data and administrative functionality

• Single repository for source data in its native format

• Pipelines and dataflows tailored to meet high volume fact tables and real time requirements

• Archive data accessible through Data Warehouse

• Eliminate On-Premise Enterprise Licensing requirements

• Delivery of rich, engaging visualisations empowering users with insight, interactive analysis and the ability to answer their own questions swiftly using a trustworthy, consistent single truth Tabular Model

• Increased user adoption through better and more engaging reports

• Provision for potential future development in the areas of AI and ML