Oracle OLAP Newsletter - February 2009

The latest Oracle OLAP newsletter, February 2009, has been posted onto OTN and is available by clicking here

The customer feature this time is R.L. Polk who have used 11g OLAP to simplify their delivery of aggregate data through the use of cube organised materialised views. This is a fantastic case study which captures the true value of this functionality (note the dramatic improvements in both build and query times), and of having Oracle OLAP embedded in the Oracle Database.

The highlights of the Product Update section this time are the release of the latest version of AWM 11g (11.1.0.7B), and also a new version of the BI Spreadsheet Add-in (10.1.2.3.0.1 - enough digits?!) which now includes support for Excel 2007.

Gartner identifies Oracle OLAP as a key strength in Oracle's DW proposition

You may have already seen elsewhere that Gartner recently published an updated magic quadrant for Data Warehouse Database Management Systems.

It was a little bit surprising to see that the HP Oracle Exadata Storage Server and the HP Oracle Database Machine hadn't provided a bigger boost to Oracle's rating (although, as you would expect, Oracle is still firmly entrenched in the leaders quadrant). Perhaps it is still a little early for their significance to be fully appreciated. There is likely to be a more pronounced improvement in Oracle's rating the next time this magic quadrant is published.

Of particular interest to this blog is the fact that Oracle OLAP is identified as a key strength of Oracle's Data Warehousing proposition. The research highlights the enhanced materialized view and cube management features added in 11g, along with transparent SQL access and incremental update.

It is really great to hear such a significant endorsement of Oracle's strategy to embed the Oracle OLAP engine inside the Oracle Database.

If you want to read more about what 11g OLAP could do for your Oracle Data Warehouse, then check out this white paper

Now Available! Two new Oracle OLAP Demonstrations

Two new Oracle OLAP demonstrations have been added to the Oracle OLAP product page on OTN:

Fast Answers to Tough Questions Using Simple SQL :- Oracle OLAP is a world class analytic engine embedded in the Oracle Database. OLAP Cubes and dimensions are easily accessible thru a star-model. Using very simple SQL, Oracle OLAP delivers fast answers to tough, analytic questions. This demonstration shows how to query OLAP cubes using several tools, including: Oracle Business Intelligence Enterprise Edition, Application Express and SQL Developer.

Transparently Improving Query Performance with Oracle OLAP Cube MVs :- Oracle OLAP cubes may also be deployed as materialized views. Summary queries written to base fact tables can transparently leverage the fast query performance delivered by Oracle OLAP - without any changes to the application's query. The Oracle Optimizer automatically rewrites queries to cubes when appropriate. This demonstration shows how Oracle Business Intelligence Enterprise Edition seamlessly benefits from this capability. The demonstration then provides an "under the covers" view of how this improvement is achieved.

Get hands-on with 11g OLAP

You may have already noticed but over the past couple of weeks some new 11g OLAP training material has been published on the OTN OLAP home page.

Two new tutorials have been added to the popular Oracle By Example (OBE) series.

The first is titled 'Building OLAP 11g Cubes' and covers using Analytic Workspace Manager (AWM) 11g to build and load an OLAP cube.

The second is titled 'Querying OLAP 11g Cubes' and is a guide to querying a cube via SQL, both directly using OLAP Cube Views, and indirectly using Cube Materialized Views.

Supporting both of the tutorials is a new sample schema which gives you the opportunity to get hands-on and experiment in your own environment. Remember, that patch level 11.1.0.7 is required and to always check the recommended release details for your chosen operating system.

New 11g OLAP Cube Materialized Views tutorial posted onto OTN

Another new tutorial has been added to the Oracle OLAP home page on OTN.

This tutorial is titled 'Oracle OLAP 11g: Setting Up Cube Materialized Views for Query Rewrite'

The tutorial describes how to enable cubes as Cube Materialized Views, and how to enable and troubleshoot Query Rewrite using Analytic Workspace Manager 11.1.0.7. It is intended as a quickstart for intermediate developers.

New! Oracle OLAP 11g Oracle University Training Course

A brand new OLAP 11g training class has been added to the Oracle University schedule.

Here is a brief synopsis:

Oracle OLAP 11g, a fully-integrated component of Oracle Database 11g, provides a full featured multidimensional data model and calculation engine that is easily accessible to any SQL based business intelligence application or tool.

In this course, students learn to progressively build an OLAP data model to support a wide range of business intelligence requirements. Students learn to design OLAP cubes to serve as a summary management resource for existing SQL table queries. Students also learn to leverage the power of Oracle OLAP by adding rich analytic content to your data model.

Students learn to create sophisticated reports of OLAP data by using simple SQL queries. Students also create and execute OLAP queries in SQL Developer, Oracle Application Express (APEX), and in Oracle BI Enterprise Edition. Students learn to implement cube security, including how to authorize access to cube data and methods for scoping user views of data. Finally, students learn to design OLAP cubes for performance and scalability.

Learn To:

* Design and create an Oracle OLAP data model
* Enable query rewrite to OLAP Cube MVs for relational summary management
* Easily create OLAP calculations that enrich the analytic content of your data model
* Query OLAP data using simple SQL
* Implement cube security
* Efficiently design cubes for performance and scalability

More details and scheduling information can be found on the Oracle University Website

New article on 11g OLAP Cube-Organised Materialized Views published onto OTN

Oracle ACE Director Arup Nanda has published a series of articles onto OTN covering important new features in Oracle Database 11g titled 'Oracle Database 11g: Top Features for DBAs and Developers'.

The series includes a feature on 'Data Warehousing and OLAP' which looks at how Cube-Organized Materialized Views can be implemented alongside other features to deliver a compelling platform for data warehousing.

With Oracle's data warehousing proposition featured heavily in the news at the moment following the recent announcement at Oracle Open World on the availability of the Exadata Storage Server and Database Machine, this is an excellently timed reminder that Oracle OLAP is a core part of this data warehousing proposition.

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.