Forum Discussion
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
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
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.
