The Problem
It is 4 a.m. Tuesday. The ETL job that populates revenue data into your organization's data warehouse fails midway through the process, and the IT Service Desk contacts the on-call ETL Architect. For the next three hours or so, the on-call ETL Architect determines what caused the data-load failure, fixes the issue, and restarts/monitors the job until it successfully completes.
At 10 a.m., your organization's Executive team is holding their quarterly strategy meeting, and they’ll be using the Executive Dashboard developed by your department to review your organization’s revenue numbers. Unfortunately, the latest revenue figures will not be available because of the ETL job's failure. Last week, a similar event occurred, and your organization's reports and dashboards were unavailable until 3 p.m. – the prior month, three similar data load failures occurred, but you can’t quite explain why they happened.
The Solution
A Data Integration/ETL Dashboard like the one below can address this problem and help data leaders manage their departments more efficiently by allowing them to monitor data loads.
It provides a high-level overview of four Key Performance Indicators (Number of Data Loads, Number of Rows Written, Average Load Time, and the amount of Data Stored), along with a time period selector and trending to help leaders understand how the ETL loads are performing over time.
ETL Dashboard
Icons Used: Icons8
Each section of the Data Integration/ETL dashboard consists of a Key Performance Indicator and its trending to indicate growth. Starting with section 1, the number of Data Loads, their success rate to benchmark against an SLA (Service Level Agreement), and the number of Failed Data Loads to provide context into how many loads fail. The following two sections, Rows Written and Average Load Time are strategically placed next to each other because the number of rows written to a table will likely increase the load time. Since enterprise data storage is not cheap, whether in the cloud or on-premise, the last section of the Data Integration dashboard displays the amount of Data Stored in the system as part of the data-loading process.
Additional KPIs that can be added to the dashboard for further insights:
Late Jobs - Number or percentage of jobs completed outside the expected SLA window.
Data Drift - Difference between source and target data volumes/values.
Transformation Time - The time it takes for a data transformation to complete.
Average Transformation Time - The average time it takes for a data transformation to complete.
Load Time - The time it takes to load data into the data lake from the source system.
Load Frequency - The frequency of data loads per source or process.
Architecture of Data Integration/ETL Dashboard
The data needed to create the report or dataset to power the dashboard can be easily sourced from the log files generated by your Enterprise Data Integration tool(s) when the ETL jobs run. The interactive front-end dashboard can be developed using a Business Intelligence tool like MicroStrategy, Tableau, or Microsoft Power BI.
Over the years, we have developed dozens of ETL dashboards for organizations of all sizes using different BI tools. If you want to learn more about how we can revolutionize your data management strategies, please contact us for further insights and details.
