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


Example SCD type 2


Example SCD type 2

Author
Message
CriticalRationalist
CriticalRationalist
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 70
Hi,

Has anybody an example of how to use Slowly Changing Dimensions type 2 in SSAS?
There are lots of examples of how to build them with SSIS, this is not what I'm looking for.
I would like to see how they are used and interpreted in the SSAS cube. With a simple example.

thanks,
Jan
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4555 Visits: 72513
Me too,

I managed to get one plugged into a cube, but the results from it don't seem any different than when the dimension was a type 1 SCD. Attributes used in calculations seem to still just use the current one even though older measures related to the older type 2 SCD value.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
RonKyle
RonKyle
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2159 Visits: 3437
What exactly are you looking for? I have a couple of dimensions that use SCD 2, but they are by definition not simple (compared to SCD 1 in any case) and aside from telling you how I did it, I'm not sure what else you'd want to know.



CriticalRationalist
CriticalRationalist
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 70
Mr & Ms 500,

Well, I have seen the datamodel used for SCD 2 (for example: http://blogs.microsoft.co.il/blogs/barbaro/archive/2007/09/08/Slowly-Changing-Dimension-type-2-in-SSAS.aspx)

But how is the Dimension defined? Which attributes do we define for this example?
And how is it consulted then?

Sure I'm missing something that seems obvious for others. But that can be hard to find w00t

thanks
Jan
RonKyle
RonKyle
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2159 Visits: 3437
I'm sorry, but you're still not providing enough information for me to give you an answer that might even remotely be what you are looking for.

Let me give an example, and then you can tell me if that's what you need or further tell me why it's not what you need.

SCD2 changes are used when a change to one or more items found in the dimension are so important in gathering the statistics that they are worth differenting from the previous value. THere is not an inherent correct answer in this; it depends on your business requirements.

If I have a dimension tracking field offices, I will have information such as location, size, manager, and so on. Let's assume that the name of the city in which the store is in changes (unrealistic I know), that change should not affect anything about the officefrom my business perspective. If the manager were to change, however, that is important (to me). Using the SCD to make a new OFFICE entry with the new manager will be necessary. This entry will be exactly the same as the old office entry except for its table generated unique number and the manager value. All subsequent fact table entries regarding that office will be posted against the new office entry. This makes it easy to compare the performance of the store under different managers.

There is no requirement that it be this way. In your business you may determine that a change of manager is not worth tracking, but the change of city name is worth tracking. This is a business decision.

Does that help? If not, why not?



Rafael H
Rafael H
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 140
Hi Ron.

Great subject as I am now in the same boat. Your example simply describes what SCD-2 is. So great, let's follow it. Let's assume that there is a fact table using the dimension in question and let slice it just by that dim. Writing a star-query in SQL would allow me to do two things:
1-easy. Create a list of transactions (events) along with their dimensional attributes as they were at the time a transaction occurred. So
Manger_A with EmpID=10 (sold) $5
Manager_A_changed with EmpID=10 (sold) $20
Total is $25
2-harder. Using some SQL and/or Dim design trickery I can get the same $25 showing as either for Manager_A, or Manager_A_changed. In other words a query sliced by a dimension as of some point in time.

So the question is how can one do that in SSAS?

A somewhat easier option is to slice by the Name (NOT the EmpID) defaulting to a specific version, and a harder option is to allow users to choose the version of a row in an SCD. Let's assume that a date is a suitable criterion for selecting a version, e.g. first day of a fiscal calendar. And, of course, how can I get option 1 above, i.e. slice by name and date get two rows, one for each incarnation of Manager A?

Thank you!


Kindest Regards,

R
hennie7863
hennie7863
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 3421
Running into this issue too... I was curieus how to implement a SCD2 dimension in SSAS? For instance I want to compare Revenue with an organisation dimension in old and a new structure. Offcourse, better is a type 3 but it's just an example...

I was looking for an example where a query is something like between start and enddate in MDX, for instance...

Greetz,
Hennie
PB_BI
PB_BI
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1808 Visits: 2490
I've done this before in SSAS.

Basically, all of your SCD 2 logic should be in your source data - so your surrogate keys in your facts map to the appropriate surrogate keys in your dimension so you get the correct attributes. These are then reflected in the Dimension Usage in your cube.

In the dimension itself you have to set up the SCD type 2 attributes as specific types. So in BIDS (or SSDT if you're a bit modern) in the dimension designer click on an attribute. In the properties for Type choose Other then Changing and then one of the following: SCDEndDate, SCDOriginalID, SCDStartDate or SCDStatus. Obviously choose the appropriate one for your SCD attributes. That covers for things like performance (for more on why you should be using attribute types, see your local search engine).

The following article covers more things you need to do (setting up appropriate attribute relationships and such) but doesn't include what I have just mentioned, otherwise I would have just linked to it. http://msbiworld.wordpress.com/2011/05/23/slowly-changing-dimension-type-ii-scd-ii-in-ssas-dimension/

I hope that this helps you folks. :-)




I'm on LinkedIn
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