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:

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 Transactions
  • Bank Transactions
  • Stock levels
  • HR Data, like salaries or Headcounts
  • Data about events like the entrance of a person into a building

  • Etc.

  • The Dimensions
    These tables contain descriptive data, like:- The Date of the transaction

  • Information about the customer
  • Or accounts
  • Or products
  • The 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 dimensions
    For example, customer, product or geography
  • Junk Dimensions
    These mix different entities into one dimension instead of having multiple small dimensions
  • Degenerate Dimensions
    These 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 3rdnormal 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:

You see the following columns

  • CustomerKey
    This is the unique surrogate key for each row.
    See below for what a surrogate key is.
  • CustomerLabel
    This column contains the Customer number. This is the Business Key
  • FullName
    The complete Name of the Customer
  • Gender and House Owner Flag
    These columns contain additional information about the Customer
  • Continent, “Region Country”, State Province and City
    These 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:

The columns have the following meaning:

  • ID
    The unique ID for each row.
  • Area
    A grouping column to understand the scope of each group or row.
  • AreaID
    A unique ID for each row in each group. This column can help sort rows within each group.
    Alternatively, it can contain the Business key.
  • Name
    The Name for each row
  • Description
    This 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:

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:

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.