July 16, 2003 at 9:25 pm
I have an OLTP data model that I want to be the publisher. The subscriber will be a de-normalized version of that data model. Thus two different schemas.
I want to transactionally replicate from the OLTP model to the de-normalized model.
Is this possible?
How is this accomplished?
July 17, 2003 at 5:08 am
Should be possible. One way would be to run a DTS transform as part of replication (I know its there, but I've never tried it), another would be to replicate the data normally, then put triggers on the subscriber to handle the denormalization.
Andy
July 17, 2003 at 8:15 am
how would triggers handle the denormalization?
can you elaborate please.
July 22, 2003 at 3:13 am
How many tables are involved..? If you have many tables involved in the denormalising process say a customer details table identified by a customerID in your main table then the transformation may be more difficult.
You would have to replicate into normalised tables on the subscriber then every time your main non normalised table is updated or an insert is applied on the subscriber run a trigger to merge the information into a new denormalised table with all the extra columns required for say customer addresses in this example.
If the information is not time sensitive you could write a DTS task to merge the data into the one denormalised table periodically regardless of updates. If there are many many updates expected it may be wiser to use a DTS task periodically.
I have never run a DTS task as part of the replication process so I cant help you there.
Rolf
July 23, 2003 at 6:37 am
You could also do it by using custom replication stored procedures, or modified versions of the ones automatically created by replication.
To do this create replication as per usual pretending you are fully replicating all tables to like tables, then edit the subscriber side auto-generated replication procedures to put the data in to the desired schema.
ie. Customer table is replicated to Customer and replication will create the stored procedures for this table on the subscriber (sp_msins_customer, sp_msupd_customer, sp_msdel_customer). You then deploy modified versions of these procs to the subscriber that spread the customer data to your denormalised schema.
This means that each time you rebuild replication from scratch you need to reapply your modified procs before replication starts.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply