Thursday, March 13, 2008

Comparing Materialized Views and Analytic Workspaces in Oracle Database 11g

One of the great new features of Oracle OLAP in 11g is the Cube Organized Materialized Views. This feature allows OLAP Analytic Workspaces (AWs) to be included in the Summary Management capabilities of the DB including automatic query rewrite and refresh. This provides the same capabilities of Materialized Views (MVs), a feature which has been part of the database since 8i. Now that 11g supports both AWs and MVs the question of when to use relational materialized views and when to use OLAP AWs becomes more interesting.

Oracle has created a new white paper that explains the shared capabilities and differences of both of these powerful features.

A key benefit of this capability, from the whitepaper, is that "Materialized views are used for pre-computed results for both relational and multi-dimensional data: the cost based optimizer can now rewrite SQL queries to the analytic workspace and materialized views can be defined over the cubes."

This ultimately provides greater capabilities to increase performance and provide robust analytics without requiring the BI tool or SQL programmer to understanding where the summary data is. Performance and advanced analytical functions are provided automatically whenever they exist.

Here is an overview of the materialized view architecture taken from the white paper.



I will be posting more information and examples of how this feature can be used to improve BI and Analytical applications.

3 comments:

Mauricio Leyzaola said...

Hi Brian, thanks for your article. I have a couple of questions I'd like your advice if possible:
1) Enabling Olap MV in Oracle 11g is going to increase performance over traditional MV?
2) What should I need to do in the Oracle DBMS in order to support this new feature?
3) Could I write SQL syntax for retrieving OLAP sentences (YTD calculations e.g.)? Is it fully compatible with current Oracle applications?
These days I'm designing a DWH and this information captured my attention very fast. I would like to know if it is worth to dig into this technology.
Thanks again.

Brian Macdonald said...

First, yes it is worth digging into the OLAP technology in the Oracle DB. It offers a wealth of capabilities that are quite exciting when you learn them.

Here are some answers to your questions.

1) Enabling Olap MV in Oracle 11g is going to increase performance over traditional MV?

Answer: Probably. This is completely application specific though. There are also a few ways to measure what you mean by increase performance? Query performance against the base table/MV? How many MVs are there in total? The load time to build the MV?

Let’s answer the first part. Query performance. If you only have one MV and the MV is fairly small, than the OLAP option and its rewrite capabilities will likely not add any benefit to performance. In both cases you should get results back very fast and neither route would be better.

But the true value comes in when you want you consider all the queries that will be going at the tables. Think about what happens if a query does not match a MV that is created? Than you need to aggregate the data at query time and your performance will be slow and inconsistent. So you can create another MV for this scenario. This will add to your management overhead. As you continue to add more MVs you will get to the point where the optimizer will require more time to figure out where to get the data from, than actually retrieving the data. In this situation the OLAP option will now be faster.

The AW in the OLAP option is modeled to account for all permutations of data at all levels of the hierarchies. And this is all stored in a single Aw. So management requirements are dramatically reduced. Refresh times are reduced. And the ability to find the right data is dramatically faster.

So my general recommendation is if you have a few queries that are always the same and the table will not be used for a lot of ad-hoc queries, than MVs are a good solution. If you need to address a wide range of users and applications, than the AW in the OLAP option would be the right choice.

2) What should I need to do in the Oracle DBMS in order to support this new feature?

Answer: The OLAP option is already set up in a typical install of 11g, so from that perspective you do not need to do anything. To build your in the Analytic Workspace (AW) and then have that re-write enabled, you will need to use the Analytical Workspace Manager (AWM) to do the following steps.

1) Build a dimensional model
2) Map the dimensional model to the source tables/views.
3) Check some boxes to enable re-write
4) Start querying.

It is fairly straight forward, but you do need to build the AW first. Keith Laker has several posts on how to build AWs on this blog. A good place to start is

http://oracleolap.blogspot.com/2007/12/olap-workshop-1-basic-olap-concepts.html


3) Could I write SQL syntax for retrieving OLAP sentences (YTD calculations e.g.)? Is it fully compatible with current Oracle applications?

You can use SQL to retrieve the data from the OLAP cubes. You can either use the SQL analytic functions or you can create the calculations in the AW itself. Here are examples of two queries that would do exactly the same thing.

This query calls the YTD calculation that exists in the AW.

select t.long_description, s.sales, s.sales_YTD
from units_cube_view s, time_calendar_view t
where channel = 'TOTAL'
and customer= 'TOTAL'
and product = 'TOTAL'
and s.time = t.dim_key
and level_name = 'MONTH';

This query calculates YTD based on the result set returned using analytic SQL functions.

select t.long_description, s.sales , sum(s.sales) over (partition by calendar_year order by T.dim_key) ytd
from units_cube_view s, time_calendar_view t
where channel = 'TOTAL'
and customer= 'TOTAL'
and product = 'TOTAL'
and s.time = t.dim_key
and level_name = 'MONTH';

Access to the AW is done via SQL and thus would be supported by any application. The query rewrite feature is only available in Oracle 11g, so you should make sure that the application is certified on 11g.

Mauricio Leyzaola said...

Thank you Brian, you gave me such good clues to begin with. I will give it a try in my testing enviroment.
Your blog contains very interesting information, keep it up.

Cheers!