Case Study 1 - Data Quality

Program/Project overview:

Reduce defects in data loading from various transactional systems into the Executive Summary (Digital Cockpit) to provide better and accurate information. The Digital Cockpit was viewed by the CEO of a $14 billion business. The Cockpit showed data about orders, sales, finance, span etc as well as variance across time periods. The Cockpit was the single place for information for the top management to help devise business strategies. However, because of the complexities of the feeds and the various hops, there were usually large differences in actual data vs. the data shown on the cockpit leading to wrong decisions. Slowly, the veracity of the data was being questioned, leading to disillusionment among users and subsequent reduction of DW projects.

Size in FTE / $$:

12 FTEs (6 onsite, 6 offshore)

Scope:

About 150 feeds from various transaction systems including mainframes, Oracle ERP, Siebel, local data marts of sub businesses to be checked for data inaccuracies and reduction of such accuracies.

Challenges:

· Complexity of source systems

· Absence of business rules documentation

· No ETL tool – scripts to load data were written long back with no documentation

· Inadequate or unstructured logging – hence difficult to do root cause analysis

· Small monitoring team, basically meant that rejects were not checked up

· No alerting mechanism

Our Solutions/Value Propositions:

The EIS team member proposed a Six Sigma DMAIC approach to resolve the problems and ensure root cause fix. The DMAIC (Define, Measure, Analyze, Improve, and Control) process offers a structured measurable approach to analyze defects, find causes for it, do subsequent improvements and finally ensure that the improved processes are controlled.

Execution Strategies:

Voice of Customer was taken from various stakeholders to understand business pain. A core SWAT team was formed with experts from business as well as IT professionals. Code was evaluated and logging mechanics were improved. Data from logs were analyzed to find out common causes for errors and rejects during the data load process. A simple example was when a $2 million order was rejected because the date was sent from a mainframe system was inaccurate. Another reason was when a $1.5 million order was rejected because dimensional data was load AFTER fact data, leading to integrity issues. The errors were analyzed and solutions were applied at source rather than the target (DW) system. This was to ensure that data was accurate in BOTH places. Due diligence was also taken care to ensure that there was a constant review of error logs and this make sure that the improvements did not fall through the cracks.

An alerting mechanism was also set up to ensure that mails were sent to the monitoring team, in case of errors, to ensure faster follow up.

Benefits:

· The initial process was measured at 2.51 Sigma with a DPMO (Defects Per Million Opportunities) score of 132,270. The improved process was measured at 4.27 with a DPMO score of 2780.

· Improved productivity of the monitoring team

· Faster response to rejects because of alerting mechanism