TAGS

Recent Posts

Archives

Advantages and Disadvantages of the Aggregate Storage Option (ASO) Plan Type Feature in Oracle Hyperion Planning Version 11.1.2.3
Posted on May 7, 2014
Author: Mohan Chanila, Performance Architects

During my most recent consulting project, I had a chance to implement the Aggregate Storage Option (ASO) feature in Oracle Hyperion Planning Version 11.1.2.3.

Creating additional plan types whether they’re Block Storage Option (BSO) or ASO is easy in Version 11.1.2.3. 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 11.1.2.3 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 11.1.2.3. 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

Share
© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Leave a Reply

Your email address will not be published. Required fields are marked *