Using a Shared Connection with HSGetValue/HSSetValue with Planning or PBCS

If you are a fan of the HSGetValue and HSSetValue, you probably are using a private connection. As you know, anybody that uses the template has to either change the connection string to their own predefined private connection, or set up a private connection with the same name. When dealing with inexperience users, both methods can be problematic.

You may be surprised to know that the Get and Set Value functions can use a shared connection. Rather than using the private connection name, the following can be specified to use a shared connection in place of the private connection name.

Private connection syntax:
HsGetValue(“PrivateConnectionName”,”POV”)
HsSetValue (dollar amount,”PrivateConnectionName”,”POV”)

Shared connection syntax:

HsGetValue(“WSFN|ProviderType|Server|Application|Database”,”POV”)
HsSetValue (dollar amount,”WSFN|ProviderType|Server|Application|Database”,”POV”)

Parameter Summary

  • “WSFN” is a static string and never changes
  • The provider type for planning is “HP” regardless of whether the server is a cloud server or on premise server
  • The server specifies the location of the server housing the application. For PBCS, use the URL provided by Oracle (planning-test-domain.pbcs.us2.oraclecloud.com)
  • The application is the application name
  • The database is the plan type, or database name

Put that all together and the string looks like this.
WSFN|HP|planning-test-A12345.pbcs.us2.oraclecloud.com|Finance|Revenue

Conclusion

Although there are a few drawbacks to using a shared connection (users could use the wrong connection and not get the expected result), my experience has been that the pros (no setup of private connections, can be used in multiple environments without changing anything, etc.) far outweigh the cons.




Recommended Settings for Oracle Hyperion Products

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.




Creating a SmartList: Linking Smartlists to Web Forms

What is a Smart List?
  • Allows for creation of custom lists that can be used in data forms
  • Goes beyond the limitations of Essbase: these lists are not limited to numbers only
  • Users select a member from a designated list (each Smart List cell has a dropdown arrow that expands to allow member selection in web forms)

Examples…

  • Active Period: Manage Forecast/Budget months by setting them to either “Active” or “Inactive”
  • Employee Status: Set employee status to “Full Time” or “Part Time”
  • Justification: Choose from “Research”, “Customer Feedback”, or “Expansion”

Creating a Smart List

First, create a new dimension (File-> New-> Dimension) with SmartList as the dimension type:

Next, create children of ActivePeriod. These will be the members of the Smart List (Right click on ActivePeriod, Create Member-> As Child):

Set the properties for each of the Smart List’s members. The label will be what appears in the dropdown menu, while the value is the number that will be assigned to that member in Essbase. The list will be sorted from smallest to largest. Here are the properties for Inactive:

And the properties for Active:

The way this Smart List is currently set up, Inactive will appear above Active because 0 precedes 1.

Set the properties for the Smart List:

Here is a description of each of the Smart List Dimension Properties referenced from Oracle’s knowledge base:

Property

Description

Label

Enter the text to display when the Smart List is selected. Smart Lists and Smart List members must have a Label assigned. Spaces and special characters are allowed.

Auto Generate ID

Generate a numeric ID for each Smart List entry. If you do not select this option, you can customize Smart List ID values.

Start Value

Populates the Value property of the first member in the Smart List. For example, if the Smart List dimension is ActivePeriod with Start Value set to 0, the first member added to this list has a value of 0.

Increment

This value is appended to the value of the last member in the list to determine the value for the selected member. For example:

ActivePeriod (Start Value=0, Increment=1)

Inactive (Value=0)

Active (Value=1)

Display Order

Smart Lists can be sorted in the dropdown menu by 1 of 3 ways:

ID: Unique number that sets the order for the Smart List entry

Name: Unique name containing only letters/numbers & underscores. (i.e. “Active”). No special characters or spaces.

Label: Displayed text for the Smart List entry in the dropdown

#Missing Data Form Label

How #Missing values are displayed in Smart List cells:

Dropdown: Displays the label set in #Missing Drop Down Label

Grid: This selection determines what the cell will display on a form when the cell is not highlighted/selected

#Missing Drop Down Label

Enter a label to be displayed as an entry in the Smart List whose value is #Missing

 

Linking a Smart List to a Web Form

Create and/or use an already existing member in a dimension that is being used in the application to be associated with the Smart List. In this example the account associated with the Smart List is called “Active Period”. Notice that this member has a space in the middle, unlike our Smart List dimension “ActivePeriod”, which is all one word:

Set the properties for “Active Period” so that the Smart List property is tagged to ActivePeriod and the Data Type is SmartList:

The most critical step before deploying the application is to set the Data Type Evaluation Order for the Planning application. In order for Smart Lists to appear on web forms, the dimension that the Smart List is associated with (in this case the Account dimension) must be included in the evaluation order. From the local library, right-click on the application:

Move the account dimension over to the selected dimensions pane for the selected plan type:

Deploy the application for the changes to take effect so that the Smart List can be utilized in a web form.

Next, select the form that will be using the Smar tList. Edit it and go to the Layout tab. For this example, “Active Period” is placed as the lone member of the account dimension in the rows:

Save the form and open it to check that the Smart List is working properly. The list is functioning correctly if a dropdown arrow appears in the highlighted cell:

Click the dropdown to make a selection:

The Smart List has been successfully created and linked to a Planning web form. This feature offers great functionality for users that require text data, as this is not available through Essbase alone. Smart Lists can also be utilized in member formulas and business rules.

For more detailed technical information on Smart Lists, here is a link to the Oracle documentation:

http://docs.oracle.com/cd/E1282501/epm.111/epmarchitect/frameset.htm?ch04s16.html

 

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.




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.




Why NOT Using the Currency Option with Hyperion Planning has Benefits

Hyperion Planning applications often require multiple currencies.  Hyperion Planning includes a currency option that easily allows multiple currencies to be managed.  Allowing Planning to manage this introduces a couple of limitations and inherent costs. These can be avoided if currency is managed manually.

When the Hyperion Planning currency option is enabled, an additional 2 dimensions are required.  This raises the required dimensions from 6 to 8.  Most planning applications have a need for at least 2 to 3 custom dimensions.  Even smaller applications suffer greatly when adding the additional 2 dimensions.  So, by using the currency option, the ability to use custom dimensions is limited.  By adding a few accounts to hold the currency conversion and adding one dimension that has members for all the currencies, multi-currency applications can be handled with only one additional dimension.  If the currency option is not used, the currency calculations may be written more efficiently than the default calculations introduced with the currency option.

Another drawback with the currency option is that is only allows data input to the base currency.  The majority of the applications I have built that require multiple currencies require the input at more than base currency.  Assume a retail company has stores in a number of countries with different currencies.  Salaries may be budgeted in the local currencies, but the cost of the bags used by customers to carry merchandise out of the store is budgeted in USD.  The costs are distributed in USD based on units, and converted to the local currencies.

Lastly, using the currency option, because of the number of dense dimensions, limits the number of time periods.  Executing calculations is limited to using 64k of memory.  Applications that use something other than month (like week, or day) can regularly hit this limit.




Managing Virtual Machines

Many developers that work with Hyperion products, as well as many any other software product, use virtual machines.  Virtual machines are an easy way to create multiple environments for testing and developing multiple product versions

The 3 main applications to create and use virtual machines are

I have used all 3.  Opinions can be found that promote all 3.  Many IT professionals prefer VMWare.  I have found it to be a little cumbersome to use, and find sharing virtual machines to be a frustrating experience.  I am not a stereotypical IT professional, but rather a business person with an aptitide for technology.  That said, I prefer VirtualBox.  For me, VirtualBox is easier to install and manage the virtual machines.  It is easy to move virtual hard drives to another computer, simple to duplicate a virtual hard drive and allows users to take snapshots, which allows, for lack of a better explanation, a huge undo if required.

Through my VirtualBox travels, I have found the following knowledge to be very valuable.  Here are some HOW TOs that might be useful if you decide to use Sun’s VirtualBox.

How to reduce the size of a virtual machine
The use of virtual machines (just like any system) cause fragmentation and the size of the virtual hard drive to grow, sometimes substantially.  Managing the size of the virtual machine is relatively easy, and is not time consuming.  It involves 3 actions (defrag, delete free space, and compact the virtual hard drive).  Here is one way to accomplish reducing the size of your virtual machine / virtual hard drive.

  1. Open the virtual machine that needs compressed
  2. Download sDelete, and extract the sDelete.exe to c:\
  3. NOT REQUIRED:  Download and install Smart Defrag – this is a free disk defrag tool that I have found to be more effective than the one that comes with the Windows OS.
  4. Use the disk defrag tool that comes with Windows or the one above, and defragment the hard drive.
  5. Go to Start/Run, and enter “c:\sdelete.exe -c”
  6. Shut Down the OS on the virtual machine
  7. On the host computer, open a command window (Start/Run, and enter cmd)
  8. If VirtualBox was installed in the default location, change the directory to the VirtualBox directory by entering “cd C:\Program Files\Sun\xVM VirtualBox\”
  9. Enter “VBoxManage modifyvdi HardDrivePathAndName compact” where HardDrivePathAndName is the full path to the hard drive the virtual machine is using.

If the virtual machine/hard drive has free space, this process will find it and reduce the overall size of your virtual machine/hard drive.

How to duplicating, or clone, a hard drive
Often times there is a need to replicate a virtual machine on the same host environment.  Copying the file doesn’t do the trick, as every virtual machine’s hard disk must have a unique key.  VirtualBox comes with a tool to duplicate the hard drive and assign it a new key.  To accompolish, follow the following steps.

  1. Reduce the hard drive size (see previous topic)
  2. On the host computer, open a command window (Start/Run, and enter cmd)
  3. If VirtualBox was installed in the default location, change the directory to the VirtualBox directory by entering “cd C:\Program Files\Sun\xVM VirtualBox\”
  4. Enter “VBoxManage clonevdi Source Destination” where Source is the full path to the hard drive the virtual machine to duplicate and Destination is the location of the new virtual machine hard drive.
  5. Open VirtualBox and create a new virtual machine that points to the newly created hard drive in the previous step.