August 19, 2008 at 10:17 am
not sure where to start but my company has kicked off a new project for reporting against our data.. the current application is a nightly process that runs and builds a snapshot of the data after flattening down some of the tables using SSIS. this has worked for a while but tge sheer amount of data is making the job run longer every iteration.
In the new scenario:
1. We would be needing to take our current data tables and replicate over the data to the new reporting server in a near real-time
2 While the data is being moved over in real time we are also needing to do some transformations on the data.
3. We would also like to actually capture the deltas for a true historical warehouse as well but that is a different beast all together I fear.
so here are my questions:
1. Any thoughts on what type of replication we should use ( transactional I am leaning to)
2. How can we replicate the data over in real time and also transform it real time as well?
August 19, 2008 at 3:06 pm
Transactional Replication doesnt allow for transformations, so it wont work as a single solution.
Have you thought about a third-party product to do the data movement, such as Informatica / Ab Initio / etc / etc (not that I claim to be an expert on any of these products). Or you could possibly make it a two-phase data movement with A-Synch Mirroring with a Snapshot on the target, and then an SSIS package to take the mirror / snapshot data and load it into your DW.
August 20, 2008 at 9:59 am
we replicate the main db's to reporting servers and then the reporting people run their transformations from those copies and build their own tables, etc.
August 21, 2008 at 9:40 am
By default, transactional replication uses stored procedures to do the individual INSERT/UPDATE/DELETE operations. So, modifying the stored procedures (or specifying different ones) can allow a great deal of flexibility with transformations.
August 21, 2008 at 3:46 pm
Totally agree with Michael above. You can built your custom logic on the MSrepl_* stored procedure scripts. Usually people use post_snapshot scripts to keep stuff easy to setup in case snapshot re-generate these sp.
Just my $0.02
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply