| Reporting schema suggestions [message #485700] |
Thu, 09 December 2010 09:14  |
rolex.mp
Messages: 161
Registered: February 2007
|
Senior Member |
|
|
I am in the process of designing a 11gR2 OLAP database on RHEL 5.
The requirement is that we would be receiving around 9G of data everyday from the OLTP database. This would involve around 10-15 tables and number of rows that we would be receiving per table would be around 5 million. These would be 99.9% inserts and the remaining would be updates. Deletes are very rare.
In this case I was thinking of using Streams or CDC for transferring data from OLTP to OLAP database. These data would be in a raw format which would need to be converted to reporting format, so I was thinking of using a staging area for ETL and then the final structure(presentation area) would use a dimensional model(star schema).
The tables would contain 2 years of data and most of the reports would look for a day's or month's worth of data. So I need to store almost 4-5TB of data in the database.
I was initially thinking of purging the streams destional tables on the reporting database every day and then using CTAS to populate the staging area and then use ETL mechanism to populate the presentation area.
Now there is a change in requirement and they want to do real time reporting which means that the delay in reports can be oply upto 2 mins. Now I'm stuck here as I am not sure whether the streams and the ETL overhead would allow this. Also the model that I have in mind seems like wouldn't be the appropriate one.
Any suggestions on what can be done to accommodate the real time reporting needs?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Reporting schema suggestions [message #486128 is a reply to message #486123] |
Mon, 13 December 2010 15:25  |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
rolex.mp wrote on Thu, 09 December 2010 10:14I am in the process of designing a 11gR2 OLAP database on RHEL 5.
...
Now there is a change in requirement and they want to do real time reporting which means that the delay in reports can be oply upto 2 mins. Now I'm stuck here as I am not sure whether the streams and the ETL overhead would allow this. Also the model that I have in mind seems like wouldn't be the appropriate one.
Any suggestions on what can be done to accommodate the real time reporting needs?
Suggestion is Materialized views and could also be Streams.
I will NOT copy and paste what can be found in the fine Oracle® manuals.
[Updated on: Mon, 13 December 2010 23:30] by Moderator Report message to a moderator |
|
|
|
| Re: Reporting schema suggestions [message #486130 is a reply to message #486128] |
Mon, 13 December 2010 16:21 |
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
How on earth did you get the idea that Michel is asking you to copy the docs?
He is asking you to explain how MV's are relevant to this particular problem (which is blatantly clear from his last message).
As for the further the suggestion of streams - You have actually read the original post haven't you?
|
|
|
|
| Re: Reporting schema suggestions [message #486269 is a reply to message #486130] |
Tue, 14 December 2010 10:05 |
|
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
rolex.mp (SUMMARY)I am in the process of designing a 11gR2 OLAP database on RHEL 5.
The original requirement is:
1) Receive around 9G of data everyday
2) From 10-15 tables
3) 5 million rows
4) 99.9% inserts and the rest updates. Deletes are very rare.
Change in requirement:
Real time reporting with up to 2 mins. delay
Any suggestions on what can be done to accommodate the real time reporting needs?
In order to try and accomplish the "2 min" reporting requirement, My suggestion was to base the solution either on Materialized Views and/or Streams.
As per why MV's are relevant? Maybe you need to review again all the benefits stated in the fine Oracle® Database Advanced Replication manual.
[Updated on: Tue, 14 December 2010 11:12] by Moderator Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
