@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

More Options to filter by Job type in Job Console

Under the “Job Type” drop down, you can now see following options:
1.    Business Rules
2.    Ruleset
3.    Sequence
4.    Clear Cell Details
5.    Copy Data
6.    Push Data
7.    Copy Decision Packages

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: 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.

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.

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."

Using Custom Defined Function @HspConcat() and @HspNumToString()

Many times we use smart-list for mapping purposes. We store ids that directly or indirectly match with member names so that we can perform calculations. I had similar requirement and wanted to get the smart list value for example (1001) to concatenate with “ENT” (to get ENT1001) and perform calculation. So, I was looking for a function that will convert numeric to String and then which concatenate. I achieved by using @HspConcat() and @HspNumToString() functions as follows:

@MEMBER(@HspConcat(@LIST("ENT",@HspNumToString(<Account Member>))))

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: “”