for simple allocations, this feature is very
useful. For example, we’ll use this capability
to allocate budget values for a particular
sales city, based on the performance in a
Like calculations, ASO database allocations can be performed with Calculation
Manager or MaxL scripting. Unlike calculations, however, MaxL scripted allocations do
not reference a separate calculation script
but instead include all the information
required for the allocation in just the one
Our simple allocation example requires
the following six parameters:
The POV, which provides the context
and scope within the database for the
The amount, a static member that
constitutes the amount to be allocated.
It can be expressed as a member or a
numeric expression, and only those
dimensions that are not part of the POV
are allowed in the amount expression.
AmountContext, a static MDX tuple that
qualifies the amount expression.
Target, another MDX tuple that consti-
tutes the target region over which the
amount X POV members get allocated.
Generally this parameter and the next
(region) are used together, and if the
region parameter specifies all the
dimensions to be allocated, the target
can be empty.
Region, an MDX set expression that rep-
resents the complete target region over
which the amount X POV members get
allocated. The dimensions specified in
the target, region, and POV should all be
Basis, which is used mainly for per-
forming share allocations but is optional
for spread allocations.
More-complex allocations may require
additional parameters. See the Oracle
Essbase Technical Reference Manual, available from Oracle Technology Network, for
Creating an ASO database allocation. In the
following example, we will generate budget
figures for the next three months in Qtr2 for
the New York office, based on the total sales
figure for the same office for Qtr1. To put this
in terms of an allocation, the budget amount
is being allocated over the descendants of
Qtr2, based on the actual amount for the
same office in Qtr1.
Open the Oracle Essbase Administration
Services console, and enter valid login
2. Essbase Servers node, and
further expand the node for your Oracle
3. Applications folder, and highlight the DemoASO application.
On the Administration Services menu,
select File -> Editors -> MaxL Script
Editor, and then enter the command
below in the Editor dialog box.
Historically, aggregate storage option databases have been restricted to simple sales
analysis applications that do not require
complex calculations or write-back of data
or allocations. In the 11. 1. 2 release of Oracle
Essbase, the aggregate storage option
has been enhanced to permit calculations
against leaf-level data and to allow allocations from non-level-0 members to leaf-level members. Developers can now use the
more space-efficient and faster-aggregating
ASO database type for planning- and
budgeting-style applications. W
execute allocation process on
database DemoASO.BasicASO with
Mark Rittman is an Oracle ACE director and
a cofounder of Rittman Mead Consulting, a
U.K.-based Oracle partner providing specialized
business intelligence, data warehousing, and
performance management solutions.
Venkatakrishnan Janakiraman is an Oracle
ACE and principal consultant at Rittman Mead
Consulting, specializing in Oracle Essbase and
its integration with Oracle Business Intelligence
5. Execute Script button to run the
Right-click the database in Oracle
Essbase Administration Services and
select Preview Data to show how the
data for Qtr1 is then allocated across the
months of Qtr2, as shown in Figure 2.
READ more about calculations, allocations,
Oracle Essbase Technical Reference Manual
DOWNLOAD the sample outline and
datafile for this column