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.
SCD Type 1/2 mix - how to test for types of update
Moderators: chulett, rschirm, roy
Re: SCD Type 1/2 mix - how to test for types of update
Trying to create messy / complexity.
Your type 1 and 2 are going to be in two different table?
DS User
Your type 1 and 2 are going to be in two different table?
DS User
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).
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).
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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!)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
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.rameshrr3 wrote:All I had to use this was a combination of Lookup and Transformer.
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
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