Fact table structure, sentence by sentence

When I began learning data modelling, I could recognise terms such as fact table, dimension table, and grain, but recognising them was not the same as understanding how they worked together. Things appeared simple at first, yet I found that I could not always explain what those concepts meant or apply them confidently when building a Power BI semantic model.

So I got inspired, picked up my study books again, and decided to study the dimensional modelling techniques published by the Kimball Group in depth. Rather than reading each definition once and moving on, I began taking it apart sentence by sentence, identifying what each statement meant and testing the ideas against concrete examples.

This series, Kimball, sentence by sentence, follows the Kimball Group's dimensional modelling techniques one subject at a time. For each article, I will begin with the original explanation, then work through it sentence by sentence in clearer language. I will also connect the principle to a Power BI example so that we can see not only what the definition says, but how it affects the structure and behaviour of a semantic model.

In this article, let us see what the Kimball Group says about fact tables.

That is five sentences. Let us take them one at a time, in language that is easier to understand.

Measurements from Business Events

A fact table contains the numeric measures produced by an operational measurement event in the real world.

In plain words, a fact table records measurable results produced when something happens in a business. The event may be a product sale, an invoice being issued, an item being shipped, a payment being received, or an employee recording working hours. Each event produces values that the business may want to calculate and analyse.

Consider a customer purchasing three units of a product for a total of €90. The purchase is the real-world business event. A row in a Power BI sales fact table could record values such as Quantity, Sales Amount, Cost Amount and Discount Amount.

Let us look at the table FactSales.

These values are facts because they measure the event. They can later be aggregated in Power BI to answer questions such as: How many units were sold? What was the total sales amount? How much did the products cost? What was the gross margin?

The same row would normally also contain keys such as DateKey, ProductKey, CustomerKey and StoreKey. These columns may also use numeric data types, but they are not numeric measures. Their purpose is to connect the fact table to the related dimension tables.

One Row, One Event

At the lowest grain, a fact table row corresponds to a measurement event and vice versa.

The grain of a fact table tells us what one row represents, it defines the table's level of detail. The lowest grain is the most detailed level of information available. For example, a sales system may store an entire transaction, but that transaction can contain several products. If the most detailed information available is one product line within the transaction, then the grain of the fact table is: One row represents one product sold as part of one transaction.

Suppose transaction 1001 contains two different products:

These rows belong to the same transaction, but they represent two separate measurement events at the chosen grain. The first row records the sale of product 101, while the second records the sale of product 102.

The phrase "and vice versa" means that the rule also works in the opposite direction:

• each row represents one measurement event;
• each measurement event is stored as one row.

Once the grain is clear, it becomes easier to decide which dimension keys and numeric measures belong in the fact table.

Design Around Business Activity

Thus the fundamental design of a fact table is entirely based on a physical activity and is not influenced by the eventual reports that may be produced.

This means that a fact table should be designed around what happens in the business, and not around the layout of a particular Power BI report.

For example, suppose a retailer records each product sold as one transaction line. That sales activity determines the structure of the fact table. One row may contain the transaction number, the product, the store, the date, the quantity sold, the sales amount, and the cost amount.

The grain could therefore be defined as: One row represents one product line in one completed sales transaction.

Now imagine that the first Power BI report only needs to show monthly sales by store. It might be tempting to create a table containing only one row per store and month:

This table may support that one report, but it no longer represents the original sales activity at its most detailed level. The individual transactions, products, quantities, and customers have been removed. If the business later asks for sales by product category, average transaction size, units sold, or customer purchasing behaviour, the summarised table may not contain enough detail to answer those questions.

Keys, Dimensions, and Timestamps

In addition to numeric measures, a fact table always contains foreign keys for each of its associated dimensions, as well as optional degenerate dimension keys and date/time stamps.

This sentence tells us that a fact table contains more than the numbers we want to calculate. It also contains columns that connect each measurement event to its business context.

Consider this simplified sales fact table:

Quantity and SalesAmount are numeric measures. They tell us what was measured during each sales event.

The other columns help us answer questions such as:

• Which product was sold?
• In which store was it sold?
• Which customer bought it?
• On which date did the sale occur?
• At what exact time did it occur?
• Which transaction did the row belong to?

To understand how this works, we need to separate the concepts Kimball mentions.

What is a dimension?

A dimension is a table that describes the business entities connected to an event.

For example, DimProduct may contain:

The fact table records that product 101 was sold. The product dimension tells us that product 101 is a Wireless Mouse in the Accessories category.

The same idea applies to other dimensions:

• DimStore describes the store;
• DimCustomer describes the customer;
• DimDate describes the calendar date.

The fact table stores the event and its measurements. The dimensions provide the descriptive information used to filter and group those measurements.

What does "associated dimension" mean?

An associated dimension is simply a dimension that is related to the business event recorded in the fact table.

A sales event may be associated with a product, a store, a customer, and a date. These dimensions belong to the sales fact table because they describe the context of each sale.

What is a foreign key?

A foreign key is a column in the fact table that points to a matching row in a dimension table. For example, ProductKey appears in both tables: FactSales[ProductKey] holds the value 101, and DimProduct[ProductKey] holds the value 101. That value connects the sales event to the correct product.

In Power BI, this connection is represented by a relationship:

The 1 means that each ProductKey appears once in DimProduct. The * means that the same product can appear many times in FactSales because it can be sold in many transactions.

The same pattern applies to the other dimensions. DimDate, DimStore and DimCustomer each connect to FactSales in exactly the same way, through DateKey, StoreKey and CustomerKey.

These foreign keys allow the dimensions to filter the fact table. For example, when a report user selects the Accessories category in a Power BI report, DimProduct identifies the products in that category. The relationship then filters FactSales to the rows containing those products.

What is a degenerate dimension?

Some useful business identifiers belong to the event but do not need a separate dimension table. Kimball calls these degenerate dimensions.

In our example, SaleID identifies the sales transaction:

Both rows belong to transaction 1001.

We could create a separate DimSale table, but that table might contain only the transaction number and no additional descriptive columns. In that situation, creating another table would add little value.

The transaction number can remain directly in the fact table. It behaves like a dimension value because report users may use it to identify, group, filter, or count transactions, but it does not have its own dimension table. That is why it is described as degenerate. The dimension value exists, but the separate dimension table does not.

In Power BI, SaleID could be used to calculate the number of transactions:

Transaction Count =
DISTINCTCOUNT(FactSales[SaleID])

It could also appear in a detailed table when someone needs to trace a result back to a specific source transaction.

Other common examples of degenerate dimensions include order numbers, invoice numbers, ticket numbers, shipment numbers, and claim numbers. These identifiers are optional because not every business process produces one that is useful for analysis.

What are date and time stamps?

A date or time stamp records when the measurement event happened. In one of our earlier examples, 2026-08-01 10:32:15 means that the sale occurred on 1 August 2026, at 10:32:15.

The fact table may contain both:

• a DateKey that connects to DimDate;
• a timestamp that records the exact moment of the event.

DateKey can then support analysis by calendar attributes such as day, week, month, quarter, and year.

The timestamp can support more detailed analysis, such as sales by hour, the busiest part of the day, the order in which events occurred, and the time between two events.

What we have covered so far

The fact table contains the numeric results of the event, but it must also preserve enough context to explain those results.

In our sales example:

• Quantity and SalesAmount measure the event;
• ProductKey identifies the related product;
• StoreKey identifies the related store;
• CustomerKey identifies the related customer;
• DateKey connects the event to the calendar;
• SaleID identifies the transaction without requiring a separate dimension;
• SaleTimestamp records the exact time of the sale.

This gives Power BI both parts it needs. The fact table tells us what happened numerically, while its keys and timestamps tell us the context in which it happened.

Dynamic Calculations and Aggregations

Fact tables are the primary target of computations and dynamic aggregations arising from queries.

This sentence explains what happens when someone interacts with a Power BI report. The fact table contains the detailed measurements, and Power BI uses those measurements to calculate the results shown in visuals.

What does "the primary target of computations" mean?

A computation is a calculation performed on the data. In our sales example, FactSales contains values such as:

Power BI can use these fact table columns in measures such as:

Total Sales =
SUM(FactSales[SalesAmount])
 
Units Sold =
SUM(FactSales[Quantity])
 
Total Cost =
SUM(FactSales[CostAmount])
 
Gross Margin =
[Total Sales] - [Total Cost]

The calculations are primarily performed against the fact table because that is where the measurable business values are stored. The dimension tables provide descriptions such as product names, categories, store regions, and calendar periods. However, the amounts being added, averaged, counted, or otherwise calculated normally come from the fact table.

What is an aggregation?

An aggregation combines several detailed rows into a summarised result. For example, the four rows in FactSales contain these sales amounts: 90 + 45 + 120 + 120 = 375. The Total Sales measure therefore returns €375.

The fact table stores the individual measurement events. The aggregation combines those events into a result that is useful for reporting.

Common aggregations in Power BI include summing sales amounts, counting transactions, averaging order values, finding minimum or maximum values, counting distinct customers, and calculating ratios such as gross margin percentage.

What makes the aggregation dynamic?

The aggregation is dynamic because Power BI recalculates it according to the filters applied to the report.

Suppose a card visual shows Total Sales of €375. If the report user selects Store 1, Power BI keeps only the fact table rows associated with that store:

The same Total Sales measure now returns €135. If the user selects Store 2, it returns €240. The DAX measure has not changed. Power BI has recalculated it over a different set of fact table rows.

The result can also change when the user filters by product, product category, customer, store region, date, month, and year. This is what Kimball means by dynamic aggregations. The fact table stores the detailed measurements, while the report query determines which rows are included in each calculation.

What does "arising from queries" mean?

A query is a request for a result from the data model. The report user does not usually write the query directly. Power BI generates it when a visual needs data or when someone interacts with the report.

Each visual, slicer selection, filter, and drill-down can create a different query. Power BI responds by recalculating the measures against the relevant fact table rows.

Bringing the Five Sentences Together

This completes Kimball's explanation of the basic structure and purpose of a fact table. We have seen that a fact table:

• records measurable business events;
• stores one event per row at a clearly defined grain;
• is designed around the underlying business activity rather than a specific report;
• contains numeric measures, foreign keys, optional degenerate dimensions, and timestamps;
• provides the detailed values that Power BI calculations aggregate.

Those measurements become meaningful when they are connected to descriptive information such as products, customers, stores, and dates.

In the next article in this series, I will examine how Kimball explains dimension tables and work through his definition sentence by sentence in the same way.