April 12, 2012 at 6:05 am
Hi,
I am trying to decide on what is the best approach (fastest & easiest to maintain) for shifting data from staging DB to a Data Warehouse.
My situation is like this:
I have a staging DB, which contains a table that regularly gets data inserted into it (through our ETL process), the data from this table has to be merged with one with the Data Warehouse DBs (its a scale-out scenario, so depending on the "client id" value data will end up in a different Data Warehouse DBs).
I need to merge the content of this table (in the stagingDB) with the data that already exists in the Data Warehouse, and then empty the staging table (in order to start receiving new data).
There is no transformation that happens during this process, data in stagingDB is inserted if its new, or updated if it already exists in the Data Warehouse DB.
I was originally going for Transactional Replication, since data is almost instantly replicated, and I can truncate the table on the StagingDB regularly, not replicate DELETEs on articles, and not initialize from snapshot (so data doesn't get deleted in the data-warehouse). But am now thinking this might be an overkill in terms of management, especially since there will always be only 1 subscriber per publication. Am also worried that I missed an option in replication and it might cause all my data in the Datawarehouse DB table to get deleted/truncated (due to a forced snapshot situation or something)
Any thoughts you have are much appreciated.
Thanks!
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
April 12, 2012 at 6:14 am
SSIS has slowly changing dimensions for just this type of scenario.
That said, which one you should use is what works best for your business. All three options are valid options, though most people will say "SSIS" as an automatic reaction to the question.
April 12, 2012 at 6:24 am
Thanks for the quick reply Brandie!
Thats a pretty good suggestion, I'v not used this transformation before, interestingly (and by name) it seems like its used for Dimension tables, although am trying to move Fact data, but I don't see any reason not to use it since it delivers the required functionality. (currently am using Transaction Replication for replicating Dimensions because there are many subscribers, including multiple Data Marts.)
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
April 12, 2012 at 7:03 am
Try it out.
Then write an article for SSC reviewing the process, your steps, and what you think of it. @=)
Or just try it out and let us know if that did or did not work for what you need.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply