One of the challenges with Hyperion Planning is the ability to move data between applications in real time. A classic example of this is a P&L application with other modules that have greater detail. The following is an example. Read more
One of the huge benefits that available in Groovy Calculations is the ability to interact with a user, validate data, and act on the validation. Now, we can interrupt form saves, stop Run Time Prompts from continuing, and communicate information back to the user. Read more
We all know the Data Form validation rules are serviceable, but they are not robust. When Smart View advanced and forms were opened in Excel, the validation logic developers had in JavaScript became useless. Since then, we have really missed the ability to communicate with the user interactively with visual cues and validation rules that halted the saving of data. Well, Groovy calculations to the rescue! Read more
Recently, Groovy scripting was added to ePBCS business rules as an option instead of the GUI, or the go-to scripting for you old-timers who still refuse to change. These are defined in the Business Rule editor as Groovy calculations. So, what is Groovy? Read more
One of the problems with giving users of Hyperion Planning the ability to run calculations is opening up the possibility for all of them to run the same calculation at the same time. Read more
I recently learned the importance of encrypted passwords in batch files. Without a password file, the scripts will still run, but the user is prompted to input a password in the command prompt after initialization. Encrypted passwords allow for the automation of these scripts. Shout out to Sumit Deo for his patience in guiding me through the initial process and helping me with my batch scripting skills along the way. Read more
I was recently testing out the performance of rulesets that were attached to forms, and ran into an issue that had me scratching my head. Certain rulesets were not running or even appearing on forms that they were attached to in the Planning application. Turns out, the rules had been corrupted in the transfer between Calc Manager and the Planning application Read more
There are several ways to export data from Essbase on a large scale. Pulling it via Excel (Smart View or the Essbase Add-In) is not the best way to get large amounts of data when the goal is to move the data somewhere else, so this option will not be covered.
The easiest method is to export all the data from a database by exporting the database. This can be done in EAS. This method is easy to automate with Maxl, but has little flexibility with formatting and the only option is to export all the data. It can be exported in column format so the data can easily be loaded into another data repository. If the data needs to be queried, or manipulated, this is a good option. Read more
Everybody knows the quickest way from point A to point B is a straight line. Everybody assumes that the path is traveled only one time – not back and forth, over and over again. I see a lot of Essbase calculations and business rules, from experienced and novice developers, that go from point A to point B taking a straight line. But, the calculation travels that line multiple times and is terribly inefficient.
Here is a simple example of a calculation. Assume the Account dimension is dense, and the following members are all members in the Account dimension. We will also assume there is a reason to store these values rather than making them dynamic calc member formulas. Most of these are embedded in a FIX statement so the calculation only executes on the appropriate blocks. To minimize confusion, this will not be added to the example.
Average Balance = (Beginning Balance Ending Balance) / 2; Average Headcount = (Beginning Headcount Ending Headcount) / 2; Salaries = Average Headcount * Average Salaries; Taxes = Gross Income * Tax Rate;
One of the staples of writing an effective calculation is to minimize the number of times a single block is opened, updated, and closed. Think of a block as a spreadsheet, with accounts in the rows, and the periods in the columns. If 100 spreadsheets had to be updated, the most efficient way to update them would be to open one, update the four accounts above, then save and close the spreadsheet (rather than opening/editing/closing each spreadsheet 4 different times for each account).
I will preface by stating the following can respond differently in different version. The 11.1.x admin guide specifically states the following is not accurate. Due to the inconsistencies I have experienced, I always play it safe and assume the following regardless of the version.
You might be surprised to know that the example above passes through every block four times. First, it will pass through all the blocks and calculate Average Balance. It will then go back and pass through the same blocks again, calculating Average Headcount. This will occur two more times for Salaries and Taxes. This is, theoretically, almost 4 times slower than passing through the blocks once.
The solution is very simple. Simply place parenthesis around the calculations.
( Average Balance = (Beginning Balance Ending Balance) / 2; Average Headcount = (Beginning Headcount Ending Headcount) / 2; Salaries = Average Headcount * Average Salaries; Taxes = Gross Income * Tax Rate; )
This will force all four accounts to be calculated at the same time. The block will be opened, all four accounts will be calculated and the block will be saved.
If you are new to this concept, you probably have done this without even knowing you were doing it. When an IF statement is written, what follows the anchor? An open parenthesis. And, the ENDIF is followed by a close parenthesis. There is your block!
"East" (IF(@ISMBR("East")) "East" = "East" * 1.1; ENDIF)
I have seen this very simple change drastically improve calculations. Go back to a calculation that can use blocks and test it. I bet you will be very pleased with the improvement.
Often times with a Hyperion Essbase or Planning application, an allocation of data will be required. Many times, the allocation is simply moving data from one member to another. When the number of members involved is large, developing the script can be time consuming. When the members frequently change, the maintenance of the calculation can be a nuisance.
When the members involved in the allocation are similar on both sides (the from and the to), the following method can be employed to speed the development and limit, or eliminate, any maintenance required.
The application has 50 members in which the data needs to be moved. The data originates from an account coming from the general ledger. The data needs to be moved to a new member that doesn’t exist in the chart of accounts. The new member will exist in a different part of the hierarchy.
The first step is to create a corresponding member for each of the 50 accounts that need allocated. These accounts will be identical to the original 50, except they will be prefixed with a “D” identifying them as a dummy, or made up, account. Each of these new accounts will have a UDA of “allocation.” The prefix of the member and the UDA are not critical. They will likely be something more meaningful to the requirements.
| GL Acct | Dummy Account |
| 500345 | D500345 |
| 500578 | D500578 |
| 607878 | D607878 |
Once the hierarchy is ready to handle the allocation, the following function can be used. In layman’s terms, this only executes on the new members added (identified by the unique UDA) and makes them equal to the corresponding member without the added prefix. We will assume that this is being executed on a scenario that equals “Actuals.”
FIX(@UDA(“allocation”))
/* Make the new member equal to the old member */
“Actuals” = @MEMBER ( @SUBSTRING ( @NAME(@CURRMBR(“Account”)) , 1));
/* Clear the old member */
@MEMBER ( @SUBSTRING ( @NAME(@CURRMBR(“Account”)) , 1)) = #Missing;
ENDFIX
Let’s assume that the UDA is NOT added to the new, or dummy, member. If the UDA is on the originating member, the calculation would look like this.
FIX(@UDA(“allocation”))
/* Make the new member equal to the old member */
@MEMBER ( @CONCATENATE(“D”,@NAME(@CURRMBR(“Account”)))) = “Actuals”;
/* Clear the old member */
Actuals = #Missing;
ENDFIX
Now we can break down these functions. Remember, the calculations loop through all members in all dimensions. In this example, setting the result equal to “Actuals” is simply making the account that the calculation is looking at, at that particular point in the loop, equal to whatever is on the other side of the equation.
@MEMBER ( @SUBSTRING ( @NAME(@CURRMBR(“Account”)) , 1))
There are four functions used in this string.
Putting this all together, this calculation (from inside out) is getting the current member of the Accounts dimension (“d345678”). It converts that member to a string. It takes all the characters to the right of the first character (“345678”). Then it converts the string back to a member. At this point, we can set that member equal to something.
@MEMBER ( @CONCATENATE(“D”,@NAME(@CURRMBR(“Account”))))
The functions here are the same as above, except we are not removing the “d.” We are adding it.
Putting this all together, this calculation (from inside out) is concatenating two strings, a “D” and the current member of the Accounts dimension (“d345678”). It then converts the string to a member. At this point, we can set that member equal to something.
By using these functions, the calculations can be much smaller, quicker to develop, and completely maintained by the outline. This effectively gives the user community ownership on the maintenance.