Forum Discussion
Fact table modeling best practices
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.
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.