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

Example SCD type 2 Expand / Collapse
Author
Message
Posted Thursday, February 17, 2011 1:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 25, 2011 6:58 AM
Points: 16, 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
Post #1065481
Posted Monday, February 28, 2011 8:05 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: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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
Post #1070545
Posted Tuesday, March 1, 2011 8:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:11 PM
Points: 817, Visits: 2,059
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.


Post #1071331
Posted Tuesday, March 1, 2011 9:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 25, 2011 6:58 AM
Points: 16, 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

thanks
Jan
Post #1071364
Posted Wednesday, March 2, 2011 5:29 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:11 PM
Points: 817, Visits: 2,059
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?



Post #1071844
Posted Monday, October 22, 2012 12:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 10:56 AM
Points: 12, Visits: 131
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
Post #1375672
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse