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

The issue first appeared when looking at a Planning form. Prior to the testing, I had attached a ruleset to the Brokerage and Commission form. However, when looking at the form, the ruleset was nowhere to be found:

However, with a little digging, I was able to verify that there is indeed a ruleset attached to the form:

So, where is the breakdown happening?

From the Planning application, let’s take a look at the Business Rules to see if we can gather any more information:

Notice that some of the rulesets say “None” instead of displaying the play button. Broke_Comm, our missing ruleset, is displaying “None”:

It looks like we found the issue. These rules have been corrupted and will not launch in the Planning application. That would explain why we were not seeing the rulesets on the forms. Some maintenance is required to get the rules back up and running. Here are the steps I took to fix the issue:

Open up Calc Manager and select deployment view:

Expand the “To Be Deployed” folder and uncheck all of the rules (this list of rules should match up with the corrupted rules – rules displaying “None” in the Launch column – from the Planning app):

Right click on the application in deployment view and select Deploy:

In Planning, verify that there are no longer any non-launchable rules:

Next, head back to Calc Manager and check all of the rules that are under the “To Be Deployed” folder. Right click on the application and deploy. Navigate back to the Business Rules tab in the Planning application, where all of the rules should now be launchable:

Looking back at the Brokerage Commission form, the Ruleset that is attached is now displayed:

Note: This works most of the time, but sometimes following the above steps will not bring back all of the corrupted rules/rulesets. When this happens, I’ve found that the easiest solution is to:

1. Take an LCM backup of Calc Manager in Shared Services
2. Delete the rules that are corrupt
3. Import the rules from the backup that was just taken
4. Navigate to Calc Manager->Deployment View and Deploy all of the newly imported rules

This second option might take a little bit longer than the first solution outlined, but it will clean up your rules/rulesets and get them back to performing as expected.

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

1. Access Essbase Adhoc
2. Access FR reports using an Essbase connection
3. Access Essbase directly

you are not alone. This is not classified as a bug, but it sure can cause a lot of frustration. If you have a user that needs access to Essbase directly, the user can’t be associated to ONLY Planning applications. For them to get access to Essbase, even to access the Planning application, they must have security to a native Essbase application (a NON Planning application).

From the Oracle Doc ID 1328741.1

SYMPTOMS

Trying to add the “Essbase” application access type to a Planning user in Shared Services so the user can access native Essbase applications using the Excel Add-in. Shared Services confirms the update when saved but when checking the user again, it only has “Planning” access. This also happens when trying to use the MaxL command to modify the application access type.

CAUSE

Starting in EPM v11.1.2, a user’s type (application access type) cannot be changed by Maxl, the EAS console or manually via Shared services console. The type is automatically assigned based on the roles that the user has.

• If a user has a role on a Planning application only, then that user is treated as a Planning user.
• If the user has a role on a Essbase application only, then the user is treated as an Essbase user.
• If the user has roles on both Planning and Essbase applications the user is treated as a Planning and

Essbase user.

SOLUTION

In order for a Planning user to access native Essbase applications in the Excel Add-in, the user will need to be given access to a native Essbase application. For example, assign the Planning user “Read” access to the Demo application.

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.

Quick Overview of Dense and Sparse

If you are unfamiliar with the concept of dense and sparse, here is a quick overview.  A data block in Essbase is constructed from the dense dimensions of the database.  The number of members in each dense dimension impacts the size of each data block.  The combination of a member in each sparse dimension is what defines a block.  The number of members in the sparse dimensions directly correlates to the number of blocks that may exist.

For a more detailed overview, reference Sparse, Dense, and Blocks For Dummies.

Comparison To Relational Database

A FIX is a lot like a SELECT statement in a relational database using a WHERE clause.  The WHERE clause limits the number of records, an Essbase FIX limits the number of blocks for which an action is taken.  An IF statement in Essbase is similar to a CASE statement in a relational database in that it executes on all the records and acts only when a criteria is met.

Limiting the records

Relational Example

UPDATE table_name
SET Salary=Annual Salary * Merit Increase
WHERE Year=2013;

Essbase Example

FIX(“2013”)
  Salary = “Annual Salary” * “Merit Increase”;
ENDFIX

Executing on all records when they meet criteria

Relational Example

SELECT 
   CASE 
      WHEN Year = 2013 THEN Salary = Annual Salary * Merit Increase 
      ELSE Salary = Salary 
   END 
FROM table_name

Essbase Example

IF(@ISMBR(“2013”))
  Salary = “Annual Salary” * “Merit Increase”;
ENDFIX

When running an UPDATE query, limiting the number of records is more efficient than running the query on all the records and checking for specific criteria to execute the logic.

Why Fix On Dense?

The reason we are taught to FIX on sparse dimensions and use IF on dense dimensions is that a FIX will improve performance by limiting the number of blocks on which the calculation executes.  There is no reason to FIX on dense dimensions because it isn’t limiting the number of blocks on which the calc is executed.

Forget all that!

Calculations still run for every intersection, not JUST the intersection of sparse members.  Assume a calculation fixes on one intersection of sparse members.  Also assume that there are 20 measures and 12 periods that are stored, and both dimensions are dense.  The following calculation

Salary = Annual_Salary * Merit_Increase;

would run on every dense combination, so it would execute 240 times (12 x 20).  You can easily prove this by incrementing the value of one dense member by 1.

Salary = Salary   1;

If Salary starts as #Missing, or 0, and the above line is executed, Salary will be 20 for each month.

Solution

This can easily be resolved.  Since you only want the calculation to execute one time on the block, add one member from the measures dimension to your fix statement.  This member doesn’t have to be the member you are calculating.  I typically will fix on a generic measure to eliminate confusion.  Change the calculation to the following.

FIX(No_Measure)
  Salary = Salary   1;
ENDFIX

Make sure Salary is set to #Missing or 0, and execute the new calculation.  When the new calculation script is executed, you should see a value of 1 for every month.

In a situation where Salary = Annual_Salary * Merit_Increase, the result will be correct regardless of whether the calculation fixes on one measure, but the performance will be far worse when executed on every Measure because it will run the same calculation multiple times.

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.  If 1,000 errors occurred because of one member, the feedback provided will show one line.

Hopefully our community will be able to use this utility to save themselves time and frustration.  It’s a free download!  For more information about the license, requirements, and installation, read Show Unique Essbase Error utility page.

It was a pleasure meeting a ton of new people in the industry at Kscope 2013, and being exposed to another great city.  It included 4 nights on Bourbon Street and 4 days of fantastic presentations.  Attendance was up 17% from last year!

Once again, I thank all those who presented and organized the event.  I want to extend a special thank you to all the attendees who recognized in2hyperion for its work in the Hyperion community.  Because of the number of people who offered to pay me for the In2Hyperion Excel Ribbon, I am now accepting donations. Your generosity will help fund the maintenance of the ribbon and the website. Donate Now!

If you would like to read my presentation, download it here.  I strongly believe that automation, if done right, can enrich the lives of those maintaining and using Hyperion applications.  Hopefully, it will inspire you to rethink the possibilities that automating your tasks can have on your life and those around you.  In the presentation, I discuss what these utilities offer, including the layout of the scripting and the setup of the batch utility from the command line.  I explain in detail the EPMA Batch Utility, the Planning utilities most often used, the LCM Utility, and parameterizing MaxL.

OK, I have opened up a ticket with Oracle.  Now, they want my logs.  Frustration sets in, and I wonder…how long is it going to take to figure out where they all are and which ones are important?  If I miss one, my resolution will surely get delayed. This is all I need!

In newer versions of the Hyperion environment, Oracle has made some huge improvements.

First, the logs are much more centralized.

• The installation logs are now located in EPM_ORACLE_HOME/diagnostics/logs.
• The configuration, service startup, and runtime logs for all the service components are in EPM_ORACLE_INSTANCE/diagnostics/logs.
• The web application runtime logs are in MIDDLEWARE_HOME/user_projects/domains/domain name/servers/managed server name/logs.

Second, they have a batch, or shell script depending on the environment, that will zip all the needed logs into one file so you can easily provide all the detail for Oracle to be as efficient as possible in diagnosing my problem.

In the EPM_ORACLE_INSTANCE/bin folder, the ziplogs.bat (ziplogs.sh) script will create a zip file with all the logs for me, and place the zip in EPM_ORACLE_INSTANCE/diagnostics/ziplogs.

Now, there is one file per server with the appropriate collection of logs, configuration files, and other pertinent info that the help desk can use.