What's changed: Deepened DP-600 Chapter 2 (ja figures; comparison tables/scenarios/FAQ/traps/deep paragraphs in all sections)
2.1Dimensional Modeling and Star Schema
Understand analysis-friendly data design—star schema (facts and dimensions), Slowly Changing Dimensions (SCD), and surrogate keys—key preparation that drives serving (semantic model) quality.
For analytics/BI, the star schema—separating data into facts (numeric measures) and dimensions (perspectives)—is the standard. It’s the foundation for serving.
2.1.1Star schema structure
- Fact table: holds numeric measures (sales, quantity) and foreign keys to dimensions.
- Dimension tables: hold analysis perspectives (date, product, customer) to slice aggregations.
- Surrogate keys: system-generated keys on dimensions, stabilizing joins and history.
- SCD: design to retain attribute history (e.g., Type 2) over time.
Common on DP-600: facts = numbers, dimensions = perspectives = star schema, retain history = SCD (Type 2), stable joins = surrogate keys, denormalized star over normalized for analytics/BI. Star schema performs best in Power BI semantic models.
A date dimension (calendar table) is essential for time intelligence (e.g., YoY) and is included in most models.
For analytics/BI, a denormalized star schema is faster and easier than full normalization (3NF) and performs best in Power BI semantic models. For a fact, first decide the grain (what one row represents), then hold measures (additive/semi-additive/non-additive) and foreign keys. Dimensions are analysis perspectives (date, product, customer) with a surrogate key (system-generated integer) as primary key, separated from the business (natural) key to stabilize joins and history. Attribute changes are handled by SCD: Type 1 (overwrite, no history), Type 2 (add rows with validity dates/current flag to keep history), Type 3 (keep prior value in a column). A snowflake (further-normalized dimensions) adds joins and often hurts BI performance, so prefer star. Use bridge tables or multiple facts for many-to-many or differing grains. A date dimension with contiguous dates, year/quarter/month, and fiscal year underpins time intelligence (e.g., YoY). Good schema design keeps downstream DAX simple and reports fast.
| SCD type | Behavior | History |
|---|---|---|
| Type 1 | Overwrite the attribute | Not kept |
| Type 2 | Add rows (validity/current flag) | Fully kept |
| Type 3 | Keep prior value in a column | Limited (prior only) |
Scenario: even when a customer’s address changes, analyze past sales by the region at that time. → Make the customer dimension SCD Type 2: on an address change, add a new row (new surrogate key) with validity dates/current flag to keep history. Facts reference the surrogate key as of the event, so the past aggregates correctly by the then-current attributes.
FAQ: Q. Why surrogate keys? → A. To stabilize joins and history (SCD Type 2 allows multiple rows per business key) independent of business-key changes/duplicates/source differences. Q. Star or snowflake? → A. For BI/Power BI, prefer star (denormalized); snowflake adds joins and often hurts performance.
Trap: “fully normalized (3NF) is best for analytics/BI” is wrong—BI favors a denormalized star schema. Also “use SCD Type 1 to keep history” is wrong—Type 1 overwrites (no history); history retention is Type 2.
2.1.2Section summary
- Star schema = fact + dimensions (joined by surrogate keys)
- History = SCD, time intelligence = date dimension
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. Which data design—separating facts and dimensions—suits analytics/BI?
Q2. What is the technique to retain dimension attribute changes as history called?
Q3. What does a fact table mainly hold?