Addressing the Elephant in the Room
The Art of Lean Governance: Resolving the Elephant in the Room: Data Risks in Spreadsheets
Hands down one of the most frequent observations when walking the data factory at different clients is the excessive use of spreadsheets for data collection and purification. These spreadsheets are used as part of a data enrichment process that are critical to getting reports out the door on time or other business processes.
Yet most people do not want to discuss these spreadsheets in any great detail. Often, they fly below the compliance and control radar. In reality what is yet one more spreadsheet? However in the world of data risk management they represent a significant control problem exposing the company to all the problems inherent with inaccurate data.
In the world of Lean Governance, we must acknowledge the use of spreadsheets for data enrichment as the elephant in the room, to use in the old expression. Few want to talk about them. One reason is there is a reluctance to acknowledge data gaps after spending millions on a new transactional system or data warehouse projects. We stopped counting the number of times we heard, “the new system was supposed to eliminate the need for a spreadsheet”. This is reality in the world of system project management.
What is needed is a mechanism for controlled data enrichment outside of a company’s transactional systems. In fairness to the people (and spreadsheets) involved, it simply is not practical to provide an automated or transactional source for all data given the complexity of business processes and vast availability of data. In most cases we are talking about the missing data sources for 5-10% of a company’s data, certainly not the majority.
One way technologists attempt to deal with this elephant is with master data management systems (MDM). MDM is a very powerful tool and can make advances in overall governance assuming the integration is seamless in the overall data factory of the organization. However most of our observations indicate well-designed MDM solutions to tackle a specific data problem such as customer or product masters. But the holistic integration with other systems or warehouses is ineffective, leading to stove-piped data flows and ultimately an increase in the use of spreadsheets. The cure is often worse than the problem.
In this column we are proposing an alternate solution that is very much in line with our goal of Lean Governance. Namely to transform raw data assets into finished inventory (reports, models) with the least amount of time, effort, resources and risks. In prior columns we have written about the fundamental need for an integrated data governance framework. A framework that is possible to completely define the relationship between a business and its data as a major metadata puzzle. A puzzle that is solved one piece at a time, and in the end results in an overall Awareness of the governance parameters across the business and data factories.
Our definition of this integrated governance framework encompasses the complete definition of enterprise governance including organizational structure, information, data assets, business glossaries, business process, security, data loss prevention, and data quality controls. This list continues, but a comprehensive definition of this integration is not the intent of this particular column. What is important here is to consider adding a very simple yet powerful technology and process component to this framework stack. Namely some sort of data enrichment module built around the core governance metadata that defines a company.
The fact that data needs to be enriched or supplemented, and that this is done outside of production systems, is given. Anyone who disputes these needs only to walk their data factory with their eyes wide open and willingness to acknowledge the control gaps that exist. MS Access and MS Excel remain two of the most widely used data processing tools in the industry. Data enrichment sometimes involves very complex user-developed applications. So much that companies have annual goals (often not met) to eliminate a specific number of end-user computing (EUC) or user-defined applications (UDA) that designed for data enrichment, collection or processing.
A data enrichment facility incorporated into an overall governance framework needs to have key characteristics. First and foremost the overall process needs to be deployed in the context of required SOX or other controls. If the overall implementation cannot be designed to be under production control, it will be of little use. Locking away a spreadsheet on a secured drive does little to minimize overall data risk given the reality of the chain of control over the file. In our work we have eliminated countless spreadsheets for clients through the use of our data enrichment approach. We drive to improve production control and not weaken it.
Another key point is that data enrichment needs to be a business tool. Not a technology mechanism. Yes, there are times when IT needs to enrich data across the technical data factory such as domain codes, tags, or other taxonomy groupings. But this is the exception, not the norm. We have taken great delight eliminating countless spreadsheets through the use of tactical data enrichment and enabled clients to certify business processes under their control frameworks. These efforts support the messaging of lean and decrease operational data risk.
The last area we will cover here is the concept of auditability and access control. In certain terms, who changed what and when? What were the previous values? Which business user has access to alter a certain data domain? We talk about lineage and the importance of retaining lineage across the entire data factory. Sorry to say, but lineage usually stops with a spreadsheet. A data enrichment facility needs to be considered a production data source similar loading a transactional system to data warehouse.
It is time to openly discuss the elephant in the room. I happen to be a huge fan of elephants and have a deep respect for them. So when you are walking the data factory don’t bypass the spreadsheets used for data enrichment. Bring them out into the light (and full view of compliance and controls). Get with your governance technology team and ask them how they can incorporate data enrichment into your governance framework. If they look at you as my old beagle did with her head cocked sideways, perhaps it is time for a new technical approach to data and information governance.
