Mik Panchal, Managing Consultant – Altis Sydney
Are you implementing an Analytics solution using Azure and Power BI? Mik Panchal, our Sydney Managing Consultant, shares our recent success story using Microsoft Cloud infrastructure.
This post will walk you through the benefits of using our Azure Framework and Power BI to provide robust self-service capabilities to Business.
Altis Azure Framework
Should we invest more time getting data into a Data Warehouse (DW) or spend more time analysing the data and creating insights for the business?
This is a common question asked by our customers across both IT and Business.
Data which is modeled in a format that is easy to consume by self-service BI tools provides a robust solution for a mature Data Warehouse and Analytics solution; however, can this process be sped up?
In order to address this, Altis has developed a collection of Framework in Azure to get the most out of your investment and deliver quicker insights. Altis Azure Framework is made up of the following:
- Azure Data Factory Metadata Driven Framework; used to automate data integration from various source systems into Azure.
- Reconciliation and Automated Testing Framework; used to automate testing and provide a reconciliation report to Business and IT.
- Auditing Framework; used to tract each record through its lifecycle from extract to Staging to Dimension and Facts. The Auditing also provides re-start ability to the ADF framework and error logging throughout the Extract, Load, Transform (ELT) process.
The Framework can be rapidly set up in an Azure environment which will allow you to ingest data into the DW with ease to quickly deliver business insight. The framework is a growing entity, it has been successfully implemented on multiple projects and continues to evolve and mature through each implementation.
Having successfully implemented the ADF Framework meant we were able to deliver data into Azure at a much faster rate, we reduced the overall development effort of ingesting data by 60-70%. This meant we were able to focus more effort on providing the right dimensional model for self-service and thus delivering Business Insights with increased agility.
The reconciliation framework allowed greater visibility to the business and IT, delivering more confidence in the accuracy of our model.
We were able to deliver insight at a much faster rate using a combination of prebuilt reports and dashboards, and Azure Analysis Service for self-service; this also allowed feedback to be incorporated at an earlier stage of the project.
We will go through each framework more in detail in the sections below.
Data Warehouse Architecture
The Architecture below shows how our Framework integrates with the Azure technology stack that aligns closely to Microsoft recommended architecture.
Below are links to Microsoft recommended architecture.
https://docs.microsoft.com/en-us/azure/architecture/reference-architectures/data/enterprise-bi-adf
https://azure.microsoft.com/en-au/solutions/architecture/modern-data-warehouse/
Azure Data Factory (ADF) Metadata Driven Framework
We have created templates in ADF for extracting data from Source to Landing, Staging and Presentation (Dimensions and Facts) layers. This has been integrated with end-to-end auditing that is a key part of our ELT process; allowing us to track the lifecycle of each record from the moment it is extracted to the Dimensions or Facts.
Below are the key advantages of this framework:
- Minimum setup time
- Speedup development time to extract data to Land, Stage, and Blob
- Automated code – reduce the effort of creating ELT code for each object
- Highly configurable and repeatable process – can be used for various source systems both on-prem and cloud (SQL Server, Oracle, JSON, APIs, OneDrive, SharePoint etc.)
- Restart-ability – Data loads will resume on failure using Auditing
- Run extracts in parallel
- Allow for Source filters and Watermarks on data extracts to enable delta or window loads
- Ability to disable or enable individual process
- Reduce the overall objects in ADF – less objects are easy to maintain and update when required
Spending less effort in Extracting, Loading and Transforming (ELT) data allows more time to deliver a Dimensional Model that meets business requirements and supports self-service.
Reconciliation and Automated Testing Framework
The framework was built using SQL Server and has been integrated with ADF to allow the test cases to be orchestrated as part of the ELT process. We have created PBI reports to enable daily monitoring and provide visibility of the Data Warehouse to IT and Business.
Automated testing is a difficult topic for a Data Warehouse project, as each test case can be unique to the business transformation. The solution we have created involves automating all the generic test cases and then allowing the development team to create new test cases to match business transformation. This provides a flexible architecture that can grow as more data is loaded and transformed into the Data Warehouse. The test cases are housed within a simple structure that is re-usable and easy to maintain.
Below are some examples of automated reconciliation and test cases:
- Track growth of -1 and -2 Surrogate Keys
- Attribute mismatch count
- Business keys reconciliation
- Contiguous records fail count
- Measure reconciliation
- Missing records count
- Deleted records count (count of logically deleted records from source)
- Database size growth
WIIFM – What’s in it for me?
There isn’t a single solution fit for every business, we are able to customise our Azure Framework to suit your environment, enabling a quicker turnaround to business for their data needs; you will spend more time creating insights for business and less time getting the data in the Data Warehouse.
Please see our Azure Practice page or contact us if you would like to know more about our Azure offering.
Below are links to more of our Azure Blogs: Using SSIS within Azure Data Factory V2
