Refresh of ASO Plan Type failing while setting up an Alternate Hierarchy in PBCS

I was working on PBCS application where the application had a BSO cube and ASO cube. After some initial setup and dimension creation we were creating an alternate hierarchy in Period dimension. Somehow Cube Refresh was failing for ASO plan type. It seemed that the issue was with the shared member but that was not the problem.

The error message I got for my ASOPlan (cube name) was as follows:

Cube refresh failed with Essbase Cube: ASOPlan, Error code: 1060053, Message:

The message was not clear but the issue was that I had one of the member defined as Ignore in the period hierarchy for ASO. It is suggested that all members must be set as Addition and then it will work (for ASO). This worked for me.

Following is the checklist if you encounter similar issue:
1. Check that the Hierarchy Type of Period Dimension is set to Multiple
2. Check the Hierarchy Type of hierarchies in Period dimension are set to "Stored"
3. Check all the members are set as "Addition" (for ASO)

If the point number 2 from above is not satisfied i.e. if the Hierarchy type is set to "Dynamic" then you may get the following error message:

ASO members with time balance members requires a time dimension to only contain stored hierarchies. Member: <Member name>

After appropriately updating the member / hierarchy, if the issue still persists then try re-creating the alternate hierarchy. I had similar problem post changing the properties and after re-creating the member(s) it worked.

Hope this helps.

Do away with aggregation in BSO by using ASO plan type in Planning Application

In my Hyperion Planning implementation experience, I have worked more on implementation of customized Workforce Planning for various clients than any other module and it has always been a challenge when it comes to aggregating the data (in BSO application).

But this time with an option of ASO plan type and CDF to copy data from BSO to ASO, it has made things quiet interesting and easy to implement workforce planning.

I have been working on customized workforce planning where we have in total of 10 dimensions within one Hyperion Planning application.

Following are few details on number of members in various dimensions:

Employee – 150K members (based on various regions / Entities)

Entity – 18K

Level / Positions – 48K

Employee Categories - 19

As part of PoC, initially we developed an application without ASO plan type and the aggregation times ranged from 10 mins to 20 mins per region which was bit at higher side than expected. Not to mention that the page file size was in GBs (25 to 30+ GBs).

We then explored the possibility to have ASO plan type with the design such that most of the user input and level zero calculations were performed in BSO and the review / reporting at upper level will be done via ASO cube.

We used following artifacts to extract and move data from BSO to ASO:

1.       Data Export to export data to a file.

2.       Following CDF MaxLFunctions to execute MaxL which loads data in ASO using rule file.

RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLFunctions

The above steps are performed in one Calc Mgr rule and it executes in less than a couple of minutes.

User can execute this rule based on user variables and see the data in FR report or via Smart View immediately at upper level from ASO cube.

Also, index and page file are less than few GBs combined.

Point to note that the "RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLFunctions" may not work in Unix machines.

I was getting the following error:

Error: Cannot run program "essmsh": error=2, No such file or directory 

 and for it to work try the following:

In your OPMN.xml file, add the below line:

<variable append="true" id="PATH" value="$ESSBASEPATH/bin"/>

It should be as below:

<variable id="ESSBASE_CLUSTER_NAME" value="EssbaseCluster1"/>
<variable append="true" id="PATH" value="$ESSBASEPATH/bin"/>
</environment>


Restart Essbase service once for the changes to take effect.

MDX Queries - Misc

I have always find it difficult to have some examples ready to query using MDX. Here are some examples and will be adding more:

To get the leaf level members:

SELECT 

             {[Time].levels(0).members} 

ON ROWS

To get all members of one dimension:

SELECT 

             {[Time].members} 

ON ROWS

To get asymmetric columns:

SELECT {

([Dim1 Member1],[ Dim2 Member1]),

([Dim1 Member2], Dim2 Member2)

} ON COLUMNS