Koen Verbeeck (5/29/2014)
ps_vbdev (5/29/2014)
Thanks KoenHow do you know what and when something should be in the dim tables or fact, this is what im find hard as just starting in DW, just experience i guss? Was reading your blog post on less than dot some nice articles :0
Thanks.
If it is something you measure (and typically changes over time), it is a fact.
If it is purely informational (a descriptive attribute), it goes into the dimension.
Also measures are something you aggregate.
A fact table contains sales, which I would assume each record has an Item, Price, Qty, Surcharge, Extended Price and Actual Sales Amount.
You are describing margin, which it sounds like will vary by Retailer or Individual invoice line.
Dimensions are what the users slice the data by.
Part of the modeling also might depend on how the data is being used.
Price and Surcharge could be an attribute, and may change over time, for each individual item (Designer, Color, Size).
This could be used when loading the fact table for each Sale Fact to compare.
Modeling data can be somewhat of an art.