By default, only data values can be loaded into Essbase. However, sometimes it is helpful to load dates into an application (i.e. Product Inception Date). Follow these steps to enable an Essbase application to accept dates as data. Read more
Tag Archive for: Essbase
What is a Smart List?
- Allows for creation of custom lists that can be used in data forms
- Goes beyond the limitations of Essbase: these lists are not limited to numbers only
- Users select a member from a designated list (each Smart List cell has a dropdown arrow that expands to allow member selection in web forms) Read more
In Hyperion 11.1.2.1, there is a change in how security is deployed. If you are having an issue deploying Planning security with Essbase adhoc access, and the user can’t
- Access Essbase Adhoc
- Access FR reports using an Essbase connection
- Access Essbase directly
you are not alone. Read more
The generic rule in Essbase is that calculations FIX on sparse members because sparse members are what define the number of blocks. When you want to limit the members of the block on which the calculation is executed, an IF statement is appropriate. Read more
How many times have you been in a situation where you have to traverse through hundreds of lines and errors from an Essbase data load only to figure out that all the rejected records are caused by an issue with one member? You load the file again and wham – another error file with issues you didn’t see the first time.
Although this is typically less of an issue in a production environment, these situations are very likely in the development and testing phases of a project.
In2Hyperion is introducing another free tool that will navigate through errors and summarize the reasons for the rejects. Read more
The introduction of Hyperion 11.1.2 has some fantastic improvements. Many of these have been long awaited. The next few articles on In2Hyperion will describe some of the enhancements to Hyperion Planning, Hyperion Essbase, and Hyperion SmartView.
XREF Background
If you have been developing Planning applications, you are probably very familiar with the XREF function. This function is used in business rules, calculation scripts, and member formulas. It provides a method to move data from one plan type (Essbase database) to another plan type. It is executed from the target database and pulls the data from the source. XWRITE was actually introduced in later versions of 11.1.1.x, but is very stable in 11.1.2.x. XWRITE is executed from the source and pushes data to the target. This function is a huge improvement over XREF. Read more
Although implied shares can improve performance by not storing the same data multiple times, it has many negative impacts. For example, implied shares cause problems in Hyperion Planning at the load level (level 0). A parent with a storage property of Stored that has one child (or only one child that consolidates) will create an implied share. This results in level 0 members being locked, preventing web form data entry. In Essbase/Planning, the storage method of any parent with one child has to be changed to Never Share to allow user input.
For those of you who have been snake bitten by this, you will welcome a relatively unknown Essbase configuration setting in the Essbase configuration file (essbase.cfg). 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.
Database Export
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
The format of the data that is loaded to Essbase is often an after-thought. But, should it be? When requesting the data file from a source system, it is more important than you may think to have it sorted to mirror your outline.
Assume an outline has the following dimensions.
- Period [DENSE]
- Account [DENSE]
- Region [SPARSE]
- Category [SPARSE]
- Product [SPARSE]
- Organization [SPARSE]
The most efficient way to receive a data file would be to have it sorted by Organization, Product, Category, Region, and then Account. Data files load faster when the columns that hold the sparse members are sorted in reverse order of the sparse dimensions that exist in the outline.
The reason the data loads faster is because it opens a block of data only one time. If the data was sorted by the dense members first, then every block would have to be opened multiple times. If the same sparse member combinations have 3,000 dense members with data, the block would be opened up to 3,000 times.
There are some more important benefits of doing this, however. When the block is opened multiple times, the database becomes far more fragmented than it needs to be. Fragmentation causes calculations to be slower and retrieving data can also be impacted, which can lead to frustrated customers.
By not sorting the data when loaded, every time a data load occurs, any performance issues that may exist are exacerbated. So, anytime possible, sort the data load files by the last sparse dimension in the outline, the second to last sparse dimension in the outline, and so on. You may be presently surprised at the benefits.
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.