Slowly Changing Dimension Stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 80
- Joined: Sat Mar 18, 2006 10:27 pm
Slowly Changing Dimension Stage
aaaa
Last edited by venkatvelpula on Tue Jan 24, 2012 8:42 pm, edited 3 times in total.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 80
- Joined: Sat Mar 18, 2006 10:27 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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 |
| | | | | | | |
+------+ +------+ +------+ +------+
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 80
- Joined: Sat Mar 18, 2006 10:27 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 80
- Joined: Sat Mar 18, 2006 10:27 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: