Chapter 2 of 7 · 4 practices · 3 min · 4-question quiz
Fact tables
Fact tables hold the measurements of a business process. These practices cover picking the right type of fact table, keeping measures summable and keeping keys clean.
Start: Pick the fact table type that matches the process4 practices in this chapter
- 1Pick the fact table type that matches the processMicrosoft's guidance describes three types of fact table: Accumulating snapshots carry several date keys, one per milestone, and their measures are usually durations between milestones, such as days…
- 2Know which measures can be summed, and store the parts of ratiosMicrosoft's guidance groups measures by how they aggregate: Additive: can be summed across every dimension, like quantity or revenue in one currency; Semi-additive: can be summed across some…
- 3Make every dimension key NOT NULL and use special members for missing valuesA null foreign key in a fact table drops rows silently from inner joins and shows up as a blank group in reports. Microsoft's guidance recommends setting every dimension key
NOT NULLand pointing… - 4Keep transaction identifiers on the fact table as degenerate dimensionsAn order number or invoice number is useful for filtering and drilling into detail, but it has no attributes of its own. Creating a dimension table that holds only that one column adds a join and…
Check yourself on this chapter
4 questions, right here once you are signed in. Your progress is saved as you read.