Metadata saves the day – and weeks of costly error correction

The Situation

A large securities trading and investment firm published daily figures to its agents and customers on its member website.  The information was derived from the previous day’s business and was produced as reports from the corporate data warehouse.  The agents and customers viewed it as a very useful, value-added function from the investment firm.

The Problem

One of the business managers was looking at the business intelligence reports and noticed that some of the numbers didn’t look right.  Upon checking, the manager found several errors just on one report and realized that the integrity of the published information was compromised.  Management lost all faith in the transformation logic used to create the warehouse reports.  They shut down production of the data warehouse and demanded that IT immediately produce the code responsible for all the derivations of the erroneous information.  The data was sourced from multiple systems of entry, extracted and transformed and loaded into the data warehouse by hundreds of complex programs that were written by an army of consultants using a vendor-supplied ETL tool.  It was critical to the business that the website functionality be restored “yesterday”, but the information had to be right.

MV360 Solution

Metaview360 integrated the ETL tool to the company’s enterprise metadata repository.  As a result the repository contained a rich store of ETL metadata – data about the source systems, data transformation algorithms, and data locations in the data warehouse.  Using those metadata relationships and the repository reporting features we created a data lineage report that tracked the erroneous columns back through the transformations to the source data.  Together business and IT analysts were able to quickly identify the faulty calculation.  Once it was identified we next created a “where-used” report to identify the exact places in any of the ETL programs where the incorrect calculation was used.  With the correct calculation in hand, and the where-used impact analysis report, IT was able to quickly correct and regenerate the ETL programs and re-run the production which produced corrected results in the data warehouse.

The Result

IT had been ready to launch 25 ETL consultants working overtime to find the algorithm that was causing the problem.  Instead, by partnering with the repository administrator, the identification of the problem literally happened overnight (14 hours). In addition the metadata showed flaws in the ETL job promotion process that IT did not know existed.  The data warehouse and website information were up again, delivering correct information, in record time.  Business Management forged a much closer and more respected relationship with IT as a result of the impressive turnaround.  IT management truly experienced the value of detailed metadata capture of their ETL environment.  As a result they made it a highest priority to ensure that all of the development groups incorporated ETL metadata management into their development cycles.