Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

publish fact tables Expand / Collapse
Author
Message
Posted Friday, July 27, 2012 7:17 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:54 AM
Points: 24, Visits: 222
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.
Post #1336484
Posted Friday, July 27, 2012 8:47 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:50 AM
Points: 635, Visits: 1,213
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





I'm on LinkedIn
Post #1336558
Posted Friday, July 27, 2012 10:29 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:54 AM
Points: 24, Visits: 222
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
Post #1336640
Posted Thursday, August 2, 2012 4:32 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:50 AM
Points: 635, Visits: 1,213
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.





I'm on LinkedIn
Post #1339048
Posted Thursday, August 2, 2012 6:31 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:54 AM
Points: 24, Visits: 222
thanks, I appreciate your help!
Post #1339091
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse