Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is this part of product dimension Expand / Collapse
Author
Message
Posted Tuesday, May 27, 2014 12:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 4:12 AM
Points: 79, Visits: 461
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
Post #1574931
Posted Tuesday, May 27, 2014 12:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 13,238, Visits: 11,016
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1574937
Posted Tuesday, May 27, 2014 3:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 4:12 AM
Points: 79, Visits: 461
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?
Post #1575003
Posted Wednesday, May 28, 2014 12:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 13,238, Visits: 11,016
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1575318
Posted Thursday, May 29, 2014 2:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 4:12 AM
Points: 79, Visits: 461
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

Post #1575473
Posted Thursday, May 29, 2014 4:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 13,238, Visits: 11,016
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1575509
Posted Thursday, May 29, 2014 7:25 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 8:10 PM
Points: 672, Visits: 6,756
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.
Post #1575571
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse