Memory hook: Facts measure events; dimensions describe them.
Must remember
Define the grain of each fact table before choosing relationships. A sales-line table and a monthly budget table have different grain; blindly joining them can multiply values. Dimensions contain descriptive attributes with a unique key, while fact tables contain foreign keys and measures. A star schema makes filtering and aggregation easier to reason about.
Relationship cardinality describes uniqueness: one-to-many commonly links a dimension’s unique key to repeated fact keys. Cross-filter direction controls propagation. Prefer clear single-direction paths where they meet the need; bidirectional filtering can create ambiguity and surprising totals. Many-to-many designs require careful bridge/grain decisions, not just clicking a convenient setting.
Only one relationship between the same tables can be active for a given straightforward path; inactive relationships can be invoked by appropriate calculations such as USERELATIONSHIP. Role-playing dates such as order date and ship date can use separate dimension instances or carefully designed measures with inactive relationships.
Build a complete common date table with unique, contiguous dates over the needed range and configure it appropriately for time intelligence. Sort month names by month number, hide technical keys from report consumers, set data categories/formats and use hierarchies where they aid navigation. A date column with missing days or duplicate timestamps is not automatically a valid date dimension.
Calculated columns are evaluated for rows and stored in Import models; calculated tables materialize model expressions on refresh. Measures evaluate in query context. Choose a column for a relationship/grouping attribute, and a measure for an aggregation that should respond to slicers.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Slice sales by customer region | Customer dimension filters related fact rows. |
| Compare order and shipment dates | Role-playing dimensions or intentional inactive-relationship measures. |
| Need a reusable filter-responsive total | A measure. |
Traps
- Different fact grain can cause double counting.
- Bidirectional filtering is not a universal fix for a broken model.
Active recall
1. What is grain?
The meaning of one row in the fact table.
2. Why unique dimension keys?
To support unambiguous one-side relationships.
3. What does filter direction control?
How filter context propagates across relationships.
4. Column versus measure?
Stored row-level result versus a calculation evaluated in query context.
5. Why a proper date table?
It gives consistent calendar structure for reliable time-based filtering and calculations.