Home » Server Options » Streams & AQ » OLTP to OLAP data transfer - CDC or Streams?
OLTP to OLAP data transfer - CDC or Streams? [message #484850] Fri, 03 December 2010 03:25 Go to next message
Messages: 161
Registered: February 2007
Senior Member
We are in the design phase of building an OLAP database for reporting purpose. We would be using 11gR2 2 node RAC on Linux.

The data would be fed from a OLTP database(around 6 tables). We would be using similar table structure for our OLAP database. All these tables would be having primary keys. Amount of data would be around 8G per day which would involve few million records.

I was having a look at asynchronous hotlog CDC and Streams. Both were good for transferring data across databases. What I was planning was to use the tables (which would receive data from OLTP database) to hold 1 day worth of data and then transfer the data to staging dictionaries for transformation purposes and then populate data into partitioned tables which would hold data for 24 months(partitioned by month) to serve reports which will be using 24 months of data. So the tables which would be receiving data from OLTP databases would be purged every day(after the records are transferred to staging area). Data older than these would be archived into separate partitioned tables which would be used for running adhoc queries for regulatory purposes.

The latency between OLTP and OLAP database should be less than 2 mins, thats why I was not looking at materialized views. Also if I purge the table and if records are updated or deleted, will I be able to get those data(change records) though the table is empty?
In this case, does it mean once I get the data through streams or CDC, based on primary key values I need to do a comparison with the actual table data before updating/deleting the appropriate rows?

Am I in the right path?
Which of these (Streams or CDC) would be better suited for this approach?
I am not sure whether I have provided all the details, if I have missed any please let me know.
Re: OLTP to OLAP data transfer - CDC or Streams? [message #486964 is a reply to message #484850] Tue, 21 December 2010 11:05 Go to previous message
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Asynchronous hotlog CDC is the better options, which gives lesser latency across the database.
Previous Topic: QMNC problem
Next Topic: 10gR2 Streams Configuration error
Goto Forum:

Current Time: Sun May 26 06:22:23 CDT 2024