SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Slow Changing Dimension Question


Slow Changing Dimension Question

Author
Message
imani_technology
imani_technology
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 Visits: 777
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?
SpringTownDBA
SpringTownDBA
Mr or Mrs. 500
Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)

Group: General Forum Members
Points: 590 Visits: 1499
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.
richykong
richykong
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 621
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)
imani_technology
imani_technology
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 Visits: 777
Thank you for your response. I am inclined to put the price in a dimension, specifically a type 2 SCD.
imani_technology
imani_technology
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 Visits: 777
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?
imani_technology
imani_technology
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 Visits: 777
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.
Thomas LeBlanc
Thomas LeBlanc
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4068 Visits: 906
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/
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