cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Fact table modeling best practices

Hi all,

I would like to have some advices regarding best practices creating a fact table:

I have an Excel file with a pivot table that looks like below.

 Brand Stock AVG days in Stock Stock Rotation Stock Ratio A 5000 5,6 6 0,4 B 400 9 4 0,72 C 100 4.5 5 0,33 D 700 3.5 8 0,47 E 600 6.4 8 0,87

The file only concerns stock values.

The columns stock, avg days in stock and stock rotation are numbers. The column stock ratio should be expressed as a percentage on the front end.

My question is the following:

In Power BI , should I create one fact table and leave it as it is? so one column per value type.

Or should I unpivot it and put Stock, AVG days in Stock, Stock Rotation, Stock Ratio in the same column? So it would look as below:

 Brand Attribute Value A Stock 5000 A AVG days in Stock 5,6 A Stock Rotation 6 A Stock Ratio 0,4 B Stock 400 B AVG days in Stock 9 B Stock Rotation 4 B Stock Ratio 0,72 C Stock 100 C AVG days in Stock 45 C Stock Rotation 5 C Stock Ratio 0,33 D Stock 700 D AVG days in Stock 35 D Stock Rotation 8 D Stock Ratio 0,47 E Stock 600 E AVG days in Stock 64 E Stock Rotation 8 E Stock Ratio 0,87

Or should I get the Stock Ratio in a separeted fact table since it is a percentage?

Thanks for the help

1 ACCEPTED SOLUTION
Resolver II

Hello!

Conceptually, your fact table only needs to have the raw data, all the others that need to be calculated can be created as measures in your Power BI model, for example. Thinking about the best modeling practices, I don't see the need to do this unpivot, I think your fact table can have a structure similar to your pivot table (one column for each value). The "AVG days in stock" and "Stock Ratio" columns, if they have a calculation formula that uses other fields from the same table, would not need to be in the fact table. It may be interesting to also have a reference date column, to know which day this stock position refers to.

2 REPLIES 2
Frequent Visitor

thanks @marcosvin

Resolver II

Hello!

Conceptually, your fact table only needs to have the raw data, all the others that need to be calculated can be created as measures in your Power BI model, for example. Thinking about the best modeling practices, I don't see the need to do this unpivot, I think your fact table can have a structure similar to your pivot table (one column for each value). The "AVG days in stock" and "Stock Ratio" columns, if they have a calculation formula that uses other fields from the same table, would not need to be in the fact table. It may be interesting to also have a reference date column, to know which day this stock position refers to.