scd stage

bachi · Post by **bachi** » Tue Aug 12, 2008 6:47 am

Hi all

plz give me some brief information about scd stage and mainly i want to know about expiration date and output links

Jasti · Post by **Jasti** » Tue Aug 12, 2008 7:07 am

The SCD stage reads source data on the input link, performs a dimension table
lookup on the reference link, and writes data on the output link. The output link
can pass data to another SCD stage, to a different type of processing stage, or to
a fact table. The dimension update link is a separate output link that carries
changes for the dimension. You can perform these steps in a single job or a
series of jobs, depending on the number of dimensions in your database and
your performance requirements.

SCD stages support both SCD Type 1 and SCD Type 2 processing as follows:
SCD Type 1
Overwrites an attribute in a dimension table.
SCD Type 2
Updates the existing row to indicate it expired and adds a new row to the
dimension table.

Please Refer to the Documentation, you will find more info. there

chulett · Post by **chulett** » Tue Aug 12, 2008 7:12 am

Why not point them to the proper place, mention the actual document and location rather than copy/paste a portion of it and then make generic mention of "the documentation".

Jasti · Post by **Jasti** » Tue Aug 12, 2008 7:45 am

I have the Draft Document on IBM InfoSphere DataStage Data Flow and Job Design..In that SCD stage is described in IBM InfoSphere DataStage stages(Chapter 2)-->Slowly Changing Dimension

bachi · Post by **bachi** » Tue Aug 12, 2008 8:01 am

thnaks

but it give 2 o/p links one contains updated data and other contains source data or inserted data and one more thing is iam getting in dim_update surrogate key as 34,41,56,.... plz help

chulett · Post by **chulett** » Tue Aug 12, 2008 8:05 am

Please help how? You haven't asked a question or clarified what issue you believe you are having.

Jasti · Post by **Jasti** » Tue Aug 12, 2008 9:28 am

Try this ...
You have 2 output links from SCD stage.One is for inserts(say link1) and the second is for Updates(link2).You can design the job to have two instances of target table one is for inserts and the second is for updates. Connect link 1 to targettable(insert) via a surrogatekeygen. stage(if required). and connect link2 to targettable(update).. and provide the proper insert and update sqls.

it give 2 o/p links one contains updated data and other contains source data or inserted data and one more thing is iam getting in dim_update surrogate key as 34,41,56,.... plz help

You need to use surrogate key in the where clause of the update satement...I guess..
I am not sure as I have never worked on the SCD stage

ray.wurlod · Post by **ray.wurlod** » Tue Aug 12, 2008 3:18 pm

For one dimension table:

Code: Select all

           DimTable
               |
               |
               V
     ----> SCDStage ----> FactTable
               |
               | 
               V
           DimTable