Applying slowly changing dimension type 2

[RudiHendrix]

Hello experts,

I got a new challenge... So far nobody has been able to help me out or point me to best practices or tell me if it even is remotely possible.

(My apologies for the long question)
In our DWH we've got a customer dimension with a customer (CUST_ID), natural key (NK), parent name, name, hierarchy key (HK) field.
The parent is always added as a child of itself as well. The hierarchy key displays the natural key of the parent (if applicable).

CUST_ID  NK     PARENT_NAME    NAME          HK
1            101    SAM                 JUDITH        103
2            102    SAM                 GEORGE       103
3            103    SAM                 SAM            0
4            104    PETE                SUSAN         106
5            105    PETE                RAY             106
6            106    PETE                PETE            0

Next to that we have a date dimension
DATE_ID   MONTH
1             012009
2             022009
3             032009

And we have a sales fact
PK_ID   CUST_ID   DATE_ID   AMOUNT
1         1             1             50
2         2             1             100
3         2             2             150
4         4             1             50
5         4             2             50

If we run some reports on that situation we get
Parent
SAM
PETE

Parent Name
SAM    JUDITH
SAM    GEORGE
SAM    SAM
PETE   SUSAN
PETE   RAY
PETE   PETE

Parent   Amount
SAM      300
PETE     100

Parent  Name     Amount
SAM     JUDITH   50
SAM     GEORGE  250
PETE    SUSAN    100

Parent  Name      Date       Amount
SAM     JUDITH    012009   50
SAM     GEORGE   012009   100
SAM     GEORGE   022009   150
PETE    SUSAN     012009   50
PETE    SUSAN     022009   50

NOW THE CHALLENGE KICKS IN!
The Customer dimension is a slowly changing dimension type 2

So let's say: from March 2009 onwards the customer parent SAM is known as SAM1
The dimension changes as follows:
CUST_ID  NK     PARENT_NAME    NAME          HK
1            101    SAM                 JUDITH        103
2            102    SAM                 GEORGE       103
3            103    SAM                 SAM            0
4            104    PETE                SUSAN         106
5            105    PETE                RAY             106
6            106    PETE                PETE            0
7            101    SAM1               JUDITH         103
8            102    SAM1               GEORGE        103
9            103    SAM1               SAM1           0

So, 3 new records are added for SAM1. SAM1 will have the same natural key as SAM. Thus the "new" child records will also have the same HK.

Let's add some facts for March as well. The new fact record will be:
PK_ID   CUST_ID   DATE_ID   AMOUNT
1         1             1             50
2         2             1             100
3         2             2             150
4         4             1             50
5         4             2             50
6         7             3             100
7         8             3             150
8         5             3             200

Let's pull up the same "reports"
Parent
SAM1
PETE

Parent   Name
SAM1    JUDITH
SAM1    GEORGE
SAM1    SAM1
PETE     SUSAN
PETE     RAY
PETE     PETE

Parent   Amount
SAM1     550
PETE     300

Parent  Name    Amount
SAM1   JUDITH  150
SAM1   GEORGE 400
PETE    SUSAN  100
PETE    RAY      200

Parent  Name      Date       Amount
SAM     JUDITH    012009   50
SAM     GEORGE   012009   100
SAM     GEORGE   022009   150
PETE    SUSAN     012009   50
PETE    SUSAN     022009   50
SAM1   JUDITH     032009   100
SAM1   GEORGE    032009   150
PETE    RAY         032009   200

Now the question is: HOW CAN THIS BE MODELLED IN COGNOS FRAMEWORK MANAGER?

[twlarsen]

Hey,

Maybe I'm not following right, but this part doesn't sound like SCD Type 2

"Parent   Amount
SAM1     550
PETE     300

Parent  Name    Amount
SAM1   JUDITH  150
SAM1   GEORGE 400
PETE    SUSAN  100
PETE    RAY      200"

By having only SAM1 in there, you are showing the reference to current dimension data, so this is a SCD Type 1.  If you want it easily done in Cognos, your business needs to decide to only use SCD1 or SCD2.  Since they want a hybrid, I'd be making some changes to how your data is set up otherwise it's going to be a fun time for the report writers.

[RudiHendrix]

Thanks! This is what I was thinking too... I think it is strange that, although it is KNOWN that information is specific to SAM it is being displayed as part of SAM1. (At least within the framework of SCD2 this seems strange to me.)

Since they want a hybrid, I'd be making some changes to how your data is set up otherwise it's going to be a fun time for the report writers.

Yes... I AM the report writer as well :(

[twlarsen]

I'm wondering if you could do a modified SCD2.  Have a field that says Current Parent Name.  This would be SAM1 for all SAM or SAM1 records.  Your ETL would have to go and update previous records each time a parent name is updated, but it would make it easy for the reports.

[RudiHendrix]

In that case I would create an additional level to display only the current name. That could be a possibility.

Fortunately I have been able to convince people to use the solution where we display what is known. (So no mixing of SCD types)