Why Every Data Integration Leader Needs an ETL Dashboard

ETL Dashboard

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 IT Help 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 figures may not be available because of the failure of the ETL job that loads revenue data. 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 Warehousing/Data Integration leaders manage their departments more efficiently by providing them the ability 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 a time period selector and trending to help leaders understand how the ETL loads are performing over time.

Data Integration/ETL Dashboard

ETL Monitoring Dashboard

HEX color codes used: #488A99, #FDFAF5, #D32D41, #4A4A4A, #3B5795

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 are failing. The next 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 increases 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 into the system as part of the data loading 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 by using a Business Intelligence tool like MicroStrategy or Tableau.

ETL Dashboard Architecture

In a MicroStrategy environment, the Data Integration/ETL Dashboard above would serve as the landing page, created using a Report Services Document, providing a bird’s-eye view of the ETL loads. The detail/data discovery dashboard which allows the ability to drill-down and filter the data via selectors would be achieved by creating a link in the landing page to a MicroStrategy Dossier.