Stores the numbers captured when business events happen, like what each sale brought in; an analytical model rolls these up into measures and slices them by dimensions.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains Fact table in context, with comparison tables and the common traps.
Terms in this definition
- LIKE
Compares strings with a pattern that can contain the % and _ wildcards. Because it only understands character patterns, searching big volumes of text this way is much slower than using full-text search.
Related terms
- Aggregate table
A summarised copy of a fact table at a coarser grain or with fewer dimensions (daily store sales, for instance) that makes frequent queries faster. Power BI semantic models can achieve the same result through user-defined aggregations.
- Degenerate dimension
Something like an order number that describes the data but has the same grain as the fact table, so it lives in the fact table instead of a dimension table of its own; a Power Query table or a view can expose it.
- Dimensionality
Which dimension keys a fact table carries determines its dimensionality. Granularity is a separate idea: it depends on the key values themselves.
- Grain
What a single row in a fact table stands for at its most detailed level, defined by its dimension keys and attributes, such as one row for each order line on each day.
- Junk dimension
Groups many minor attributes with only a handful of possible values, flags and statuses for instance, into a single table keyed by a surrogate key. The fact table then carries one key in place of many.
- lookup operator
A KQL operator for enriching a big fact table with columns from a small dimension table. Only leftouter (the default) and inner are allowed, key columns are not duplicated, and because the right-hand table is broadcast, one bigger than a few tens of MB causes the query to fail.
- Measure
A figure like total revenue, worked out by aggregating fact table data wherever dimensions intersect within a semantic model.
- Relationship cardinality
Describes whether the values on each side of a relationship are unique, giving one-to-one, one-to-many (many-to-one) or many-to-many. With one-to-many, the dimension table sits on the one side and the fact table on the many side.