I'm setting up a new DWH and now we're working on the templates for filling dimensions. What we now have is that everything is coming in daily(no delta's but complete tables), and we transform this into the same format as the dimension table.
At this point we need to do 3 actions
-check for inserts: all records in input and not in dwh
-check for updates : all records in input for which we find the key in dwh, and for which relevant columns have changed
-check for deletes:records in dwh that are not in the inputs anymore.
When going trough the documentation of DS7.5 I've found 2 stagetypes which seem to be ideal for this, but with which I'm not familiar: the difference-stage and the compare stage.
Would it be a wise setup to use the following as template:
-create 2 ordered sets:one for the input, one for the current status of the dimension.
-use a difference with input as after and dwh as before : this will give the inserts
-use a compare :this will give us the updates
-use a difference with dwh as after and input as before: this will give us the deletes
main advantage I see is that this can easily be reused, main disadvantage seems the large amount of data we'll need to process.
Any one else any idea's?
dwh-setup using compare and difference-stages
Moderators: chulett, rschirm, roy
thanx for the hint, but when I try this it seems harder to get information for deleted records.
In the compare stage I can easily log some info from the before record for the ones that are not in the after image (which can make it easier to do the correct updates for this delete). this looks more difficult from the change capture (or am i missing something?)
In the compare stage I can easily log some info from the before record for the ones that are not in the after image (which can make it easier to do the correct updates for this delete). this looks more difficult from the change capture (or am i missing something?)
Hi Jasper,
We are doing exactly what you want.
Pls search this forum. I have already discussed this in detail.
HTH
--Rich
Pride comes before a fall
Humility comes before honour
We are doing exactly what you want.
As mandy suggested earlier we are using the change capture stage and it works like a charm.Jasper wrote:At this point we need to do 3 actions
-check for inserts: all records in input and not in dwh
-check for updates : all records in input for which we find the key in dwh, and for which relevant columns have changed
-check for deletes:records in dwh that are not in the inputs anymore.
Pls search this forum. I have already discussed this in detail.
HTH
--Rich
Pride comes before a fall
Humility comes before honour