Update for ASO (1/12/2015):

Recently, I was attempting to create Smart List functionality in an ASO cube. My only previous experience had been with BSO cubes, so I was not prepared for the couple of key differences that came up that hindered my progress. Here are the steps I took to successfully create a Smart List associated with an ASO cube. First, create a Smart List dimension by right clicking on the application in the dimension library:

For this example, I’ve named the Smart List “ClientSource”:

The Smart List has one member in it, “PV”:

And the Smart List will be associated with the account member, “ClientSource”:

In the account dimension, select the member that will have the Smart List associated with it (“ClientSource”),  and update the following 2 highlighted settings. This is the first part of the process that differs from that outlined above. In BSO, the settings are labeled “Data Type” & “Smart List”, rather than “Type” & “Smart List”:

After saving, I figured that the Smart List was good to go, but on deployment, I received the following error:

After some trial & error, the issue turned out to be coming from the data storage settings on the parents of “ClientSource”. After setting both parents (“AttributeInfo” & “Accounts”) to “LabelOnly”, the application deployed successfully.

To recap, there are 2 key differences between ASO & BSO cubes when setting up Smart Lists:

1. For BSO, the user must set “Data Type” to “SmartList” for the associated member. While in ASO, the user must set “Type” to “SmartList”
2. Before deploying the ASO application, the parent members of the member that is associated with the Smart List must be set to “LabelOnly”

I hope that this can help a few of you from having to spend time troubleshooting the difference between Smart Lists in ASO & BSO.

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.

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 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.

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.

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