• 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