FDMEE: Loading Data to Different Plan Types

I’m currently working with a Planning application that has 2 data types with different dimensionality. This proved to cause some issues when I would try to import data via FDMEE. I would receive a validation error during the import phase for dimension UD4 (Customer dimension) which was valid for Plan Type 2 but not for Plan Type 1

For this specific case, I was trying to load data for Plan Type 1, which has a different set of dimensions from Plan Type 2. The customer & product dimensions are not valid for Plan Type 1. Note the settings from the dimension library for both dimensions.

Customer:

Product:

From the setup tab in FDMEE, click Target Application. We need to remove Customer & Product from the “Data Table Column Name” column:

Remove the values for Product & Customer and click save:

Then click refresh metadata:

After refreshing the metadata, go back to the Data Load Workbench and import the data file. The import & validate steps should complete successfully now:




Adding a New Custom Dimension to HFM

Unlike a Planning application, adding a new custom dimension to an HFM application (after it has been deployed) requires a few extra steps to ensure a successful deployment. This post will provide step by step instructions on how to successfully complete the process.

Initially, I attempted to deploy my HFM application without following the steps that have been outlined in the post below. I received 8 warnings upon validation of the application prior to deployment:

In order to deploy the HFM application, we will need to log on to the server and stop some processes and services. Once you’re logged onto the HFM server, open up the task manager to begin shutting down a few key processes. You will need to right click and stop CASSecurity.exe, HsxServer.exe, & all of the HsvDataSource.exe processes:

Next, open up Services on the HFM server:

Right click and select to stop Oracle Hyperion Financial Management – Management Service (EPMA):

Return to the Task Manager and stop DMElistener.exe & HsxService.exe:

Now you can deploy the HFM application with the new custom dimension included. Check the job console to see that the deployment was successful:

Once deployment is complete, head back to the HFM server to start the Oracle Hyperion Financial Management – Management Service (EPMA) service to ensure that everything is back up & running.




Essbase Security: Setting Filters to Groups

For most Essbase applications, user and group security will be a necessity. Here are the steps to set up individual filters and then apply them to a group in Shared Services.

First, create a security filter in Essbase:

Then click on “New” and add read/write access for the filter:

Here is an example of the member specification for filter access:

Next, click Verify and then Save at the bottom of the page.

The next step is to login to Shared Services and create a new group:

The group name should match the filter name to reduce opportunities for confusion. While creating the group, add group/user members:

Next, the group will need to be provisioned for access to the desired application:

For Read/Write access only, assign “Filter” to the group:

For access to run calc scripts on the application along with Read/Write access, assign “Calc” to the group:

The next step is the part that has always been the trickiest piece for me. Right click on the application under Application Groups and select Access Control:

Search for the desired group and move it to the selection window on the right:

Select the desired group and then use the filter & calc dropdowns to select the required filters and/or calc scripts to assign to the group:

Click save after the desired access control for the group has been set. Remember, calc’s can only be assigned if the group was given “Calc” provisioning for the application.

Now the security filter has been successfully assigned to a group in Shared Services.




Automating SQLCMD Statements

Recently, I wanted to automate the process of loading monthly csv data files into a SQL table. I spent some time researching the syntax of SQLCMD as well as reading multiple different posts on the subject. After some trial and error, I was able to get the automation functioning properly. Here’s a quick summary.

SQL Statement

Here’s the syntax for the SQL query I was using to upload the monthly files:

csv_input.txt

DATABASE NAME, TABLE, & DATA_FILE_LOCATION.csv are query specific. For my instance I used the following:

  • DATABASE NAME: HYP_TEST
  • TABLE: HYP_STG_AP_DATA
  • DATA_FILE_LOCATION.csv: ‘G:\Data\AP\AP Aging FY14\ AP Aging Jan FY14.csv’

Open notepad and paste the query into the blank sheet. Next, save this file as a Microsoft SQL Server Query (.sql) file:

SQLCMD Batch File

To automate this process, a batch file will need to be created that calls the L_AP.sql file. Here’s the syntax to be used in the batch file:

SQLCMD -S server -U login_id –P passwordi input_file -o output_file

Notes on the syntax:

For more detailed information on the SQLCMD syntax – https://msdn.microsoft.com/en-us/library/ms162773.aspx

Once you have tested SQLCMD in the command prompt to verify that it runs, paste it into notepad and save the file as a Windows Batch (.bat) file:

I also created the ‘Logs’ folder, which is where SQLCMD will write the output file (as specified after –o in the SQLCMD command line). The output file will be created as .txt:

At this point, the process should be good to go and you can replicate this setup for other SQL queries using sqlcmd.

As a result of this exercise, a years’ worth of data can be loaded all at once rather than having to upload the monthly data files one at a time. This saved a good amount of time that had previously been spent manually uploading monthly files during the incremental process of data validation.




One at a time, please

Introduction

One of the problems with giving users of Hyperion Planning the ability to run calculations is opening up the possibility for all of them to run the same calculation at the same time.  This can cause a range of issues, from slower performance, to calculations never finishing due to locked blocks, to crashing the server.

Prior to Planning, I created VB applications to monitor what was calculated to make sure multiple calculations were not executed at the same time.  Initiating a calculation through a web portal allowed us to notify the user that the calculation request was ignored because a calculation was already running.

Both Essbase and Planning have come a long way since the 90s.  With the introduction of the @RETURN function, developers can interact with users and create a break in a calculation (business rule) so it doesn’t proceed.  The message is still reactive, but with some creativity, there are some really awesome things you can achieve.  Controlling what calculations are executed simultaneously is one of those things.

The Goal

Assume an application has a global consolidation calculation that is required to be executed for reporting requirements.  Since the administrators don’t want to be bothered at all hours of day and night, they want to enable the users to run the calculation and ensure it isn’t run more than one time during the calculation window.

This assumes the 6 required dimensions in Planning, plus a Department dimension.

The Method

 

Make a predefined placeholder where an indicator can be saved – a 1 or a 2.  When the calculation is executed, the value will be set to a 1.  When the calculation is finished, the value will be set to 2.  When the calculation is initiated, it will check that value.  If it is a 2, the calculation will execute.  If it is a 1, it assumes a calculations is already running so it will abort and notify the users.  This ensures that the calculation will never run twice at the same time.

Note:  I prefer the use of 1 and 2 over 0 and 1.  Many times a process is implemented to eliminate zeros and restructure the application periodically.  Not using a zero can eliminate errors in some situations.

Example

[crayon-5bc9ed08db5ea584542650/]

Summary

This method could be used in a variety of situations, not just a global calculation.  If this inspires you to use the @RETURN in other ways, please share them with the In2Hyperion and we can make your solution available to everybody.

 

 

 

 

 




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.




Essbase (ASO): Clearing Data Using CrossJoin

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!




Using MaxL Scripts to Create, Alter, & Grant Filters

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

 




Force Excel to Calculate Dependencies In Order

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




Countdown to 11.1.2.4

The Countdown begins

11.1.2.4 is being releases in as little as 10 days.  It is really exciting and here is what you will see.

On the fly iterative planning using sandboxes

  • Create version on the fly
  • Honors security and access rights
  • Iterative modeling
  • Dynamic scenario comparisons
  • Collaborative modeling

Web form performance improvements

  • Near 0 lag times
  • Fast cell-to-cell navigation
  • Quick scrolling
  • Forms with 100,000 cells loads in less than 5 seconds

Setup valid combinations

  • Forms show only valid combinations
  • End users can only interact with valid combinations
  • No more data entered in the wrong place
  • Bypasses the lack of multidimensional security
  • Speeds user interaction
  • Out of the box clear of invalid combinations

User defined calculations

  • Loaded from Excel by the user
  • They persist with member references
  • Excel syntax
  • Supports most of the Excel financial, mathematical, and statistical functions

Real time push to ASO, including relational data

  • Calculate and aggregate instantaneously

Simpler user interface

—–

This might introduce more questions than answers, but you don’t have to wait long to get them.  11.1.2.4 will be released this month – as soon as the end of next week!