Replicate OLTP data to OLAP datawarehouse

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan - 03 Apr 2008 16:02 GMT

I'm looking at replicating data from our OLTP database to a separate
SQL server for purposes of reporting on.
It needs to be real-time so i'm trying to use transactional
replication.

The data i'm replicating needs to be denormalized into my OLAP data
warehouse. What's the best way to do that?
Should i use DTS to transform the data, or use a Custom Sync Object
with a View?

For example, my OLTP database has the following tables

Client
--------
ClientId
BusinessName
ClientStatusId
ClientTypeId
ParentId,
etc....

ClientStatuses
---------------------
ClientStatusId
ClientStatus

ClientTypes
-----------------
ClientTypeId
ClientType

Now i need to replicate my Clients to my OLAP database, which has been
denormalized as follows:-

Clients
----------
ClientId
ClientName
ClientStatus
ClientType
ParentName

Can people recommend the best way to do this in real-time?

Many thanks

Dan

Reply to this Message

Paul Ibison - 04 Apr 2008 10:12 GMT

You won't get real-time with replication, but let's say you mean near
real-time. I know there are a few alternatives here, but in that case my
preference is to replicate exactly as is. This is the simplest means of
maintenance. On the subscriber you can have views - indexed or not - which
reprresent the denormalised data to the client applications.
HTH,
Paul Ibison (www.replicationanswers.com)

Reply to this Message