Data Warehouse Design Approaches

Stability of a data store is a key behind successful data centric application. Typically there are four different approaches used for designing a data warehouse.

They are:

• Dimensional modelling
• Material views,
• Bottom-up
• Top-down.

Dimension modelling

In Dimension modelling technique, depending on the nature of the application we pick star model or snow-flake model. Star model is best suited for OLAP (Online Analytical Processing) analysis or reporting kind of applications where retrieval performance is of higher priority than storage performance while data redundancy takes low priority. Snow-flake model is best suited for OLTP (Online Transaction Processing) where data redundancy is of high priority as well as retrieval and storage performance are of equal priority.

Materialized Views

In materialized views approach which is best suitable for applications which address the problem selecting a set of views to materialize in a data warehouse (i) the space allocated for materialization, (ii) the ability of answering a set of queries using exclusively these views and (iii) the combined query evaluation and view maintenance cost.

Bottom-up

In bottom-up approach, various data marts are created individually to cater the reporting needs of the application and together they are integrated to build the complete data warehouse. The integration point between these data marts would be dimensions that are shared across these data marts and this type of architecture is called bus architecture. This approach is optimal for building business intelligence type of applications which allows phased implementation.

Advantages of bottom-up design are:
This model contains consistent data marts and these data marts can be delivered quickly.
As the data marts are created first, reports can be generated quickly.
The data warehouse can be extended easily to accommodate new business units. It is just creating new data marts and then integrating with other data marts.

Disadvantages of bottom-up design are:
The positions of the data warehouse and the data marts are reversed in the bottom-up approach design

Top Down

In top-down approach, the data warehouse is designed first and the data marts are then created from the data warehouse. This approach facilitates consistent dimensions across the data marts as they are built based on the same data warehouse. This approach is optimal for applications which allows big-bang implementation.