2.3.4. Shaping Fact Tables, Dimension Tables, and Keys

💡 First Principle: Sources rarely arrive as a star schema, so you build one. The recipe is consistent: pull each set of descriptive attributes out into its own dimension with one row per member and a unique key, keep the measurable events in a fact table, and connect them with keys that match exactly.

The standard pattern for splitting a flat table (say, a sales export with order, product, and customer columns on every row):

1. Keep the flat table as a staging query with load disabled (2.3.6).
2. Reference it to create each dimension query (Product, Customer): keep only that entity's columns, clean them, and Remove Duplicates on the key so each member appears once.
3. If the natural key is long text, composite, or unreliable, add an Index Column as a surrogate key.
4. Reference the staging query again for the fact: merge with each dimension to bring the surrogate key across, then remove the descriptive columns so only keys and numeric measures remain.

Keys are where relationships succeed or fail, and the exam tests the rules directly:

• The key on the one side must be unique and should be non-blank. Duplicates make the relationship one-to-many impossible (or force many-to-many); blanks cause ambiguous matches.
• Both key columns must have the same data type. A Text "1001" won't match a Whole Number 1001.
• When no single column is unique, build a composite key by merging columns (StoreID + ProductID) in both tables with Merge Columns, because Power BI relationships use a single column on each side.
• Clean keys before relating: trim spaces, standardize case, and check unique counts in column distribution (2.2.1).

Choose natural keys (business codes from the source) when they're stable and unique; choose surrogate keys when combining sources with overlapping codes, when keys are composite, or when compact integers improve compression.

Denormalize dimensions as you build them: merge Subcategory and Category text into the Product dimension rather than keeping a snowflake (1.2.2).

⚠️ Exam Trap: Power BI relationships can't use two columns at once. When a stem says rows are uniquely identified only by Store plus Product, the answer is creating a combined key column in both tables (Merge Columns or a custom column) and relating on it, not creating two relationships between the same tables.

Reflection Question: Two source systems both number customers from 1 upward, and you must combine them into one Customer dimension. Why does a surrogate key solve a problem that natural keys can't, and at what step should it be created?