Hello everyone
As I improve my technical techniques, I would like to ask the eDNA community for guidance on what is considered best practice in where to store individual $ related data points - fact or dimension tables.(in this example it is for wholesale and retail unit costs)
I have always left them in the fact table, but I wanted to reduce the redundancy levels in my fact table so I included the unit costs in my product dimension table so went from 14K data points down to 18 relating to unit costs and adjusted the DAX accordingly. The final calculations were the same as you would expect, but it just got me thinking hence reaching out to this fabulous community.
Thank you
Caroline
@Cazvon storing cost in product is common but that would mean storing the current cost, the transactions in the Fact table will be historic, same product will have different costs over time. Keep it in Fact table, that’s where all the numerical/aggregation columns should belong.
Thank you for taking the time to share your expertise. Fact table it is.
Caroline