Planning Tutorial: How to deploy Planning Utilities Standalone!

Introduction

If you ever had to publish Planning utilities for administrators in e.g. Terminal Server or Citrix, you experienced the difficulties of doing that. Planning utilities can only be executed on the server where Planning is installed. Solutions to provide access to these utilities range from full administrative (RDP) access on the Planning server to PowerShell scripts using remote execution. In my experience non of these solutions are ideal and at best cumbersome to maintain.

Already for a few years I though: "There must be another way".

... and now there is!

The concept

My idea was to create a standalone package that could be placed anywhere similar to a "normal" FAT client... automate the creation of the "package" and make it easy to maintain in case e.g. patches are installed for EPM. In addition I want to prevent the usage of third-party utilities or changing Oracle code. Last, but not least... all input and output files should be provided or created locally (not on the Planning server).

The solution (Draft)

Currently the solution is still in development, but I already wanted to share the solution in the stage as it is currently. The current solution consists of two batch files and a text file:

• createPackage.cmd - Creates the packages
• excludelist.txt - Contains a list of file and folder names to exclude. Used by the above script.
• setHPenv.bat - Replacement of original provided by Oracle

There are a few things to be aware of:

• Works only on Windows (tested on Windows 2008 R2 and 2012 R2).
• Tested only in combination with EPM 11.1.2.4, however it should work for all 11.1.2.x versions.
• Tested on JDK 1.6 and 1.7.
• 64-bit only.
• Current script assumes all files required are located on a single server.
• When the environment in patched after using this script, the script has to be re-run in order to include all updates files.
• The location that will run the Planning utilities must have full network access to the EPM servers. In the future I will provide a list of all the ports and protocols required, so you can configure the firewall(s) accordingly.
• The package created is "connected" to the environment on which the script is run, so run the script on each environment e.g. production, test and development.
• The package cannot connect to multiple environments (see comment above). 
• Spaces in folders should be avoided.
• Obviously totally unsupported by Oracle.

After running the script the following folder structure is created:

\---resources -/- Contains all required files     +---common     |   \---EssbaseRTC-64 -/- Essbase RTC Client     |           +---config     |   \---foundation     |       \---11.1.2.0 -/- reg.properties     +---lib -/- all required java classes (jar files)     +---lib64 -/- all required Windows DLL's     \---tmp -/- Target for audit logs produced by the Planning utilities

During first-run, the folders Diagnostic/logs/planning folders are created and contains all logs produces by the Planning Utilities.

The main steps

1. Run the createPackage.cmd script on the (or one of the) Planning server(s);
2. Replace the value of the variable "EPM_PLANNING_INSTANCE" with "%~dp0" for each script in the package root, except for setHPenv.bat;
3. Zip the whole folder;
4. On the target system change the JAVA_HOME variable value in the setHPenv.bat to match your environment.

The Scripts

createPackage.cmd

@ECHO OFF REM ########################################################################### REM # Author  : christian-hoekstra.blogspot.com REM # Version  : 1.1 REM # Creation Date : 29 June 2016 REM # Last Changed : 15 November 2017 REM # Purpose  : Build offsite Planning utilities package REM # Changelog : REM # added support for CalcMgrCmdLineLauncher.cmd REM ###########################################################################     :: Test if EPM_ORACLE_HOME exists IF "%EPM_ORACLE_HOME%" == "" (  ECHO " Environment variable EPM_ORACLE_HOME not defined "  GOTO end  )   ::CALL %EPM_ORACLE_HOME%\common\config\11.1.2.0\setJavaRuntime.bat   :: Define internal variables SET PACKAGE_BUILD_LOC=%~dp0 SET PACKAGE_ROOT=%PACKAGE_BUILD_LOC%hsputils SET LIB=%PACKAGE_ROOT%\resources\lib SET LIB64=%PACKAGE_ROOT%\resources\lib64 SET JAVA=%PACKAGE_ROOT%\resources\java SET COMMON=%PACKAGE_ROOT%\resources\common SET COPY_OPTIONS=/y SET XCOPY_OPTIONS=/y /i /e /exclude:%EXCLUDE_LIST% SET EXCLUDE_LIST=%PACKAGE_BUILD_LOC%excludelist.txt   :: Create required folders MKDIR %LIB% MKDIR %LIB64% MKDIR %COMMON% MKDIR %PACKAGE_ROOT%\resources\tmp   :: Custom setHPenv.bat COPY %PACKAGE_BUILD_LOC%setHPenv.bat %PACKAGE_ROOT% %COPY_OPTIONS%   :: Required java classes COPY %EPM_ORACLE_HOME%\common\jlib\11.1.2.0\registry-api.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\common\CSS\11.1.2.0\lib\css.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\common\CSS\11.1.2.0\lib\ldapbp.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\common\loggers\Log4j\1.2.14\lib\log4j-1.2.14.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\common\SharedServices\11.1.2.0\lib\wlpool.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\common\SharedServices\11.1.2.0\lib\audit-client.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\common\SharedServices\11.1.2.0\lib\interop-sdk.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\common\calcmgr\11.1.2.0\lib\calcmgrparser.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\common\calcmgr\11.1.2.0\lib\calcmgrscheduler.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\oracle_common\modules\oracle.pki_11.1.1\oraclepki.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\oracle_common\modules\oracle.xdk_11.1.0\xmlparserv2.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\oracle_common\modules\oracle.jdbc_11.1.1\ojdbc6dms.jar %LIB% %COPY_OPTIONS% ::COPY %EPM_ORACLE_HOME%\..\oracle_common\modules\datadirect_4.1\wldb2.jar %LIB% %COPY_OPTIONS% ::COPY %EPM_ORACLE_HOME%\..\oracle_common\modules\datadirect_4.1\wlsqlserver.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\oracle_common\modules\org.codehaus.jackson.mapper.asl.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\oracle_common\modules\org.codehaus.jackson.core.asl.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\modules\com.bea.core.apache.commons.pool_1.3.0.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\modules\com.bea.core.apache.commons.lang_2.1.0.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\modules\javax.mail_1.1.0.0_1-4-1.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\modules\javax.servlet_1.0.0.0_2-5.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\modules\javax.mail_1.4.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\modules\com.bea.core.apache.log4j_1.2.13.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\wlserver_10.3\server\lib\ojdbc6.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\wlserver_10.3\server\lib\mysql-connector-java-commercial-5.1.17-bin.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\wlserver_10.3\server\lib\wlsqlserver.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\wlserver_10.3\server\lib\wldb2.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\wlserver_10.3\server\lib\wlsybase.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\wlserver_10.3\server\lib\wlinformix.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\wlserver_10.3\server\lib\fmwgenerictoken.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\oracle_common\modules\oracle.owasp_11.1.1\commons-lang-2.3.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\oracle_common\modules\oracle.odl_11.1.1\ojdl.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\..\oracle_common\modules\oracle.dms_11.1.1\dms.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\common\misc\11.1.2.0\json.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\common\misc\11.1.2.0\servlet-api.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\common\calcmgr\11.1.2.0\lib\calcmgrcommon.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\common\planning\11.1.2.0\lib\mvel.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\common\planning\11.1.2.0\lib\ognl-2.6.7.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\products\Planning\lib\HspJS.jar %LIB% %COPY_OPTIONS% ::COPY %EPM_ORACLE_HOME%\products\Planning\lib\HspPPIT.jar %LIB% %COPY_OPTIONS% ::COPY %EPM_ORACLE_HOME%\products\Planning\lib\HspPPITEPMA.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\products\Planning\lib\hbrhppluginjar.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\products\Planning\lib\hbrmigrator.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\products\Planning\lib\HBRExporter.jar %LIB% %COPY_OPTIONS% COPY %EPM_ORACLE_HOME%\products\Planning\lib64\*.dll %LIB64% %COPY_OPTIONS%   :: Essbase RTC XCOPY %EPM_ORACLE_HOME%\common\EssbaseRTC-64 %COMMON%\EssbaseRTC-64 %XCOPY_OPTIONS%   :: reg.properties XCOPY %EPM_ORACLE_HOME%\..\user_projects\config %PACKAGE_ROOT%\resources\config %XCOPY_OPTIONS%   :: log configuration file COPY %EPM_ORACLE_HOME%\..\user_projects\epmsystem1\Planning\planning1\loggingCLU.xml %PACKAGE_ROOT%\resources %COPY_OPTIONS%   :: Scripts XCOPY %EPM_ORACLE_HOME%\..\user_projects\epmsystem1\Planning\planning1\*.cmd %PACKAGE_ROOT% %XCOPY_OPTIONS%   :: Java SDK :: XCOPY %JAVA_HOME%\*.* %JAVA% /y /i /e /exclude:%EXCLUDE_LIST%     :end

setHPenv.bat

@ECHO OFF   SET EPM_PLANNING_INSTANCE=%~dp0 SET EPM_VERSION=11.1.2.0 SET JAVA_HOME=%EPM_PLANNING_INSTANCE%/resources/java SET EPM_PLANNING_HOME=%~dp0resources SET HP_LOG_PATH=%~dp0resources/diagnostics/logs/planning SET HP_SHARED_LIB_PATH=%EPM_PLANNING_HOME%/lib64 SET PATH=%EPM_PLANNING_INSTANCE%;%EPM_PLANNING_HOME%/common/EssbaseRTC/%EPM_VERSION%/bin;%PATH% SET CLASSPATH=%CLASSPATH%;%EPM_PLANNING_INSTANCE%/resources/lib/*   SET HP_JAVA_OPTIONS=-DEPM_ORACLE_HOME=%~dp0resources SET HP_JAVA_OPTIONS=%HP_JAVA_OPTIONS% -DEPM_ORACLE_INSTANCE=%~dp0resources SET HP_JAVA_OPTIONS=%HP_JAVA_OPTIONS% -Dhyperion.home=%~dp0resources SET HP_JAVA_OPTIONS=%HP_JAVA_OPTIONS% -Djava.library.path=%HP_SHARED_LIB_PATH% SET HP_JAVA_OPTIONS=%HP_JAVA_OPTIONS% -Djavax.xml.parsers.DocumentBuilderFactory=oracle.xml.jaxp.JXDocumentBuilderFactory SET HP_JAVA_OPTIONS=%HP_JAVA_OPTIONS% -Djavax.xml.transform.TransformerFactory=oracle.xml.jaxp.JXSAXTransformerFactory SET HP_JAVA_OPTIONS=%HP_JAVA_OPTIONS% -Djava.io.tmpdir=%EPM_PLANNING_INSTANCE%/resources/tmp SET HP_JAVA_OPTIONS=%HP_JAVA_OPTIONS% -Djava.util.logging.config.class=oracle.core.ojdl.logging.LoggingConfiguration SET HP_JAVA_OPTIONS=%HP_JAVA_OPTIONS% -Doracle.core.ojdl.logging.config.file=%EPM_PLANNING_INSTANCE%/resources/loggingCLU.xml SET HP_JAVA_OPTIONS=%HP_JAVA_OPTIONS% -Dlogging.folder=%HP_LOG_PATH% SET HP_JAVA_OPTIONS=%HP_JAVA_OPTIONS% -Xmx512M SET HP_JAVA_OPTIONS=%HP_JAVA_OPTIONS% -Dcom.hyperion.planning.datamodel=64

excludelist.txt

/* Not working */ CreatePFPImportExportTable.cmd ExportPFPSampleApp.cmd ExportPFPSampleApp_EPMA.cmd ExportPFPTableColumnToMemberMapping.cmd ExportPFPTemplates.cmd ExportPFPTemplates_EPMA.cmd   /* HBR nolonger available in 11.1.2.x */ HBRExport.cmd   /* We have no use for the original */ setHPenv.bat   /* Essbase RTC - no need for old stuff */ 9.3.1 11.1.1.3   /* No need for JRE if we copy JDK */ jre

Let's get to work

Copy the files to the Planning server and execute the createPackage.cmd script. A folder should be created with the structure as shown earlier. Make sure to check that the script completed without errors. In case files where missing, identify and copy them manually from another server. Personally I used Notepad++ to replace the variable EPM_PLANNING_INSTANCE value by opening all scripts (except setHPenv.bat) in Notepad++ and use the search / replace in all opened files option. Search for "D:\Oracle\Middleware\user_projects\epmsystem1/Planning/planning1" and replace with "%~dp0". Change the drive-letter in the search field with whatever matches you environment. Finally, ZIP the whole folder and you are done.

In case the target system has no java run-time installed, uncomment the Java copy command in the createPackage.cmd.

On the target, extract the ZIP file in a location of your choice. In case you included the Java run-time, you are done. In case you want to use an already installed Java JRE on the target system, change the variable "JAVA_HOME" in the setHPenv.bat accordingly.

Final notes

So far I have tested and confirmed following scripts to be working:

• BroadCastMessage.cmd
• CubeRefresh.cmd
• ExportSecurity.cmd
• FormDefUtil.cmd
• HBRMigrateSecurity.cmd
• HspUnlockApp.cmd
• MaintenanceMode.cmd
• MenuDefUtil.cmd
• PasswordEncryption.cmd
• TaskListDefUtil.cmd
• UpdateUsers.cmd
• CalcMgrCmdLineLauncher.cmd
• HBRExport.cmd (only for EPM versions that still have Business Rules)

As soon time allows, I will confirm all other utilities. Please be aware that this solution is not supported by Oracle (or by me for that matter). The solution is provided as is, and I take no responsibility whatsoever (see my blog disclaimer). Feel free to test the solution and provide me your comments and/or suggestions.

Enjoy!