SCD Stage Clarifications

ray.wurlod · Post by **ray.wurlod** » Fri Jan 13, 2012 3:11 pm

The model is, essentially:

                              |
                              |  Dimension
                              V
            -------------> SCD Stage --------------->
                              |           Fact
                              |
                              |  Dimension
                              V

The input has new data, including the business key. The business key is looked up against the Dimension table to return the corresponding surrogate key. This determines whether the dimension row yet exists and, if so, whether it is changed, so that the SCD part can be done. The Fact output includes the surrogate key value (whether generated or looked up) because it has to be a foreign key in the fact table.

venkatvelpula · Post by **venkatvelpula** » Mon Jan 16, 2012 9:05 am

ray.wurlod · Post by **ray.wurlod** » Mon Jan 16, 2012 3:00 pm

Easily fixed. Get yourself a premium membership. Then you will be able to read the entirety of Premium Posters' contributions. It's not expensive, less than 30c (Rs12) per day.

vmcburney · Post by **vmcburney** » Mon Jan 16, 2012 7:38 pm

I usually separate my dimension jobs from the fact jobs so when using the SCD stage to update a dimension I write the fact rows to a copy stage with no outputs to dispose of those rows. I then process just the dimension output rows. When most data marts have 10-12 dimensions it can be very difficult to create a single DataStage that updates all dimensions and the fact table in one pass - the job becomes very large and complex and harder to support. So I use a bunch of dimension jobs with SCD stages and a single fact job.