Slowly Changing Dimension Stage

ray.wurlod · Post by **ray.wurlod** » Tue Oct 04, 2011 3:12 pm

You should never have deletes in a Type 2 dimension.

The SCD stage does not behave in the way you require. It generates a single output for the dimension table, which is typically set to Upsert.

You can do what you require with Change Capture, Compare or Difference stage followed by Transformer stage.

venkatvelpula · Post by **venkatvelpula** » Tue Oct 04, 2011 3:28 pm

ray.wurlod · Post by **ray.wurlod** » Tue Oct 04, 2011 4:37 pm

The other output ends up populating the fact table. It has (typically) the surrogate key of the dimension table and does not have the business key. It also, obviously, has the measures. A job to populate a star schema with four dimensions might look like this:

Code: Select all

        +------+    +------+    +------+    +------+
        |      |    |      |    |      |    |      |
        | Dim1 |    | Dim2 |    | Dim3 |    | Dim4 |
        |      |    |      |    |      |    |      |
        +------+    +------+    +------+    +------+
           |           |           |           |
           |           |           |           |
           V           V           V           V
        +------+    +------+    +------+    +------+         +--------+
        |      |    |      |    |      |    |      |         |        |
---->   | SCD1 |    | SCD2 |    | SCD3 |    | SCD4 | ----->  |  Fact  |
        |      |    |      |    |      |    |      |         |        |
        +------+    +------+    +------+    +------+         +--------+
           |           |           |           |
           |           |           |           |
           V           V           V           V
        +------+    +------+    +------+    +------+
        |      |    |      |    |      |    |      |
        | Dim1 |    | Dim2 |    | Dim3 |    | Dim4 |
        |      |    |      |    |      |    |      |
        +------+    +------+    +------+    +------+

venkatvelpula · Post by **venkatvelpula** » Wed Oct 05, 2011 2:17 pm

ray.wurlod · Post by **ray.wurlod** » Wed Oct 05, 2011 3:29 pm

That's the point - you don't have to. However I am sure you could devise logic to add a field to the link that updates the dimension table if you really wanted to - or to a copy of that stream.

venkatvelpula · Post by **venkatvelpula** » Thu Oct 06, 2011 11:04 am

ray.wurlod · Post by **ray.wurlod** » Thu Oct 06, 2011 3:08 pm

In an ideal world, surrogate keys and measures.