DM SCD Type 2 function in the conformed dimension and its operation in the fact.

[CAPP]

Hi,
Defining the SCD Type 2 in the dimension build of DM seems to be straight forward with the use of Track changes and selecting the column(s) to track and the resulting dimension table from the Dim Build shows the changes thru' time via the new rows with new values for SKey, Start_date, End_date, Updated_date and Curr_ind columns based on incoming dimension data from staging.
However, the conformed dimension definition using a select of each column from the created dimension table seems to only capture the newest values for each Business Key (ie those with Curr_ind = Y), at least, that is what is displayed in the Reference Explorer of DM, with the SKey of that current row included, but there are more than one SKey value for the employee (eg dimension hierarchy of employee level, and department level. Employee moves from one department to another and SCD Type 2 is used to keep history) .
But most importantly the fact (It is this conformed definition that has been used as the referenced Dimension in any fact build via the transformation model mapping facility), seems to only match on dimension rows where the Curr_ind = Y.
Therefore I would be very grateful for some input to explain the method to use to provide both the old and new SKeys in the fact.

Many thanks before hand
:)

[MFGF]

You are referring to processing Late Arriving Facts - ie fact rows whose dates indicate that they refer to rows other than the current row in the dimension table.

To be able to process late arriving facts, you need to make a couple of changes:

1. In the lookup based on your dimension table, you need to bring in the business key, the surrogate key and the effective date attribute from your dimension. The lookup should be populated using template access not datastream access.
2. In the properties of the dimensional build element in the Transformation Model of your Fact build, you need to go to the Late Arriving Facts tab and enable Late Arriving Fact processing. You then need to select the column used as the date in your Fact build, and exception behaviour for either null date values being processed or out-of-range dates being processed.

Regards,

MF.

[CAPP]

Hi MF,

Yes - I have the classic case of an employee moving from one department to another, which has been successfully captured in the dimension, and so I found out that I should use the Late Arriving Facts (LAF) as the data does have earlier dates than the current in the dimension (ie for the earlier dates I want to use the earlier SK, based on he has moved once so far) for his BK.

As per the instructions in the LAF section of the userguide for IBM Cognos Data Manager v8.4.1, I created a Lookup with the 3 columns as you pointed out, which opened up the LAF tab in the reference dimension properties in the fact. I have also set up, in the LAF tab, what to choose in the event the data source date is out of range (ie earlier in time than the start_date in the dimension rows for him).

However, I am having trouble with matching. So I would very much appreciate a bit more guidance / tips in this area if possible.

Normally (not SCD handling) I would be offering the fact the BK of the employee in the staging from the data source, and the fact would resolve the current SK in the transformation, but now it needs to know which SK to use, and for the lookup to hold all the SKs available I seem to have to make the SK the ID. So can you enlighten on how the match will work in theory and  the steps I need to take make it work.

Finally, I also tried to achieve  the dimensional history setup to open the dimensional history options tab but this also seems illusive. All the steps I have taken regarding effective start date etc have been done but no luck so far.Can you give tips as to how to get this open  and is this a necessity for LAF to operate in a correct manner.

Thank you for the reply so far. It means I am on the right track, at least.

CAPP

[MFGF]

Hi,

The issue is in making the surrogate key the ID of the lookup. You need the ID of the lookup to be the business key not the surrogate key. The fact that you are also bringing in an attribute with a behaviour of Effective Date in the input template will mean that each employee member will have multiple instances with multiple surrogate keys and effective dates, but the same business key. This is the one situation where the ID of the lookup is repeated. Confusingly, the reference explorer does not show multiple member instances - you just see the last member for each employee when you explore. Don't be fooled though - the members are all there and can be referenced by the fact build. The date you identify in the fact build will be used to target which historical member (and therefore which surrogate key) to tie in to.

Regards,

MF.

[CAPP]

Hi MF,

I adjusted the SK to BK for the lookup id and used same column "start_date" (set up as effective start date) in the template for the dim build and the template for the lookup, plus for the fact FAL process, the choose closest SK worked for when the transaction date was out of range.

Now I have full SCD working

Please accept a big thank you.

CAPP

[MFGF]

Great news!  You're welcome, and thanks for the update :)

MF.