SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


publish fact tables


publish fact tables

Author
Message
boriskey
boriskey
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 256
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.
PB_BI
PB_BI
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1991 Visits: 2519
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
boriskey
boriskey
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 256
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
PB_BI
PB_BI
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1991 Visits: 2519
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




I'm on LinkedIn
boriskey
boriskey
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 256
thanks, I appreciate your help!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search