Rittman Mead - Delivered Intelligence

Oracle have released for public download the

Microsoft Excel-based OLAP Calculation Builder, that I’d

mentioned earlier in the year. Using this piece of shareware, you can use an
Excel template to create, en-masse, calculations that go into your Oracle
Database 10gR2 analytic workspace.

According to the

readme file, "Oracle OLAP 10g Release 2 provides an open API for
accessing metadata and defining OLAP calculations in analytical workspaces. 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."

The calculations that you create can either use existing
templates, or any valid OLAP DML syntax (if-then-else, calls to custom functions
and so on). The spreadsheet works by using ODBC to query the analytic workspace
Active Views (views over the cube metadata held within the analytic workspace),
then calls a Java program that uses the AW/XML API to create the calculations.
From what I’ve seen, it looks like a useful piece of code if you need to create
a set of calculations in bulk, things like time series calculations, margin
calculations and so on, which up until now you’d need to either create using the
AWM user interface, or script yourself using AW/XML.

Whilst I was on the OLAP Homepage, I noticed that they’d
released an updated version of the

GLOBAL sample schema, which now comes with some code that implements
fine-grained security over the products dimension, and scripts to create
relational views (which use the MODEL clause, to improve performance) over the
GLOBAL analytic workspace. Worth downloading if you’re looking to implement
individual user security over an AW’s data, or you’re trying to work out to
create SQL views over your OLAP data.

Posted in Oracle OLAP | 1 Comment »