MS Replicated table to a staging table.

[STLDROCK]

I need some help designing a technical document for our upcoming ETL package.

The current system transactionally replicates a table from the Transactional System to a Reporting environment.

Now we want to start using MS Intergration Services to load Dimensions from views from the replicated table.

The problem is the view only catches the Delta between the times that it is run since there is not a transaction key.

The solution I would like to implement would be to add a transaction_id to a copy of the destination table. Basicly, replicating the table to two spots and putting a transaction_key_id on one of the tables. I thought this was a feature of replication, but I do not seem to see it. Other ideas I've had are putting insert/update triggers on the destination table to insert to another table, or adding an insert to in the MS_rep procs.

I'm real curious on how people are doing this.

Thank you,
Derek Sigler

 

[johnherman]

Your situation is a common one. You have a choice of a snapshot of changes (between views) or capturing every change.

Your solution should depend on user requirements. If user requirements are not well known, go for the one which has the bigger bang for the buck. If you need a quick victory, use the views, if you need the flexibility for the future, you will need to have the each transaction delta/change, and that may require triggers or some other change to the transaction system.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[STLDROCK]

I'll answer my own question. Hopefully others will get something out of it. Here's what we have done.

First we made duplicate articles in the publication. One article points to the exact same table like we have always done. The second points to a Staging table. For example we have the contracts table in the transactional DB replicating to contracts in Replication and replicating to "Staging_Contracts" in the subscribing DB.

We then added an identity and a timestamp to the staging table.

Last we modified the update, insert and delete replication procs for the staging article.

seems to work fairly well. Hopefully with this we'll be able to get all changed for our slowly changing dimensions.

Thanks,
derek.

If anyone has comments please share them!