split logic when loading slowly changing dimensions

[former_member660243]

Hi everyone,

I know when loading slowly changing dimensions you load as follows:

source -> Table comparison -> History preseving -> Key generation -> target;

What if now when loading I want the following:

For a changing dimension, I need to do something;

For a historical dimension, I need to do something else;

For a brand new one, I need to do something total new.

How can I achieve this? I want to split my logic and cater for all 3 scenarios and run scripts for each splits.

Thanks and regards,

Edited by: gsecure on Apr 8, 2010 9:07 AM

[Former Member · Accepted solution]

Hi

I always question when people suggest they need to do this, especially with a type 2 SCD.

Whilst you can use the map operation method, you need to use it with caution as multiple instances of the same target table aren't generally a good idea as you can run into concurrency/sequencing issues, as controlling the order of inserts and updates isn't straight-forward.

Can you explain what needs to be done differently for each type of operation? The main reason I ask is that the transforms automatically handle the from/to dates logic and when to generate a surrogate ID.

Michael

[former_member660243 · Accepted solution]

Hi Michael,

The operations I wanted to do are covered by the objects, you are right.

I wanted to have a current flag field which will indicate if a record is old or new.

Thank a lot.

[former_member660243 · Accepted solution]

Hi Michael,

A question though about the current flag indicator column, its only putting new rows as 1 as I have specified but it does not reset old columns to 0 as I wanted it to.

Any ideas.

Kind regards,

[Former Member · Accepted solution]

Hi

The history preservation should be doing what you require, by setting the current indicator for the latest record and changing the previous version. I would check that you have your columns defined correctly in both the table comparison and history preservation.

Michael

[Former Member]

Hi,

If you need to carry out different actions after the history preserve you can use map operations to capture the different type of actions. Add a map operation for each action you want to capture e.g. Insert, Update, etc. However be careful here as the history preserve will provide two records (insert and update) if the table comparison detected an update. In this situation the update is to change the old record and the insert to add the record. Don't forget you will still have normal inserts where the record does not exist at all on the target.

Richard

[former_member660243]

Hi Richard,

If I try to connect the Map Operation to History Preserving I get the following error:

This object <Map_CDC_Operation> requires input row types:[Normal]. But it is receving only two type:[input, Update]

Am not sure how I can use it.

Thanks a lot.

[Former Member]

Hi,

You need to change the settings within the Map Operation before you join it to any target objects. The Map Operation allows you to capture and change the different internal operation codes DI generates (see the manual - Transforms - Operation Codes).

You probably want something like the below:

TC-> Map OP 1 ->QRY -> Target

-> Map OP 2 ->History preserve -> Map OP 3->QRY -> Map OP 4->Target

->Map OP 5-> QRY -> Target

Note: Map Op 1 & 2 are joined to the Table Comparison. Map Op 3 & 5 are joined to the History Preserve

Map OP 1 = Captures Inserts only, set insert row to insert and discard all other types. This will get your normal inserts when a record does not exist.

Map OP 2 = Captures Updates only, set Update row to Update and discard all other types. This will pass only updates to the history preserve.

Map OP 3 = Captures history Preserve update (old record) only, set Update row to Normal and discard all other types. This allow you to change the historical records. Map Op 4 = set normal to Update, discard all other types. This then performs the update on the target.

Map OP 5 = Captures the history preserve insert (new record) only, set insert to Normal and discard all other types.

Richard

Edited by: Richard Saltern on Apr 9, 2010 9:33 AM

Edited by: Richard Saltern on Apr 9, 2010 9:36 AM