ETL -- 7. Basic Design Approaches for a Data Warehouse

Data Warehousing Design Approaches

There are two standard design approaches for designing a Data Warehouse

Top-Down Approach or Bill Inmon Approach

In the top down approach, data from the source is extracted transformed and loaded to a Data Warehouse.
The data available in the DW is then loaded to smaller subsets of the DW called data marts

Bottom-up Approach or Ralph Kimball Approach

Bottom up approach:
This is also called by Ralph Kimball as the bus structure. He designed the DW with data marts connected to it with a bus structure
The bus structure contains all common elements that are used by data marts (conformed dimensions, measures etc.)
This architecture makes the DW more of Virtual reality than physical reality

• Data Flow in the bottom up approach starts with the extraction of data from operational databases into staging areas where it is transformed and loaded to ODS.
• From ODS the data is transformed to fit the structure of Data Marts and is then loaded to Data marts
• Data from Data Marts is loaded to staging area, aggregated/summarized and then loaded to the Data Warehouse for analysis by end users