Guest

• Jun 16th, 2006

 

Hi,

Slow changing dimension is a common problem in Dataware housing. For example: There exists a customer called lisa in a company ABC and she lives in New York. Later she she moved to Florida. The company must modify her address now. In general 3 ways to solve this problem

 

Type 1: The new record replaces the original record, no trace of the old record at all, Type 2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two different people. Type 3: The original record is modified to reflect the changes.

 

In Type1 the new one will over write the existing one that means no history is maintained, History of the person where she stayed last is lost, simple to use.

 

In Type2 New record is added, therefore both the original and the new record Will be present, the new record will get its own primary key, Advantage of using this type2 is, Historical information is maintained But size of the dimension table grows, storage and performance can become a concern.

Type2 should only be used if it is necessary for the data warehouse to track the historical changes.

 

In Type3 there will be 2 columns one to indicate the original value and the other to indicate the current value. example a new column will be added which shows the original address as New york and the current address as Florida. Helps in keeping some part of the history and table size is not increased. But one problem is when the customer moves from Florida to Texas the new york information is lost. so Type 3 should only be used if the changes will only occur for a finite number of time.

 

Hope i made the point clear,

Bye.

shiva

• Jun 29th, 2006

 

it is where the data is to be stored in the intermediate files

ajinkyac

 Profile Answers by ajinkyac 

• Jun 16th, 2007

 

you can use change-capture stage -
This will tell you whether the source record is insert/update/modified after comparing with DWH record and then accordingly you can choose the action

manoharkolukula

 Profile Answers by manoharkolukula Questions by manoharkolukula

• Feb 12th, 2008

 

Hello ,

He is asking about implementation of type2 in ds, u r telling about type1,type2, type3 history, everybody know what are those things. if u know then tell otherewise leave it.

1. we have to use scd,lkp,trasformer stage to implement these I,II,III.

2. If u see the mapping then only u can understand.

3. we have to give changed values, key values, ....

4. we have to give condition in trasformer(constraint),..

Amritendra kumar

 Profile Answers by Amritendra kumar 

• Feb 22nd, 2008

 

shiva is right and he suggested with a great example. You can perform this logic by taking  transformer and hashfile  stage in server job and if you want to perform scd in parellel then you will have to use change capture and change apply stage.