September/October 2010
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:
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:
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:
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:
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:
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:
([Bud Var],[Jan],[Sales],[Stereo]) := ([Actual],[Jan],[Sales],[Stereo]) - ([Budget],[Jan],[Sales],[Stereo]);
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.
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.
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:
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.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;
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.READ more about calculations, allocations, and parameters |
Photography by Ricardo Gomez, Unsplash
Integrated Cloud & Platform Services
