A data warehouse is not the only approach to extracting and presenting information from multiple business (source) systems. Below we compare the strengths of the data warehouse approach with three common alternatives.
Manual consolidation
Almost every business uses Excel or Access to combine and analyse information from source systems. This is usually carried out by a business analyst or a team of analysts and can be an expedient way of delivering a one-off analysis. However the Data Warehouse provides a number of benefits that manual consolidation can't provide:
Efficiency: the Data Warehouse automates the repetitive data consolidation tasks associated with manual consolidation. This leaves the analysts with time to do their real job, which is to analyse.
Reliability: the Data Warehouse is a "single version of the truth". Typically with manual consolidation, multiple versions of these Excel spreadsheets proliferate, often with conflicting data leading to a considerable risk of business decisions being made based on incorrect information.
Supportable: the Data Warehouse is a component in a BI system that has been developed as a partnership between the business and IT. This means that the knowledge to enhance and support the Data Warehouse is spread among multiple people. This overcomes the problem with manual consolidation where often only one employee actually understands the inner workings of the Excel spreadsheets that the business relies on for management reporting. Even worse, the employee has left and the spreadsheets are unable to be modified or maintained.
Data Consolidation "on the fly"
Two methodologies, Virtual Data Warehousing
(VDW) and Enterprise Information Integration
(EII), directly access data in the various
source systems and through an abstraction
mechanism, present the data as if it were from a
single source.
Superficially, this is attractive as it avoids
the effort of creating a data warehouse.
If you have only a single data source from which
you never delete data and it is capable of
supporting resource intensive queries during the
working day then you may well be right.
However in our experience this not a very common
situation and there are also additional benefits
that a data warehouse can deliver.
So let's look at what a "Real" (as opposed to virtual or "on the fly") data warehouse provides:
Business system performance: resource intensive reporting and analysis is performed in isolation from the source business systems. This allows the business systems to be managed and tuned for optimal operational performance unencumbered by the burden of running computationally heavy reports. There is an additional benefit in that should an operational system be unavailable, analysis is still able to be performed in the data warehouse.
Historical Data: a data warehouse is usually designed and built to never remove any of the data that is loaded. This gives the ability to perform analysis over large sets of historical data. Operational systems usually either delete or archive historical data to maintain system performance.
Clarity of information: a guiding principle for the design of a data warehouse is to optimise reporting, query and analysis. To this end, the data is stored in structures that reflect how the business sees the world. This enables ease of understanding and speed of analysis. In contrast the data in operational systems is stored in structures which are designed to support the application which runs on top and are almost invariably unintuitive from a business perspective.
Speed and reliability: the physical nature of a data warehouse allows for the creation of summary and aggregate structures. When combined with indexing strategies and database tuning this results in fast and predictable analysis. A virtual data warehouse is not able to implement any of these approaches and in conjunction with the need to re-query source data every time a report is viewed, results in slow and unpredictable performance.
Centralised Business Understanding: the data warehouse provides a single physical repository of the business rules applied to transform the data from each source system. A well designed warehouse will, in addition, keep a history of the business rules so that it is possible to compare the effect of changes in business rules.
Audit ability: the Extraction, Transformation and Load (ETL in the jargon) process that is used in a data warehouse provides an audit trail to show when and how a particular piece of data in the data warehouse was populated. This is of particular importance in meeting regulatory requirements associated with Sarbanes Oxley Act for US companies and equivalent regulations in other jurisdictions. In the virtual data warehouse there is no history kept so should a historical figure change between report runs it is impossible to determine how the original number was determined.
Analytical Applications (a.k.a. The "Hidden Data Warehouse")
Another common situation is where a component of a business purchases an application which provides pre-built reports, dashboards, scorecards or other forms of reporting and analysis. These are focused on a specific part of the business or target a particular software product. These are known in the trade as Analytical Applications and are seen as the answer to many a manager's prayers. They just pay for the software and a portion of time to "install and configure" and they get all the reports they can pay for.
So, why shouldn't every organisation just go out and buy the appropriate Analytical Application for each of its businesses, rather than build a data warehouse? Let's compare what a data warehouse provides that an Analytical Application doesn't.
Extensibility: a data warehouse is designed to have the capability to add new data sources, create new analyses and serve the needs of an organisation beyond a specific functional area. The Analytical Application is focused on the delivery of analysis for specific functions within an organisation or tied to a specific application.
Flexibility: a data warehouse is designed to reflect the understanding of the people within an organisation as to how that organisation functions. This approach means that the design is abstracted from the design of the source systems allowing source system changes to occur without impacting on the resulting analysis. A number of Analytical Applications are tightly tied to an underlying application which means that if the application is replaced the reporting needs to be replaced as well.
Openness: in a data warehouse the business rules associated with transformations are visible to the organisation. This results in clarity of organisational understanding of the meaning of figures and how they have been derived. In comparison, the Analytical Application is a "black box" which magically delivers reporting. Whatever transformations and business rules are applied, they are not exposed to the organisation let alone to the end user of a report.
- RH
