What is the Oracle OLAP Option?

Oracle OLAP, an option to Oracle Database Enterprise Edition, provides valuable insight into business operations and markets using features previously found only in specialized OLAP databases. Because Oracle OLAP is fully integrated into the relational database, all data and metadata is stored and managed from within Oracle Database providing superior scalability, a robust management environment, and industrial-strength availability and security.
Oracle OLAP Option enables companies to easily gain insights into business performance. Oracle OLAP makes this possible by providing:

• Simple user model that reflects business usage
• Exceptional query, calculation and data preparation performance
• Open access to both SQL and multidimensional tool

To understand whether the OLAP Option to the Oracle Database can benefit your customers as k the following questions:

• Do your customers use BI tools such as Business Objects, Cognos, Microstrategy or Oracle BI EE?
• Would users of these applications benefit from significantly improved query performance?
• Would users benefit from richer analytic content being made available within these applications?
• Would business users benefit from the ability to update data sets more frequently? Would the IT department benefit from the ability to update data sets more frequently?
• Would organisations benefit from the ability to manage data and calculations centerally in the database and share these assets across any number of different applications and tools?
• Does IT currently support a stand-alone multi-dimensional database to deliver a dimensional query experience, improve query performance and rich analytic content to business users?

If the answer to any of the above questions is Yes, the OLAP Option has the potential to improve your business intelligence solutions with OLAP and summary management capabilities embedded into the Oracle Database’

For more information on the benefits of OLAP data models see the following white paper: Benefits of a Multi-dimensional Model

Oracle OLAP Option Content

The Oracle OLAP Option is a costed option for the Enterprise Edition of the database. A free to download design client is provied called Analytic Workspace Manager to help create and manage Oracle OLAP content. In addition, the standard elements of Oracle Warehouse Builder that are shipped free with 10gR2 database also support the creation and management of OLAP content. Data within the OLAP Option can be accessed using a number of business intelligence tools, such as:

• Multi-dimensional accees (10gR2 access only)
• Discoverer for OLAP
• OLAP Spreadsheet Addin
• BI Beans
• Reports OLAP Plugin
• SQL Access (both 10gR2 and 11g)
• Business Objects
• Microstrategy
• Cognos
• Oracle Application Express
• Oracle BI EE
• Oracle BI Publisher
• All other applications and/or tools that use SQL

Configuration guides for both multi-dimensional and SQL based access methods are provided on the OLAP Option Home Page on OTN: http://www.oracle.com/technology/products/bi/olap/olap.html. Look for the links to the following topics:

• Improve SQL Based Business Intelligence Tools with Oracle OLAP 11g
• Leveraging Business Intelligence Tools
  

Oracle OLAP Option Architecture

There are three primary parts to an OLAP Solution:

• OLAP Engine
• Design/ETL Clients

OLAP Engine
The Oracle OLAP Option originally started life over 35 years ago as the ground breaking multi-dimensional engine called Express. Oracle OLAP Option is the multidimensional engine embedded in Oracle Database. It includes a powerful dimensional data model that:

• simplifies the task of defining calculations
• expressing queries
• supports advanced multidimensional calculations
• supports advanced planning functions
• provides a transaction model suitable for what-if analysis and modelling.

It is also known for excellent query response times – and all Oracle OLAP data is accessible through SQL. Because the Oracle OLAP Option is a native part of Oracle Database, it leverage's the scalable, secure and resilient platform of the Oracle Database. If you have the Enterprise Edition then you have the OLAP option available.

The engine is based around four key areas:

• OLAP Cubes
• SQL Interface
• OLAP API
• Cube Organised MVs

Cubes
Cubes are a key part of the OLAP option. They provide data storage, fast update, fast query, analytic calculations and (along with the Oracle Data Dictionary) meta data. OLAP Cubes provide support for a wide variety of of calculations including hierarchically aware time series, shares, indicies, financial functions, models, non-additive aggregations (varying by dimension), statistical functions, statistical forecasting and allocations.

SQL Interface
The SQL interface to multi-dimensional data types presents cubes and dimensions as relational views and allows them to be queried by SQL based applications. Both stored and calculated measures, at detail and summary levels, are revealed as columns in cube views. SQL based applications can query complex calculations and data models that have been defined in the cube simply by querying a fact column.

OLAP API
This API has two purposes. Firstly to define and maintain the cube and it’s related dimensions, including mapping those objects to source relational tables. Secondly, the OLAP API can be used to query cubes using a multi-dimensional context.

Cube- Organised Materialised Views
This is a new feature of OLAP 11g and they are materialised views that can be to manage the periodic refresh of cubes and dimensions as well as allowing SQL based applications to transparently use the cube as a summary management feature.

Design ETL Clients
Two design/ETL clients support the OLAP Option:

• Anlaytic Workspace Manager (AWM)
• Warehouse Builder (OWB)

AWM is aimed at business users who want to quickly model one or a series of OLAP cubes. The main limitation with AWM is the lack of a transformation engine since the tool assumes the source data is correctly formatted and ready for use by AWM. AWM does expose the full power of the OLAP engine by providing easy access to the rich analytic functions available within the engine. For more information about AWM see the following white paper:
Analytic Workspace Manager 10g

OWB is an enterprise level ETL tool that supports both relational and multi-dimensional designs. It contains many of the features of AWM but does lack access to some of the more sophisticated calculation and data modelling features. For more information on how to manage OLAP data models using OWB see the following white paper:
Oracle Warehouse Builder 10gR2 and Oracle OLAP

Training

Training Both a classroom based and on-line training is available for Oracle OLAP. For details on formal instructor led and web based training please visit the Oracle University BI Web Page, found here. Product Management derived training is available on the OLAP Option OTN Home Page which can accessed by clicking here. Currently there are four online PM training/demo samples that you can follow:

• Oracle OLAP 10g Release 2
• Oracle by Example - AWM 10g
• Analytic Workspace Manager 10.2.0.3.0
• Using Oracle OLAP with OBIEE 10g R3 Lesson

Support

Support for OLAP Option is available through Metalink. Technical notes are available here as well as the ability to raise service requests relating to OLAP Option.

Documentation

Documentation for the latest release of OLAP Option can be accessed from the Database Documentation link on the OLAP Option OTN Home Page. The library contains release notes, installation and configuration guide, system requirements and support platforms, and Upgrade guides for the database with OLAP Option specific documentation contained within the section on Data Warehousing.

For 11gR1 documentation go here.
For 10gR2 documentation go here.

Useful links

OLAP Option on OTN
Link to the OLAP Option page on the on OTN, containing white papers, presentations and data sheets.

OLAP Option on OTN Forum
Link to the OLAP Option Forum on OTN, where you can ask both technical and non-technical questions to the OTN OLAP community.

OLAP Option Wiki
Link to the Wiki home page for the OLAP Option.

OLAP Option Blog
Link to the home page for the OLAP Option Blog.

OLAP Option Newsletter
Link to the OLAP Option Newsletter. Regular features in each newsletter are:

• Partner/Customer Feature
• Product Management Oracle OLAP Update
• Oracle OLAP DBA Corner
• Downloads and Links the the latest software versions and related patches
• Education and Assistance
• Oracle OLAP DBA Tip
• Events
• News and Press

To subscribe to the Oracle OLAP Newsletter click here, or see the link at the top of each newsletter

OLAP Option Terminology

There is a large glossary of terms relating to data warehousing and OLAP. Many of the most popular terms have already beed added to the OLAP Option Wiki Terminology page.