The Cobras of BI or the Excel spread mart: Within each organization, groups of analysts spend endless hours of manual labor producing reports in Excel. A large part of this manual effort is expended in gathering, massaging, standardizing, grouping, segmenting and loading data from a myriad of systems into Excel. Let’s call this first part data consolidation. The remaining effort is spent in creating calculations (several layers at times), Pivot tables, charts and graphs to present and make this data consumable by end users. In this process of producing reports, analysts also conduct analysis, albeit limited but crucial to producing insights for decision making. Let’s call this second part freedom of analysis and insights.
In this two-step process of creating Excel spread marts, analysts are challenged to maintain accuracy, consistency, scalability and flexibility. Traditional Excel is limited in functionality but nonetheless affords freedom, i.e. analysts have flexibility to produce reports in formats they deem best suited for the consumer of analysis and insights. Analysts also have the ability to create new types of analyses, including calculations, filters, sets and groupings that are necessary to “reason with data,” i.e. improve understanding of business issues and opportunities. This freedom is absolutely critical to questioning status quo and identify changes necessary for better outcomes – the core objective of BI.
The potential for errors and lack of manageability i.e. problems of manual data consolidation outweigh the benefits of freedom. Senior leadership certainly appreciates reasoning with data but they also recognize the risk of making decisions based on potentially erroneous data. When the logic for metrics delivered via traditional Excel spread marts is questioned, the scale of the problem is truly realized. A daisy chain of logic implemented as SQL queries, scripts in folders, network of temporary files, Excel macros and finally complex Excel formulas are utilized. In other words there is little or no governance, traceability and resilience in this traditional Excel spread mart implementation.
The traditional Excel BI is the nuisance from an IT and governance perspective i.e. Cobras of BI
The EDW or “Single version of Truth” incentive: To counter proliferation of spread marts (i.e. cobras of BI), organizations embark on a long and arduous journey of building a monolithic collection of data marts, The Enterprise Data Warehouse (EDW). This EDW journey is typically led by IT, often known as the “Single Version of Truth.” The EDW will be built over time with strong participation by business leaders, typically one data mart at a time. The single version of truth promises to include all possible calculations in addition to automating data movement and transformation from a variety of source systems. Now, traditionally IT is very good at building automation of systems coupled with robust processes for development and management of this automation. It’s only natural to believe that an automated single version of the truth is a great solution; as it reduces errors, centralizes business logic and calculations, and manual labor via automation.
Yes, for the most part it is, and all organizations should have a focused initiative to create and maintain a single version of truth but it’s certainly falls short in providing the same level of freedom to create new analyses and insights. To counter this limitation, IT elects to invest in a BI tool that allows for flexible, fast and accurate reporting which utilizes the “single version of the truth” accompanied by a promise to include additional calculations desired by analysts down the road.
In return for the single version, analysts have to give up and agree to sunset spread marts (cobras of BI).
At this point we must really question hard if this is the only approach and if IT will deliver on the core objective of BI, i.e. enable freedom of analysis and insights.
In my next post we will walk through the journey of creating and rolling out this single version of the truth. Will this strategy prevail over the Cobras?
Until next week, please feel free to reach out in the comments, offer feedback or share your experiences, successes and challenges of your BI journey.