December 23, 2009 at 1:38 am
Hi,
I have a new, call it a "reporting" database based on an existing OLTP database. This reporting database is a slimmed down version of the main db & currently using SSIS to copy & transform the data needed.
The SSIS package transforms +-40 tables into 7 with a extremely flat structure...
This works well & without any issues! The problem I'm facing though, is that this secondary database HAS to be as up to date as the primary database, almost realtime. Scheduling the package to run periodically is not quick enough.
I also tried setting up replication to "staging" tables with triggers that does the transformation of data... The down side is that there's a lot of code to maintain & I sometimes run into foreign key errors...
Anyone have any ideas on how I can pick up the data, transform it & insert it into the live reporting tables as quickly as possible?
Thanks in advance!
December 28, 2009 at 2:11 pm
Replication
December 28, 2009 at 3:08 pm
If low latency is essential then what's the reason for having two databases? Maybe you could just make sufficient bandwidth into the OLTP database so that it can be used for reporting as well.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply