Prevalent Data Warehouse Development approaches

Posted: June 2006 C: Business Intelligence Posted by: K. Panayotakis

There are two prevalent approaches to the development of Datawarehouse Architectures:

Data Warehouse (DWH) bus architecture (introduced by Ralph Kimball)

According to this approach the DWH is developed in phases. Each phase includes the development of a set of dimensional models which are linked together via conformed dimensions, thus forming a virtual ‘bus architecture’.

Therefore, according to this approach, at the core of the DWH resides a denormalised dimensional data model, which handles data at the atomic level.

The major advantages of this approach are inherited from the use of the dimensional model combined with the ‘conformed dimensions’ principle. This model’s simple and symmetric structure is easily understood by Business Analysts (easier than complex normalized data models). Moreover the so called ‘star schema’ allows the efficient execution of queries (less relational joins). The ‘conformed dimensions’ principle allows for the gradual development of a Data Warehouse, in which all information is linked efficiently and analytics spanning different business processes or subject areas are feasible.

Each ‘star schema’ involves a fact table linked to a number of dimensions in a star.

Three fundamental types of fact tables: transaction, periodic snapshot, and accumulating snapshot have been defined.

Examples of high level conceptual models of star schemas: tax process monitoring data mart, tax audit process data mart, inpatient process data mart.

In order to define a DWH development roadmap, Kimball introduced the concept of the DWH bus matrix.

The ‘bible’ on this approach is: ‘ The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling’, John Wiley & Sons, 2002 Ralph Kimball and Margy Ross

Corporate Information Factory – CIF (introduced by Bill Inmon)

According to this approach, the first step involves the design of a comprehensive abstract data model for the Enterprise (a model mapping the way the Enterprise exploits information).

Based on this abstract model, the central DWH data model is developed following a normalized design approach (3NF), which handles data at the atomic level.

According to the Inmon approach, dimensional models embedding aggregated facts are built by querying this central atomic DWH data model and serve departmental needs (this is one of the major disagreements between the two thought leaders – read Kimball’s open letter to the data warehousing community).

However, it is not clear how the user can drill down, starting from aggregated star schemas. Moreover, the ability to efficiently perform ad-hoc complex querying on the normalised (3NF) DWH data model, is questionable. In addition, the ability to initially design a comprehensive abstract data model is also questionable, given that the business intelligence needs change frequently.

Both development approaches agree to the following points:

Phased-iterative implementation is the way to proceed, by prioritizing on business processes or subject areas.
The use of a separate staging area in which extraction-transformation-cleansing of source data takes places in order to be loaded to the DWH (known as ETL operations).
The power of information resides in the atomic data, which embed all available info dimensionality.