The Oracle 10g Enterprise Edition OLAP Option extends the analytic capabilities of the Oracle database by providing new multidimensional datatypes, a multidimensional calculation engine, and a framework to build OLAP applications using SQL, PL/SQL, and Java. This article explains the background of the OLAP Option, shows how it extends the analytic capabilities of Oracle data warehouses, and gives you the first steps in harnessing the power of the OLAP Option.

Background

Most Oracle data warehousing DBAs and developers would have heard of OLAP, a process by which data is loaded into virtual cubes and then “sliced and diced” using one or more dimensions. OLAP, or Online Analytical Processing, focuses on query performance and ease of use for data analysts, and has been a feature of the Oracle product line for many years.

Ever since the OLAP industry began, there has been debate over the best way to store and query the data used for OLAP analysis. Some advocate storing data in relational tables and querying the cubes and dimensions using SQL, giving you a familiar query language and the advantages of using an enterprise-class RDBMS to store your data; others believe that OLAP data is best processed using a dedicated multidimensional query language and held and stored in specialized multidimensional databases. Oracle has for many years had a “foot in both camps,” with relational OLAP being provided by the Oracle database, and multidimensional OLAP being provided by Oracle Express Server, a technology Oracle acquired from IRI Inc. in 1995.

With Oracle 10g, Oracle has incorporated the Express Server multidimensional engine into the core Oracle database, which is now known as the OLAP Option.

Introducing the OLAP Option

The OLAP Option consists of five key elements:

• Additional multidimensional datatypes, used for holding cubes and dimensions, that can be temporary or stored permanently in LOBs within schema tables
• A multidimensional calculation engine that processes the new datatypes
• A Java application developer framework that works with both relational and multidimensional data, together with reusable OLAP components to use with JDeveloper
• Extensions to SQL to allow SQL access to these multidimensional datatypes
• An additional layer of OLAP-specific metadata known as the OLAP Catalog

Because the OLAP Option is based on the Express Server calculation engine and multidimensional datatypes, it brings across all the Express functionality such as forecasts and demand plans, support for financial models, allocations and budgeting, and support for what-if analysis. Also, unlike relational OLAP cubes, multidimensional OLAP Option cubes are usually “fully solved,” with all aggregations computed at load time, giving a faster, more predictable response time for users’ queries.

Another advantage of using the OLAP Option is that you will then be able to deploy Oracle’s more advanced OLAP query tools, such as BI Beans and the forthcoming Oracle OLAP-enabled Discoverer “Drake” release, which allow your users to build up complex, multidimensional queries using a proper OLAP-style measure and dimension selector.

These Oracle OLAP-enabled query tools allow your users to create more powerful queries, yet present data items to them in a more intuitive way than with SQL reporting tools such as Oracle Reports and Oracle Discoverer, by using the new Java OLAP API and the additional metadata stored in the OLAP Catalog.

A key difference between the OLAP Option, and other vendors’ multidimensional OLAP offerings, is that Oracle OLAP is stored and managed directly in the Oracle database — OLAP data can be partitioned just like normal relational tables, it can take advantage of technologies such as RAC, Data Guard, parallel query and RMAN, and is managed using Oracle Enterprise Manager, just like the rest of the database.

Creating an Analytic Workspace

Once you’ve installed the OLAP Option, the first step in working with the new multidimensional datatypes is to create an “analytic workspace.” An analytic workspace is a multidimensional data source that can be temporary or persisted as a LOB in a relational table.

To create your first analytic workspace, start up the OLAP Analytic Workspace Manager that comes on the Oracle 10g Client CD. Log in as your schema owner, then select Tools > OLAP Worksheet from the menu bar. Think of the OLAP Worksheet as the equivalent to the SQL Worksheet; commands you type are in the bottom pane, with the output displayed in the top pane.

The OLAP Worksheet uses a special language for Oracle OLAP, called OLAP DML. Based on the Express SPL, OLAP DML is a language not unlike PL/SQL that’s been around for over 20 years, and allows you to create, query, and programmatically control multidimensional datatypes using procedural constructs such as conditions, loops, and subroutines. You can enter OLAP DML commands using the OLAP Worksheet, or you can execute them from PL/SQL using the DBMS_AW.EXECUTE procedure. All the while you’re in the OLAP Worksheet you’re actually working within an Oracle schema, and in fact you can switch between OLAP DML and SQL if you need to execute an SQL command.

To create your first analytic workspace, type in

aw create my_first_aw

Your analytic workspace should now be created. If you take a look in your schema, you’ll find a new table with an AW$ prefix, that contains the analytic workspace you’ve just created.

Next, we need to create some dimensions. Dimensions are our business entities and consist of lists of values that are used to categorize our analytic workspace data. In our case we want to create two dimensions, “Geography” and “Product.”

Unlike the Oracle CREATE DIMENSION statement that defines all the dimension levels and the hierarchies in one go, with OLAP DML, you define all the levels as individual dimensions, wrap this up in a “concat” dimension that concatenates the values in individual dimensions, then create what’s called a “relation” object that describes the hierarchical relationship between the level values. For example, with our Geography dimension, we’d type in

CREATE city DIMENSION TEXT
CREATE county DIMENSION TEXT
CREATE country DIMENSION TEXT

DEFINE geography DIMENSION CONCAT (country county city)

DEFINE geography.parents RELATION geography 

The key differences between OLAP Option dimensions and relational dimensions are that relational dimensions use level-based dimensions, whilst OLAP Option dimensions are parent-child based. Level-based Dimensions’ hierarchies are defined by the relationship between levels, and levels map to columns in relational tables. Oracle OLAP dimensions, however, use parent/child relationships between levels, where dimension members map to a parent column and a child column. The parent/child combination in a given row expresses a hierarchical relationship, and this relationship is stored in the analytic workspace relation object. The advantage of the parent/child approach is that unbalanced or ragged hierarchies can be more easily used, as each route down the hierarchy doesn’t need to contain the same number of levels and can be individually defined for each dimension member.

The values that are to be contained in the dimensions are loaded in a later process, together with the links between dimension members that are loaded into the relation object.

Now, do the same for our products dimension

CREATE sku DIMENSION TEXT
CREATE category DIMENSION TEXT
CREATE class DIMENSION TEXT

DEFINE products DIMENSION CONCAT (class categoy sku)

DEFINE products.parents RELATION products 

Once we have created our dimension objects, we next create a variable to hold our transactional data. A variable corresponds to a relational fact table with a single measure, and is created using the command

DEFINE sales VARIABLE NUMBER (10,2) < geography products>

This tells the OLAP Option to create a variable called “sales,” and dimension it by our geography and products concatenated dimensions.

To add data into our dimensions, we can simply type

MAINTAIN city ADD 'LONDON' FIRST, 'PARIS' AFTER 'BERLIN'

Or we can write a program in OLAP DML to retrieve data from an Oracle database

ALLSTAT
NLS_DATE_FORMAT = '<YYYY><MM><DD>'
DATEFORMAT = '<YYYY>-<MM>-<DD>'
SQL DECLARE prod_cur CURSOR FOR SELECT prod_name FROM sales
SQL IMPORT prod_cur INTO :APPEND sku
UPDATE
COMMIT

OLAP DML commands can be stored in programs, much like Oracle stored procedures, and contain all of the looping, conditional, variable definition, and control functions that you’d find in an 4GL language.

To report on data in an analytic workspace, you use two commands, LIMIT and REPORT. The LIMIT command allows you to “hone-down” your query selection in stages, building up a query in a multi-step process that follows the users’ analysis into the data. For example, you might issue the following LIMIT commands.

LIMIT geography TO city
LIMIT product TO ‘EGGS’ ‘BREAD’ ‘APPLES’

With the data then being reported using

 REPORT across product : sales

                                ------------SALES-------------
                                -----------PRODUCT------------
 GEOGRAPHY                         EGGS     BREAD     APPLES
 ------------------------------ ---------- --------- ----------
 <CITY : LONDON>                 34234.34   3523.34   34235.45
 <CITY : BERLIN>                  2354.23    342.23    2353.34
 <CITY : PARIS>                  64344.23   6434.45   64453.45

Now that you’ve got your data into an analytic workspace, you might want to take advantage of some of the features of the OLAP Option engine, such as the ability to create a sales forecast,

FORECAST LENGTH 12 METHOD EXPONENTIAL FCNAME sales.fcst TIME month sales

create financial models,

DEFINE sales.calc MODELLD Model 
for calculating Sales 
MODEL
dimension line
net.sales = opr.sales - taxes
opr.sales = gross.sales - (marketing+selling+r.d)
gross.sales = revenue - cogs
END

or preaggregate your data to speed up query response times.

DEFINE sales.agg AGGMAP <time, product, geography>
AGGMAP
RELATION time.r PRECOMPUTE (time NE 'Year99')
RELATION product.r PRECOMPUTE (product NE 'All')
RELATION geography.r
CACHE 
STOREEND

All of these are possible with OLAP DML, and full details of the language syntax can be found in the Oracle OLAP Developer’s Guide to the OLAP DML on OTN.

However, as an alternative to manually creating analytic workspaces using OLAP DML, Oracle have provided the OLAP Analytic Workspace Manager to provide access to analytic workspaces using a graphical user interface. OLAP Analytic Workspace Manager allows you to create analytic workspaces either from scratch or based on data in relational tables, create and maintain dimensions, variables, formulas, models and programs, and load and refresh those objects with data from the Oracle database.

SQL Access to OLAP Option Data

Data held in analytic workspaces can be queried using SQL SELECT statements using the new OLAP_TABLE and an Oracle feature called “Abstract Data Types,” used to provide a relational shape to our multidimensional data.

For example, to query our analytic workspace called sales, which is dimensioned by products and geography, we would first define an abstract data type to hold a row of data for our SELECT statement:

create type SALES_ROW as OBJECT(
  product                    varchar(20),
  city                       varchar(20),
  sales                      number(10,2),

Then we create another abstract data type that creates a table of these row types.

create type SALES_TABLE as table of SALES_ROW

Lastly, we use our SELECT statement, with the OLAP_TABLE function, to retrieve rows into our table abstract data type.

select product, city, sales,
  from table(OLAP_TABLE('my_first_aw DURATION session',
   'SALES_TABLE',
   '',
   'DIMENSION sku from product
    DIMENSION city from geography
    MEASURE sales FROM sales
    ));

OLAP DML commands and calls to programs can be embedded in OLAP_TABLE calls and by using the DBMS_AW PL/SQL package, allowing full interaction and the passing of data between the SQL engine and analytic workspaces.

As well as providing SQL access, the OLAP Option comes with a Java OLAP API, together with the OLAP Catalog, a layer of metadata that sits above both relational star schemas, and OLAP Option analytic workspaces that is used by JDeveloper to build OLAP-aware applications using Oracle’s BI Beans technology.

Issues and Shortfalls

Licensing the OLAP Option extends the analytical capabilities of the Oracle database, and gives your users the option to use Oracle’s more advanced analytic OLAP applications with their data. But what are the shortfalls and issues with the Oracle OLAP technology.

The first issue is that, although your OLAP Option data will be held and managed within the Oracle database, you still need to go through a process of loading this data into analytic workspaces before you can use it. This process takes time, and is not all that different to loading the data into a standalone OLAP engine. Although Oracle provide tools to make this process easier, so do third-party OLAP vendors, and you will still need to factor this process into your overall data warehouse load and refresh window.

Also, although OLAP DML is easy to use (compared to languages such as Java, or C++) it is still another language to learn, and although again tools such as Oracle Warehouse Builder or the OLAP Analytic Workspace Manager remove much of the OLAP DML coding, you’ll still need to understand OLAP DML to take advantage of financial models, forecasting, budgeting, and so on.

Lastly, even though Oracle has now integrated the OLAP engine into the Oracle database, time will probably show us that the integration at this point in time was still at an early stage. We still have to explicitly load data into analytic workspaces, and we still have to use the OLAP_TABLE function to get data into a SELECT statement. It will probably be the case, in versions of Oracle to come, that this integration will become far more embedded, such that the database itself will decide whether to store data relationally or multidimensional, or a bit of both, with data being made available to SQL in a completely transparent way.

Summary

The Oracle 10g Enterprise Edition OLAP Option extends the analytic capabilities of the Oracle database by providing new multidimensional datatypes, a multidimensional calculation engine, and a framework to build OLAP applications using SQL, PL/SQL and Java. Uniquely, the OLAP Option uses the Oracle database to store and manage its data, allowing you to take advantage of the scalability, reliability and manageability of the Oracle 10g platform.

--

Mark Rittman is a Certified Oracle Professional DBA and works as a Consulting Manager at SolStonePlus, specializing in developing BI and DW applications using the Oracle Database, Application Server, Discoverer, Warehouse Builder, and Oracle OLAP. Outside of Plus Consultancy, Mark chairs the UKOUG BI and Reporting Tools SIG, and runs a Weblog dedicated to Oracle BI and Data Warehousing technology. Mark recently was awarded an OTN Community Award for contributions to the Oracle developer community, and is a regular speaker at Oracle User events in both the UK and Europe.