Companies build Data Warehouses and Data Marts to answer business questions that cannot be answered with operational systems. Strategic versus Tactical questions call for different data to be captured but here are ten Guiding Principles to take into account when planning your strategy:
- Data Warehouses / Marts (DW/DM) reflect data over time.
– Operational systems reflect a point in time (ODS – operational data stores)
– The amount of history in the data warehouse/mart is determined
by business requirements from either analytic or legislative drivers.
- Every time the data is "handled" (moved, copied or transformed) it adds to the complexity, cost and accuracy of the DW/DM.
- Can’t buy a shrink-wrapped DW/DM solution
- Cleanse data at the source (system of record)
- Gather source data from the system of record
- Generally accepted flow is
– Source -> ETL -> DW -> Analytics; or
– Source -> ETL -> DW -> ETL -> DM -> Analytics
- Place sourced data into third normal form in the data warehouse
- Populate data marts from the data warehouse or System of Record
- OLTP and OLAP are different and each serves a specific purpose*
- DW/DM are an investment in support of Business Intelligence – average 3 yr TCO around $12 million **
* OLTP – On Line Transaction Protocol
* OLAP – On Line Analytic Processing (multi-dimensional analytics and reporting or data mining)
** estimated by IBM