Data Warehousing & Business Intelligence
Data Warehousing and Business Intelligence
This domain tests the warehouse as a governed, integrated analytical environment: architecture, dimensional design, load patterns, and how BI delivers trusted information. Expect questions on schemas, slowly changing dimensions, fact table types, and the boundary between warehouse, mart, lake, and ODS.
Core rules and concepts
- A data warehouse is subject-oriented, integrated, time-variant, and non-volatile, built to support analysis rather than transaction processing. An ODS holds near-real-time integrated operational data; a data mart is a subset for a department; a data lake stores raw and varied data at scale and needs governance to avoid becoming a swamp.
- Layered architecture: staging for extract and land, integration for conform, cleanse, and historize, presentation for dimensional marts and the semantic layer, plus metadata, quality, and operations across all layers.
- Dimensional design: choose the business process, declare the grain, identify dimensions, identify facts. Fact types: transactional (one row per event), periodic snapshot (one row per period), accumulating snapshot (one row per process instance with milestone dates).
- Slowly changing dimensions: Type 1 overwrites and loses history; Type 2 adds a new row with a new surrogate key, effective dates, and current flag; Type 3 keeps a previous-value column. Type 2 preserves history for facts that reference the old key.
- Conformed dimensions use the same keys and attribute definitions across marts; the bus matrix maps business processes to conformed dimensions.
- BI layers: data sources, warehouse, semantic model, and presentation. OLAP variants are ROLAP, MOLAP, and HOLAP; self-service BI still depends on governed semantic definitions.
Worked example
A customer moves from Leeds to Bristol on 1 July. Sales facts before that date must still report Leeds. Under SCD Type 2, the dimension row for the customer is closed with an end date of 30 June and current flag N, and a new row opens on 1 July with a new surrogate key and Bristol. The June fact row keeps the old key, so a report by region attributes June revenue to Leeds and July revenue to Bristol. A Type 1 overwrite would move all history to Bristol and misstate regional performance. If the business only needs the current address for mailing, Type 1 is acceptable; the requirement, not preference, drives the choice.
Traps that cost marks
- Declaring grain after designing dimensions, causing duplicated or double-counted measures.
- Assuming Type 2 is always right; overwriting is valid when history is not required.
- Confusing conformed dimensions with identical tables; conformance is about shared keys and definitions.
- Treating a data lake as a warehouse substitute without schema, quality, and catalog controls.
- Building BI directly on operational systems and calling it a warehouse.