TECHNOLOGY: Business Intelligence

As Published In
Oracle Magazine
September/October 2010

  

Using Oracle Essbase Release 11.1.2 Aggregate Storage Option Databases

By Mark Rittman and Venkatakrishnan Janakiraman

 

Take advantage of new persistent calculation and allocation capabilities for planning and budgeting applications.

Oracle Essbase developers creating planning 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 allocate 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
  • Oracle Essbase Administration Services Release 11.1.2

To try the examples in this column yourself, download the sample Oracle Essbase Release 11.1.2 outline and data load file. The download includes information (readme.txt) about how to set up the database and import the data.

 

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 calculations 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, aggregate 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.

Creating an ASO calculation. You can create a calculation in an Oracle Essbase Release 11.1.2 ASO database in either of two general ways: 

  • Use Calculation Manager (included with Release 11.1.2 of Oracle’s enterprise performance management system) to create the calculation graphically and then store it in a library so that it can be used across multiple applications.  
  •  
  • Use MaxL scripting—it has the advantage of being easily automated and enables us to focus more on the structure of the calculations than on the Calculation Manager features—which is the approach we will use for the examples in this column.

 
The Oracle Essbase Release 11.1.2 ASO database we will use in these examples contains actual and budget data for several sales regions and cities over several periods. We want to calculate the budget variance between actual and budgeted sales for our New York and Boston offices and store the results in the database before aggregating the data and making it available for analysis.

Creating a calculation in Oracle Essbase Release 11.1.2 with MaxL requires you to create two script files: 

  • A calculation script that contains the actual calculations to be performed
  •   

  • A MaxL script that references the calculation script, along with some mandatory and optional parameters

 

You can create these script files with a text editor or through Oracle Essbase Administration Services Release 11.1.2.

As with the Oracle Essbase BSO scripts, the ASO scripts can contain multiple sets of calculations. However, whereas BSO calculations are executed serially and can reference calculations performed on dimension members in prior steps, calculations within an ASO calculation script are executed in parallel, as long as sufficient resources are available to Oracle Essbase at execution time. This has advantages when you're running calculations against the large data sets typical of ASO databases, but you have to define separate calculation and MaxL scripts if you want to define multistep ASO calculations that are executed in a particular order—something to bear in mind if you are used to creating BSO calculations.

In our example, we want to use actual and budget data in the scenario dimension to calculate the budget variance for two of our offices. To do this, we develop three parts (or parameters) of the calculation:

 

  • A tuple expression that defines how the budget variance is calculated, based on actual and budget data
  •  

     

  • A point of view (POV) definition that restricts the calculation to the New York and Boston offices
  •  

     

  • A source region definition that describes to Oracle Essbase the database region in which to perform the calculation

 

The tuple expression is contained in a calculation script. The POV and source region definitions are contained in a MaxL script that references the tuple expression.

To create this calculation with Oracle Essbase Administration Services, perform the following steps, using the DemoASO.BasicASO database:

 

  1. Open the Oracle Essbase Administration Services console, and log in to the Oracle Essbase server that holds the sample database.
  2.  

     

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

     

  5. Expand the Applications folder, and highlight the DemoASO application.
  6.  

     

  7. On the Administration Services menu, select File -> Editors -> Calculation Script Editor. Within the Editor dialog box, type in the following calculation, which is the tuple expression that will calculate the budget variance for sales of stereos in January.

     

     

    ([Bud Var],[Jan],[Sales],[Stereo]) 
    := ([Actual],[Jan],[Sales],[Stereo]) 
    - ([Budget],[Jan],[Sales],[Stereo]);
    

     

     

  8. Save this file within the directory containing the ASO database (typically, {ESSBASE_HOME}/app/DemoASO/BasicASO/), using the filename BudVar.csc. You have now created the calculation script that will be called by the MaxL script defined in the next steps.
  9.  

     

  10. On the Administration Services menu, select File -> Editors -> MaxL Script Editor. When the Editor dialog box is shown, type the following script into it, amending the path to the calculation script as necessary.

     

     

    execute calculation on database DemoASO.BasicASO with
    local script_file "/u01/app/Middleware/user_projects/epmsystem1/
    EssbaseServer/essbaseserver1/app/DemoASO/BasicASO/BudVar.csc"
    POV "Crossjoin({[Jan]},
    Crossjoin({[New_York],[Boston]},
    Crossjoin({[Stereo]},
    Crossjoin({[Sales]},{[Bud Var]}))))"
    SourceRegion
    "Crossjoin({[Actual],[Budget]},
    CrossJoin({[Jan]},
    CrossJoin({[Sales]},{[Stereo]})))";
    

     

    Note the POV definition in the script, which restricts the calculation to the intersection of January, New York, Boston, and Bud Var (budget variance), and the SourceRegion definition that declares which database cells will be affected by the calculation.

     

  11. Click the Execute Script button in the Editor dialog box to run the calculation.

 

When the script runs, it assigns the values specified in the script to the corresponding intersections. To view the results of the calculation, right-click the database within Oracle Essbase Administration Services and select Preview Data from the menu. The results should look similar to those in Figure 1.

sep/oct 2010 essbase image 1

 

Figure 1: Results of the ASO calculation

 

After the calculation completes, you can query the database by using any query tool, such as the Oracle Business Intelligence Enterprise Edition application. The Oracle Essbase server presents fully aggregated data to the query tool.

 

New Allocation Capabilities for Aggregate Storage Option

Another new feature in Oracle Essbase Release 11.1.2 is support for allocations in ASO databases. Allocations can take place from cells at any level in the database’s dimension hierarchies but can be used only to write back changes at the level-0 (leaf) level.

 

ASO database allocations are not quite as flexible as BSO allocations. For example, ASO allocations cannot make relative references to dimension members, nor can ASO allocations write allocated figures to non-leaf-level member intersections. However, 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:

 

  1. The POV, which provides the context and scope within the database for the allocation.
  2.  

     

  3. 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.
  4.  

     

  5. AmountContext, a static MDX tuple that qualifies the amount expression.
  6.  

     

  7. Target, another MDX tuple that constitutes 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.
  8.  

     

  9. Region, an MDX set expression that represents 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.
  10.  

     

  11. Basis, which is used mainly for performing 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.

Next Steps

READ more about calculations, allocations, and parameters
Oracle Essbase Technical Reference Manual

DOWNLOAD the sample outline and datafile for this column

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 credentials.
  •  

     

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

     

     

  • Expand the 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.

     

     

    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;
    
  •  

     

  • Click the 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.
  •  

 sep/oct 2010 essbase image 2

Figure 2: Results of the allocation routine

  

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.

 

 


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.

 

Send us your comments