Data Warehousing and Business Intelligence
Data warehousing integrates subject-oriented, time-variant data for analysis; business intelligence turns it into governed reporting and decisions. The exam tests dimensional design, grain, history, conformed dimensions, semantic layers, and BI delivery.
Core rules
- Declare the fact grain first. A sales fact at order-line grain cannot be safely summed with a daily target fact.
- Separate facts (measures, foreign keys) from dimensions (context). Use star schemas for usability; snowflake only with a reason.
- Manage history with slowly changing dimensions: type 1 overwrite, type 2 version with effective dates, type 3 limited prior value.
- Conform dimensions across subject areas so metrics can be compared; use a bus matrix to plan.
- Publish a semantic layer with certified metric definitions, and govern report proliferation.
Worked example
A sales dashboard mixes order-line revenue with monthly sales targets. Revenue by region doubles because order lines multiply by product. The modeler rebuilds the fact at order-line grain, adds a separate target fact at region-month grain, and makes the semantic layer aggregate both to the common region-month level. The reasoning: grain mismatch, not the BI tool, caused the error; the fix is in the model and the metric definition.
Traps
- Mixing grains in one fact table.
- Non-conformed dimensions that make cross-domain comparison invalid.
- Defining the same KPI differently in each report.
- Ignoring late-arriving facts and dimension changes.