# 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-v10/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://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fbib2AvktNMRZUtXm1mVW%2F01-Reverse-Engineered-Database.png?alt=media\&token=73025274-8702-404b-bbc3-a5a5d6f27094)

### 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://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Ftw8Uj1VaVvHEI3yi5d65%2F02-Right-Click-Change-Entity-Type.png?alt=media\&token=84ec99e5-0797-4f4f-99e7-74fa89eae049)

* 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://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FRbrLNGhh3NW4n4gsbAGa%2F03-Entity-Property-Window-Screen.png?alt=media\&token=6db40ede-d536-48dc-aea2-eba60fc1c41f)

​ 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://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FDSDVOfMu2yyAHDEJiCU1%2F04-General-Entity-to-Fact-Entity.png?alt=media\&token=ae98cc5f-0f62-4acf-ae4e-bad271ffc719)

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://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FEWkbX5Yc25mQSFoZVkEo%2F05-Fact-Layout-Builder.png?alt=media\&token=aaadf137-e9ff-45f6-aff8-6674d6de9616)

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

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FyrXlVeueJqTAGnmYmCzR%2F06-Fact-Entity-Indexes.png?alt=media\&token=2437c2a6-5d2f-4a5e-9026-7f962a5b190b)

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://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FJw6NuHzOj6wEL1IJv0fE%2F07-Dimension-Entity-Properties.png?alt=media\&token=31876128-47f2-405d-b348-15381e1b1b78)

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://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FtPkyoBoJ3K6OGOlwPiBK%2F08-Dimension-Layout-Builder.png?alt=media\&token=98f32483-4508-456f-b2c8-940d1cc14fe7)

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

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fwh3AOqibUdVnZ13ElqMR%2F09-Dimension-Role-Options.png?alt=media\&token=5f03ccd2-ae9f-439b-b835-fc597fa67beb)

To learn more about Dimension Roles, click [here](https://documentation.astera.com/astera-data-stack-v10/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/astera-data-stack-v10/data-models/general-entity-properties#data-model-entity-indexes).

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FtbJmRSYCzSyAYcGulaj6%2F10-Dimension-Entity-Indexes.png?alt=media\&token=f4a985bf-f336-487c-b5dc-7b890320cd18)

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://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fh8oY8QCSzJW2bGEEvZZZ%2F11-Dimensional-Model.png?alt=media\&token=baeaa045-c971-472c-ae9b-9e10e27813b7)

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