Convert mademade table columns to dimension table.

[Syndicate_Admin]

Good

I have an order table which has approximately 60 columns.

Applying the concept of normalization / denormalization, what I intend is to separate the different columns into several dimension tables. So I would leave in a table of facts the base information and in the tables of dimensions auxiliary information.

The question is very simple but it generates doubts.

To create these column dimension tables that come out of the fact table, what is the most recommended option?

The option I have chosen is to upload to power BI the table of raw facts and from this "double" as many times as you need.

In these duplicate tables I will remove the columns I don't need or even populate the tables with more information. This is how I will create the dimension tables.

Every time I update the fact table with new information will I update the rest of the duplicate tables?

Is this the best way to convert fact columns into dimension tables?

Thank you for your help.

Best regards.

[Syndicate_Admin]

Thanks for the reply. Regarding the comment of creating static dimension tables. The truth is that I would like that. Create static dimension tables. But in some cases the problem I'm going to have is that updating those dimension tables is going to come to me because of the fact table. For example: a new customer. I'll find out when I come through the fact table. So in this case the solution would be to create the dimension table through the duplicate of the facts right? Because this way I make sure that the dimension table is updated automatically. That's right?

[DataVitalizer]

To prevent the dimension tables from getting updated everytime the fact table does, you can uncheck 'Include in report refresh' for Dims, as I mentioned previously it depends on the frequency of having new values that should be added to the Dimension Table

Another scenario would be creating dataflows to have the ability of configuring independent refreshes for your different tables and the refresh will run cloud-side.

Did it work ?  ‌‌ Mark it as a solution to help spreading knowledge
Was this exchange helpful? A kudos 👍 would be appreciated

[DataVitalizer]

Hi @Syndicate_Admin 
The recommended approach is to duplictae the Fact table to create separate dimension tables, from the fact table remove the columns used in Dimension tablels and keep only those which can be used as foreign keys to keep the link between them.

Most of time dimension tables don't get updated frequently, in this case I could create static Dimension tables instead of duplicating the Fact one many times, depends on your use case. 

Did it work ?  ‌‌ Mark it as a solution to help spreading knowledge
Was this exchange helpful? A kudos 👍 would be appreciated