Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is this part of product dimension


Is this part of product dimension

Author
Message
ps_vbdev
ps_vbdev
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 704
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
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16469 Visits: 13207
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
ps_vbdev
ps_vbdev
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 704
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
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16469 Visits: 13207
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
ps_vbdev
ps_vbdev
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 704
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
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16469 Visits: 13207
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Greg Edwards-268690
Greg Edwards-268690
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 8267
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search