# 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/astera-data-stack-v9/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://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2F2eDTAbfnwSUqESRK0FS6%2F0.png?alt=media)

## **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://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FJix7R90fXdfDI9LmsjlZ%2F1.png?alt=media)

* 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://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FEgSY51DuYIDdmrz4NCcZ%2F2.png?alt=media)

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://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FV06PUt4p9H1z3TU13gBD%2F3.png?alt=media)

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://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FwZVi23PiqYNp3x5H2FF2%2F4.png?alt=media)

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/astera-data-stack-v9/data-model/dimensional-modelling/fact-entities).

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/astera-data-stack-v9/data-models/general-entity-properties#entity-properties).

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FPVqn1z0ZnKdgpjxvbCWi%2F5.png?alt=media)

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://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FJU3WzZ9EX2Qg1ZWHxNXX%2F6.png?alt=media)

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://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2F4ykw7uiTzVRDKTcRFHFk%2F7.png?alt=media)

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

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FxkoYBtfbYwDnqGxjP186%2F8.png?alt=media)

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

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/astera-data-stack-v9/data-models/general-entity-properties#entity-properties).

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FXYvX9h3EvBIwuHVQC5ix%2F9.png?alt=media)

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://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FEWO60adNFKPUeKwzm0iL%2F10.png?alt=media)

{% 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.