Need SCD 2 and SCD 1 logic help

jagadam · Post by **jagadam** » Fri Nov 04, 2011 11:33 am

Hi,

We have designed SCD-2 model maintaing the history in dimension tables. Now a requirement came up to include current value for the attributes. ( SCD-1). For this we are planning to add new attribute for each and every SCD-2 attribute to the dimension table so that the current job maintains the scd-2 history and have to create another process to update the new attributes with current value ( CURR_IND='Y) on business keys.

Can i achieve this with in the same job instead of creating post process step for each dimension?Please throw some inputs on better way of desigining.

Thanks in Advance

nani1974 · Post by **nani1974** » Fri Nov 04, 2011 2:19 pm

Hi,
As per my knowledge you can do both in SCD stage..

Kumar.

ray.wurlod · Post by **ray.wurlod** » Fri Nov 04, 2011 3:35 pm

SCD-1 is not "include a current value for the attribute". SCD-1 is "update the dimension record with the new value". In SCD-1 there is only ever a current value - SCD-1 does not preserve history.

The SCD stage does allow you to generate an IsCurrent indicator field when SCD-2 is in play.

vmcburney · Post by **vmcburney** » Sun Nov 06, 2011 4:58 pm

I think you are getting confused between the SCD types. Type 1 means you have no history in that dimension at all - so current flag is irrelevant. Type 2 means you have current values and historic values. You can include a current flag and it remains type 2 but it makes it easier to find the current values. If you want a dimension row with two attributes - one with the historic dimension value and one with the current value then you are into Type 3. Two sets of attributes that describe the same thing.

What you describe will be either standard Type 2 or modified Type 3 functionality - as long as the requirement to keep history is retained.