DSXchange

I am implementing a load of a dimension that is a mixture of Type 1 and Type 2. The behaviour I need to implement is slightly different than the standard behaviour provided by the SCD stage. Therefore I would like to do some post processing based on the type of the update that is being made.

I can figure our which records are Type 2 expired ones - the current indicator would be 'N', but for the records with the current indicator 'Y I can't find a way to distinguish between records that are the new versions for Type 2, for just Type 1 updates to the existing record.

Have someone caome across the similar problem and can suggest the solution? Otherwise I will have to replace SCD stage and use Change Capture and Transformer stages instead.

Trying to create messy / complexity.

Your type 1 and 2 are going to be in two different table?

DS User

Are you sure you've got all of your terminology straight? I've never seen (and can't really imagine) a dimension with any kind of "mixture" of SCD types.

Yes, This is one and the same dimension.
It is possible to have a dimesion that will have Type 1 updates when changes are detected in some columns and Type 2 updates when changes are detected when changes are in some other columns.
SCD stage allows for that and DataStage Data Flow and Job Design.pdf contains such an example (see Figure 2-94 on page 123).

I trust those examples are for column level and not for row level.

I never heard to have type 1 and 2 in a same table.

Best of luck.

DS User

Example: customer dimension. Name is Type 2, telephone number is Type 1. You never want to call people using their old phone number, but you do need to know previous name information. (This example may not be pertinent to a telco!)

Ok, I see what is meant by that. I'd be suprised if the SCD stage supports a mixture like that, however.

Yes, it does. It works for me fine. However the Effective Date in my dimensions need to behave a bit differently to the standard.
I could handle it post SCD stage, given I can sufficiently identify the records.

How big is your dimension table? - consider if not a full reload is faster and easier.

Can't do reload as I need to keep the history of changes. That's why it's Type 2.

well... my assumption were that you had your history availiable in your EDW and that it was only a dimension table in a DM you were trying to populate.

All I had to use this was a combination of Lookup and Transformer. The lookup dataset will only be created with the CURRENTLY ACTIVE records from the database. I've used 3 Transformer Variables and directed the Output Links As Follows

1. Expire Old Record If Type 2 Update Detected. ( Update only End Date and Actiev Indicator column )
2. Insert New Record OR Insert Type 2 Changed Record ( outputs to Surrogate Key Stage) - and optionally to dataset.
3. Update Record ( Type 1 Update ONLY) - if No Type 2 attributes have changed. ( can be direct to Dimension or DB)

Type 2 change indicator is a stage variable that compares all Type 2 Fields.

Alternate implementations can also use Change Capture Stages to detect Changes in Type 2 attributes, and can work as well.

I would not use Join Stage If dimension reference was small enough to use lookup . If Join was used , My updates and inserts would be staged in a dataset to achieve source and target isolation for the Dimension Table.

In my case it was a Product Dimension Table , with Hierarachy Info Columns ( Type 2) , and other static attributes ( Type 1) . Source Data was clean because an MDM ( Hyperion-DRM) was used to maintain product hierarchies.

rameshrr3 wrote:All I had to use this was a combination of Lookup and Transformer.

I my previous projects I was using Change Capture and Transformer, but since Datastage offers SCD stage specifically for that purpose (i.e. handling of slowly changing dimensions) I wanted to use that.
But it looks I might have to go back to using CC and Transformer. Pity .

SCD Stage can be used , but IBM says there are bugs in some of the dot versions of 8.0***.

SCD stage theoretically has the facility to handle a mixed SCD dimension where only SOME attributes are type 2 and rest are Type 1, but So far I haven't used it , I can try this if I get some extra time .

That is evident from the PURPOSE Codes