Why Can’t Planners Access Essbase

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.




FDM: Loading Data to Multiple Databases Within the Same Application

Although FDMEE is the data management tool of the future for Workspace, it is still lacking some of the basic functionality that can be utilized in FDM classic. One of these issues arose recently on my current project: How can we have 2 separate load rules in FDMEE, but have them each pointing to a separate database in the same application? The answer, it seems, is that you can’t. To begin, let me describe the issue in FDMEE in a little more in depth…

First, in the locations tab, notice that there are 2 separate locations for our planning app (DFPLAN2). IFS_Plan should be pointed to the FinPln database, while RevCOGS should be pointed to the RevCOGS database. Notice that they are both tagged to point to the application DFPLAN2, with no differentiation of databases:

 

The same issue arises in the location details for each location, as there is nowhere to discern between the two databases:

 

This brings up the issue of bringing in the wrong dimensionality  for each of the locations. RevCOGS includes the BusinessUnit dimension, even though its Essbase cube does not have that dimension:

 

And IFS_Plan includes Customer & Product, even though those should be ignored for this database:

 

Upon importing the data to the data load workbench, the data does not validate. The only output given is a couple of blank columns (which doesn’t provide much intuition to go off of). That leads us back to our issue at hand…How can we distinguish between the 2 plan types, so that we have the correct dimensionality for our data loads? The simplest solution that we found is to create another Target System Adapter. This is done via the FDM Workbench on the FDM server.

 Log on to the server and open the workbench. Once in the workbench you will see your Target system adapters:

 

To copy an adapter, right click and select “Copy…”. Name the adapter (here we have named ours Essbase2)

 

Expand on the adapter and expand on the dimensions folder. The activated dimensions will be black, while the non-active dimensions are greyed out. Notice that each database has a different set of activated dimensions, and how the user-defined dimensions (UD1, UD2, etc) are customized for both.

 

Since each database has different dimensionality, each adapter will have a unique set of activated dimensions. To edit which dimensions are active, right click on the desired dimension and select “Properties…”

 

In the properties screen, the name and alias of the dimension are customizable. Make sure that these match up to each database’s dimensionality in Essbase. Down below are 2 checkboxes. One activates the dimension, the other notes whether or not the dimension is a required field. Leave a checkmark next to the “Active” box for all dimensions in the database.

 

Next, right-click on the adapter itself, and select “Adapter Options”. From the dropdown, select “Essbase DB Name”. Here is where to input the relevant database name, so that FDM will know which it is pointing at during the import process:

 

Notice that we identified a different database for both of the Target system adapters (RevCOGS & FinPln):

Now that we have made that change on the FDM server, we will see those changes take affect when we look at the import formats for both RevCOGS and IFS_Plan in FDM Classic. UD2 (Source Custom2) is Product and UD3 (Source Custom3) is Customer. They are being picked up from column 1 and column 2 (as noted by the field number column below) of our load file, respectively:

For IFS_Plan, BusinessUnit is UD2 (Source Custom2) and is grabbed from column 5 in our data file:

To conclude, we were successfully able to distinguish between 2 databases in 1 application. Remember, this was only necessary because the databases had different dimensionality.  We were not able to do this in FDMEE, rather in FDM Classic, which means we cannot load more than 1 period at a time. That is the downside to this solution, but until Oracle includes the functionality in FDMEE, it seems to be our best option.




Debunk The Myth: Never Fix On Dense Members

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.




Summarize The Essbase Data Error File

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.




My Grid Rows Aren’t Aligned To My Data Rows

No, your eyes aren’t playing tricks on you. The grid lines don’t align with the row headers. It is very slight on smaller forms, but forms with hundreds of rows compounds the issue. The further down the grid, the more of an issue the offset is.

If you have ever seen this issue and pulled your hair out trying to figure out why it happens with some users and not others, fixing it is embarrassingly (now that I know) easy. Change the zoom in IE back to 100%!

Take a look at the bottom right area of IE.  If you see that the zoom is NOT 100%, select the View / Zoom / 100% menu.

Hopefully this will save you some time if you ever run accross this issue.




Change Application Maintenance Mode via Command Line

Patch Set Update: 11.1.2.1.600 offers a welcome utility

If you have ever tried to automate the state of a Hyperion Planning applications’ Application Maintenance Mode, you found it difficult. The only way to accomplish this was to run a SQL Update on the repository table, and for this to take effect, the Planning service had to be restarted.

If you are unfamiliar with the Application Maintenance Mode setting, it is found in Administration/Application/Settings menu. Changing this setting from All Users to Administrators, locks out planners from using the application. It is typically used when changes are made to hierarchies, web forms, system settings, security, and during deploys, to keep users out while changes are being introduced.

Patch Set 11.1.2.1.600, and the corresponding patch release for 11.1.2.2, introduces a new utility that allows administrators to change this setting from a command line. YEAH, it can now be automated without restarting Planning!

Without Further Adieu

MaintenanceMode.cmd (or MaintenanceMode.sh in UNIX) is found in the <EPM_PLANNING_INSTANCE> directory. The following parameters can be passed, separated by commas.

  • /A=app – Application name (required)
  • /U=user – Name of the administrator executing the utility (required)
  • /P=password – The administrator’s password (required)
  • /LL=loginLevel – [ALL_USERS|ADMINISTRATORS|OWNER]

ALL_USERS – All users can log on or continue working with the application.

ADMINISTRATORS – Only other administrators can log on. Other users are forced off and prevented from logging on until the parameter is reset to ALL_USERS.

OWNER – Only the application owner can log on. All other users are prevented from logging on. If they are currently logged on, they are forced off the system until the option is reset to All_USERS or ADMINISTRATORS. Only the application owner can restrict other administrators from using the application.

  • /DEBUG=[true|false] – Specify whether to run the utility in debug mode. The default is false. (optional)
  • /HELP=Y – View the utility syntax online (optional)

Example

MaintenanceMode.cmd /A=app1,/U=admin,/P=password,/LL=ADMINISTRATORS

MaintenanceMode.cmd /A=app1,/U=admin,/P=password,/LL=ALL_USERS

 




Article Now Available in InVision

Josh Forrest and I presented at last year’s Collaborate conference.  Along with that presentation, we wrote a white paper on the implementation of Hyperion Planning.  This paper included process of selecting a vendor, the project goals, requirements gathering, project methodology, and even the lessons learned once the implementation was completed.

The editors of OAUG asked us if they could publish the article in the summer edition, which was released this week.  The article was written closely with Abercrombie & Fitch and represents the process from the business, not from the consulting services, point of view.

The article can be downloaded here at www.oaug.org.  Access to the article requires free registration.




BUG REPORT – Shared Members Security in EPMA

Oracle has confirmed a bug related to the deployment of security with a planning application maintained in EPMA in version 11.1.2.x.  When the Shared Members checkbox is selected in an EPMA deployment of a Planning application, it ignores this option.  Even if the Shared Members box is checked, the user still only gets access to Ohio Region, and not the children, in the example below.   Oracle is currently working on a patch.

What Does Checking Shared Members Do?

By default, any member that is a shared member under a parent with security, it gets excluded.  For example, if the security for Ohio Region is set to @IDESCENDANTS with READ access, the three members below Ohio Region would have no access.
– Ohio Region
– Columbus (Shared)
– Cincinnati (Shared)
– Cleveland (Shared)

The filter that gets pushed to Essbase would look something like this.

@REMOVE(@IDESCENDANTS(“Ohio Region”),@SHARE(@IDESCENDANTS(“Ohio Region”)))

When the shared members are checked, it tells Hyperion that you want to include shared members in the security.  The same example above, with shared members selected, would give users access to all 3 members.  The filter that gets pushed to Essbase would then look like this.

@IDESCENDANTS(“Ohio Region”)

The Workaround

The workaround for this is to deploy the hierarchies from EPMA, and Refresh the database (security only) with Shared Members selected from Hyperion Planning.

When a patch is released, we will release the details.




KScope 2012 Wrap-Up

Kscope was another fantastic event.  Kudos to those responsible for organizing it.  Thank you to all the speakers who volunteered their time and shared their knowledge.  The most frequent request Josh, Rob, and I had, was to make our presentations available.  They are available on the Kscope site, but many of you don’t have access.  So, we are happy to make them available here.

I also had tremendous feedback on the ribbon. At least half the participants in our sessions used it.  We got a couple of great recommendations as well.  With some luck (meaning my schedule slows down a little), I will be working on those in the near future.

Download Josh Forrest’s presentation on Hyperion Financial Reporting

Rob Donahue’s presentation on Hyperion infrastructure

Download Kyle Goodfriend’s presentation on Hyperion Planning




Meet XWRITE, XREF’s New Big Brother

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. 

XREF will copy data to a target database and must be executed from the target database.  The function pulls data rather than pushing it.  This causes two challenges.  Normally, the data is entered in the source database and is copied to the destination database.  When a Planning web form is saved, it can only execute a calculation on the database the web form is connected to (at least in older version – stay tuned).  This means an XREF function cannot be used when the form is saved.  The user has to go to another form, or execute a business rule manually, for the data to move.

The larger issue with XREF is accounting for block creation.  Remember, XREF pulls data from a source.  The destination may not have blocks that exist where the data will reside.  XREF does NOT account for the creation of the blocks if blocks don’t exist.  XREF must be used in conjunction with the CREATEBLOCKONEQUATION setting.  This is acceptable when fixing on very finite levels of data, but execution on larger amounts of data results in an extremely slow data movement process.  Essbase is responsible for the slow data movement process because it traverses all possible sparse member combinations to validate existence of data on the source.  Normally, data exists at a very small percentage of the possible blocks. In addition to the slow data movement process, it’s worth noting that the XREF function can also create blocks in your database which are unnecessary; ultimately increasing the size and decreasing the speed of your application.

Welcome to XWRITE

XWRITE is the opposite of XREF.  Rather than using XREF to pull the data from the target, XWRITE enables you to push data from the source.  Pushing data resolves the issues which XREF creates.

When XWRITE is executed from a web form, thus pushing data from the source to the target, there’s no longer a need to account for this process with two web forms or the manual execution of a business rule.

Since XWRITE is executed from the source, there’s no longer a need for looking at every possible sparse member combination on the target.  Using a FIX statement enables Essbase to decipher which blocks need to be copied, removing the guesswork and subsequently the requirement of CREATEBLOCKONEQUATION. Utilizating the XWRITE function results in faster processing and efficient block creation.

Prior to XWRITE, my preferred method of data movement involved exports from the source and imports to the target; thus eliminating the need for the XREF function.  The introduction of XWRITE has reduced the need for a data export/import process.