Oracle Magazine - September/October 2010

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 previous period.

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 MaxL script.

Our simple allocation example requires
the following six parameters:
The POV, which provides the context 1.
and scope within the database for the
allocation.

The amount, a static member that 2. 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 3. qualifies the amount expression.

Target, another MDX tuple that consti- 4. 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- 5. 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 mutually exclusive.

Basis, which is used mainly for per- 6. 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 more information. 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 1.

Services console, and enter valid login credentials.

Expand the 2. Essbase Servers node, and further expand the node for your Oracle Essbase server.

Expand the 3. Applications folder, and highlight the DemoASO application.

On the Administration Services menu, 4. select File -> Editors -> MaxL Script Editor, and then enter the command below in the Editor dialog box.

CONCLUSION 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 pov "Descendants([Product], [Product].levels(0))" amount "([Qtr1])" amountcontext "([Actual], [New_York],[Sales])" target "([Budget], [New_York],[Sales])" range "Descendants([Qtr2], [Year].levels(0))" spread;

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 Enterprise Edition.

NEXT STEPS

Click the 5. Execute Script button to run the allocation routine.

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.