Monthly Archives: May 2014

How to Address EBS Hierarchy Changes When They Break Your Oracle EPM (Hyperion) Environment in Pre-11.1.2.3 Versions

May 22, 2014

For versions of Oracle EPM 11.1.2.2 and earlier, Oracle ERP Integrator (or ERPi, now part of Oracle Hyperion Financial Data Quality Management Enterprise Edition or FDMEE in Version 11.1.2.3) and Oracle Hyperion Enterprise Performance Management Architect (EPMA) had problems properly handling changes to an E-Business Suite (EBS) hierarchy.

For example, when members are moved to a different parent in EBS, EPMA creates a new primary instance of the member at the new location in the hierarchy. However, the originally placed member is also retained, but is switched to a shared member. The reason this happens is that, by default, ERPi imports metadata using the “Merge as Primary” process type, which will not delete or move existing members, but rather switch them to ‘shared’ while creating a new primary instance of a member based on the current location in the EBS hierarchy. This behavior is especially problematic because this will lead to double-counting, and application deployments from EPMA often fail in cases where the member was moved lower in the hierarchy. This causes an error where a shared member is encountered before the primary instance.

In the image below, member A50300 was originally a child of parent A501AM. After an EBS hierarchy change, the member is now a child of parent A501AP; however ERPi and EPMA failed to remove the member from the original parent, and instead made it a shared member. A deployment attempt with the hierarchy in this state would fail because the shared member will be encountered before the primary member.

Shane 1

This behavior is a known bug in Oracle that was not fixed until Version 11.1.2.3 in ERPi’s replacement product, Financial Data Quality Management Enterprise Edition (FDMEE). In versions prior to 11.1.2.3, Hyperion administrators must be fully aware of any and all EBS hierarchy changes so they can monitor for this behavior. When an administrator identifies the problem, they must manually delete the shared member prior to deploying the application. This known bug necessitates a very strong business process to be in place so admins are made fully aware of all EBS hierarchy changes.

As of EPM Version 11.1.2.3, FDMEE now provides the option of choosing how hierarchy changes will be handled by EPMA. There is now an option to choose “Merge as Move,” which would likely be the most typical and preferable way for handling EBS hierarchy changes. The originally placed member is actually moved from its original parent, and placed under the new parent, leaving only a single primary instance of the member in EPMA. Administrators will need to be cognizant of the version of EPM they are on, and ensure the proper business processes are in place to handle the inherent limitations of the product.

Author: Shane Hetzel, Performance Architects


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

What is the EPMExportAll Utility in Oracle EPM (Hyperion), and How Does it Work?

May 14, 2014

EPMExportAll (EPM_CloneExport.bat) is a new Oracle EPM (Hyperion) 11.1.2.3 Lifecycle Management (LCM) command-line utility that exports all LCM-enabled artifacts to the file system. It is very similar, in use and function, to the Lifecycle Management Utility (utility.bat), with a few subtle, yet key differences:

  • Unlike the Lifecycle Management Utility (utility.bat), the EPMExportAll utility does not utilize a Migration Definition File to specify which artifacts you want to export. Rather, the EPMExportAll utility grabs all LCM-enabled artifacts by default – whether you want them or not – without specifying a Migration Definition File
  • Although the EPMExportAll utility does not use a Migration Definition File, it does require a properties file as input. The properties file does not come installed, so it must be created and named “input.properties” The input.properties file then needs to be populated with the EPM username and password, to be used by the EPMExportAll utility, each time it runs
  • The EPMExportAll utility does not accept any command-line arguments. The Lifecycle Management Utility accepts some command-line arguments that can be used to list information or to specify a base path
  • While the EPMExportAll utility grabs all LCM-enabled artifacts, it will not grab any artifacts that are not LCM-enabled (e.g., Application Groups  Foundation  Deployment Metadata  Shared Services Registry). The Lifecycle Management Utility (utility.bat) will allow you to select these artifacts

The EPMExportAll utility is executed by running a file called “epm_cloneexport.bat.” This file should be on the Foundation Server, in the Middleware_Home/user_projects/epmsystem1/bin directory (the file may be on a different server and directory, depending on how the installation was done). Here are instructions on how this utility works:

1. Once you’ve located the epm_cloneexport.bat file (in the same directory that the epm_cloneexport.bat file is in), create a blank file and name it input.properties. Open the input.properties file and add the following two lines where mylcmusername and mylcmpassword are the Shared Services credentials for the account that you want the utility to use (Important tip: The password in the input.properties file will be overwritten with an encrypted password during the first run of the utility)

• user=mylcmusername
• password=mylcmpassword

Ivan 1

2. Save and close the input.properties file.

3. From a command line, cd to the directory that contains the epm_cloneexport.bat file, and run the following command:

epm_cloneexport.bat input.properties

Ivan 2

4. When completed, you should see a message that says: “Migration Status: Success.”

Ivan 3

5. Exit the command window.

6. In Shared Services, under “File System,” you should now see a folder called EPM_CloneExport. This folder should contain all of the LCM-enabled artifacts.

Ivan 4

7. If you go back and open the input.properties file, it should now display the encrypted password (Important tip: At the time that this was written, there appeared to be a potential bug with this utility, whereby the utility created a sort of ad-hoc MDF file, named epmexportall.xml, and put it in the default File System location, alongside the EPM_CloneExport folder. This file contained the unencrypted version of the password from the input.properties file. The file is generated every time the utility is run. We currently have a ticket open with Oracle to see if this is a bug. In the meantime, as a work-around, I am running the utility in a script and having the script delete the epmexportall.xml file after the epm_cloneexport.bat completes):

Ivan 5

Author: Ivan Agudelo, Performance Architects

 


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Advantages and Disadvantages of the Aggregate Storage Option (ASO) Plan Type Feature in Oracle Hyperion Planning Version 11.1.2.3

May 7, 2014

During my most recent consulting project, I had a chance to implement the Aggregate Storage Option (ASO) feature in Oracle Hyperion Planning Version 11.1.2.3.

Creating additional plan types whether they’re Block Storage Option (BSO) or ASO is easy in Version 11.1.2.3. They can be created one of two ways:

  1. At the time of application creation, you can specify additional plan types, through the application creation wizard 
  1. Or…the really cool feature is, even after you’ve created and deployed the Planning application, you can still add ASO cubes

To do this, simply navigate to Administration -> Manage -> Plan Types.

Mohan 1

Just click the “Add” button on the top and add another ASO cube to the application.

Mohan 2

Refresh the application to push it down to Essbase. The great feature here is that, through the Planning front end, we can manage dimensions to each of these plan types separately. The thing to keep in mind is that this is still a “Planning” application and, as such, the ASO cubes you’ve built are part of it.

I’ll demonstrate this functionality through a sample “CU_PLAN” application, where we have four plan types, two BSO and two ASO cubes. They include:

  1. PLAN – BSO
  2. HCP – BSO
  3. PLAN_RPT – ASO
  4. HCP_RPT – ASO

The advantage of creating these ASO cubes is obviously for reporting capabilities. With any application that utilizes a large dimension set with several sparse members, ASO cubes make reporting on data much faster. By also utilizing the “Map Reporting” functionality in 11.1.2.3 to push this data between the BSO data entry cubes and ASO reporting cubes, you can keep your data in sync.

Now to a big disadvantage…unfortunately, Oracle does not support these new ASO cubes in an Essbase connection on Smart View in 11.1.2.3. This essentially means that, security filters created for end users from shared services on the Planning application (in this case CU_PLAN) will not be created for the ASO cubes.

What this means is that, as an end user, if you log into CU_PLAN using an Essbase connection using Smart View, you will NOT see these ASO reporting cubes:

Mohan 3

This makes these reporting cubes useless for an end user, if they intend to create their own reports. However, since admins on shared services have default access, only admins can view and retrieve data on this ASO plan type in Smart View:

Mohan 4

We were able to circumvent this issue by creating native Essbase ASO cubes. Then, we used a script to copy the Planning ASO cubes into these native ASO cubes (in order to keep dimensionality in sync), and then copied data from the Planning ASO cubes into the native ASO cubes. Lastly, we created Essbase-only security filters using the Planning security filters that were created in shared services. This can be done by exporting security filters from the Planning tables and then using MaxL to generate the new Essbase security filters.

Another big disadvantage that we noticed using the ASO plan type was on performing data retrieves using Smart View using a Planning ad hoc connection:

Mohan 5

Data retrieval times on the ASO plan type took in excess of 45 seconds when pulling Level 0 data with more than two dimensions in the rows. The basic rule when performing retrieves is to limit how many dimensions are pulled into rows. The more sparse dimensions in rows, the longer the retrieval times, especially for large dimensions. But even with that understanding, our application retrieval times began at 45 seconds and performed even more poorly with more dimensions in the rows. This naturally is something most companies will find difficult to tolerate especially when the need is to have faster report access and generation! 

For those interested in diving deeper into the solution of exporting security filters from Planning, you can find a few blogs online that show you how it’s done, including using SQL to extract the filters from Planning tables. There is also plenty of information available on how to write MaxL scripts to import these into the ASO cubes and to assign them to specific applications, dimensions, etc.

As of the date of publication, Oracle has not set a timeframe on when these Planning ASO cubes will begin functioning the same as native applications.

Author: Mohan Chanila, Performance Architects

 


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.