Problem solve Get help with specific problems with your technologies, process and projects.

Oracle9i OLAP uncovered

Learn about key features in the Oracle9i and 10g OLAP option, and how multidimensional OLAP data can now be incorporated into Oracle databases.

Mark Rittman

Mark Rittman is a guest contributor. He is an OCP DBA and works as a consulting manager at Plus Consultancy in England, where he specializes in developing business intelligence and data warehousing applications using the Oracle Database, Application Server, Discoverer, Warehouse Builder and Oracle OLAP.

You can find more of Mark's work at


This paper, written by Mark Rittman and Jon Mead of Plus Consultancy, outlines some of the key features of the Oracle9i OLAP option, explains how multidimensional OLAP data can now be incorporated into Oracle databases, and then made available to standard SQL-based Oracle tools and applications. It discusses how data and programs held in Express databases can be migrated to Oracle 9i OLAP, and how existing Oracle applications can use the functionality found within the OLAP option to extend the analytic SQL capabilities found within the Oracle database.

Some of the new features of Oracle10g OLAP are also outlined, including the new SQL MODEL clause, support for OLAP data in Oracle Discoverer, and the new Oracle OLAP Spreadsheet Add-in.

Table of contents

   Oracle9i OLAP concepts
   Oracle9i OLAP key components
   Creating an analytic workspace
   Using Oracle9i OLAP data in applications
   Opportunities and scenarios for integrated OLAP in applications
   New features planned for Oracle10g OLAP
   Oracle OLAP Spreadsheet Add-In

[ Return to Table of Contents ]

Oracle Express has for many years been the industry's leading OLAP server, offering unparalleled functionality, query performance and application support. With Oracle9i, Oracle has taken the best features of Oracle Express and embedded them in the database kernel.

By moving to Oracle9i OLAP, customers can benefit from improved application performance, scalability, availability and manageability, while retaining the majority of their investment in Oracle Express technology.

Oracle9i OLAP, an option to Oracle9i Enterprise Edition, provides valuable insight into business operations and markets using features previously found only in Oracle Express. Because Oracle9i OLAP is fully integrated into the relational database, all data and metadata is stored and managed from within Oracle9i providing superior scalability, a robust management environment, and industrial-strength availability and security.

Key features of Oracle9i OLAP include:

  • The ability to support complex, multidimensional calculations
  • Support for predictive functions such as forecasts, models, non-additive aggregations and allocations, and scenario management (what if)
  • A Java OLAP API
  • Integrated OLAP administration

The integration of multidimensional technology into the Oracle database offers many opportunities for Oracle users to take advantage of the advanced modeling and forecasting features provided by the Express range of technologies, while existing users of Express-based systems can take advantage of the full range of Oracle database and application server technology, together with high-availability and scalability opportunities such as Oracle Real Application Clusters (RAC).

Existing OLAP support with Oracle databases

Ever since Oracle8i, Oracle has been incorporating OLAP functionality into the database to support relational OLAP (ROLAP). Analytic functions such as as RANK, LAG/LEAD, FIRST/LAST and aggregation operators such as CUBE and ROLLUP, enable users to carry out complex OLAP calculations using standard SQL queries. To improve response time, technologies such as query rewrite and materialized views can be used to build summaries, speeding up queries in a way that is transparent to tools such as Oracle Discoverer.

To complement this ROLAP functionality, Oracle9i introduced for the first time the concept of a relational-multidimensional database, by including the features previously found in the standalone Oracle Express Server product in the database kernel. What were previously Express databases now became Analytic Workspaces, held in tables within standard Oracle schemas, and the existing Express Programming Language was ported across to Oracle9i and renamed OLAP DML.

A revolutionary feature introduced with Oracle 9i OLAP was the capability for standard SQL to query the multidimensional data held in analytic workspaces, opening up the power of the multidimensional engine to SQL query tools such as Oracle Discoverer and Oracle Reports. Other features introduced with Oracle9i OLAP included the OLAP Catalog, a layer of metadata that described both relational and multidimensional data using common terms such as cubes, dimensions and measures, and the Java OLAP API, a set of java classes that can be used to build OLAP-aware applications using Oracle Jdeveloper.

Oracle9i OLAP concepts
[ Return to Table of Contents ]

Oracle9i OLAP is a rebranded version of Oracle Express that is integrated into the Oracle database kernel. While much of what makes up Oracle9i OLAP will be familiar to existing Express customers, it is worth taking a moment to understand some of the key concepts behind Oracle9i OLAP, and see how they relate to how things have traditionally been done with Express.

Express server now embedded in the Oracle9i database

From Oracle9i release 2 onwards, the Oracle Express codebase has been fully integrated into the Oracle Server kernel and there is no separate 'OLAP Server' process running alongside Oracle. According to sources within Oracle, some 40% of the code within the Oracle server is now dedicated to business intelligence and data warehousing.

Now called Oracle9i OLAP

The multidimensional database found within Oracle9i, previously known by the brand name Oracle Express Server, is now known as Oracle9i OLAP.

Express databases now called Analytic Workspaces.

With Oracle Express, Express databases were stored in files with a .db extension, and these files were managed by the operating system. Each operating system placed limits on the size of these files, which could be backed-up using normal filesystem backup tools. With Oracle9i OLAP, OLAP databases are stored as binary objects, one per table, within an Oracle schema, which are managed by the Oracle relational database management server. The only limit to OLAP databases now, are the limits Oracle places on them. Backups are handled in the same way as normal Oracle backups.

Integrates in with existing Oracle ROLAP capability

Oracle9i OLAP is a multidimensional database, just like Express, and it works alongside the existing ROLAP functionality in the Oracle database. A new feature, called the OLAP Catalog, sits as an additional metadata layer over both the ROLAP and MOLAP databases, and acts as an abstraction layer, allowing new Oracle OLAP applications to both work with MOLAP and ROLAP databases in the same manner.

Can be accessed through either SPL, Java API or PL/SQL

Together with the Express programming language, now known as OLAP DML (Data Manipulation Language), Oracle9i OLAP can now be queried using PL/SQL, using the DBMS_AW.EXECUTE() built in package, and a whole new Java-based API which replaces the traditional SNAPI and XCA.

SNAPI and XCA have been replaced by JDBC and OLAP API

With Oracle's move towards Java and the Internet, SNAPI and XCA have been replaced by a Java OLAP API, which, although offering many new features and preserving existing functionality (persistence of queries, for example), breaks compatability with tools and applications such as Oracle Financial Analyzer, Oracle Sales Analyzer and Oracle Express Objects.

Oracle9i OLAP key components
[ Return to Table of Contents ]

Oracle9i OLAP is made up of a number of key components.

Calculation Engine -- The Express Engine

The power behind Oracle9i OLAP comes from the dedicated multidimensional engine ported from Oracle Express Server.

The multidimensional engine provides support for the OLAP DML (Express SPL renamed) and management of multidimensional objects. With Oracle9i release 2, this engine has been completely integrated into the Oracle database and runs within the Oracle database kernel.

Analytic Workspace –- The .db files

Express databases are now Oracle9i OLAP Analytic Workspaces. Each one is a BLOB (Binary Large OBject), and is held within a table, assigned to an Oracle tablespace, and is owned by an Oracle user. Analytic Workspaces can be permanent or temporary, have permissions granted against them like any other database object, and are backed up along with the rest of the Oracle database. The AW contains everything an Express DB contains, including dimensions, variables, formulas and valuesets. It stores data the same way as Express (e.g. variable by dimensions or composites), uses about the same about of disk, and so on.

OLAP DML –- Express Programming Language

Oracle9i OLAP fully supports the Express SPL, but it's now know as the OLAP DML. The OLAP DML does everything the Express SPL does -- you can import Express code into an Analytic Workspace, and run it with only minor modifications.

PL/SQL table functions

Table functions and Abstract Data Types provide the means for presenting data through SQL. Both the OLAP API and SQL-based applications can access data in analytic workspaces through table functions and relational views, giving tools such as Oracle Discoverer and Business Objects access for the first time to Express/Oracle9i OLAP data.


The new Java OLAP API provides access to both relational and multidimensional data types, and presents these in an identical way to OLAP applications through the use of the OLAP Catalog. The OLAP API provides the ability to build up queries in a number of stages, just as with Express at the moment, to allow multidimensional cubes to be progressively 'sliced and diced' to get to just the piece of information that is required. This contrasts with SQL, which generally has to compose the entire query in one go.

OLAP Catalog metadata

Oracle9i provides a layer of metadata above both the ROLAP (Oracle database tables) and MOLAP (Oracle9i OLAP, Analytic Workspace) databases that provides an abstract layer for OLAP applications to work with. By describing OLAP databases in terms of cubes, dimensions, hierarchies and measures, applications can reference these objects without worrying whether the database is stored relationally or multidimensionally, while database administrators are free to move data between ROLAP and MOLAP databases without breaking the OLAP application.

SQL access to analytic workspaces

A big development for Express customers moving to Oracle9i OLAP is the ability to use SQL tools to access the new Oracle9i OLAP Analytic Workspaces. Now, tools such as Oracle Discoverer, Business Objects, Microsoft Excel and any JDBC or OCI compliant query tool can obtain read-only access to an analytic workspace.

This is achieved through the creation of SQL views that are mapped via Oracle to items in the Analytic Workspace. Tools such as the Analytic Workspace Manager and Oracle Warehouse Builder can build these SQL views, and, should you wish to use Oracle Discoverer, build the Discoverer End User Layer together with business areas, dimensions, hierarchies and summaries.

All of this is seamless as far as the end-user is concerned. If the end user is able to query a relational database, they can query an analytic workspace. This is not the only way in which Oracle9i OLAP can be queried, but it opens up new possibilities to use relational query tools instead of just dedicated Oracle9i OLAP query tools.

Being able to access the analytic workspace via SQL and PL/SQL has big implications for the design and architecture of Oracle applications that use multidimensional technology. In the past, with systems that used Express as the core component, it often became necessary to include within the Express database all the reference data, maintenance routines, data input program logic, and Web/client-server user interfaces, due to the difficulty for non-Express Oracle components to access the Express database. Now that access to the 9i OLAP analytic workspace has been opened up through standard interfaces, system architects can now use the multidimensional database and calculation engine as a plug-in service, carrying out specialist calculations and forecasts as required and leaving the storage of the main application data to the Oracle database, with the user input and display being handled by applications such as Oracle Portal, Oracle Forms and Oracle BI Beans.

Creating an analytic workspace
[ Return to Table of Contents ]

There are two main options when creating an populating an analytic workspace within an Oracle9i database, the choice of which depends on where the data used to populate the workspace is coming from.

    1. Migrating data from an Oracle (or other RDBMS) database
    2. Upgrading existing Express databases to analytic workspaces

1. Migrating data from an Oracle (or other RDBMS) database

Oracle has provided with Oracle9i R2 a PL/SQL package, CMW2_OLAP_AW_CREATE, that creates an analytic workspace, defines dimensions and variables, populates the resultant workspace and creates SQL views over the workspace to enable access by tools such as Oracle Discoverer and Oracle BI Beans.

A necessary first step before using this package is to create a relational representation of the cubes that are to go into the analytic workspace, in the form of a star schema made up of one or more fact tables, dimensions and hierarchies. The ROLAP metadata that is created when building this star schema is then registered in the OLAP Catalog, as one or more ROLAP cubes, and this metadata is then used by the utility to define the analytic workspace.

The package comes with a number of procedures that address each of the migration steps:

    1. AW_DIMENSION_CREATE, which defines dimensions in the analytic workspace corresponding to the dimensions in the relational star schema;
    2. AW_CUBE_CREATE, which creates variables (the 9i OLAP/Express equivalent of measures) from data in the star schema fact tables;
    3. A number of load procedures used to loading data into the analytic workspace variables and dimensions;
    4. AW_DIMENSION_CREATE_ACCESS and AW_CUBE_CREATE_ACCESS procedures, which create SQL views over the analytic workspace to enable access by tools such as Oracle Discoverer.

If the source data is held in a non-Oracle relational database, it will first need to be migrated into the Oracle database, using a tool such as Oracle Warehouse Builder, as the additional ROLAP metadata only found in Oracle databases will need to be created over the relational data before it can be migrated using the CWM2_OLAP_AW_CREATE package.

An alternative to manually creating and populating the analytic workspace using the PL/SQL utility, is to use one of the following migration tools provided by Oracle.

Analytic Workspace Manager

Delivered as part of the Oracle9i patchset, Analytic Workspace Manager (AWM) is a new tool to build and populate analytic workspaces. A screenshot of AWM is given below.

As well as administering the variables, formulas, dimensions and hierarchies within an analytic workspace, the AWM can be used to migrate existing ROLAP cubes (or star schemas) into a MOLAP analytic workspace. On completion of this migration, AWM offers to create the SQL views to allow the analytic workspace to be queried via SQL, and also set up the Oracle Discoverer End User Layer.

The first release of the Analytic Workspace Manager centers around the migration of existing relational OLAP cubes, held in Oracle9i star schemas, to an analytic workspace. It should therefore be noted that data and dimensional structures need to be first created in relational form in the Oracle database before they can be migrated, as the AWM reads the CWM2 metadata created to support these ROLAP structures when building the analytic workspace.

Oracle Warehouse Builder

Oracle Warehouse Builder (OWB) has been for some time the best way to design and build Oracle8i and 9i data warehouses. Delivered as part of Oracle9i Developer Suite, it contains functionality to design the target warehouse, define data sources, map sources to target, and generate loading scripts using PL/SQL and SQL*Loader.

The latest release of OWB, release 9.2, contains functionality to take a relational star schema that has been built by OWB and deploy it to an analytic workspace.

OWB allows developers to design and populate Oracle data warehouses, using GUI tools to create links to source databases and files, design and build OLAP data warehouses, then map sources to targets incorporating transformation logic that maps to PL/SQL packages.

Starting with the release of version 9.0.4 of Warehouse Builder, additional transformations have been made available that allow the mapping of relational OLAP cubes to Analytic Workspaces, using the same PL/SQL processes that are used by the Analytic Workspace Manager.

These transformations require an additional patch to be applied to the Oracle9i database to be installed (patch, which enables the CWM2_OLAP_AW_CREATE transformations used as the basis for the WB_OLAP_LOAD_CUBE and WB_OLAP_LOAD_DIMENSION mapping operators.

Like the Analytic Workspace Manager, analytic workspace creation using Oracle Warehouse Builder assumes that a relational version of the candidate cubes and dimensions has already been created, in this case using Oracle Warehouse Builder.

2. Upgrading existing Express Databases to Oracle9i OLAP

The release of Oracle OLAP introduces a new concept known as 'database standard form' analytic workspaces. Often shortened to 'standard form,' it is a way of constructing analytic workspaces in a standard way such that they can be used by tools such as the Analytic Workspace Manager, the Java OLAP API and BI Beans.

When analytic workspaces are created from a relational star schema using the Analytic Workspace Manager, it automatically creates the analytic workspace in standard form as part of the migration process. However, if you're migrating an Express database to Oracle OLAP, the migrated database has to be processed to be in standard form before it is usable by any of the new Oracle OLAP GUI tools.

If you create an analytic workspace using PL/SQL or the OLAP Worksheet, of course you can create dimensions, variables, relations and so on in any form, just as you can create table structures, joins, views and columns in any form in a relational database. However, the database standard form requirement stipulates that;

  • Certain objects and properties need to be found in the analytic workspace, that are used by tools such as the Analytic Workspace Manager to perform tasks such as aggregation, data loads, and OLAP API enablement.
  • OLAP DML views (beginning with AW$) need to created in the analytic workspace, to provide metadata and to identify relationships between objects in the analytic workspace.
  • Objects need to be registered in the OLAP Catalog, and these registrations have to be kept in sync so that the OLAP tools are aware of changes to the base objects.

Data that is migrated in to an analytic workspace, from a relational star schema using the Analytic Workspace Manager, is already in database standard form and no further work is needed. However, some work is needed to get migrated Express databases into standard form, and Oracle have provided a utility with the release of Oracle OLAP to help accomplish this.

This utility, known as CREATE_DB_STDFORM, is an OLAP DML program that takes existing Oracle Express Objects metadata in a migrated Express database, and uses this to create the additional metadata required to make the analytic workspace database standard form.

Once CREATE_DB_STDFORM has been used to create this additional metadata, it can also be used to import data into the workspace either from flat files, or from Oracle tables and views. If you don't have Express Objects metadata in the Express database, CREATE_DB_STDFORM doesn't work, and you'll have to use Oracle Warehouse Builder 9.2 to initially create a relational star schema that equates to the Express database, then use the OLAP Bridge within OWB to export the data into a database standard form analytic workspace.

CREATE_DB_STDFORM, once run, allows the migrated Express database to be accessed via BI Beans straight afterwards, as it creates all the OLAP Catalog entries required for the OLAP API. If the Express database only contains data at the lowest level (i.e. it hasn't been rolled up), aggregation wizards in the Analytic Workspace Manager can be used to summarise the data as required.

CREATE_DB_STDFORM doesn't do everything, however. First of all, you need to have created Oracle Express Objects metadata within the Express database, which is an additional step and not always appropriate for all systems. In addition, you need to create time dimensions in a particular way, which may require the data model to be adjusted to meet this requirement. Lastly, any Express language programs within the Express database may need to be adjusted to remove or change commands that have changed or become obsolete.

Using Oracle9i OLAP data in applications
[ Return to Table of Contents ]

Ever since Oracle8i, standard Oracle applications have been able to take advantage of relational data stored as ROLAP cubes. The additional ROLAP metadata (dimensions, hierarchies, summaries) are transparent to SQL queries and are used by relational technologies such as query rewrite to quicken query response times.

With Oracle9i and the OLAP Option, it is now also possible to expose data held in analytic workspaces so that can be queried using normal SQL query tools, using either SQL views over the data, or directly using the new OLAP_TABLE function. By accessing analytic workspace data using SQL, the power of the multidimensional engine within the Oracle9i database can be leveraged by a whole range of SQL-based applications.

SQL views over analytic workspaces

Using Oracle9i, it is possible to create views over analytic workspace data, in such a way as to appear to SQL reporting tools such as Oracle Reports and Oracle Discoverer that the data being queried is held in normal Oracle tables and columns. The SQL View functionality is provided through a feature known as the Table Function and can be implemented either through tools such as Oracle Warehouse Builder or Analytic Workspace Manager, or they can be created manually through SQL*Plus.

There are two steps to creating SQL views over analytic workspaces:

  • i. Create abstract data types to hold the analytic workspace data
  • ii. Create the view itself to enable SQL access

Abstract data types are objects in the Oracle9i database that provide a relational 'shape' (columns and rows) to a non-relational data source.

The first abstract data type defines the columns to be returned. If, for example, a view was to be created that looks like a dimension lookup table for a time dimension, the following abstract data type might be created:

create type olap_time_type_row as object (
   month               varchar(10),
   quarter             varchar(10),
   year                varchar(10),
   all_times           varchar(10)

Next, an abstract data type is created that binds the column definitions to a table object type.

create type olap_time_type_table as table of olap_time_type_row;

Now the view can be created as shown in the example below.

    'limit time KEEP levelrel_time ''MONTH''',
    'DIMENSION month FROM time WITH
       HIERARCHY parentrel_time
         LEVELREL all_times, year, quarter
         FROM familyrel_time USING leveldim_time'));

OLAP_TABLE accepts several parameters that identify the data source, interact with the multidimensional engine, and bind the select to an abstract data type. Some of the most commonly used parameters include:

 AW-ATTACH, to define which analytic workspace contains the data
 TABLE-NAME which binds our abstract data type to the query
 OLAP COMMAND allows the query to send an OLAP DML command to the multidimensional engine
 LIMIT MAP and ATTRIBUTE map relational columns and attributes to their corresponding analytic workspace equivalents
 MEASURE maps a relational column to an analytic workspace variable

An example FROM clause using the OLAP_TABLE function is as follows:

        'limit time KEEP levelrel_time ''MONTH''',
        'DIMENSION month FROM time WITH
           HIERARCHY parentrel_time
             LEVELREL all_times, year, quarter
             FROM familyrel_time USING leveldim_time
         ATTRIBUTE month_of_year FROM attr_month_of_year
         MEASURE month_label FROM month_name
         MEASURE quarter_label FROM quarter_name
         MEASURE year_label FROM year_name
         MEASURE all_times_label FROM all_times_name'))

By using the OLAP_TABLE function, together with SQL views, multidimensional data held in analytic workspaces can be retrieved and used using normal SQL queries, and OLAP DML commands can be executed to modify or limit the data being retrieved.

Accessing analytic workspaces using PL/SQL

Finally, applications can issue commands directly against the multidimensional engine without using OLAP_TABLE. In this case, the PL/SQL package DBMS_AW.EXECUTE is used to send commands to the multidimensional engine. You can send any OLAP DML command you want via an OCI or JDBC connection (load data, aggregate data, solve a model, define a formula, etc.). If you've written to Express' SNAPI API, you can think of OCI or JDBC and DBMS_AW.EXECUTE as being the replacement to SNAPI and its' SN.EXECUTE command.

Two other PL/SQL packages, DBMS_AW.INTERP and DBMS_AW.INTERPCLOB provide the means to return data from analytic workspaces to PL/SQL programs.

Opportunities and scenarios for integrated OLAP in applications
[ Return to Table of Contents ]

Now that multidimensional data can be seamlessly integrated into the full range of Oracle products, there are a number of obvious opportunities for users of Oracle's database and application server products, as well as opportunities for existing Express users to take advantage of the scalable and secure Oracle server platform.

Opportunity to re-engineer existing Express server applications

Oracle Express Server has long been the number one multidimensional database engine on the market, and over the years the Express product line has been extended to include developer tools, graphical user interfaces, and Web publishing tools.

A typical scenario is one where a complete application has been built over time around a core Express analysis or forecasting application, using Express products such as Personal Express, Express Objects, multiple Express databases, web delivery tools such as Web Agent, Web Publisher, and client-server analysis tools such as Express Analyzer and Express Objects. Data input is carried out using Personal Express blue screen form builder, with data loading largely carried out using Express data loader scripts.

A typical architecture for this type of system would be:

This type of architecture often arises for several key reasons;

  • A small, standalone Express system has been build on over time by people with mainly Express skills, and each addition to the system's functionality has been easier to add with additional Express products, such as 'blue-screen' interfaces, Web Agent front-ends, Express Objects menus and user interfaces.
  • Program logic and multidimensional datatypes held in the Express database are largely inaccessible to non-Express technology applications.
  • The core calculation and forecasting engine within the Express database cannot easily access external data.

This often leads to large, monolithic applications that were originally built around Express' calculation and forecasting ability, but now carry out a whole range of tasks, such as communications, data input, storage of low-level detailed data, and user interaction, which are not necessarily best served by being carried out by a multidimensional database product.

Oracle9i, through the new ability to expose multidimensional datatypes and programs written using the OLAP DML through standard Oracle SQL and PL/SQL, offers the opportunity to decouple the core Express/9i OLAP analytic functions from the ancilliary surrounding system functionality, to enable the migrated system to take advantage of more scalable, up-to-date Oracle products such as Oracle9iAS, Oracle Forms, Oracle Collaboration Suite and the whole new Oracle J2EE architecture.

One of the major aims of the integration of the Express database engine into the Oracle9i database was to open up the previously proprietary Express data structures and interfaces so that they can be accessed by mainstream Oracle and third-party products. In addition, by delivering a single relational-multidimensional server, developer skillsets can be merged and enhanced so that future systems developers can develop solutions that use technologies across this enhanced product set.

Previously Express-only solutions that have all elements of the systems architecture delivered by Express products can now use the new 9i analytic workspace as it was meant to be used: as a specialist multidimensional calculation and forecasting engine, with user interaction and program logic moved to more suitable Oracle products.

A typical migrated architecture that uses this principal would be:

In this scenario, the following changes are made:

  • The 9i OLAP Analytic Workspace is used solely for calculations and forecasts.
  • Program logic, where not related to calculations and forecasts, together with base data is held as Java program code in the Oracle9i database and the Oracle9i Application Server.
  • The Java OLAP API is used only where required in pure BI applications such as Oracle BI Beans.
  • 9i OLAP is now called as a service, rather than being the center of the application.

This has been made possible through the new ability to access analytic workspace data directly via SQL (negating the need for all access to be carried out via code within the Express/analytic workspace), and the ability to directly call OLAP DML commands and programs directly through PL/SQL.

The Express data, plus any Express SPL code used for forecasting or calculations, will need to be migrated into an analytic workspace and the resultant analytic workspace exposed as SQL views and as an entry in the OLAP Catalog. However, in this scenario, functionality previously carried out by the Express product set that is not directly related to multidimensional calculations will now be migrated to specialist Oracle or third-party tools such as

  • Oracle Portal
  • Oracle Forms
  • Oracle Reports
  • Oracle Advanced Queuing
  • Oracle9iAS J2EE architecture

Where it will be more future-proof, scalable, open to outside interfaces and easier to deploy over the Internet.

In this instance, where application logic previously carried out by Express products is a candidate for migration to alternative Oracle tools, it has usually been found to be best practice within Plus Consultancy to carry out migration in several stages;

    I. Export/import data from Express to 9i OLAP Analytic Workspace
    II. Move any data destined for the Oracle9i RDBMS from AW to Oracle9i relational tables
    III. Migrate SPL code to Oracle9i OLAP DML code and test
    IV. Verify data correctness in new environment (e.g. via rapid development technology such as 9i Web Agent or BI Beans via Wizards)
    V. Develop and test new front-end with the appropriate technology, such as Oracle Forms
    VI. Go live with "Phase 1"
    VII. Subsequently look at making enhancements to take more advantage of new features.

This staged approach allows the new Oracle9i-based system to be delivered in stages, verifying that each element works as intended as it is implemented.

Incorporating analytic workspace functionality into relational applications

While there are obvious opportunities for Express customers to migrate their applications to Oracle9i OLAP, and take advantage of the features available in the Oracle9i and Oracle 9iAS server platforms, it is also now possible for standard relational Oracle applications to selectively use the features of the multidimensional engine now found within Oracle9i to build financial models and perform forecasts, and to provide summaries for tools such as Oracle Discoverer.

With Oracle9i OLAP, data from relational tables can be copied into a temporary analytic workspace, and OLAP DML commands can be issued against this workspace to use the forecasting and modelling capabilities of OLAP DML through the OLAP_TABLE function. In this way, an application's data can be primarily stored in relational tables, with data being temporarily copied into a multidimensional data cache to perform complex multidimensional calculations. This functionality is being extended in the next version of Oracle OLAP, known as Oracle10g OLAP, through the use of the SQL MODEL clause, details of which can be found later in this paper.

It is also possible to use the aggregated data held in an analytic workspace to provide external summary tables for Oracle Discoverer, by using SQL views over analytic workspaces as detailed earlier in this paper. In this instance, the summary manager feature within Discoverer Administrator is used to register an analytic workspace SQL view as a Discoverer summary table, with refreshing of the analytic workspace being carried out in a process separate to the normal Discoverer refreshing of summaries. Again, with Oracle10g OLAP, the ability for Discoverer to use data held in analytic workspaces has been enhanced, and details of these new features are outlined in the next section of this paper.

New features planned for Oracle10g OLAP
[ Return to Table of Contents ]

The Oracle9i release of the Oracle OLAP option included a whole range of new features and capabilities, which are now being successfully implemented by business intelligence customers and partners, and many existing Oracle Express customers have now migrated their applications from the Express Server platform to Oracle9i. Now it is now possible to look forward to some of the new features in Oracle10g OLAP.

Enhancements to relational OLAP capabilities

As mentioned previously, the Oracle OLAP option includes both relational and multidimensional features and Oracle10g introduces a number of new ROLAP capabilities to build on those provided with Oracle8i and Oracle9i.

Probably the most interesting of these enhancements is the SQL Model clause, which allows users to embed 'spreadsheet-like' models in a SELECT statement, in a way that was previously only possible when working with multidimensional database engines. The SQL model clause brings an entirely new dimension to Oracle analytical queries and addresses a number of traditional shortcomings with the way SQL normally works.

The SQL model clause has been designed to address the sort of situation where, in the past, users have taken data out of relational databases and imported it into a model held in a spreadsheet, such as Microsoft Excel. Often, these models involve a series of macros that aggregate data over a number of business dimensions, over varying time periods, and following a set of complex business rules that would be difficult to express as normal SQL. Users in the past have often found that limitations of standard SQL meant that a number of standalone spreadsheets had to be used, and whilst these provided the analytical capabilities required, issues of scalability, reliability of replicated data, and lack of overall control often become apparent after a while.

The aim of the SQL model clause is to give normal SQL statements the ability to create a multidimensional array from the results of a normal SELECT statement, carry out any number of interdependent inter-row and inter-array calculations on this array, and then update the base tables with the results of the model.

The power of the SQL model clause becomes apparent when you consider that, as well as outputting forecasts and calculations to a query tool such as Oracle Discoverer, it can also write back to the underlying tables, inserting or updating values according to the model's set of interdependent calculations and business rules. Models can be built up that iterate many times over the same set of data, feeding the results of one calculation into another and drawing together data across many different time and dimension ranges. All of these calculations are handled natively by the Oracle database kernel, avoiding expensive database joins and unions and taking advantage of such features as parallelism and partitioning where they are available.

Coupled with the existing analytic features within the Oracle8i and 9i database, the model clause gives the Oracle10g database powerful mathematical modeling capabilities not found in competing relational database platforms. What is particularly interesting is the prospect of integration between the SQL model clause and the existing modelling capabilities within the Oracle OLAP multidimensional engine. Details released at OracleWorld in particular hint about close integration between the model clause and the SQL interface to analytic workspaces, such that queries can be handled at the detail level by the relational engine, and at the aggregated level by the multidimensional engine, in a way that is transparent to the user and with the minimum of overhead.

New multidimensional capabilities

Oracle10g OLAP includes a range of improvements to the multidimensional engine within the database, and the benefits of integration with the traditional relational Oracle engine are starting to become apparent.

The first improvement concerns the way large analytic workspaces can be partitioned, introducing into the Oracle OLAP world some of the advanced partitioning options currently enjoyed by Oracle database users. Currently, analytic workspaces, stored as AW$ tables within an Oracle schema, can be partitioned across multiple rows in the AW$ table by specifying a maximum segment size, allowing you to split an individual analytic workspace into (say) 10 GB segments, one in each table row. This table could then be partitioned just like any other Oracle table, allowing you to put one row in one tablespace, another in another, and each of these tablespaces could of course be stored in datafiles on different physical disk units. Although this was of some benefit, splitting by segment size was the only way of partitioning the data, and you couldn't specify what objects within the analytic workspace went in to each partition. Oracle10g OLAP will now include an enhancement where you can specify exactly which objects within the analytic workspace go in to each partition, and you can further subdivide this by segment size if objects are particularly large.

In a similar fashion, variables within the analytic workspace can now be partitioned, either by range of dimension members, a list of dimension members, or by reference to a CONCAT dimension. The 10g multidimensional engine then stores each variable partition as a separate physical object, which can be directed to separate rows in the AW$ table (allowing you to partition these across different tablespaces and physical disk drives); the variable however appears as just one object to the application, simplifying the data model and allowing Oracle to do all the hard work in the background.

Another excellent new feature, and a real improvement over what was available with Express, is support for multi-user read-write access to individual analytic workspaces. In the past, one drawback with Express was that only one user could attach to an Express database in read-write mode, leading Express developers to develop a whole range of alternative solutions to allow ad-hoc write access to Express databases. In Oracle10g OLAP, analytic workspaces can be attached in MULTI mode, whereafter applications then ACQUIRE individual variables in the analytic workspace for read-write access. Once an object has been acquired (and locked by the Oracle multidimensional engine), updates can then take place and the application can make whatever modifications are necessary. After all changes have been made, the UPDATE command is issued against the variable, followed by a COMMIT, and then a RELEASE command is issued against the variable to make it available for other applications to write to.

Aggregation has been improved with Oracle10g OLAP, with formulas now allowed as sources of data for the AGGREGATE command, eliminating the need to calculate and store data at the detail level. Aggregation, particularly dynamic aggregation, is another area where Oracle9i and now 10g OLAP are a distinct improvement over Express and it's well worth looking at this area in more detail if this is an issue with an existing Express system.

Initial details provided around the time of the Oracle 9i OLAP launch made reference to an Oracle OLAP feature, where analytic workspaces could be used as substitutes for materialized views, but in practice this was difficult to implement due to restrictions in the way the query rewrite mechanism worked. With 10g OLAP this process has been simplified through a feature known as query equivalence, which is used to direct the database query rewrite mechanism to a summary -- in this case, an analytic workspace with SQL views defined over it -- that doesn't share the same SQL syntax as the user's original query, but where the results would be the equivalent. This is required when a summary is provided through an SQL view over an analytic workspace, as the SQL view would never normally qualify for query rewrite due to the syntax of the underlying SELECT statement.

Oracle OLAP Spreadsheet Add-In
[ Return to Table of Contents ]

Oracle also announced the availability of an Oracle OLAP Spreadsheet Add-In, which allows Oracle OLAP data to be brought in to a spreadsheet and manipulated, just like any other database data source.

The Spreadsheet Add-In works with Microsoft Excel to add an extra menu option to the toolbar to bring up the BI Beans Query Builder, much like the Express selector, allowing the user to create a query against the OLAP Catalog. The query builder then constructs an OLAP API query against the OLAP Catalog, and then returns data from either the relational or multidimensional engine to the users' spreadsheet.

Providing easy access to Oracle9i OLAP data, through the well-known Excel interface, will prove popular with business users who do most of their analysis through the familiar Microsoft Excel interface.

Native support for Oracle OLAP within Discoverer

A welcome announcement during OracleWorld for users of Oracle Discoverer was news of forthcoming native support for Oracle OLAP as a data sources for users' queries. A forthcoming release of Oracle Discoverer will incorporate the BI Beans query selector, allowing users to build persistent multidimensional queries against Oracle OLAP cubes, measures and dimensions.

Discoverer will then have three options when working against data held in an Oracle database; straightforward relational queries against a relational star schema (or any other Oracle data model), relational queries against detail level data in a star-schema, coupled with summaries held in an analytic workspace, or now, multidimensional queries using the BI Beans selector against an Oracle OLAP datasource.

[ Return to Table of Contents ]

This paper has tried to set out the background behind Oracle9i OLAP, and describe how Oracle9i analytic workspaces can be built and populated from data held in Express and Oracle relational databases.

Oracle has provided a number of tools to migrate relational data in to analytic workspaces, including Oracle Warehouse Builder and the Analytic Workspace Manager, together with a set of PL/SQL routines to allow low-level access to the migration process. For Express users looking to upgrade existing Express databases, the paper outlines the steps required to import data into analytic workspaces, together with the steps required to create the metadata associated with the OLAP Catalog.

Once data has been migrated in to Oracle9i OLAP, it can be incorporated into Oracle applications through a variety of means. Normal SQL queries can access multidimensional data either through SQL views over analytic workspaces, or analytic workspace data can be accessed directly through the use of the OLAP_TABLE function.

There are now a number of opportunities open to Express customers who wish to re-engineer their applications to take advantage of the wider Oracle9i and Oracle9iAS platform, through the ability of standard SQL to call OLAP DML programs using the OLAP_TABLE function, together with the ability to directly call OLAP DML commands and programs directly through PL/SQL.

With Oracle10g, Oracle are extending the features introduced with Oracle9i and opening up access to a range of query tools such as Oracle Discoverer and Microsoft Excel. Oracle10g is being released, so keep an eye on OTN and Plus Consultancy's Web site, where more details will be published as they become available.

About the authors

Mark Rittman is a consulting manager and Jon Mead is a principal consultant at Plus Consultancy, a Certified Oracle Partner specializing in business intelligence and data warehousing. Mark and Jon have worked with Oracle's business intelligence products for many years and are regular speakers on Oracle OLAP at the UKOUG Conference and Business Intelligence SIGs.

Dig Deeper on Oracle DBA jobs, training and certification

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.