Problem

Oracle has recommended settings for Internet Explorer (IE7, IE8, IE9, IE10 and IE11) when using Hyperion products.  I get this question a lot from my clients, so I thought I would share Oracles suggested settings.  Without changing these, there will be intermittent problems, and frustration points.

Solution

Configure browser to check for new version every time user visits a page

• Open Internet Explorer
• Go to Tools > Internet options > General
• In “Browsing history” section click on “Settings” button and then select “Every time I visit the webpage” option
• Click OK, then Apply.

Disable default pop-up blocking

• Open Internet Explorer
• Go to Tools > Internet options > Privacy tab
• Uncheck “Turn on Pop-up Blocker”

Add Workspace URL to trusted sites

• Open Internet Explorer
• Go to Tools > Internet options > Security tab
• Select “Trusted sites” from “Select a zone to view or change security settings” window, then click on “Sites” button.
• Type your workspace URL in form http://workspaceserver:portnumber in “Add this website to the zone”
• Uncheck “Require server verification (https:) for all sites in this zone”
• Click Add, then Close.
• Click OK and Apply.

Customize security settings

• Open Internet Explorer
• Go to Tools > Internet options > Security tab
• Select “Trusted sites” from “Select a zone to view or change security settings” window
• Select zone containing your Oracle Hyperion servers and click on “Custom level…” button
• In “Miscellaneous” section enable options “Access data sources across domains” and “Allow script-initiated windows without size or position constraints”
• In “ActiveX controls and plug-ins” section enable “Run ActiveX controls and plug-ins” and “Script ActiveX controls marked safe for scripting”.
• Click OK
• Click Apply, then OK

Enable option “Always allow session cookies”.

• Open Internet Explorer
• Go to Tools > Internet options > Privacy Tab > Advanced. Check the “Override automatic cookie handling”, accept the first and third party cookies and check the “Always allow session cookies” option.

Only for SSL enabled environments: Disable option “Do not save Encrypted Pages to Disk”.

• Open Internet Explorer
• Go to Tools > Internet options > Advanced Tab. In “Security” section uncheck the option “Do not save Encrypted Pages to Disk”.

Disable option “Enable Native XMLHTTP”. This setting is recommended only for customers using HFM 9.3.1 or older with IE 7. If you are using version 11.1.1.x of EPM products or newer, this option should be enabled.

• Open Internet Explorer
• Go to Tools > Internet options > Advanced Tab. In “Security” section uncheck the option “Enable Native XMLHTTP”.

Using Internet Explorer 9 Compatibility View option.

• Open Internet Explorer
• For 11.1.2.1.600 Planning and EPMA: Compatibility View should be enabled in Tools > Compatibility View Settings
• For EPM 11.1.2.2 products: Go to Tools > Compatibility View Settings. Make sure EPM Workspace URL is not enabled for Compatibility View and uncheck all available options at the bottom of the pop-up window.

For products using JRE Plugin (Web Analysis, Performance Scorecard, Administration Services) make sure that a compatible version of plugin is installed on the client machine.

• Check JRE Plugin certification for your EPM product in support matrix
• Check installed Java version in Control Panel > Java > General > About. If required update Java version to a supported release.
• Enable option “Always Auto-Download” in Control Panel > Java > Advanced > JRE Auto-Download=

Using Internet Explorer 11 Enterprise Mode. Limited support with EPM 11.1.2.2.500 and 11.1.2.3.500. For more information see Document 1920566.1.

• Open Internet Explorer > Tools
• Select Enterprise Mode. This option is visible only after enabling it in Windows Registry. See this Microsoft document for more details: http://msdn.microsoft.com/en-us/library/dn640687.aspx

Having been working on an ASO project for the last couple of months, I have learned a lot about Essbase and its related software. One of the things that gave me trouble at first was the syntax of CrossJoin in ASO’s MDX language. More specifically, I was having trouble trying to nest multiple CrossJoin’s together when I was trying to clear out a certain portion of data that included more than two dimensions. A CrossJoin is simple; it returns the cross-product of two sets from two different dimensions. But what if I want to return the cross product of four different dimensions? That one proved to be a little trickier

Before tackling a CrossJoin of four dimensions, let’s look at a the basic syntax of the function. Below is an example from Oracle’s documentation of a simple CrossJoin (it is using members from the Year & Market dimensions in the Sample Basic outline):

Notice that all 4 possible combinations of the members are returned by the CrossJoin. This is straightforward enough. The part I had the most trouble with occurred when I started trying to include multiple CrossJoin’s in the statements. All of the statements & brackets seemed to jumble together. I figured it would be most helpful to provide examples of what I ended up using so that you can see the syntax:

Note: Since these CrossJoin’s were used to delete data, all of the members being selected are Level 0. I’m also using the ASOsamp Sample ASO outline to demonstrate the functionality.

Simple CrossJoin:

• CrossJoin({[Original Price]},{[Curr Year]})

Double CrossJoin:

• CrossJoin(CrossJoin({[Original Price]},{[Curr Year]}),{[Jan]})

Triple CrossJoin:

• CrossJoin(CrossJoin(CrossJoin({[Original Price]},{[Curr Year]}),{[Jan]}),{[Sale]})

Above demonstrates a sample syntax that will use CrossJoin to bring together four different dimensions. However, it is only grabbing one member from each dimension. To grab multiple members from a dimension, there are family functions that can be used (.Parent, .Children, Ancestor(), Cousin(), .FirstChild, .LastChild, .FirstSibling, and .LastSibling). For this example, I’m using the .Children function.

Triple CrossJoin Using Children Function:

• CrossJoin(CrossJoin(CrossJoin({[Measures].Children},{[Curr Year]}),{[Jan]}),{[Sale]})

Notice how [Original Price] was replaced with [Measures].Children. Rather than returning one member, this will return the following children from the “Measures” dimension:

Utilize these family functions to increase the CrossJoin function’s returned set.

One more thing to note. Substitution variables can be included within a CrossJoin. For example, let’s say I created the subvar &CurrMonth. I can replace Jan in the code, thus making the month variable.

Triple CrossJoin Using Substitution Variable:

• CrossJoin(CrossJoin(CrossJoin({[Measures].Children},{[Curr Year]}),{[&CurrMonth]}),{[Sale]})

MaxL Syntax to Clear Data from a Region:

Next, let’s cover the MaxL syntax to clear data in the region specified by the CrossJoin. From the Essbase technical reference 11.1.2.3 pg. 894:

The entire CUBE-AREA portion must be enclosed in single quotes i.e. ‘CUBE-AREA‘, which will define what portion of the database is going to be cleared.

And here are some more detailed notes on the syntax (also from the Essbase technical reference 11.1.2.3 pg. 897):

Based on the information laid out above, the line that I used in my MaxL statement turned out to look like this:

• alter database ASOsamp.Sample clear data in region ‘CrossJoin(CrossJoin(CrossJoin({[Measures].children},{[Curr Year]}),{[&CurrMonth]}),{[Sale]})’ physical;

Prior to reloading data, I use this command line to clear the database in this region. This is to make sure that there is no stray data leftover in the cube that might cause discrepancies later on.

Feel free to leave any tips/advice on a more efficient method of utilizing CrossJoin!

Creating security filters and assigning them to different users/groups can be a time consuming process, especially if it is done manually. Luckily, there are some simple MaxL statements that can be used to significantly expedite the process. Here are the 3 that I’ve found to be most useful:

• Create Filter
• Alter Filter
• Grant Filter

Create Filter:

The MEMBER-EXPRESSION must be enclosed in single quotation marks. It can be a comma-separated list as well (this also pertains to the Alter Filter syntax). Notice in the example below how commas are used to separate 3 different dimensions (Year, Measures, & Product) in the create filter syntax:

• create filter Sample.Basic.filter1 read on ‘@IDescendants(“Year”), @IDescendants(“Measures”), @IDescendants(Product”)’;

For the FILTER-NAME portion, the application and database must be included preceding the filter name. This syntax will be used for Create, Alter, & Grant.

After running the batch, open EAS to verify that the filter was created correctly (I’ve included a generic version of my batch & MaxL files at the end of this post in case they may be helpful). Right click on the database and select Edit->Filters:

A list of all filters in the database will appear:

Select edit and the member specification assigned to the filter will pop up. All 3 dimensions that are outlined in the MaxL command should be accounted for:

Many times, the filter will need to be updated after it has been created. There is also a command line function for that…

Alter Filter:

For this example, we’ll add another dimension into the filter. Let’s add read access for @IDescendants(“East”). Here’s an example of the Alter Filter syntax:

• alter filter Sample.Basic.filter1 add read on ‘@IDescendants(“East”)’;

After running the batch file, the filter now reflects the change that was made:

Now that the filter is built, it can be assigned to a user, group, or multiples of both using the Grant Filter command line function. However, prior to assigning a filter to a user/group, the user/group must be provisioned to have filter access to the application. This is done through Shared Services. We’ll use “Test_User1” as a sample user. Right click on “Test_User1” and select Provision:

Expand down on the Sample application until Filter appears. Highlight “Filter” and bring it across to the right side of the screen:

The selected roles should display “Filter” under Sample:

Click Save. Now, “Test_User1” is provisioned for the Sample application and the filter can be applied using the Grant Filter MaxL command.

Grant Filter:

Example of the Grant Filter syntax:

• grant filter Sample.Basic.filter1 to Test_User1;

To verify that “filter1” has been granted to “Test_User1”, head back to Shared Services and right click on Sample->Assign Access Control:

Select “User Name” from the dropdown menu in the top left and click search. Highlight “Test_User1” and click the right arrow to bring the user to the Selected box on the right. Click Next:

“Test_User1” has been granted “filter1” and the user’s access should reflect this change:

Batch File:

call MaxlPath “MaxL File Path” Sample Basic userID password ServerId filter_log

MaxL File:

login $3 $4 on $5;

spool on to “Log File Path”;

create filter Sample.Basic.filter1 read on ‘@IDescendants(“Year”), @IDescendants(“Measures”), @IDescendants(“Product”)’;

alter filter sample.basic.filter1 add read on ‘@IDescendants(“East”)’;

grant filter Sample.Basic.filter1 to Test_User1;

logout;

spool off;

exit;

To take a deeper dive into the filter functionality, or to clarify any issues, check out the Essbase Technical Reference:

https://docs.oracle.com/cd/E40248_01/epm.1112/essbase_tech_ref.pdf

Overview

If you have ever used custom functions in Excel, depending on the complexity of them, you have probably run into an issue where the accuracy of the results was sporadic. There is a quick solution. Use CTRL ALT SHIFT F9.

The lengthier explanation from Microsoft explains that the calculation of worksheets in Excel can be viewed as a three-stage process:

• Construction of a dependency tree
• Construction of a calculation chain
• Recalculation of cell

With the introduction of complex VBA functions, the default calculation can produce inaccurate results because it doesn’t evaluate the dependency tree and calculation chain correctly.

So, if you have this issue, the most complete and thorough (and time consuming) calculation can be initiated by clicking CTRL ALT SHIFT F9. This forces the dependency tree to be rebuilt and recalculates the entire workbook. There are several levels in forcing Excel to calculate.

F9

Recalculates all cells that Excel has marked as dirty, that is, dependents of volatile or changed data, and cells programmatically marked as dirty. If the calculation mode is Automatic Except Tables, this calculates those tables that require updating and also all volatile functions and their dependents.
VBA: Application.Calculate

SHIFT F9

Recalculates the cells marked for calculation in the active worksheet only.
VBA: ActiveSheet.Calculate

CTRL ALT F9

Recalculates all cells in all open workbooks. If the calculation mode is Automatic Except Tables, it forces the tables to be recalculated.
VBA: Application.CalculateFull

CTRL ALT SHIFT F9

Causes Excel to rebuild the dependency tree and the calculation chain for a given workbook and forces a recalculation of all cells that contain formulas.
VBA: Workbooks(reference).ForceFullCalculation (introduced in Excel 2007)

References

• Microsoft.com Article
• Microsoft Article (PDF)

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.