Get Started

Dimensional modeling

1. Dimensional modeling

Welcome back! I'm Sara and I will be your instructor for this next chapter. Earlier, you learned that data models provide a conceptual representation of data elements and the relationships between them. There are many approaches to data modeling. We'll focus on one, the dimensional model.

2. The Kimball Model

The Kimball model, otherwise known as the dimensional model, is one of the most popular approaches to data modeling.

3. The Kimball Model

There are two key concepts in the Kimball model: facts and dimensions. Facts are the metrics from your business process. Dimensions provide the context surrounding a business process. These combine to form a star schema. The star schema's name comes from the way that facts and dimensions connect. We typically have several dimensions surrounding each fact. In this example, measures related to Property Sales are stored in the fact table, and the Lender, Salesperson, Date, Property, and Payment Type tables provide more context about each property sale. Huge amounts of data are organized in this way in data warehouses. Power BI is optimized to use star schemas over any other way of loading data, so Power BI is faster and easier to use with a dimensional approach.

4. Fact tables

Let's take a closer look at fact tables first. A fact table typically has two types of columns; facts and keys. Facts are the measures or metrics from your business process. Examples include sales, employee count, or number of website visits. They are generally dates and numbers which we can aggregate in some way. Keys are how we establish relationships between fact tables and dimension tables. We expect fact tables to be tall and narrow. They have lots of rows, so we try to minimize the number of columns and how big those columns are.

5. Fact tables: an example

Here is an example of a fact table, Property Sales. Each row represents a property that was rented at a specific date.

6. Fact tables: an example

The first five columns contain keys or IDs that are used to link to each of the dimension tables. You'll find more information about the lender, date, property, payment type, and salesperson there.

7. Fact tables: an example

The last two columns hold the measures. Here we're tracking the rent, in dollars, and the duration of the rental agreement, in months.

8. Dimension tables

Next, we'll look at dimension tables which provide the context around facts. A fact may tell you how much or how often, but dimensions give the rest of the story. Who did it, how they did it, where they did it, and so on. Dimensions are shared business concepts, usually in the form of a noun such as Person, Employee, Customer, and Vendor. Dimensions contain static or slowly-changing data. Think of information like names, dates of birth, and height. Dimension tables are typically short and wide. They don't contain that many rows, but do contain a large amount of context for the facts.

9. Dimension tables: an example

Let's take a look at an example. Here you can see the Salesperson table.

10. Dimension tables: an example

The first column contains the same key as in the fact table and can be used to combine the information from both tables.

11. Dimension tables: an example

The table also stores additional attributes about each salesperson in the company.

12. Star schema

Here's that same star schema from before. In the Kimball model, dimensions are often used in multiple facts. These dimensions surrounding Property Sales could provide context to a different fact table as well. In a star schema, dimensions do not link to other dimensions.

13. The dataset

This chapter will continue with the Census data about establishments active in the manufacturing sector. In the exercises, you will be creating a fact table that contains measures like number of employees, number of firms, and so on. Additionally, there will be more information about the establishment in the form of dimension tables. These include data on the industry, time, age, and geography of the establishments.

14. Let's practice!

Let's check your understanding of dimensional modeling!