Skip to main content
cancel
Showing results for 
Search instead 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

Reply
cris007
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. 

BrandStockAVG days in StockStock RotationStock Ratio
A50005,660,4
B400940,72
C1004.550,33
D7003.580,47
E6006.480,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: 

 

BrandAttributeValue
AStock5000
AAVG days in Stock5,6
AStock Rotation6
AStock Ratio0,4
BStock400
BAVG days in Stock9
BStock Rotation4
BStock Ratio0,72
CStock100
CAVG days in Stock45
CStock Rotation5
CStock Ratio0,33
DStock700
DAVG days in Stock35
DStock Rotation8
DStock Ratio0,47
EStock600
EAVG days in Stock64
EStock Rotation8
EStock Ratio0,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
marcosvin
Resolver II
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.

View solution in original post

2 REPLIES 2
cris007
Frequent Visitor

thanks @marcosvin 

marcosvin
Resolver II
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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors