[DWH] Datawarehouse Design

Datawarehouse Design  

  

There are two major approaches to data warehouse design.

1. Bottom-up approach 

• This approach is recommended by Kimball.

• In the bottom-up approach data marts are first created to provide reporting and analytical capabilities for specific business processes.

• Data marts contain, primarily, dimensions and facts. Facts can contain either atomic data and, if necessary, summarized data. The single data mart often models a specific business area such as Sales or Production.

• These data marts can eventually be integrated to create a comprehensive data warehouse.

• The integration of the data marts in the data warehouse is centered on the conformed dimensions.

• The actual integration of two or more data marts is then done by a process known as "Drill across". A drill-across works by grouping (summarizing) the data along the keys of the (shared) conformed dimensions of each fact participating in the "drill across" followed by a join on the keys of these grouped (summarized) facts.

• Some consider it an advantage of the Kimball method, that the data warehouse ends up being "segmented" into a number of logically self contained and consistent data marts, rather than a big and often complex centralized model.

• Business value can be returned as quickly as the first data mart is built.

2. Top-down approach

• This approach is recommended by Bill Inmon.

• Inmon is one of the leading proponents of the top-down approach to data warehouse design, in which the data warehouse is designed using a normalized enterprise data model.

• In the Inmon vision the data warehouse is at the center of the "Corporate Information Factory" (CIF), which provides a logical framework for delivering business intelligence (BI) and business management capabilities.

• The top-down design methodology generates highly consistent dimensional views of data across data marts since all data marts are loaded from the centralized repository.

• Generating new dimensional data marts against the data stored in the data warehouse is a relatively simple task.

• The main disadvantage to the top-down methodology is that it represents a very large project with a very broad scope, cost and time.

• In addition, the top-down methodology can be inflexible and unresponsive to changing departmental needs during the implementation phases.