February 17, 2011 at 1:24 am
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
February 28, 2011 at 8:05 am
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.
March 1, 2011 at 8:47 am
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.
March 1, 2011 at 9:36 am
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
March 2, 2011 at 5:29 am
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?
October 22, 2012 at 12:33 pm
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!
R
December 4, 2014 at 8:01 am
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
December 5, 2014 at 2:05 am
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. 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply