Oracle OLAP
Creating OLAP Calculations using Microsoft Excel
Release 10.2.0.3.0

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Copyright © 2007, Oracle. All rights reserved.

Creating Oracle OLAP Calculations using Microsoft Excel

Oracle OLAP 10g Release 2 provides an open API for accessing metadata and defining OLAP calculations. This sample leverages this openness to enable the definition of Oracle OLAP calculations using Microsoft Excel. After defining these calculations, users can then access the calculations through Oracle Business Intelligence 10g (including OracleBI Spreadsheet Add-in, OracleBI Discoverer or OracleBI Beans) or any SQL-based tool.

Before You Begin

Before you begin to work with the sample, ensure that the following prerequisites have been met:

• Oracle Database 10g Release 2 with the OLAP option is installed
• ODBC access to the database is available on the client computer.

To install the sample:

• Download the sample to the computer where Microsoft Excel is installed. Unzip the file.
• Open the OLAP Calculations.xls file:
  • Excel will prompt to enable macros. Click Enable Macros.
  • If necessary, update the program home directory so that it points to the location where you unzipped the sample. This directory should contain the file SpreadsheetCalcs.bat
  • Specify connection details to the database containing the target analytic workspace. Note, you must log in as the owner of the Analytic Workspace (AW).
• The spreadsheet includes sample calculations that can be defined against the Global sample schema (available for download on OTN). Use these as examples for defining calculations and delete them before defining the calculations in your own analytic workspace.

Creating Oracle OLAP Calculations

Using Excel, you can create Oracle OLAP calculations using a template or a free-form equation. The following templates are supported:

• Cumulative Total Parent, Cumulative Total Total
• Lag, Lead
• MovingAverage, MovingMax, MovingMin, MovingTotal
• Period To Date
• Prior Period, Prior Period Variance, Prior Period Variance Percent
• Percent Future Period
• Same Period Ancestors Ago, Same Period Ancestors Ago Variance, Same Period Ancestors Ago Variance Percent
• Share to Level, Share to Parent, Share to Total
• Year To Date

Using a free-form equation, you can specify any valid OLAP DML syntax (including if-then-else, calls to custom functions, etc.).

In the example below, the measure named How_is_Margin is defined as an Equation. Equations require the following fields: Name, Long Label, Short Label, Cube, Folder and Free-form Equation. Required fields for template-based calculations depend on the selected template. For example, a Lag template has different input requirements than a Share to Total template. Required fields are highlighted with a red triangle - and these highlights update appropriately based on the selected calculation template. In cases where the value for fields can be selected from a list (e.g. the list of Cubes in an analytic workspace), drop-down lists will display the relevant values.

Calculations will be defined in the AW specified in the Select Target AW field. You can change the target Analytic Workspace by clicking on the AW field (Global in the example below).

After specifying the calculation details, click the Define Calculations button. The calculations will be defined in the target analytic workspace. When using the OLAP Query Builder in OracleBI Discoverer, OracleBI Spreadsheet Add-in or Oracle Analytic Workspace Manager, you will find the calculations organized into the folders you identified in the Folder column.

Hint: The tool will attempt to define those rows starting with "Equation" or "Template". If you want to save the definitions of the calculations but not process them, simply delete the Calculation Type value.

Hint: To save the definitions of your calculations or define calculations for other analytic workspaces, make multiple copies of the OLAPCalculations.XLS file. For example, create HR.XLS for your human resources analytic workspace, Finance.XLS for your financial analytic workspace, etc. This is possible because the macros for running the application are stored with the Excel document.

Implementation Details

The sample leverages the openness of Oracle OLAP. All Oracle OLAP data and metadata is accessible through both Java and SQL APIs.

Microsoft Excel connects to Oracle Database 10g in order to collect the OLAP metadata required to populate the drop down lists that simplify calculation definition. This metadata includes cubes, dimensions, measures, levels, etc. It uses Excel's native ODBC database connectivity to perform this task.

Oracle OLAP metadata is accessible thru OLAP's Active Views. For example, to select the list of AWs accessible to the current user, the following SQL statement is issued:

SELECT aw FROM all_olap2_aws

The result of this query is used to populate the Target AW list. When the user changes the value for the target AW, the following SQL query is issued to select the available cubes for the AW:

SELECT aw_logical_name FROM all_olap2_aw_cubes WHERE aw_name='" & strAWName & "'"

Here, strAWName is a VBA variable containing the selected analytic workspace. Similar queries are run to determine the available measures, dimensions, hierarchies and levels.

Once the calculation details have been specified, the user clicks on the Define Calculations button. The Calculations worksheet is saved as a CSV file and the SpreadsheetCalcs.bat file is automatically invoked. At this point, a Java program is run that reads the input file and uses the Oracle OLAP Analytic Workspace Java API to define the calculations in the target analytic workspace.

A snippet of the code used to define the OLAP calculations is listed below:

DerivedMeasure derivedMeasure = cube.createDerivedMeasure();
derivedMeasure.setName(name);
derivedMeasure.setLongName(longLabel);
derivedMeasure.setShortName(shortLabel);
derivedMeasure.setExpressionText(equation);

Here, a new derived measure (i.e. a measure that is a free-form equation) is created in the cube specified in the spreadsheet. Its name, long label, short label and equation are then set.

Copyright © 2007 Oracle Corporation
All Rights Reserved