Building a Data Warehouse
Building a warehouse is a business project first and a technical project second. It follows a life cycle, and there are two classic strategies: top-down (Inmon) and bottom-up (Kimball).
Expect "list and explain the steps of warehouse development" (10 marks) and "compare Inmon vs Kimball" (5-7 marks). Also remember that ETL takes the largest share of effort.
1. Warehouse Development Life Cycle
Typical effort split:
2. Two Building Approaches
| Aspect | Top-down (Inmon) | Bottom-up (Kimball) |
|---|---|---|
| First deliverable | Central normalized EDW | One dimensional data mart |
| Data model | 3NF (normalized) | Dimensional (star) |
| Time to first result | Long (years) | Short (months) |
| Cost and risk | High | Low, incremental |
| Consistency | Very high | Needs conformed dimensions and a bus matrix |
| Best for | Large enterprise, stable requirements | Fast-moving business, small budget |
3. Key Design Decisions
| Decision | Options | Comment |
|---|---|---|
| Grain | Transaction, daily snapshot, monthly snapshot | Declare the grain first, then choose dimensions |
| Schema | Star, snowflake, constellation | Star for speed, snowflake for space |
| History | Full history or current only | Use SCD type 2 to preserve history |
| Refresh | Daily batch, micro-batch, near real time | Balance freshness against load on sources |
| Aggregates | Pre-computed summary tables | Speed up repeated queries |
4. Success Factors and Risks
| Success factors | Risks |
|---|---|
| Strong executive sponsor | Scope creep |
| Clear business questions | No data ownership |
| Good source data quality | Unrealistic expectations |
| Incremental delivery | Ignoring metadata |
| User involvement | Underestimating ETL complexity |
- List and explain the steps of data warehouse development (10 marks, frequent).
- Compare Inmon and Kimball approaches with a diagram (5-7 marks).
- Why does ETL consume the largest effort? (short note)
Frequently Asked Questions
QWhat is the difference between the top-down and bottom-up approach?
Top-down (Inmon) builds a central normalized enterprise warehouse first and derives data marts from it. Bottom-up (Kimball) builds individual dimensional data marts first and integrates them through conformed dimensions.
QWhat is meant by the grain of a fact table?
The level of detail one fact row represents, for example "one product sold in one store on one day". It must be declared before choosing dimensions.
QWhat are conformed dimensions?
Dimensions with identical meaning and keys that are shared by several data marts, so results from different marts can be combined consistently.
QWhich phase takes the most effort and why?
ETL (about 60 to 70 percent) because of heterogeneous sources, data quality problems and complex mapping and testing.
QName some risks of a warehouse project.
Scope creep, poor data quality, no data ownership, unrealistic expectations and underestimated ETL work.