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.

SmartView - Install Cube Designer for OAC - Oracle Analytics Cloud

Steps to install Cube Designer is quiet simple but even after following all the steps and/or re-installing SmartView I was not able to see the cube designer under Extension tab / window.

For the Cube Designer to appear in the Extension tab / window, I had to first create a private connection to the cloud instance and then it worked.

So, the steps to install Cube Designer,
1. Create a private connection to the cloud instance.
2. From SmartView Tab, Click Options -> Extensions
3. If you see "Cube Designer", then click "Enable"
4. If you do not see the "Cube Designer", then click on the link "Check for Updates, New Installs and Uninstalls". "Cube Designer" should now appear, then click "Enable".

Hope this helps...

@CalcMgrDateDiff and @CalcMgrGetDatePart

@CalcMgrDateDiff:

Syntax:

@CalcMgrDateDiff(fromDate, toDate, date_part)

date_part options:

• "day"
• "month"
• "week"
• "year"

Example(s):

VAR V_Start = 20160701;
VAR V_End = 20170630;
VAR V_Diff = 0;

V_Diff = @CalcMgrDateDiff(V_Start, V_End, "day");

Returns 364

V_Diff = @CalcMgrDateDiff(V_Start, V_End, "month");

Returns 11

V_Diff = @CalcMgrDateDiff(V_Start, V_End, "week");

Returns 52

V_Diff = @CalcMgrDateDiff(V_Start, V_End, @NAME("year"));

Returns 1

Note that when there exists dimension "Year" you may need to put the date_part inside "@NAME" as you see in the last example.



@CalcMgrGetDatePart:

Syntax:

@CalcMgrGetDatePart(date,date_part_ex)

date_part options:

• "dayofmonth"
• "dayofyear"
• "month"
• "weekofmonth"
• "weekofyear"
• "year

Example(s):

VAR V_Start = 20160701;
VAR V_Num = 0;

V_Num = @CalcMgrGetDatePart (V_Start, @NAME("year"));
 
Returns 2016

V_Num = @CalcMgrGetDatePart (V_Start, "dayofmonth");

Returns 1

"BegBalance" = @CalcMgrGetDatePart (V_Start, "dayofyear");

Returns 183

V_Num = @CalcMgrGetDatePart (V_Start, "month");

Returns 7

V_Num = @CalcMgrGetDatePart (V_Start, "weekofmonth");

Returns 1

V_Num = @CalcMgrGetDatePart (V_Start, "weekofyear");

Returns 27

Loading Metadata: Improve Performance by using file compare utility

There are many reasons for an organisation to refresh metadata daily in Hyperion Planning and now a days you will generally have at least 1-2 hierarchies which could have 1000s of members. Not to mention if there are alternate hierarchies with shared members then outline load utility might take a while to delete and reload hierarchies.

There was a situation where my client had very tight schedule within which all the hierarchies needed to be reloaded and we were not able to fit that due to the source system’s variations in delivering the files.

So to resolve this issue and/or reduce the impact, it was decided to check every hierarchy whether there is any change compared to previous day’s file. If change exits then load it else skip and go to next. This was achieved by using Windows FileCompare utility and worked quiet well.

Following is the extract from the file which calls outline load utility to load hierarchies one after another.

LoadHierarchies.Bat (extract):

SET DimName=Account

SET InputFileName=%DimName%_dim.csv

SET LoadLogFile=FC_%DimName%_Load.log

CALL D:\script\Custom_FileCompare %InputFileName% %LoadLogFile%

if %CUSTOM_RESULT% EQU 0 goto :LOAD_Another

SET LoadLogFile=%LogFilePath%\%DimName%_Load.log

SET LoadExceptionFile=%LogFilePath%\%DimName%_Load.err

CALL Outlineload -f:%PasswordPath% /A:%App% /U:%UserName% /I:%DimFilePath%\%InputFileName% /D:%DimName% /L:%LoadLogFile% /X:%LoadExceptionFile%

:LOAD_Another

Other Code goes here...

In the following file “PreFilePath” is the path where previous day’s file(s) are placed while “DimFilePath” points to current or latest file which shall be loaded based on comparison.

Custom_FileCompare.bat:

SET CUSTOM_RESULT=0

SET CompFileName=%~1

SET FCLogFileName=%~2

SET File1=%PreFilePath%\%CompFileName%

SET File2=%DimFilePath%\%CompFileName%

FC %File1% %File2% > %LogFilePath%\%FCLogFileName%

SET CUSTOM_RESULT=%ERRORLEVEL%

if %CUSTOM_RESULT% EQU 0 ECHO Same files. Ignore this hierarchy load.

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.

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

Function: @Return

@Return is one of the very useful function that is introduced in 11x. I have been using it more frequently now and some of the cases are listed below:
Syntax:

@RETURN ("ErrorMessage", ERROR)

1. In custom Workforce planning when business need to add TBH (To be Hired) with basic information such as Start Date, Grade, salary. While adding, system need to validate that date and salary is provided and this is where @return comes handy. If validation fails, one can use @Return function with appropriate message.

2. In one of the requirements, there was a need to archive existing projects to dummy members (user selected) and if required restore back to existing available project member. system must not allow user to move project to a member that already has data. So, it was necessary to give appropriate message to the user. I used the following command in case the validation fails:


@RETURN(@CONCATENATE
(
@CONCATENATE
(
            @CONCATENATE("ERROR: Failed to archive selected project [", @NAME({Project})),
@CONCATENATE("] under Entity [", @NAME(@CURRMBR("Entity")))
)
,
@CONCATENATE
(
@CONCATENATE("]. The target project [", @NAME({Archive_Project_to})),
"] is already in use. Try using another new / archived project."
)
)
, ERROR);

The message looks as follows:

"ERROR: Failed to archive selected project [Project1] under Entity [Entity1]. The target project [Temp_Proj1] is already in use. Try using another new / archived project."

Web-form: Substitution variable error

One of my colleagues was trying to use a substitution variable in a web-form and he got the following error message:

“A substitution variable required for this calculation is undefined. Variable: Years_Act

He defined new substitution variable in planning and assigned the value as “FY10:FY13”.

There is nothing wrong here as this can easily be used in calculation script or business rules but when using this in Web-form, we can only have single valid value not range as shown above.

So, few points to note when using substitution variable in web-form are as follows:

1. Make sure you define substitution variable.
2. Make sure value is not blank
3. Make sure you have assigned a valid member name (value) (not range)

Calculation Manager: Lexical Error. Encountered

In calculation manager, I was specifying the member name in “Member Range” manually and for one member I incorrectly included in a single quote instead of double quote. On validating I got an error with the message that includes:

Lexical Error. Encountered: <EOF> after: “”