To build a successful data warehouse, data warehouse design is the key technique. To save the time and cost , it is must to choose right data warehouse design.In this post we will discuss about the approach we can take to build data warehouse. Two type of data warehouse design approaches are very popular.
- Bottom Up Design
- Top Down Design
1. Bottom Up Design :
Often called as Kimball’s bottom up approach, the most important business aspects or departments, data marts are created first.
A data mart provide a thin view into the organisational data and addresses a single business area. These data marts are then integrated into larger data warehouse to build a complete data warehouse. The integration of data marts is implemented using Kimball’s data warehousing architecture which is also known as data warehouse bus (BUS). In this architecture, a dimension is shared between facts in two or more data marts.
Advantages Of Bottom Up Design :
- This model contains consistent data marts and these data marts can be delivered quickly.
- The data marts are created first to provide reporting capability
- It is easier to extend the data warehouse as it can easily accommodate new business units. It is just creating new data marts and then integrating with other data marts.
- This Approach take less time. Initial set up is very quickly
Disadvantage of Bottom Up Design :
- Initial cost is low but each subsequent phase will cost same
- The positions of the data warehouse and the data marts are reversed in the bottom-up approach design.
- It is difficult to maintain and often redundant and subject to revisions
2. Top Down Design :
Often known as Inmon’s Top Down Approach , data warehouse is built first. Inmon defines a data warehouse as a centralized repository for the entire enterprise.Dimensional data marts are created only after the complete data warehouse has been created. Thus, the data warehouse is at the center of the corporate information factory (CIF), which provides a logical framework for delivering business intelligence.
Advantages Of Top Down Design :
- It is easier to maintain Top Down Design
- Provides consistent dimensional views of data across data marts, as all data marts are loaded from the data warehouse.
- This approach is robust against business changes. Creating a new data mart from the data warehouse is very easy.
- Initial cost is high but subsequent project development cost is lower
Disadvantages Of Top Down Design :
- It represents a very large project and the cost of implementing the project is significant.
- It is time consuming and more time required for initial set up
- Highly skilled people required for set up
So next time when you are planning to build a data warehouse for your organisation, think about which design approach you need to take.Thanks for reading.