Our team has enjoyed very productive careers over the years reviving Data Warehouse (EDW) and overall Business Intelligence (BI) solutions. We termed it second surgery in line with the medical practice of redoing previous surgical procedures. Like the surgery, the Data Warehouse failed to deliver the needed outcomes.
Without exception, there has always been two root causes for the need to redo EDW/BI solutions:
1. The Business did not trust the data and therefore would not use the solution.
2. The EDW was built with teams who did not fundamentally understand the underlying data. This resulted in incorrect queries, inflexible design, and continual failure to deliver.
These statements are nothing new but lay the groundwork for the rest of the story. While automating the SEC disclosures for a $200B bank we observed the most peculiar behavior. We had successfully generated the templates needed for financial reporting directly from the EDW. The EDW was built with SMEs in finance so the data structures were very sound. Yet Accounting was producing spreadsheet after spreadsheet to go along with the automated report. They called the End-User Computing (EUC) applications. So being a curious creature, I asked why they were producing a spreadsheet that mirrored the automated report. The answer was both simple and very eye-opening. Automation where the SEC template was produced at the click of a button had removed all their workpapers needed to prove to management and audit that the template was accurate. The paradox was that automating a template to eliminate a EUC had in fact generated the need for a different type of EUC, the manual data reconciliation control.
In my early career days, I was moved from a Data Architect role responsible for defining international customer masters in the role of Supervisor Exchange Accounting. Quite the career jump. In this new role, I was tasked to oversee the reconciliation of billions of dollars of refined product movement between oil companies. Teams of staff would manually reconcile piles of paper generated from our computer against piles of paper generated from a trading partner. The solution was obvious. Let the computers “talk” to each other to automate the reconciliations and let the staff deal only with the variances and use the extra time for more value-added work. This was my first entrance into automated controls.
Several years later I was faced with the paradox above of creating EUC to prove an automated report was valid. Again, the need for automated controls was very clear. We all know the basic premise that if A=B and B=C, then A=C. Very simple. Additionally, it became clear that only select few in the company were aware of data errors. We have spent countless hours helping clients write audit and regulatory responses on how they were going to prevent recurrences where numbers did not reconcile, or 75% of the company was using the wrong data.