Data Quality is a constant need for organisations, especially those with extensive regulatory reporting responsibilities. For our client (a utilities provider in NSW) One51 was asked to develop a report to detect and report on current data quality exceptions. The report targeted Data Stewards, and those responsible for regulatory reporting. Additionally, any identified data quality issues will be managed and actioned by this group.
One51’s reporting solution effectively streamlined the team’s operations by eliminating the need for manual issue identification, responsibility tracking, issue resolution, and verification of successful resolutions. This saved the team valuable time and resources. Impressed by the time-saving achievements among the data stewards, Senior Management decided to elevate data quality within the system. They opted to do this by enhancing and refining the data entry process, offering additional training, and extending access to the reporting system for the source system users responsible for data entry.
With the investments made into these data quality initiatives (the extra training for data entry & increased visibility on data quality), Senior Management needed to understand further the impact these strategies had on Data Quality over time.
Inspired by all the incredible new features of Microsoft Fabric, One51 Consultants (with the support of the Client Senior Manager) utilised Microsoft Fabric components to take a snapshot of the operational system daily, record the exception counts, write back the results to a Warehouse and create trending Reports from the Warehouse tables. This has now allowed improvements in Data Quality to be tracked over time, day to day, at a granular level.
The solution created (shown above) involved using a gateway to transfer data from on-premises via a gen2 dataflow. A Fabric Data Pipeline orchestrates the process.
- Data Quality exceptions are landed into a Fabric Dataflow, which is in turn used to produce the current Data Quality Report.
- Data is copied into a Fabric Warehouse, wherein a stored procedure copies daily data into a historical table as a snapshot view.
- The Power BI Report (in DirectQuery mode) will always show the latest values using the current and historical data.
- The Trend Report patterns can be analysed in the trend report for upward or downward change.
Further inspired by the release of Public Preview for Data Activator, One51 also created a few reflexes to alert team leaders and data stewards when there were spikes in Data Quality issues to be investigated or a shift in trend either positive or negative.
The outcome for the client is a simple, low-code, configurable data workflow to capture point-in-time metrics and be able to carry out trend analysis over time.