Oracle OLAP

From Oracle FAQ
⧼orafaq-jumptonavigation⧽⧼orafaq-jumptosearch⧽

Oracle OLAP is an separately licensable option of the Oracle database that offers an embedded multidimensional calculation engine within the database. It is only available with Oracle Enterprise Edition.

With Oracle OLAP, all data is accessible through standard SQL.

Overview

The Multidimensional 'OLAP' database software category largely grew in popularity as a solution to providing fast access to multidimensional data and calculations.

The first product of that category (long before the term "OLAP" was coined in the 1990's) was an early iteration of what was to become Oracle Express.

Data Warehousing and Business Intelligence professionals happily deployed multidimensional products like Express Server as specialized data marts, despite the obvious architectural drawbacks of the approach, simply because the alternatives were poor. The fact was, that for dimensional data, the best multidimensional databases could deliver faster and better query and analysis than the relational databases of the day.

Oracle decided in the late 1990's that in-database analytics was the way to go, and one of the major engineering projects undertaken was to take all the benefits of multidimensional data types and multidimensional data processing and calculation from the best multidimensional databases, and push it into the kernal of Oracle Database. This process started with the acquisition of Express from IRI in 1995, with technical integration starting with Oracle Database 9i. The OLAP Option reached maturity in Oracle 10g and especially in Oracle 11g Database releases.

One of several distinct features that differentiate Oracle OLAP, is that all data is accessible through standard SQL. This extends the reach of 'OLAP' significantly, making it easier to deploy whether for Business Intelligence (BI), Data Warehousing, or for operational applications that are calculation intensive and require fast query response.

History

The Oracle Database OLAP Option is a convergence of the core Oracle Database, and a product called Express Server. Express Server is end of life now (but still used by many organisations around the world) but was a market leading standalone multidimensional database, acquired by Oracle in 1995, and subsequently integrated into the Oracle Database.

Today, the OLAP Option to the Oracle Database retains much of the Express heritage of rich multidimensional power, but does so within the context of the Oracle Database, thus inheriting other features of Oracle, and being accessible via simple SQL. It combines first class multidimensional data types, and calculation engine with the other performance, scalability, security, high availability and manageability features of Oracle Database.

Some of the highlights:

  • Oracle purchased Express and related applications from IRI in 1995
  • Oracle introduced Sales Analyser (OSA) and Financial Analyser (OFA) on top of Oracle Express in 1996.
  • Express was initially integrated into the Oracle 9i database in 2002.
  • Continuous improvements were made in Oracle 10g and Oracle 11g.

An overview of the Oracle Database OLAP Option is available in a short video presentation on Oracle's OLAP pages on OTN, here.

Creating and Maintaining Oracle Database OLAP cubes

Oracle Database OLAP cubes are contained in objects called Analytic Workspaces.

While the OLAP DML (basically the Express SPL Language from Express Server) still exists, and technically may be used by developers to create and populate the cubes, in practice this is rarely done. More usually, Analytic Workspaces and their multidimensional objects - dimensions, hierarchies, measures and calculations etc - are created, populated, refreshed and maintained by one or more of the tools that Oracle provides:

  • Analytic Workspace Manager (AWM) is an admin tool provided with the OLAP Option at no additional cost that allows IT and Power Users to create, build, load and manage multidimensional cubes in Analytic Workspaces within Oracle Database. It is a simple to use GUI, provided on the Oracle Client, with wizards and advisors to help the user define an efficient cube from a given data source. The data source is data in relational tables or views (and thus any data that is contained in or can be accessed from within the Oracle Database, including External Tables), and the source data is mapped to the logical dimensions and measure defined in the AWM interface. A video demonstration of using AWM to create cubes is available on Oracle's OTN OLAP Pages here.
  • Oracle Warehouse Builder (OWB) is an in-database ETL solution available with the Oracle Database, which is designed for IT professionals to build and manage entire Data Warehouses in the Oracle Database, and includes the functionality to deploy dimensional summaries to multidimensional OLAP cubes as an alternative to traditional Star Schemas. Additional Optional features for OWB are available at additional cost - some of them included in the Oracle Data Integration Enterprise Edition (ODI-EE) product; others as options to the Database itself (for example the Data Profiling and Data Quality Option).
  • DBMS_MVIEW.REFRESH. Oracle Database 11g OLAP cubes can optionally be registered as Materialized Views (MVs) in Oracle Database, and therefore be refreshed with the standard policies and procedures familiar to DBAs who use MVs today.
  • DBMS_CUBE PL/SQL Package. In Database 11gR2, a new feature was added that allows cubes and dimensions to be entirely defined via PL/SQL calls, thus making it a much simpler job to automate the creation and refresh of cubes within the context of an application. The new feature is described in a blog entry here.

Querying Oracle Database OLAP cubes

Oracle Database OLAP cubes deliver excellent query performance, which scales well for large numbers of concurrent users. Even on modest hardware platforms the benefits are clear, but because Oracle OLAP is an embedded part of the Oracle Database, it can leverage even the largest SMP platforms, and the Grid technologies of Real Application Clusters (RAC) and Automatic Storage Management (ASM) for even greater scale and resilience.

Direct SQL Access

Oracle Database OLAP Option's cubes are full-blown multidimensional structures and support calculations that are difficult, complex, or even impossible to define using standard SQL.

Other, standalone multidimensional databases on the market require specialized multidimensional query languages to access their data and leverage their calculation engines. Most are proprietary, though in recent years a Microsoft language called MDX (MultiDimensional eXpressions)has become popular (although each vendor has ended up coding to different versions of MDX as there is no published standard for it).

The multidimensional OLAP cubes in Oracle Database however are accessible via simple ANSII-standard SQL. This is a significant development.

Even what would otherwise be very complex calculations such as time series calculations, aggregations with mixed or non additive aggregation methods, allocations, forecasts etc show up to the SQL query layer as if they are fully solved columns that can simply be SELECTed in SQL. Even if the calculations are being performed on the fly by the Oracle Database in OLAP Cubes (as is usually the case), the SQL used can 'assume' that all the measures and calculated measures have already been aggregated (or allocated, or forecasted) and calculated.

This feature dramatically simplifies the configuration of Business Intelligence (BI) tools such as Oracle's BI Suite Enterprise Edition (OBIEE), and products from other vendors such as Business Objects, Microstrategy etc. Indeed, ANY SQL based tool or application that is able to connect to Oracle Database, and issue very simple SQL to it should be able to leverage the performance and calculation power of the cubes. For example, Oracle's Application Express (APEX) tool is a perfect example of a SQL based front-end which has no particular "BI" knowledge and certainly no "multidimensional" knowledge, but which is a very effective front end for Oracle Database OLAP cubes.

Because the SQL required is so simple, developing and testing custom calculation-intensive applications is also dramatically simplified. The amount of code, and its complexity will be much less than if the same thing is attempted without OLAP.

As of Database 11g, the only 'special' requirement is that the tool or application has some basic 'aggregate awareness' or can be configured that way. this is so that the tool does NOT try to perform aggregations in the SQL (or in the middle-tier based on large amounts of raw data fetched from the Database) that are much more efficiently performed within the database.

A detailed explanation of the SQL Access feature is available on OTN, here. Also on OTN, can be found demonstrations of querying Oracle Database OLAP cubes with:

Indirect SQL Access

Since Oracle 11g, the Database OLAP Option cubes have been able to participate in the Materialised View / Query Re-write capability of Oracle.

Materialized Views explained

Regular relational table based MVs have been available in Oracle Database since Oracle 8, and are widely used by BI systems as they simplify the summary management aspects of those systems, and also deliver a feature called query re-write which can improve query performance.

Query Rewrite allows SQL queries that are requesting aggregate data from a detailed fact table to be automatically re-directed by the Oracle Database optimizer to access a suitable summary table in the database instead. Because the summary has less rows, the result can be calculated faster and query performance is improved. The feature allows DBAs great control over tuning performance since they can add or drop MVs according to the usage patterns they see in the Database without having to alter anything in the querying application. The querying application simply always queries the leaf level fact table (requesting aggregates via SUM() and GROUP BY in the SQL SELECT), and the Database intelligently services the query from the best available summary MV that has been refreshed with the latest data. MVs are thus a very popular and widely used feature of Oracle in many BI & DW systems.

In addition to the query re-write aspects of the MV system, there is an entire maintenance aspect where policies control the manner and timing of refreshes of the MV summaries, for example, when new data is added to the underlying fact tables that the summaries are aggregated from.

This use of MVs works extremely well in BI Reporting environments where the query load is predictable - the DBA has an easy job choosing which MVs to create and refresh (indeed, there are advisors in Oracle Enterprise Manager that make recommendations to help him), and typically a realtively small number of MVs will be all that is required for acceptable performance.

However, as the query load becomes more ad-hoc and unpredictable - as is usually the case when the users move from simple reporting, and more towards query and analysis, the decisions as to which MVs to build gets more complicated. There is a temptation to add more an more MVs to the system as new slow running queries are identified. But each MV adds to the update/refresh time required when new data is loaded into the Database - and this soon becomes the constraint. Also, having got to a situation where hundreds of MVs exist, many DBAs are reluctant to DROP any of them for fear of impacting performance of some other application.

Cube Organized Materialized Views

In Oracle 11g, the Database was enhanced to allow not only relational tables to be registered to the Database as MVs, but also multidimensional OLAP cubes in Analytic Workspaces. Now, a relational query directed at a fact table from which an OLAP Cube was populated may be automatically redirected by the Oracle Database query optimizer to fetch the aggregate from the Cube, instead of processing the SUM/GROUP BY in the relational engine. For BI systems that support a varied workload, and one that includes ad-hoc access to the data, the OLAP Option therefore provides an imaginative alternative.

Cubes are VERY efficient at aggregations, so query performance is dramatically improved. Also, a single Cube will deliver aggregate data equivivalent to all the possible summary combinations that exist along the hierarchies of the dimensions over that original leaf level fact table. Not only is the calculation of the aggregates very efficient, but the storage, and management of them is too.

Instead of large numbers of MVs that the DBA has to manage and keep refreshed, he has a single OLAP cube. Refreshing the OLAP cube can be plugged into exactly the same MV refresh mechanisms used for regular relational MVs, so the cubes can easily slot into existing maintenance procedures.

Thus, Oracle Database OLAP cubes can be IN-DIRECTLY queried with SQL too. Used simply as an enhancement to the summary management of the database, and for query performance benefits in this way, the presence of the OLAP Option is totally transparent to the application. That is, not one change to the BI tool or SQL Application is required. It continues to query the same underlying relational fract table it ever did, and the Database transparently accesses data from the cubes instead. The only impact on the application is that the queries are faster.

A demonstration of the use of the Cube Organized Materialized Views feature can be found on Oracle's OTN page here.

MDX

Announced at Oracle Open World 2009, Oracle Database OLAP cubes are now also accessible via MDX. MDX is a language popular with standalone multidimensional databases, and for which a number of BI tools and applications exist. In particular Microsoft is investing heavily in 'cube enabling' Microsoft Office (especially Excel) with MDX. Thus, Excel becomes a client to the cubes. With the MDX Provider (available from Oracle Partner and MDX specialists, Simba Technologies), these MDX tools can now leverage the power and superior architecture of Oracle Database OLAP as an alternative to the architecture of separate specialist cubes required by other vendors. The 'version' of MDX that is used by this solution is equivalent to that used by Microsoft Excel to communicate with Microsoft Analysis Services (Microsoft's OLAP database which is sold with SQL Server).

Oracle and Simba demonstrate the use of this MDX provider, with Excel, in a short video available on the OTN pages here or via Simbas own web page here.

OLAP API

Oracle Database OLAP also comes with a multidimensional OLAP API which can be used by developers to query and interact with OLAP cubes in the Database. The OLAP API is used by Oracle tools such as the BI Beans (now being folded into the ADF framework for JDeveloper), Discoverer Plus OLAP, and the Excel Spreadsheet Addin, all of which were popular tools for use with Oracle Database OLAP in Oracle 10g. However, since the shift of emphasis in Oracle's BI tools to Oracle BI Suite Enterprise Edition (OBIEE) (based on technology acquired with Siebel Systems), most new users of Oracle Database OLAP 11g are leveraging the SQL access or MDX.

External links

Retrieved from "http://www.orafaq.com/wiki/index.php?title=Oracle_OLAP&oldid=14480"