Top-down versus bottom-up design methodologies

Top-down design

Bill Inmon, one of the first authors on the subject of data warehousing and the man credited with coining the term "data warehouse", has defined a data warehouse as a centralized repository for the entire enterprise.^[3] 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. "Atomic" data, that is, data at the lowest level of detail, are stored in the data warehouse. Dimensional data marts containing data needed for specific business processes or specific departments are created from the data warehouse. 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 CIF is driven by data provided from business operations.^[4]

Inmon states that the data warehouse is:

Subject-oriented

The data in the data warehouse is organized so that all the data elements relating to the same real-world event or object are linked together.

Time-variant

The changes to the data in the data warehouse are tracked and recorded so that reports can be produced showing changes over time.

Non-volatile

Data in the data warehouse is never over-written or deleted - once committed, the data is static, read-only, and retained for future reporting.

Integrated

The data warehouse contains data from most or all of an organization's operational systems and this data is made consistent.

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. Top-down design has also proven to be robust against business changes. 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. The up-front cost for implementing a data warehouse using the top-down methodology is significant, and the duration of time from the start of project to the point that end users experience initial benefits can be substantial. In addition, the top-down methodology can be inflexible and unresponsive to changing departmental needs during the implementation phases.^[3]

Bottom-up design

Ralph Kimball, another well known author on data warehousing, defines a data warehouse as "a copy of transaction data specifically structured for query and analysis."^[5] Kimball is a proponent of the bottom-up approach to data warehouse design. In the bottom-up approach data marts are first created to provide reporting and analytical capabilities for specific business processes. Data marts contain atomic data and, if necessary, summarized data. These data marts can eventally be unioned together to create a comprehensive data warehouse. The combination of data marts is managed through the implementation of what Kimball calls "a data warehouse bus architecture".^[3]

The bottom-up approach to data warehouse design provides the advantage of a quick turnaround. Business value can be returned as quickly as the first data marts can be created. However, a long term risk of this approach is inconsistencies in the multiple data marts and the resulting multiple "version of the truths" seen by users retrieving data from the data marts.^[3] Conforming dimensions among data marts and maintaining tight management over the data warehouse bus architecture can help mitigate these risks.

Hybrid design

Over time it has become apparent to proponents of bottom-up and top-down data warehouse design that both methodologies have benefits and risks. Hybrid methodologies have evolved to take advantage of the the fast turn-around time of bottom-up design and the enterprise-wide data consistency of top-down design.