Hyperion Planning Utilities

Hyperion Planning comes loaded with several utilities that come in very handy. Knowledge about what those utilities are, what they do, how to use them, the proper syntax to run them can be of huge benefit during routine as well as ad hoc activities in the EPM world.

In regards to the EPM 11.1.2.1 version, the Utilities reside in the Planning1 directory (default) in the application server where you have Planning installed and configured. In my case, the folder is \\appserver\d$\oracle\Middleware\user_projects\epmsystem1\Planning\planning1. It might be different depending on how you have configured Planning.

Here, I have tried to list some of the utilities that are available in the Planning1 folder and a brief description regarding what they do. If you are interested to know more about any of them, Oracle document library is a great resource. I have also written blogs on some of those utilities. So you can feel free to search for them.

Note: Most of the utilities (may be all) are case sensitive, so please take great care during type those commands and their syntax.

OutlineLoad.cmd – The mother of all the processes to load metadata in Hyperion Planning, this is a very handy utility using which you can load metadata in Planning application. CSV, TXT files can be used to for default dimensions as well as user-defined dimensions, attributes, and UDAs. Make sure the metadata or data file is formatted correctly.

The following is a common syntax. It has some other parameters as well.

OutlineLoad [/S:server] /A:application /U:userName [/E:outputFileName] [/I:inputFileName/D[U]:loadDimensionName|/DA:attributeDimensionName:baseDimensionName][/X:exceptionFileName] [L:logFileName]

FormDefUtil.cmd – The form definition utility comes in very handy if you want to export the forms in planning application and import in a different target environment, for example, export-import between development and production, or so forth. You can export either a single form or all the forms in a planning application. It creates an XML file(s) which you can use to import in the target environment.

Syntax

FormDefUtil.cmd export Name_of_Form servername username Application_Name.

In case you are exporting all the forms, replace “Name_of_Form” with “-all” and in case you are importing, replace “export” with “import.”

ExportSecurity.cmd and ImportSecurity.cmd – By using the ExportSecurity utility in conjunction with ImportSecurity.cmd, we can export and import access permissions across applications. Running this utility creats a secfile.txt file which can be used to import security in the target environment or application. Before running the ImportSecurity.cmd utility, we need to edit the secfile.txt file to make sure we have the right names.

The syntax for ExportSecurity.cmd is

ExportSecurity.cmd /A=Application_Name,/U=admin_username (can use other parameters as per need)

For example, If the name of the application is khoonks and username is nirmal, the syntax would look something like:
ExportSecurity.cmd /A=khoonks,/U=nirmal (you can add other parameters as required)

The default location where the resulting secfile is created is what we call the "bin" folder. The location in 11.1.2.1 (my environment) is oracle\Middleware\user_projects\epmsystem1\Planning\planning1.

We can specify the output file name/location by using the parameter "TO_FILE"
In the following example,
username is nirmal, application is khoonks, and we would like to export the resulting security file to a file named "mysec.txt" in E:\my_folder.
So the syntax would be something like:
ExportSecurity.cmd /A=khoonks,/U=nirmal,/TO_FILE=E:\\my_folder\\mysec

Moreover, we can also use the (-f) option to encrypt the password while doing the security export. In this case the syntax of a batch script to call the ExportSecurity.cmd would look something like:

call D:\oracle\Middleware\user_projects\epmsystem1\Planning\planning1\ExportSecurity.cmd -f:D:\oracle\Middleware\user_projects\epmsystem1\Planning\planning1\passwordFile /A=appname,/U=ADMIN,/TO_FILE=E:\\samplefolder\\subfolder\\mysec_secfile

The above script will create a file called mysec_secfile.txt inside the "subfolder" folder in the specified path. 

NOTE: In the above example, there is a space before "-f" and after "passwordFile"
The utility is very sensitive in terms of case and spaces. Therefore, make sure they are correct.

ImportSecurity.cmd “appname,username”
For example, if the app name is khoonks and username is nirmal, the syntax would be:
ImportSecurity.cmd "khoonks,nirmal"

ProvisionUsers.cmd – The provision users utility can be used to synchronize Planning users, groups, and roles in Shared Services console with Planning application and with Essbase.

Syntax is

ProvisionUsers /ADMIN:admin_user /A:Application_Name

SortMember.cmd – The sort member utility can be used to sort dimension members outside of Planning. This is an alternate for sorting members in Dimensions tab in Planning web. If you want to sort members without having to open planning, you can use this.

The syntax is

SortMember servername admin_username password Dimension member_name descendants ascend

Other parameters such as descend, etc can be used as per requirement.

Apart from these utilities, there are other utilities as well which can come in handy in Planning administrative job.

You can go and check the Planning1 folder to see and find out what the other utilities are and what they do.

FormDefUtil.cmd – a way to export and import Planning Forms

FormDefUtil is a pretty decent Planning utility that can be used to export planning data forms from one environment to another. Being a planning utility it resides in the Planning1 folder (at least in the 11.1.2 version of EPM).

Path - D:\oracle\Middleware\user_projects\epmsystem1\Planning\planning1.

In order to launch the FormDefUtil utility

            Open the command Prompt

            Go to the Planning1 folder where the FormDefUtil utility is.

From here you can either export or import forms by typing in the required syntax

If you want to export all the forms that you have you can type in

FormDefUtil export –all server username application (FormDefUtil export -all localhost admin sampleapp)

If you want to export only one form, you can type in

FormDefUtil export FormName server username application (FormDefUtil export retirement localhost admin sampleapp)

The above export command creates an XML file that you can import in a different environment.

In order to import form in the target planning app, you can type in

FormDefUtil import –all server username application (FormDefUtil import -all localhost admin sampleapp)

SOME MORE

All forms within an application:
FormdefUtil.cmd export -all localhost khoonks (username here) normal (application name here)

it will ask for password

One form that you want to export:
For example, if you want to export a form named "Other Expenses" from the "normal" application, the syntax would be 

FormdefUtil.cmd export "Other Expenses" localhost khoonks normal

It will ask for password.


After the execution of the script, the output fill will be seen in the Planning1 folder > D:\oracle\Middleware\user_projects\epmsystem1\Planning\planning1. 
Output is an XML file and it is something like "Other Expenses - Capital.xml"

In order to import only one app, you can type in

FormDefUtil import filenamepluslocationoffile server username application (FormDefUtil import formname.xml localhost admin sampleapp)

After you run the command, if it asks for the password, type in the password and wait for it to run.

There are other parameters that you can use with the FormDefUtil utility. You can refer to http://docs.oracle.com/cd/E17236_01/epm.1112/hp_admin/frameset.htm?ch12s06s04.html for more information.

During the budget preparation for the next year, we had a requirement where we had to delete the SUPPORTING DETAILS that were entered in Planning Data Forms.

It was not simply deleting all the details but we had to “wipe out everything from some of the supporting detail forms” where we had to “delete only the numbers and keep the description in some of the supporting detail reports.”

Well, in this case, there are two different options that came to my mind.

1                1. Use the “Clear Cell Details” options in Workspace. Administration -> Manage -> Clear Cell Details and specify the Plan Type and dimensions.

2                2. Use SQL script.

I thought about using the first option to start with, and I cleared the cell details using the Workspace. However, it wiped away the descriptions as well. So, it was not the proper method of clearing cell details in this case.

Therefore, I chose to use the second option of running the SQL script. The following Oracle link is where you can look to see how to “clear cell details.”

http://download.oracle.com/docs/cd/E17236_01/epm.1112/hp_admin/ch10s04s02.html

This document mentions two different SQL scripts – “sddelete.sql” and “aadelete.sql” and these can be found under the Hyperion home path D:\oracle\Middleware\EPMSystem11R1\products\Planning\config\sql.

By modifying the script as per our need and running the script, we were able to delete only the data in the supporting detail forms without having to delete the description.

If you have similar kind of situation, you can use the two scripts in order to delete supporting details.

TNS:could not resolve the connect identifier specified – ORA 12154

I was trying to load some data and each time I ran the SQL script, I got the message “ORA 12154 TNS:could not resolve the connect identifier specified.” Since, I am not on the Oracle side but rather on the Hyperion side, some research regarding “TNS names” and the “ORA 12154” error helped me solve the problem. The reason I am entering this on my blog is because it might be helpful to folks who come across the same issue.

The above error can be seen for various reasons as per documents:

                a. Oracle net libraries not being able to get the TNS alias from “TNSNAMES.ORA” file.

                b. Something is wrong with the way the alias is created in the “TNSNAMES.ORA” file, such as   incorrect syntax.

                c. TNS alias could not be resolved into a connect adapter.

There are several things we need to check and make sure they are correct.

1. Verify that the TNSNAMES.ORA file has the "service name" and "alias" that are being used. In my case, the path to the file is \\SERVER\D$\oracle\database\product\11.2.0\client_1\network\admin

2. Take a look at a file SQLNET.ORA in \\SERVER\D$\oracle\database\product\11.2.0\client_1\network\admin folder and make sure that you have TNSNAMES in NAMES.DIRECTORY_PATH.

For example:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

3. Try and see if  you can log in to Oracle using SQL developer, SQL PLUS or any other tool that you use in your environment.

4. Ensure that the ENVIRONMENT VARIABLE "PATH" has the path for the TNSNAMES.ORA file specified.

If you are not sure how to check the ENVIRONMENT VARIABLE "PATH", - in Windows XP
a. Start -> My Computer -> Right Click -> Properties.
b. Click the ADVANCE tab and then ENVIRONMENT VARIABLES
c. Under "SYSTEM VARIABLES" you will see a bunch of "varialbes" and "values" They should be in alphabetical order. Key in "P" to get to the variables starting with "P"
d. When you get the "PATH" click the EDIT tab.
e. You will see a lot of paths here. The easiest way to view is to copy and paste it to a word processor such as Notepad or Textpad, whatever you prefer or have.
f. Check and see if the path to TNSNAMES.ORA is specified here.
g. Also ensure that you have the path for Oracle_Home specified.
h. One thing very tricky about the PATH that I have noticed is that even when you have the path specified, it does not work. In that case, if your variable path is towards the end of the path, try to move it towards the beginning. I am not sure if it is a glitch or if there is restriction in the number of letters, but I have noticed quite a few times when it worked by moving the path towards the beginning.
i. Another interesting and sometimes confusing issue is that sometimes we happen to have the Client installed more than once and the path points to the wrong one. This is also sometimes an issue. So make sure that you have the path of the right one.
j. Check the registry for Oracle_Home (the value). If you are not sure how to check it.
          a. Start command prompt. START -> RUN -> Type in "REGEDIT" and hit enter. You will see the REGISTRY EDITOR screen.
          b. On the left pane -> HKEY_LOCAL_MACHINE \SOFTWARE\ORACLE\Key_OraClient11g_home1. On the right pane, you will see the path. Ensure they are correct.
k. Check entry for TNS_ADMIN in the registry editor: HKEY_LOCAL_MACHINE \SOFTWARE\ORACLE. If it is there make sure it has the right path: ...client1\network\admin. If it is not there, create one.
i. If you think you have more than one instance of Oracle client, you can check if there are more in the registry editor: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\HOME_COUNTER.

After ensuring all of the things specified above are correct try to run the SQL script, it should work unless there are some other issues with the system.

Cannot log in to EAS - Java related

If you are not able to launch the EAS console from your Workstation, these are some of the steps that you could follow. For example, when you try to launch EAS you might get a box that would tell you to save/open the file called EASCONSOLE.JNLP.

You can do a couple of things in this situation.

1. Make sure that you have the right version of Java installed.

2. Save the EASCONSOLE.JNLP file somewhere on your computer. Right-click the file and click OPEN WITH ->CHOOSE PROGRAM.

3. Browse and choose the javaws.exe file. The path in my case is C:\Program Files\Java\jre6\bin.

4. Check “Always use the selected program to open this kind of file”

5. Click OK.

6. Try to open your EAS console, and you should be able to log in now.

Also some other things to be noted.

1. Go to your control panel and open JAVA.

2. Click the JAVA tab and VIEW.

3. Make sure that the proper version is enabled.