This post looks at the “Allow Expansion” functionality in Financial Reporting Studio. “Allow Expansion” is a great feature, as it gives the user more flexibility when running and displaying reports. However, it can also require more setup work as it makes the report more complex. Two situations that I encountered recently:

1. Adding a custom heading using conditional formatting
2. Calculating formulas on expanded members

1) Adding a Custom Heading Using Conditional Formatting

For this example, I want to rename the account “PROMO”, to “Promotions” in the report. Typically, this setting can be changed by clicking on the “PROMO” cell and then editing the Custom heading in the properties box. However, with expansion enabled, the result will be that the member and its children will be renamed to “Promotions” with expansion enabled.

To demonstrate this, first we must enable expansion.  Highlight the entire row by clicking on the desired row number. Check the box next to “Allow Expansion” in the “Heading Row Properties” box. When expansion is enabled we will see that the custom heading will appear for all descendants of “PROMO”.

Run the report in the HTML preview, and click the arrow next to “Promotions” to see the children of the account. Notice in the screenshot below that both children now display as “Promotions”. How can we edit this so that the Custom heading only appears for “PROMO”, and not its children? This is where conditional formatting comes into play

The desired outcome is that both children will display their unique name as is stored in the dimension library. In order to accomplish this, some additional conditional formatting needs to be set up within the report:

The report needs to be given instructions on when to apply the custom heading. To apply this setting, we need to know what level or generation the account is, so that the rule will tell FR only to apply the custom heading on that specific account. For example, looking at the account structure below, “PROMO” is a Generation 7/Level 1 member of the hierarchy. We want the custom heading to only display for this level (i.e. “PROMO”) and allow the Generation 8/Level 0 members (i.e. children of “PROMO”) to display as usual.

The following 2 screenshots are examples of possible conditional formats for the member (based off the hierarchy above). Either of the 2 will work.

Select “Format Cells” and select the “Replace” tab. Enter in “Promotions” and click OK. After setting the conditional format, go ahead and run the report in HTML Preview again (Allow Expansion only works in HTML Preview, not in PDF Preview)

Expand on “Promotions” and notice the difference in formatting from before. The children will be displaying their unique member names as expected:

That took care of our first problem – How to set Custom Headings on Allow Expansion members using conditional formatting. Next up…

2) Dynamically Calculating using Allow Expansion

Another issue I encountered was that calculations didn’t update upon expansion of members. We’ll focus on the highlighted cell below to solve this issue:

The formula is a simple calculation, “PROMO”/”GROSS_SALES” or [A,3]/[A,2]:

When expanded, I want the calculation to update to show the relevant values for the children of “PROMO” i.e. the children will display as a percentage of “Gross Sales”. However, the values are static and do not update as expected. Notice that the 19.7% repeats itself for “Promotions” and its 2 children in the screenshot below. It seems that by hardcoding the formula to specific cells ([A,3] & [A,2]), the report only calculates “Promotions” / “Gross Sales”, instead of recalculating for the 2 children upon expansion (“425000/”Gross Sales” & “425040”/”Gross Sales”):

Rather than locking the numerator in place by selecting [A,3], we can improve the report by switching this value to reference the entire column [A]. Edit the formula by highlighting column B and typing the new formula into the formula box at the top of the report.

The new formula should look like this:

Instead of hardcoding the formula to one cell, the formula now has more flexibility for expanding members. Notice that the values calculate dynamically upon expansion this time around:

Overall, “Allow expansion” is a beneficial feature that allows users more flexibility with their reporting needs, but it sometimes takes a little more maneuvering to get the reports to display as expected.

This post covers the basics of building a Planning app. Building a Planning application can be a straightforward process; however, there are some pitfalls to watch out for. One or two mistakes can lead to some major headaches in trying to decipher what went wrong. Whether a beginner or a seasoned vet looking for a quick refresher, this guide will outline the steps necessary to successfully create/deploy a Planning application.

First, open up the dimension library, and then click File, New, and Application. This will take you to the first screen in the Application setup: Application Type

A. Application Type

There are 3 sections that make up the first screen

• Application Information

• Planning

• Calendar

We will tackle them one at a time…

Application Information:

1. In this first section, give a name for your application, as well as select Planning from the type dropdown. Giving the application a description is optional
2. Since we are creating an application using dimensions from the EPMA library, leave “Create Blank Application” & “Auto Create Local Dimensions” unchecked

Planning:

1. Select the application type (typically this will be General), and select the default currency for the application. You can select “Use Multiple Currencies” but this is not the most effective way of going about it. For more detailed insight on the utilization of multiple currencies in a Planning app, check out the following article:
• http://www.in2hyperion.com/post/2010/11/28/Why-Not-Using-the-Currency-Option-with-Hyperion-Planning-has-Benefits.aspx
2. Select and name the Plan Types that will be in the application. From the screenshot below, the application will have 3 plan types (Plan1, Plan2, Plan3)

Calendar:

1. Monthly, Quarterly, and Yearly application setup.
• Base Time Period: This can be 12 months, Quarters, or Custom (if you need a lower level of detail such as weeks)
• Fiscal Year First Month/Fiscal Year Start Date: Set the first month of the fiscal year as well as if it is the same calendar year or the previous calendar year
• Weekly Distribution: There are 4 options from this dropdown (Even, 445, 454, 544)
2. CAUTION: For the following section, both the Period and Year dimensions should be renamed to “Periods” & “Years” if Dynamic Time Series is going be turned on for the application (M-T-D, Q-T-D, Y-T-D, etc). If the dimensions are named Year and Period, Planning will not be able to differentiate between the dimensions and the dynamic time series members, causing errors.
3. Name the dimension “Years” and select the first fiscal year/number of fiscal years. The number of years ranges from 1-100.

After completing all of the above settings, click next to move on to the next screen, Dimension Selection:

B. Dimension Selection

This screen will have sections for the required, custom, as well as other dimensions for the application. First, the screen will auto populate whatever dimensions are already in the EPMA library. In this example, Entity, Account, and Alias are already filled in. The remaining dimensions (Version & Scenario) need to be populated via the dropdown menu:

These dimensions will be added as local dimensions to the application. Give the dimensions a name and make sure that the type matches the dimension that is being updated:

After setting the required dimensions, the custom dimensions can be selected in the next section. By clicking on the “[Select]” dropdown, the rest of the dimensions from EPMA will be available for selection in the application (note: your dropdown menu will not display the same selection as in this screenshot, as these dimensions had to be created in the Shared Library first):

If there are any Attributes, Smart Lists, or UDA’s that need to be added, the “Other Dimensions” section is the place to do that. Notice that there are 3 different Attributes to choose from in our Test application (I created these in the Shared Library beforehand as an example).

Reviewing the dimensions, there are 4 new local dimensions (Version, Scenario, Years, & Periods), 3 custom dimensions (Customer, Product, & BusinessUnit), & 3 other dimensions (Attribute, SmartList, & UDA). Click Next to go to the Application Settings screen.

C. Application Settings

1. The entire dimensionality of the new application is available to view/edit. Exclude/add members to the hierarchy as necessary. Make sure that all the dimensions are accounted for.
2. Properties of the application: This is a review of the application settings from the first screen. Double check that base time period, fiscal start year, and fiscal year first month are set. Also, make sure that the default alias table is set.

After making those changes, click Validate. A list of errors and warnings will populate below. These errors and warnings need to be taken care of before clicking Finish. There are a wide variety of errors that may be encountered such as duplicate members, duplicate aliases, invalid members, invalid plan types, etc. depending on your application. After fixing an error, click validate again and the error will disappear from the list.

Once there are no more errors, the application is ready to be deployed. Click Finish and go to the Application Library and right click, then deploy the application.

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.

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.

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.

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.

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.

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

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.

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.