Slowly Changing Dimensions (SCD)

[seppy]

How do I load the different types of Slowly Changing Dimensions (SCD) using DataStage 7.x? I want to be doing incremental loads (or updates) based on the new data (mostly about shipment dates) that is obtained daily without necessarily re-loading the whole target table every time?

[dssiddu]

hi

there r 3 types in SCD

1-type1 -overwrite the data
2-type 2-again 3 types
2.1-timestamp-if time and date is there in the data to maintain histry
2.2-version-if no time and date
2.3-flag-same as 2.2

3-type 3-maintain only privious histry.

select any one sutable for u r applicatiion.

[Andal]

Hi Seepy,

Design your job as such it will write the max(Shipment Date) to a Hash file at the end of every run, and make your source sql which will fetch the datas greater than the shipment date which you stored in the Hash file.

Care Should be taken while updating the source records as such it will update the shipment_date, Only then the modified records will be fetched in the next run.

[ray.wurlod]

Beware, no matter which type of SCD you choose to implement, that you heed the possibility that other applications may also update tables in the target. You need to check, as part of the preliminaries of each (daily?) run, the next available key value.

[Andal]

A nice article from Vincent McBurney on incremental loads is Here