July 27, 2012 at 7:17 am
what would be the best way to publish changes in a large fact table including deletes?
we create one in SQL Server environment and need to publish once it is processed to a few remote servers - one of them is Oracle.
the reason why we need to delete from it, because source system is not very reliable so we often need to go back and reload data.
July 27, 2012 at 8:47 am
If the destination is a SQL Server db, you cannot beat a MERGE statement in T-SQL for performance.
As for the Oracle thing, SSIS would be your best bet. Do a lookup to your destination and then compare it to your data using a conditional split component (You would have outputs for new, changed and deleted). Connect the outputs to either a destination component (for the new ones) or a sql component (for the updates/deletes). This is a bit of a row by row solution but works marginally better than the out of the box SCD component. There is also the Kimball SCD component which is almost as quick as MERGE, but it involves third party software and sometimes you will be governed by a policy that doesn't allow this. As it's oracle you will need some kind of driver on your machine in order to read/write data.
Hope this helps 🙂
July 27, 2012 at 10:29 am
thanks,yayomayn, for an explanation. This is what I was going to do but I am still puzzled how to handle occasional deletes to a fact table. Any ideas? my fact table is going to be pretty long
August 2, 2012 at 4:32 am
Hi,
Sorry for the delayed reply, work has been mad.
It's a difficult one to do unless you are loading in a full table each time (and not doing anything incrementally). If this is the case then create an additional source from your destination table and do a lookup to your source. If any of the keys in our destination do not exist in your source (this can be achieved via a conditional split) then use a sql component to delete them (or retire them with a flag, whichever suits). It's a bit dirty but if you have a source system that allows physical deletes then you're a bit stuck with it. :crazy:
August 2, 2012 at 6:31 am
thanks, I appreciate your help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply