Converting a Data Model to a Dimensional Model

In this article, we will discuss how we can convert a data model to a dimensional model in Astera Data Warehouse Builder.

Video

Overview

• The first step is to Reverse Engineer a database.To learn more about Reverse Engineering in Astera Data Warehouse Builder, click here.
• Once a database has been reversed engineered, the entities on the designer are set to be general entities by default. To convert this data model into a dimensional model, these entities need to be changed, according to their attributes, into facts and dimensions.

How to Change Entity Types

There are two ways to change entity types in Astera Data Warehouse Builder:

• Right click on the entity you want to change, and hover over the Entity Type option. You will get the option to choose an entity type between General, Fact and Dimension. Select the type that you want to assign.

• Open Properties of the entity and select the Entity Type on the Entity Properties window. Now let’s discuss this in detail:

  1. Right click on the entity you want to assign as a Fact. An Entity Properties window screen will open. The following elements are available on this screen.

  • Table Name
  • Schema
  • Entity Type
  • Definition

​ 2. Currently, the entity type of the table is set to General. Click on the Entity Type drop-down and select Fact to convert this General entity to a Fact entity.

​ 3. On the next screen, there is a Layout Builder, where we will specify the keys for the dimensional model. For the model to work properly, there should be a Primary Key, and Foreign Key specified in the entity.

There are other columns like Column Type, Data Type, Db Type, Fact Role, etc. where you can make modifications according to the requirements of the dimensional model.

To learn more about fact roles, click here.

​ 4. The next screen is for Data Model Entity Indexes. Once you have set the properties for the fact entity, click OK.

To learn more about Entity Indexes, click here.

​ 5. Once a Fact table has been specified, all the other tables in the model would be dimension tables, in this case only. To change an entity to a Dimension, follow steps 3 and 4. In the Entity Type drop-down, select Dimension. Click Next.

​ 6. On the Layout Builder screen, you can make modifications to the table. Specify the Dimension Role (Business Key, Surrogate Key and SCD Types) and the Keys in the dimension table.

The Dimension Role options include options for Business Key, Surrogate Key, SCD Types and Insert Only.

To learn more about Dimension Roles, click here.

​ 7. The next screen is for Data Model Entity Indexes. Once you have set the properties for the fact entity, click OK.

To learn more about Entity Indexes, click here.

​ 8. Follow steps 7 to 9 for all the other entity objects in the model to transform them into dimension entities.

​ 9. Once Entity Types have been set for all the entities, your dimensional model would be ready to use.

Note: In this case, the entity names Sale is the fact entity, and the rest are dimension entities.

This concludes our discussion on how to convert a data model to a dimensional model in Astera Data Warehouse Builder.