The Types of Dimensions in a Star Schema, and How to Use Them

A short Intro to Dimensional modelling To begin, here is a short introduction to what a Star Schema is and what dimensional modelling is. A Star schema can be best explained with the following diagram: Figure 1 – The principle of a Star Schema and of Dimensional modelling (Figure by the Author) You can see at first sight why it is called “Star Schema”. A Star Schema consists of two object types: The Fact table(s)Fact tables describe business processes. More precisely, they contain information and numbers that describe events in business processes.They can be:Sales TransactionsBank TransactionsStock levelsHR Data, like salaries or HeadcountsData about events like the entrance of a person into a building Etc. The DimensionsThese tables contain descriptive data, like:The Date of the transactionInformation about the customerOr accountsOr productsThe geographical information about the transaction Etc. Because there are multiple Dimensions, this modelling approach is also called Dimensional data modelling. But the term “Star Schema” is easier to understand, and it is used much more. This modelling approach has multiple benefits: Easy to understand and use by the business user Has a clear separation between transactional and descriptive data Can cover complex business scenarios Is widely used when building reporting models It can be considered the de facto standard for modelling approaches in data analysis and reporting scenarios. One can say a Dimension is easy to define, as I did above. But dimensions come in multiple types. Let’s explore them. The main types of Dimensions There are three types of dimensions: Regular dimensionsFor example, customer, product or geography Junk DimensionsThese mix different entities into one dimension instead of having multiple small dimensions Degenerate DimensionsThese are pieces of information that change frequently, such as a transaction number. Next, let’s explore these dimension types. Regular Dimensions Regular Dimensions are the most common type of Dimensions. They contain information to describe a Business Entity. An Example of such an Entity is the Customer. A Regular Entity follows these rules: Each row explains one occurrence of an entity Each row is uniquely identifiable The Data is denormalised Denormalised data contains duplication of information (As opposed to normalised data in the 3rd normal form) In the References section below, you can find two links explaining the third normal form and how to model data in this form. In addition, a regular dimension contains: Hierarchies Additional Attributes Information about the historization of the occurrence Here, an extract from the Product dimension from the ContosoRetailDW sample set: Figure 2 – Extract from the Customer dimension from the ContosoRetailDW sample set (Figure by the Author) You see the following columns CustomerKeyThis is the unique surrogate key for each row.See below for what a surrogate key is. CustomerLabelThis column contains the Customer number. This is the Business Key FullNameThe complete Name of the Customer Gender and House Owner FlagThese columns contain additional information about the Customer Continent, “Region Country”, State Province and CityThese columns describe the geography hierarchy. As you can see, the same Continent, “Region Country”, and State Province name appear multiple times in the data. These columns are the denormalised part of the dimension table. Hierarchies, like the geography hierarchy, are essential for grouping and aggregating data. Report users love them because they let them see the bigger picture of the results. Dimensions can have multiple hierarchies. Junk Dimensions This is a special type of dimension. Imagine the following scenario: Your data model contains several business entities with only a small number of rows. Let’s say fewer than 10 rows. You end up with many dimensions that can confuse users. You can group them into a smaller number of so-called “Junk Dimensions”. For example: Figure 3 – An example of a Junk Dimension containing rows for three Areas (Figure by the Author) The columns have the following meaning: IDThe unique ID for each row. AreaA grouping column to understand the scope of each group or row. AreaIDA unique ID for each row in each group. This column can help sort rows within each group.Alternatively, it can contain the Business key. NameThe Name for each row DescriptionThis can be a descriptive column, or it can contain any other information about each row. If needed, I add a separate sorting column to ensure the rows appear in the correct order when alphabetical order isn’t useful. Another example of a junk dimension is a Measure dimension. You can use such a dimension to let the user select which measure to display in the report. But be careful not to group smaller dimensions into the same junk dimension when both should be selectable to generate a result. For example, you might combine two dimensions used to select transactions into one junk dimension. The selection in one area might exclude rows from another area, which can be counterproductive. Degenerated Dimensions A degenerated dimension is a dimension that has a direct relation to each transaction in the fact table. A common example of a degenerated dimension is a transaction number. You can have millions of transactions, which wouldn’t make sense to store in a dimension table. Usually, such information is stored in the fact table alongside the transaction values. Here is an extract from the Online Sales table with the Sales Order Number: Figure 4 – Example of a degenerated dimension (Figure by the Author) In this case, the “Sales Order Number” column is the degenerated dimension. The Online Sales fact table contains information about 1’674’320 Orders. Each order has multiple rows; one for each line item. But we can add a filter to the report so the user can search for information about one specific transaction, if needed. Reusing Dimensions This is a more general topic in data modelling. Dimensions used by multiple Facts are called Conformed Dimensions. An example of a conformed dimension is the product dimension. In the ContosoRetailDW sample set, the product dimension is used by the Online and Retail sales facts. As both use the same dimension in the same way, the same dimension can be used by both. Another conformed dimension is the date or calendar dimension. Almost all fact tables contain date information. It is not uncommon to add attributes to a conformed dimension that is used only by a subset of all fact tables. This helps in reducing the number of dimensions. Planning for Dimensions with Facts When planning the dimensional model, I use a Dimension Matrix. In the DWH Toolkit, this is called a Bus Matrix (Starting from page 109). Both terms are interchangeable. The matrix connects the fact tables to the Dimension tables. The following image shows the Dimension matrix for a part of the Contoso data model: Figure 5 – Dimension Matrix for the Contoso Retail sample set (Figure by the Author) I always put the Date dimension first, as it is used in almost every case. As you can see, this way you can immediately see which dimension each fact table uses. This helps a lot when building the data model. Usually, I compile this matrix with the client to define the data model structure and the reporting axes. Each reporting axis filters the results. This is called slicing. The term “Slice and Dice” comes from this. It doesn’t include technical details but shows how business processes connect to the dimensions. Other concepts in Dimensions Dimensional modelling includes a few more concepts. The most important are: Surrogate Key Slowly changing dimensions A Surrogate Key is a technical key that is unrelated to the business key. A Business Key can be: Product number Employee ID Account number A surrogate key is either a number or a composite key that combines the business key with a time key, such as a date. But we discover the real power of a surrogate key only when using slowly changing dimensions. Imagine the following scenario: Multiple assurance representatives are responsible for clients in a specific geographical region. Now, a client moves from one region to another. Which representative can take the sales bonus for the moving client? The correct answer should be: One representative before the move. Another one after the move. When you assign a Surrogate key to the client, you can have two rows for the same client with the same client number. One before the move with the old address A second one after the move with the new address This way, no conflicts occur, and the data is distributed correctly. This modelling approach to historize dimension data is called Slowly Changing Dimension 2 (SCD2). These two concepts are key to receiving the correct result. You can find more details on both concepts after page 113 in the DWH Toolkit mentioned. The only dimension with a readable surrogate key is the date table. This table gets a numeric representation of each date in the form of YYYYMMDD. This makes it easy to calculate the dimension ID from the facts, and it helps with sorting. Conclusion Knowing how to design a dimensional model is key to a good data model. Users will be grateful when they can clearly understand which entity, or part of a business process, each dimension describes. In most cases, the fact table is invisible to the report creators because it doesn’t contain descriptive information. The only exception are degenerated dimensions. When starting with a new data model, create a dimension matrix. This matrix helps all project members describe how the data model will be set up and what reporting possibilities exist. You can also make it public to help report creators understand how to use the data model. Imagine a report creator calling you to ask, “Why can’t I slice the data by the dimension xyz?”, and the answer lies in the dimension matrix. I’ve built several data models with this approach, and it was always beneficial. But the most important thing is: Remember who the users and consumers of your data model are! Report users don’t have access to the data model. But you can allow them to create new reports or an Excel PivotTable connected to your data model. At that moment, they become report designers. They must understand the data model without training, using only their knowledge of the business processes they are interested in. So, it’s always important to have an easy-to-use data model. References The main Reference for this Article is the Kimball Data Warehouse Toolkit. You can get the PDF for the book and all the materials from here: The Wikipedia article on the third normal form. Here is an easier-to-read article on the third normal form. Like in my previous articles, I use the Contoso sample dataset. You can download the ContosoRetailDW Dataset for free from Microsoft here. You can use the Contoso data freely under the MIT License, as described in this document.