Is this part of product dimension

Question

Is this part of product dimension

ps_vbdev

SSCarpal Tunnel

Points: 4060
More actions
May 27, 2014 at 12:51 pm

#293820

Im building a product dimension of all the dresses designers can sell. each dress can have multiple sizes and colors a cost price and rrp price and also a size surcharge, so size 14+ could be an extra £30. these are cost designers pass on to the retailer as they dont sell direct to the public, in effect they sell to the retailer
retailers also then have the ability to apply a sale price (their mark up) to the dress and then also a size surcharge.
my question is, should these sale price and retailer set surcharges be in included in the product dimention.
if not how would i calculate the actual sale price to compare it the cost price etc.
thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Personally, I would store the prices themselves in the fact table, unless it is fixed for a certain color and size.

If it changes too often, definately in the fact table. If it changes infrequently, you can keep it in the dimension as a slowly changing attribute.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ps_vbdev SSCarpal Tunnel Points: 4060 More actions · Answer 2

So the designers prices and surcharges would go in the product dimension as they are fixed im sure when the product is created. But would pull the the retailers prices and surcharges through into the orders fact table when an order is placed as this is in effect the actual price of the product.

this would then give you visibility of both the designers prices and retailers prices. Yes?

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

ps_vbdev (5/27/2014)
So the designers prices and surcharges would go in the product dimension as they are fixed im sure when the product is created. But would pull the the retailers prices and surcharges through into the orders fact table when an order is placed as this is in effect the actual price of the product.
this would then give you visibility of both the designers prices and retailers prices. Yes?

Yes.

The prices in the dimension are informational though, you cannot aggregate them. If that is a requirement, you need to turn them into measures.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ps_vbdev SSCarpal Tunnel Points: 4060 More actions · Answer 4

Thanks Koen

How 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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

ps_vbdev (5/29/2014)
Thanks Koen
How 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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 6

Koen Verbeeck (5/29/2014)
ps_vbdev (5/29/2014)
Thanks Koen
How 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.