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

Calculation Manager: Enable Parallel Execution - Bug and Trick

When creating new Ruleset Designer there is an option to execute rules in parallel as given in the following screenshot.

However, as soon as you add / assign Rules under this ruleset, it disappears.

Note that this option is only available when the ruleset is selected and not the individual rule. So, you need to select / click on the ruleset to get this feature.

I believe this to be as a bug however there are at least two ways to get this option back.

• Save and close the ruleset and reopen.
• Another option is go to another tab in workspace and come back to this ruleset and you will see the option visible / enabled.

Job Console Entries...

Job console data is stored in table “HPS_JOB_STATUS” and when planning is restarted this data is cleared. After some research I finally found the details from here.

There is a property which can be set to never clear / delete these details or clear / delete periodically.

Property Name: JOB_STATUS_MAX_AGE

Value: 60000 (1000*60 for 1 minute – this is value in milliseconds)

Value: -1 (setting -1 will never clear / delete the data)

To set the property:

1.       Select Administration, then Application, then Properties.

2.       Select Application Properties to set properties and values for a Planning application.

3.       Add the property by clicking Add, entering the property in the blank row, and entering a value in Property Value

Reference / More Info:

1. http://docs.oracle.com/cd/E17236_01/epm.1112/hp_admin/frameset.htm?properts.html

2. https://community.oracle.com/thread/2516103?tstart=0

Calculation Manager Business rule / Script validation issue

Business rule / Script not getting validated when line of code exceeds certain number

We have a calculation manager business rule that calls a script which calls a template around 22 times by passing different entity values. When we compiled the business rule it threw the following error:

A validation error was received from the Planning server 'http://<URL>:80/HyperionPlanning/servlet/HspAppManagerServlet?appname=<appname>'.

'Error:Error parsing formula for [<member name>] (line 5106): infinite loop in macro processing; current macro [@VAL] Rule <Rule Name>'.

On analysis, it is observed that when we made less than or equal to calls (17 – where line of code is less that 5000) to the template then it successfully compiled. In short, any further calls (more than 17 – i.e when line of code is probably greater than 5000) to the script system failed to compile.

To confirm where this is the issue with number of line, I created a rule with entire logic written as part of single script which had more than 5000+ lines. Again this time the system failed to validate and gave the same error.

Viewing Hierarchy by Plan Type - "Filter by Plan Type"

When having more than one plan type, whenever I wanted to see the hierarchies separately by Plan Type wise, I always used to go to EAS console to check the hierarchies. I never knew that there is an option right in front of me in workspace (11.1.2.2) which could make life easier for me and not to login to EAS console just to view the hierarchies.

When you click the filter icon “Filter by Plan Type”, system provides necessary options and selecting it one can view hierarchy based on Plan type.

MDX Query - Count Level Zero Members

In one of our implementation I wanted to know the number of level zero members in a dimension and after some research finally ended up having following MDX query:

WITH

Member [Product].[ProCount] AS 'Count([Product].Levels(0).members)'

SELECT {[Year]} on AXIS(0),

{[Product].[ProCount]} on Axis(1)

FROM [Sample.Basic];

Syntax for MaintenanceMode Utility

You may be aware that MaintenanceMode utility can be used to temporarily withdraw access to applications during maintenance. As part of metadata refresh process, I used this utility to make sure no users are logged in the application, however I was not able to execute it successfully using the syntax provided in documentation.

Syntax provided is as follows:

MaintenanceMode [-f:passwordFile], /A=app, /U=user, /P=password,  /LL=loginLevel [ALL_USERS|ADMINISTRATORS|OWNER], [/DEBUG=[true|false]], [/HELP=Y]

Note the comma after "[-f:passwordFile]"; this is not required.

Following command works without any errors (in windows):

CALL %BinPath%\MaintenanceMode -f:%PasswordFile% /A=%HypApp%, /U=%UserName%, /LL=ADMINISTRATORS

Time Balance and Formula on Summary Time Period

System ignores the formula on summary time period when Time Balance is not "Flow"

For P&L (Profit and Loss) and BS (Balance Sheet) accounts, we set the Time Balance property depending upon the account type. Generally, Flow for P&L accounts and "Balance" for BS accounts.

We had a Measure dimension that mainly had 2 members.The requirement was that for one measure member users wanted to see data as "Flow" (Q1 = Jan + Feb + Mar, Q2 = Apr + May + Jun) where as for another member they wanted to see data as "Weighted Average - Actual_Actual" i.e.

(Weighted daily average, based on the actual number of days in a year; accounts for leap year, in which February has 29 days. In the example, the average for Q1 is calculated: (1) Multiply each month’s value in Q1 by the number of days in the month, (2) Sum these values, (3) Divide the total by the number of days in Q1. Assuming it is a leap year, the result is calculated: (10 * 31 + 15 * 29 + 20 * 31) / 91 = 15)

In this case we decided to have custom formula on summary time period with appropriate condition on measure members. We kept the Time Balance for all the accounts as "Balance" as we normally have.

While testing, it was observed that the formula on summary time period was ignored and Balance (TB Last) data was displayed on summary time period.

When we changed the Time Balance property to "Flow" system displayed the expected output based on the formula defined on summary time period.

Essbase DataExport

We all know about the limitation on the Dataexport command as we do not have much control over the formatting and ordering of columns. But one is sure that this is one of the fastest way to get the output as compared to Report Script.

An interesting observation was when using DATAEXPORTOPTIONS.

I wanted to extract level zero data from all dimensions except one upper level member from measure dimension. Measure hierarchy was something as follows and wanted to extract value of Balance which is upper level member.

Balance
 - Calculated Balance (+)
 - Adjusted Balance (+)

To my surprise, following code gave the required output:

SET DATAEXPORTOPTIONS
{
DataExportLevel LEVEL0;
}

FIX("Balance")

  DATAEXPORT "File" "," "output1.out";

ENDFIX

Based on this, one can extract upper level member even when DataExportLevel is set to LEVEL0.