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?
Keep track of your progress
The full study guide is free to read. Sign up free to practice with the question bank, track what you have read, review your mistakes, and highlight passages.