Search code examples
ssasscdscd2

SSAS & SCD2 - how to deal with IsActive row in Dim

I am using SQL Server 2014 and Visual Studio 2015.
I have an SCD2 for staff names, for example 

SK     AltKey       Name            Gender    IsActive
1      15           Sven Svensson    M        1
2      16           Jo Jonsson       M        1

and in the fact table

SK      AgentSK      CallDuration    DateKey
100     1            335             20160808
101     2            235             20160809

So, you can see the cube is currently linked on FctAgentSK and DimSK. This works as planned. However, when Jo changes gender the SCD2 makes the row inactive (0) and inserts a new row with the new gender and IsActive of '1'.

The problem I face is that the factSK 101 still references the 'OLD' details for the Agent. How should I deal with this to be able to still report on the call, but also reference the "correct" details of the Agent - reflecting their current gender.

When a new fact is inserted it will have the 'NEW' SK assigned, but basically I would need to report on ALL calls that have happened either side of the gender change.

Any suggestions please?

Thank you.

Solution

  • As Nick.McDermaid suggested, if you don't want SCD2 functionality, you could remove it from the dimension design (I've often seen it over-implemented when it's not actually wanted: perhaps you've inherited that kind of setup?).

    If you want to/must keep the SCD2 design, but want to report on current staff attributes (gender and any other SCD2 attributes).

    1. Kimball documents a "Type 6" here: SCD types 0,4,5,6,7. You add a "current" value of the attribute to an existing Type2 design. You could then report on the "current" attributes only.

    2. I'm assuming that the Staff Name "Alt Key" is the durable staff-member key, that stays the same through changes in staff attributes? You could make a slightly different Employee dimension (or, hierarchy inside the Employee dimension), that has Alt Key as its leaf-level key. If you don't still have SK as a dimension attribute, this will make the dimension table "collapse" into one member per AltKey, not one member per SK. Obviously, you can't add any SCD2 attributes to this Alt Key hierarchy, as there won't be a single value per key; and this raises special problems about what to call the durable "employee" (i.e. what the Name Column of the leaf level will be), since Employee Name is one of the most obvious SCD2 attributes that will not remain the same. Probably this approach is best combined with an underlying "Type6" inclusion of the "current value" in the dimension data, as described in (1) above.