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?
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.
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.)
Quote from: twlarsen on 13 Nov 2009 10:27:08 AM
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 :(
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.
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)