During my most recent consulting project, I had a chance to implement the Aggregate Storage Option (ASO) feature in Oracle Hyperion Planning Version 188.8.131.52.
Creating additional plan types whether they’re Block Storage Option (BSO) or ASO is easy in Version 184.108.40.206. They can be created one of two ways:
At the time of application creation, you can specify additional plan types, through the application creation wizard
Or…the really cool feature is, even after you’ve created and deployed the Planning application, you can still add ASO cubes
To do this, simply navigate to Administration -> Manage -> Plan Types.
Just click the “Add” button on the top and add another ASO cube to the application.
Refresh the application to push it down to Essbase. The great feature here is that, through the Planning front end, we can manage dimensions to each of these plan types separately. The thing to keep in mind is that this is still a “Planning” application and, as such, the ASO cubes you’ve built are part of it.
I’ll demonstrate this functionality through a sample “CU_PLAN” application, where we have four plan types, two BSO and two ASO cubes. They include:
PLAN – BSO
HCP – BSO
PLAN_RPT – ASO
HCP_RPT – ASO
The advantage of creating these ASO cubes is obviously for reporting capabilities. With any application that utilizes a large dimension set with several sparse members, ASO cubes make reporting on data much faster. By also utilizing the “Map Reporting” functionality in 220.127.116.11 to push this data between the BSO data entry cubes and ASO reporting cubes, you can keep your data in sync.
Now to a big disadvantage…unfortunately, Oracle does not support these new ASO cubes in an Essbase connection on Smart View in 18.104.22.168. This essentially means that, security filters created for end users from shared services on the Planning application (in this case CU_PLAN) will not be created for the ASO cubes.
What this means is that, as an end user, if you log into CU_PLAN using an Essbase connection using Smart View, you will NOT see these ASO reporting cubes:
This makes these reporting cubes useless for an end user, if they intend to create their own reports. However, since admins on shared services have default access, only admins can view and retrieve data on this ASO plan type in Smart View:
We were able to circumvent this issue by creating native Essbase ASO cubes. Then, we used a script to copy the Planning ASO cubes into these native ASO cubes (in order to keep dimensionality in sync), and then copied data from the Planning ASO cubes into the native ASO cubes. Lastly, we created Essbase-only security filters using the Planning security filters that were created in shared services. This can be done by exporting security filters from the Planning tables and then using MaxL to generate the new Essbase security filters.
Another big disadvantage that we noticed using the ASO plan type was on performing data retrieves using Smart View using a Planning ad hoc connection:
Data retrieval times on the ASO plan type took in excess of 45 seconds when pulling Level 0 data with more than two dimensions in the rows. The basic rule when performing retrieves is to limit how many dimensions are pulled into rows. The more sparse dimensions in rows, the longer the retrieval times, especially for large dimensions. But even with that understanding, our application retrieval times began at 45 seconds and performed even more poorly with more dimensions in the rows. This naturally is something most companies will find difficult to tolerate especially when the need is to have faster report access and generation!
For those interested in diving deeper into the solution of exporting security filters from Planning, you can find a few blogs online that show you how it’s done, including using SQL to extract the filters from Planning tables. There is also plenty of information available on how to write MaxL scripts to import these into the ASO cubes and to assign them to specific applications, dimensions, etc.
As of the date of publication, Oracle has not set a timeframe on when these Planning ASO cubes will begin functioning the same as native applications.
Author: Mohan Chanila, Performance Architects