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

Slow Changing Dimension Question Expand / Collapse
Author
Message
Posted Thursday, August 9, 2012 4:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
I think I already know the answer to this question, but I want to double check.

We have a Suggested Retail Price (SRP). The SRP changes over time, depending on factors such as the time of the year. So there is a starting effective date and an ending effective date for an SRP.

This sounds to me like a Type 2 SCD. However, some might consider a price to be a measure. Which is it?
Post #1343067
Posted Thursday, August 9, 2012 10:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
It goes in the dimension. Sometimes prices act as a measure as well, but typically if you don't aggegrate them, they aren't measures.
Post #1343134
Posted Tuesday, August 14, 2012 1:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 8:51 AM
Points: 132, Visits: 581
The answer really depends on how that data is going to be used.

A. If you are planning on doing analysis based on price, you would need it in the dimension.
B. If you need to do aggregations on the price, then you'd want it in the fact.

If you need both, then I see two ways to deal with it.

1. Do both A and B. Having the price stored in the fact allows quicker aggregations when you need it.
2. Do A and create a calculation to derive the aggregation when you need it. This can also be done on the SSAS side through a calculated measure. (QTY * Item.Price)
Post #1344973
Posted Tuesday, August 21, 2012 10:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
Thank you for your response. I am inclined to put the price in a dimension, specifically a type 2 SCD.
Post #1347935
Posted Thursday, October 4, 2012 10:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
The project was temporarily put on hold, but now that it is back in play I have another question.

Yes, I think the SRP (Suggested Retail Price) column should be part of a type 2 SCD. However, is it possible to create a formula in SSAS based on the SRP in the SCD?
Post #1368577
Posted Thursday, October 4, 2012 12:25 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
For more information, the SCD might include the following attributes. This columns are from the source data.

SKU
Item Name
UPC
Adjustment Date
Current SRP
New SRP

Again, I'm wondering if we can create formulas in SSAS based off of dimension attributes.
Post #1368625
Posted Friday, October 5, 2012 8:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:48 PM
Points: 3,099, Visits: 788
A similar question is in this link. scroll down to a followup question where the answer shows some MDX

http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/3a93bff6-0e5f-4e7f-9b6c-686f08ad73fa



Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Post #1369072
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse