Business Reporting on an OLTP Application

Question

We have an OLTP application using Oracle Database 10g Enterprise Edition, and plan to build a business reporting layer to meet the following needs.

• Sheilding complexity of the current OLTP database design
• Improving query performance of the current OLTP reports
• Providing read-only access to other applications
• Allowing business users to perform adhoc reporting

The solution we are thinking of is to create a DB cache layer using Oracle Materialized Views(MV) over the current OLTP. MV's would be denormalized and designed for reporting. MV log's would synchronize changes to the MV using incremental refresh.

My questions are,

1. Does this approach make sense (MV's)? Has anyone used MV's for building OLTP reporting solutions?
2. What are the drawbacks of this approach(MV)?
3. How about using Oracle CDC and tables, with procedures to perform the synchronize.
4. Any other approaches?

Thank you, Sherry

Answer 1

In general, I would be thinking of either a view layer or a materialized view layer for reporting users. My preference unless there are concrete performance issues would be to go with views with an eye to creating occasional materialized views that use query rewrite to speed up selected reports.

If you use materialized views, you'll obviously be materializing the data a second time but in a format that is going to result in less efficient storage. That means that most of the space in the system will be allocated to the denormalized materialized views and their indexes. That can generate a pretty hefty bill from your disk vendor and can create contention for SAN resources.

Materialized views also mean more competition for cache space between OLTP and reporting users. Since they're stored in different objects, reports that are looking for recent activity won't be able to benefit from hot blocks in the cache from the OLTP activity and vice versa. You can mitigate this problem by throwing RAM at it or by moving reporting to non-peak times but it's not the most efficient solution. If you have almost exclusively historical reporting, this probably isn't a big deal-- there would be no sharing anyway because the processes are interested in completely different blocks-- but if you have a lot of operational reporting, it becomes significant.

Materialized views are also likely to be less flexible. If you want to present the same data multiple ways, it's materializing it multiple times runs up real costs both in disk and cache as well as increasing the time required to do the periodic refresh of your materialized view layer. In practice, that tends to mean that reporting users get a least common denominator view of the data and have to re-invent the wheel when they slice and dice the data because IT doesn't want to create a new materialized view for them.

As I said earlier, my preference would be a regular view layer. That avoids the cost of storing the data multiple times and makes it possible to share blocks in the cache between OLTP and reporting queries. It also makes it relatively easy to give users different views of the data and eliminates the need to keep business users informed about how stale the data they're reporting on is. If and when performance becomes an issue because the OLTP data model doesn't support the sorts of queries you want to run, you can create targetted materialized views that act like indexes via query rewrite. That means that users can query regular views and the DBA can later add a materialized view that generates all or part of the result and the optimizer can change the query plan to use that new materialized view rather than scanning the table(s) and doing things like aggregating data at runtime.

At some point, you'll likely want to move the reporting traffic to hit a real data warehouse with a more dimensional data model. If you find that you really need the performance of a materialized view layer rather than a regular view layer, I'd be strongly thinking about going to a real data warehouse with facts and dimensions. You'll get something that is much more flexible for reporting with basically the same ETL headaches that you'd likely get with a complete materialized view layer.