Fact Entities

In a dimensional model, a fact entity represents a database table that contains quantitative information regarding a business process, such as measures and metrics. It is the central entity of a star schema and is surrounded by related dimension entities. This implies that the primary key of each dimension table is also part of the fact table as a foreign key.

Video

Overview

Let’s assume that we have a sample dimensional model that looks like this:

The Sale entity in this model represents the fact table and is surrounded by numerous dimension entities in the star schema.

In the next sections of this article, we’ll examine the layout of the Sale entity to learn how you can configure facts in Astera Data Warehouse Builder.

Layout Builder

To open the properties of the fact entity, right-click on it and select Properties from the context menu.

A configuration window will appear. This window provides the same options as it does for a general entity, the only addition being the Fact Role column on the Layout Builder screen. To learn more about the general entity properties, click here.

On the first screen, you can view and edit some general information regarding the table, including its name, schema, and type.

Click Next to proceed to the Layout Builder screen.

Foreign Keys

In the layout of this fact table, you’ll notice quite a few foreign keys, such as City_Key, Customer_Key, etc.

Each of these foreign keys represents a relationship between this fact table and one of the dimension tables. For example, City_Key represents the primary key of the City table, Customer_Key represents the primary key of the Customer table, and so on.

Fact Role

The Fact Role column in the layout builder provides a dropdown menu for you to assign the role of Transaction Date Key to one of the fields.

Transaction Date Key: A key date field that is coming from the OLTP system.

Note: Assigning the Transaction Date Key fact role to more than one field in a fact entity will cause a verification error in the model.

In this case, we’ve assigned the role to the Invoice_Date_Key field.

Once you’ve assigned the Transaction Date Key role to a field, click OK.

This concludes our discussion on fact entities in Astera Data Warehouse Builder. In the next article, we’ll talk about dimension entities.