Dimension table structure, sentence by sentence

In the first article in this series, we looked at how the Kimball Group defines a fact table. We learned what a fact table represents, how its grain defines what one row means, and how facts and foreign keys describe a measurable business event.

If you have not read that article yet, you may find it useful to start with Fact table structure, sentence by sentence. Fact tables and dimension tables are closely connected, and understanding the role of the fact table makes it easier to understand the purpose of a dimension table. In this article, we will learn about the other side of that relationship: the dimension table.

As with the previous article, we will begin with the complete Kimball Group explanation and then break it down sentence by sentence.

“

Every dimension table has a single primary key column. This primary key is embedded as a foreign key in any associated fact table where the dimension row's descriptive context is exactly correct for that fact table row. Dimension tables are usually wide, flat denormalized tables with many low-cardinality text attributes. While operational codes and indicators can be treated as attributes, the most powerful dimension attributes are populated with verbose descriptions. Dimension table attributes are the primary target of constraints and grouping specifications from queries and BI applications. The descriptive labels on reports are typically dimension attribute domain values.

One Primary Key

Every dimension table has a single primary key column.

Let us start with the primary key. A primary key is a column that uniquely identifies each row in a table. This means that every value in that column must identify one specific row.

For example, let us use the DimProduct table:

ProductKey	ProductName	Category
101	Wireless Mouse	Accessories
102	Laptop Stand	Accessories
103	Monitor	Electronics

In this example, ProductKey is the primary key.

Each product has its own ProductKey, and each key identifies one specific row in the dimension table. ProductKey 101 identifies the Wireless Mouse, 102 identifies the Laptop Stand, and 103 identifies the Monitor.

Kimball also says that the dimension table has a single primary key column. In other words, we do not need to combine several columns to identify a row. One column provides the key for the dimension. This is useful because the descriptive information about the product can change or become more detailed, while the key continues to provide a simple way to identify the correct dimension row.

For now, the important point is simple:

Each row in a dimension table has one key value that uniquely identifies it.

Connecting Facts to Dimensions

This primary key is embedded as a foreign key in any associated fact table where the dimension row's descriptive context is exactly correct for that fact table row.

In the previous sentence, we learned that each row in a dimension table is identified by its primary key. Kimball now explains how that key connects the dimension table to a fact table.

Let us continue with our DimProduct example:

ProductKey	ProductName	Category
101	Wireless Mouse	Accessories
102	Laptop Stand	Accessories
103	Monitor	Electronics

Here we recall, ProductKey is the primary key of DimProduct.

Now imagine that we also have a FactSales table:

SaleKey	ProductKey	Quantity	SalesAmount
1	101	2	50
2	103	1	300
3	101	1	25

Notice that ProductKey also appears in the fact table. In FactSales, ProductKey is a foreign key. A foreign key is a column that refers to a key in another table. In this case, it connects each sales row to the correct row in DimProduct. The first row in FactSales contains ProductKey 101. We can use that value to find ProductKey 101 in DimProduct and learn that the sale was for a Wireless Mouse in the Accessories category.

This is the first part of what Kimball means when saying that the dimension table's primary key is embedded as a foreign key in the associated fact table. The same key value appears in both tables and creates the connection between them.

The second part of the sentence is also important:

where the dimension row's descriptive context is exactly correct for that fact table row.

The descriptive context means the descriptive information stored in the dimension row. For ProductKey 101, that context includes:

• ProductName: Wireless Mouse
• Category: Accessories

If a row in FactSales contains ProductKey 101, that key should point to the dimension row whose descriptive information correctly describes the product involved in that specific sale. This is why the relationship between facts and dimensions is more than simply matching numbers. The foreign key tells us which descriptive dimension row belongs to a particular business event recorded in the fact table.

In Power BI, this is also the basis of the relationship between the two tables. DimProduct[ProductKey] is on the one side, while FactSales[ProductKey] is on the many side because the same product can appear in many sales rows.

The important point for you to remember is:

The dimension key stored in a fact table connects each fact row to the dimension row that correctly describes that business event.

Wide, Flat Dimension Tables

Dimension tables are usually wide, flat denormalized tables with many low-cardinality text attributes.

There are several important terms in this sentence, so let us break them down one at a time. First, Kimball describes dimension tables as wide.

A wide table contains many columns. This is common for dimensions because their purpose is to provide descriptive information about a business entity such as a product, customer, store, or employee.

For example, our DimProduct table could contain:

ProductKey	ProductName	Category	Subcategory	Brand	Color	Size
101	Wireless Mouse	Accessories	Computer Accessories	Contoso	Black	Standard
102	Laptop Stand	Accessories	Computer Accessories	Fabrikam	Silver	Standard
103	Monitor	Electronics	Monitors	Contoso	Black	27 inch

Instead of containing only a product key and product name, the dimension contains several columns that describe each product.

Kimball also describes dimension tables as flat and denormalised. These two ideas are closely related. In a more normalised relational database, information about a product may be separated across several tables. We could have one table for products, another for categories, another for subcategories, and another for brands.

A dimensional model usually takes a different approach. Instead of requiring the report to travel through several related tables to understand a product, we can bring those descriptive attributes together into one DimProduct table.

Our dimension may therefore contain ProductName, Category, Subcategory, Brand, Color, and Size as columns in the same table. This is what makes the dimension flat: the descriptive information is available directly in one table rather than spread across several levels of related tables.

It is also denormalised because some descriptive values will be repeated. For example, several products may belong to the Accessories category:

ProductName	Category
Wireless Mouse	Accessories
Laptop Stand	Accessories
Keyboard	Accessories

The value Accessories appears several times. In a normalised database, we might store Accessories once in a separate category table and reference it using a category key. In the dimensional model, repeating that descriptive value is intentional because it keeps the dimension easier to use for reporting and analysis.

The final part of Kimball's sentence describes low-cardinality text attributes.

An attribute is a column that describes the dimension member. In DimProduct, columns such as Category, Brand, Color, and Size are attributes.

Cardinality tells us how many distinct values a column contains. For example, imagine that DimProduct contains 10,000 products, but only:

• 8 categories;
• 25 brands;
• 12 colours.

Those columns have relatively low cardinality because the same values are repeated across many product rows.

Kimball specifically mentions text attributes because descriptive text is what makes dimensions useful to people. A report user is usually more interested in seeing Accessories, Contoso, or Black than the codes that may represent those values in the source system.

We can therefore read Kimball's sentence in simpler language:

A dimension table usually keeps many descriptive columns together in one table, even when some of those descriptive values are repeated across many rows.

Use Descriptive Attributes

While operational codes and indicators can be treated as attributes, the most powerful dimension attributes are populated with verbose descriptions.

In the previous sentence, we learned that dimension tables contain attributes that describe the dimension member. Kimball now explains that not all attributes are equally useful for analysis.

Let us start with operational codes and indicators.

Operational systems often use short codes to represent business information. For example, a source system could store a product status like this:

ProductKey	ProductName	StatusCode
101	Wireless Mouse	A
102	Laptop Stand	A
103	Monitor	D

Here, StatusCode is an attribute of the product dimension. The values A and D may work perfectly well in the operational system. Perhaps A means Active and D means Discontinued.

However, Kimball says that the most powerful dimension attributes contain verbose descriptions. In this context, verbose does not mean unnecessarily long text. It means using a clear and understandable description instead of making the user interpret a code.

For example, our dimension could contain both values:

ProductKey	ProductName	StatusCode	ProductStatus
101	Wireless Mouse	A	Active
102	Laptop Stand	A	Active
103	Monitor	D	Discontinued

StatusCode preserves the operational value, while ProductStatus gives us a description that people can immediately understand.

The same principle can apply to many other attributes. Instead of showing a category code such as CAT-01, we can show Accessories. Instead of a region code such as NO-E, we can show Eastern Norway.

The important point to remember:

Dimension tables can keep operational codes, but descriptive attributes written in clear business language are usually much more useful for reporting and analysis.

Filtering and Grouping Data

Dimension table attributes are the primary target of constraints and grouping specifications from queries and BI applications.

So far, we have focused on what dimension tables contain. Kimball now explains how their attributes are used when we analyse data. Let us start with the word constraints.

In this context, a constraint limits the data that should be included in a query or analysis. In Power BI, the easiest way to think about a constraint is as a filter.

For example, our DimProduct table contains attributes such as:

ProductKey	ProductName	Category	Brand	Color
101	Wireless Mouse	Accessories	Contoso	Black
102	Laptop Stand	Accessories	Fabrikam	Silver
103	Monitor	Electronics	Contoso	Black

If a report user selects Accessories in a slicer, DimProduct[Category] is being used to constrain, or filter, the analysis to products that belong to that category. The same could happen with Brand, Color, ProductName, or another dimension attribute.

Kimball also mentions grouping specifications. Grouping means organising the result into categories so that a measure can be calculated separately for each group.

For example, if our report contains a column chart showing Sales Amount by Category, Sales Amount comes from the fact table, while Category comes from DimProduct.

Power BI can group the sales into categories such as:

Category	Sales Amount
Accessories	45,000
Electronics	120,000
Office Equipment	32,000

Here, Category determines how the sales are grouped, while Sales Amount provides the numeric value being calculated for each group. This shows us an important distinction between fact tables and dimension tables.

Fact tables usually contain the values we want to measure, such as sales amount, quantity, or cost. Dimension attributes provide the descriptive information we use to filter and group those values. In Power BI, we do this constantly. We may filter sales by Brand, group revenue by Category, compare quantities by ProductName, or use Color in a slicer.

That is what Kimball means when saying that dimension attributes are the primary target of constraints and grouping specifications.

Or in our, simpler terms:

Dimension attributes give us the fields we use to filter our data and organise measures into meaningful groups.

Descriptive Labels in Reports

The descriptive labels on reports are typically dimension attribute domain values.

In the previous sentence, we learned that dimension attributes are commonly used to filter and group our data. Kimball now explains that the values stored in those attributes are also what we usually show to the report user.

The important term here is domain values. A domain is the set of possible values that an attribute can contain.

For example, if DimProduct[Category] contains these values:

• Accessories
• Electronics
• Office Equipment

then those three values belong to the domain of the Category attribute.

Similarly, the domain of ProductStatus could contain values such as Active and Discontinued, while the domain of Color could contain values such as Black, Silver, and White.

When Kimball talks about descriptive labels on reports, he means the text that users see when they interact with a report.

For example, a Power BI column chart showing Sales Amount by Category may display:

Category	Sales Amount
Accessories	45,000
Electronics	120,000
Office Equipment	32,000

The labels Accessories, Electronics, and Office Equipment come from the values stored in the Category attribute of DimProduct. The same principle applies throughout a Power BI report. A slicer may show product names, a chart axis may show regions, a matrix may group results by customer type, and a table may display product status.

These descriptive values come from dimension attributes. This connects directly to the previous sentences. Dimension tables do not only provide keys that connect to fact tables. They also contain the descriptive information that makes the numbers understandable to the person reading the report.

A sales value of 120,000 means much more when the report tells us that it belongs to Electronics, Eastern Norway, or another meaningful business category.

Or, again in our simpler terms:

The names and categories that users see in a report usually come from the descriptive values stored in dimension table attributes.

Bringing the Six Sentences Together

Dimension tables give our fact tables the descriptive context that makes the data useful for analysis. Across these six Kimball sentences, we learned that a dimension table uses a single primary key to identify each row, while that same key appears in associated fact tables as a foreign key. We also saw why dimensions are usually designed as wide, flat, denormalised tables that keep useful descriptive attributes together.

Fact tables tell us what happened and what we can measure, while dimension tables give us the descriptive information we need to understand and analyse those measurements.

In the next article in Kimball, sentence by sentence, we will return to fact tables and look at another important Kimball technique: additive, semi-additive, and non-additive facts.

We will break down what each type means, why some facts can be summed across every dimension while others cannot, and how those differences affect the way we design measures and analyse data in Power BI.