# 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 Stack.

### Video

{% embed url="<https://youtu.be/7zV4mx2aQP0>" %}

### Overview

* The first step is to *Reverse Engineer* a database. To learn more about *Reverse Engineering* in Astera Data Stack, click [here](https://documentation.astera.com/data-model/data-models/reverse-engineering-an-existing-database).
* 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.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/y83NxQI85SupHUyXTXtl/01-Reverse-Engineered-Database.png)

### How to Change Entity Types

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

* 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.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/SdkwM1ZbPQ78i6REij8N/02-Right-Click-Change-Entity-Type.png)

* 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*

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/A0NhAkVpVDDVfxHNsRdS/03-Entity-Property-Window-Screen.png)

​ 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.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/s66NJyWKLW9FIFLMKIai/04-General-Entity-to-Fact-Entity.png)

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.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/4nphBe41myXSmE4a7v7D/05-Fact-Layout-Builder.png)

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](https://documentation.astera.com/data-model/fact-entities#fact-role).

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](https://documentation.astera.com/data-models/general-entity-properties#data-model-entity-indexes).

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/KTTu2DsYB4rRkIlBXDS9/06-Fact-Entity-Indexes.png)

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*.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/I7EIsxQN4UrZmNKRAGr7/07-Dimension-Entity-Properties.png)

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.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/TT0SJuUJFX3sZXAoOxqk/08-Dimension-Layout-Builder.png)

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

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/I7ESlHjixHEeEgukvlHN/09-Dimension-Role-Options.png)

To learn more about Dimension Roles, click [here](https://documentation.astera.com/data-model/dimension-entities#dimension-role).

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](https://documentation.astera.com/data-models/general-entity-properties#data-model-entity-indexes).

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/w7ft1Ek0jOHmOsbaZjFO/10-Dimension-Entity-Indexes.png)

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.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/FYRy8CsVLwuF4gmI7Los/11-Dimensional-Model.png)

{% hint style="info" %}
**Note**: In this case, the entity names *Sale* is the fact entity, and the rest are dimension entities.
{% endhint %}

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