Database Application Developer
BUSINESS INTELLIGENCE BY MARK RITTMAN AND VENKATAKRISHNAN JANAKIRAMAN
Using Oracle Essbase
Release 11. 1. 2 Aggregate
Storage Option Databases
Take advantage of new persistent calculation and
allocation capabilities for planning and budgeting
applications.
ORACLE ESSBASE
Oracle Essbase developers creating plan- ning and budgeting applications have
been keenly aware of the advantages and
disadvantages of the two different storage
types available to them:
Block storage option (BSO) databases •
support a wide range of calculations,
including forecasts, allocations, and aggregations through procedural calculation
scripts. The results of these calculations can
be written back to the database, but applications that use BSO storage are limited to
about 10 dimensions and member counts
in the low hundreds of thousands.
Aggregate storage option •;(ASO) databases can support about 20 dimensions
and member counts in the millions. ASO
databases use multidimensional functions
rather than procedural scripts to perform
calculations, but the results of queries
cannot be stored back in the database,
making ASO unsuitable for most financial
management applications.
With the 11. 1. 2 release of Oracle Essbase,
developers no longer need to make this
trade-off between the functionality provided
by BSO databases and the scalability of ASO
databases: Release 11. 1. 2 brings persistent
calculation and allocation capabilities to
ASO databases.
In this column, we explain how the
new features work, using an example ASO
database and several scripts that persist a
calculation in the database and then allo-
cate budget figures based on the previous
quarter’s actual figures. The examples in this
article require the following products to be
installed and configured:
Oracle Essbase Release 11. 1. 2 server •
New;CalCulatioN;Capabilities;for
aggregate;storage;optioN
Prior to the 11. 1. 2 release of Oracle Essbase,
the only way to create calculations against
data in an Oracle Essbase ASO database
was through a query in MDX (the data
manipulation language for Oracle Essbase)
generated by an application such as Oracle
Business Intelligence Enterprise Edition.
ASO databases were routinely loaded
with detail-level data from a data warehouse, aggregated, and then made available for query. Calculations were subsequently performed dynamically, at query
execution time, by the calculation engine,
but results could not be persisted back into
the database.
With the 11. 1. 2 release of Oracle Essbase,
ASO databases are no longer limited to cal-
culations based on MDX queries. Now we
can also take the leaf-level (or “level-0” in
Oracle Essbase terminology) data, perform
calculations on it to create additional detail-
level data, and then aggregate this data
before making it available to users. We can
also load detail-level data as before, aggre-
gate it, and then run allocation routines that
use this aggregated data to populate other
level-0 data entries. This feature enables us
to do things such as using last year’s total
sales figures to generate individual sales
targets for next year.
