EPM 11.2 - How to Start OHS Remotely?

In the on-prem Hyperion EPM world it’s common to have one script sitting on your primary Foundation server which can start and stop all the services across all of the host machines in your environment. In 11.1.2.x this was simple because every process had a Windows service associated with it and we could use the Windows SC command to start a Windows Service remotely on another machine.

In 11.2.x there is no Windows service for the OHS component which needs to be started via the command line. ThatEPMBloke wrote a service wrapper to create a service but some customers don’t allow custom code in their environments. So in a clustered EPM 11.2.x environment how can we start our second instance of OHS from our primary Foundation server?

OHS Node Manager to the Rescue!

TLDR;

To put it simply we use the weblogic scripting tool wlst.cmd on the primary OHS server to connect to the node manager on the second OHS server and start OHS remotely.

Detail:

When you install OHS with the EPM installer it configures OHS as a standalone node. OHS needs the local OHS Node manager service running in order to start. By default each node manager instance only listens on localhost (you can’t connect to it remotely) but we can tweak the node manager Windows service to listen externally which enables us to connect from the primary node to the secondary node manager and start OHS on the secondary node.

Alter the Node Manager Listen Address

On the second OHS server:

• Stop OHS using the command line command stopComponent.cmd ohs_component
• Stop the OHS Node Manager Windows service
• Open up the Windows registry at: 

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle Weblogic ohs NodeManager (E_Oracle_Middleware_ohs_wlserver)\Parameters

• Edit the CmdLine parameter and find the -DListenAddress='localhost' in the string
• Change the ListenAddress so that it listens on the hostname rather than localhost e.g. -DListenAddress='Hostname2' This enables us to connect to the node manager remotely.
• Start the Node manager Windows service on the second OHS server

Create a Super Simple Python Script to Start OHS on Server2

We can now write a simple python script on the primary OHS server to start OHS on the secondary OHS Server.

startOHS2.py:

nmConnect('epm_admin', 'password', 'hostname2', '5557', 'ohs', 'E:/Oracle/Middleware/user_projects/EPMFDN2/httpConfig/ohs','ssl')
nmStart(serverName='ohs_component', serverType='OHS')
nmDisconnect()

• nmConnect() - this command connects to the OHS node manager on hostname2
• The user name and password are the Weblogic Admin credentials
• The hostname is the servername of the second OHS server
• 5557 is the default port that OHS NodeManager runs on in EPM installations
• nmStart() - this command starts the OHS 'ohs_component' server on hostname2
• nmDisconnect() - disconnects from NodeManager

Use the Weblogic Scripting Tool to run the script. You should use the wlst.cmd found in the Oracle\Middleware\oracle_common\common\bin folder:

e.g.

E:\Oracle\Middleware\oracle_common\common\bin\wlst.cmd E:\Oracle\Scripts\startOHS2.py

To stop OHS on hostname2 you can run a similar script which uses the nmKill() command to kill the secondary instance of OHS:

stopOHS2.py:

nmConnect('epm_admin', 'password', 'hostname2', '5557', 'ohs', 'E:/Oracle/Middleware/user_projects/EPMFDN2/httpConfig/ohs','ssl')

nmKill(serverName='ohs_component', serverType='OHS')

nmDisconnect()

Points of Interest

This works nicely, however it stores the password in plain text which isn't great. You can alter the nmConnect() command to use userConfigFile and userKeyFile parameters to store the encrypted user credentials.

Another point to make is that your usual "startComponent.cmd ohs_component" script won't work any more. This is because the script tries to connect to nodemanager on localhost instead of the hostname. The simple solution is to use the same startOHS2.py script on your second OHS server.

Hope you all have a safe and merry Christmas!

Data Load Fails in EPM Cloud Data Management when using Replace Data Option

 We were trying to load data into an EPM Planning Cloud application and we saw this:

Oh no, the dreaded red cross when doing a data export in Data Management!

In this example we were running a data load to a target plan type using the 'Replace Data' option.

Here is the error message in the log:

2020-11-06 09:55:02,837 INFO  [AIF]: Replace data script:

FIX ("Manchester",”Apr”,”Working","FY21")

CLEARDATA "null";

ENDFIX

2020-11-06 09:55:02,838 INFO  [AIF]: EssbaseRuleFile.getReplaceDataScript - END

2020-11-06 09:55:02,875 ERROR [AIF]: Cannot calculate. Essbase Error(1012004): Invalid member name [null]

2020-11-06 09:55:02,882 INFO  [AIF]: EssbaseService.loadData - END (false)

2020-11-06 09:55:02,901 FATAL [AIF]: Error in CommData.loadData

 

The issue we have is that the calc script is obviously invalid. When you run data load rule with the “Replace Data” option it dynamically creates a calculation to clear the data in the target cube before it loads the data.

The calculation generated is usually of the format CLEARDATA “Scenario Name”

where the Scenario (DM Category) is derived from whatever category you have set in your data load rule.

So why was ours failing?

We were loading data into multiple scenarios from the same file. In order to do this we had set our Scenario dimension to Generic in the target application dimension details:

 

This of course means that the Category (Scenario) in the rule is ignored so the dynamically generated CLEARDATA calculation sets the scenario as null.

 

The solution?

Set the data export mode to 'Store Data'.  This will just load the data into the target without trying to clear the data using a dynamic script. We run a business rule prior to the load to clear the data target.

Ahhh, that's better. 

EPM 11.2.2 - APS Fails to Start Correctly

There is a bug in EPM 11.2.2 which causes the Analytic Provider Services web application to fail to start up correctly the second time it is launched. You won't be able to connect to Essbase using Smart View and you will see these errors in the APS log file:

<Error> <HTTP> <BEA-101216> <Servlet:"oracle.webservices.essbase.DatasourceService" failed to preload on startup in Web application: "/essbase-webservices".

weblogic.application.ModuleException: java.lang.RuntimeException: Failed to deploy/initialize the application as given archive is missing required standard webservice deployment decriptor.

The fix can be found in Oracle Support Doc ID 2693784.1. You'll need to apply APS patch 11.1.2.4.040 to your EPM 11.2.2 install. This might seem strange to have to apply a patch from the previous version but remember that EPM 11.2.2 actually runs Essbase version 11.1.2.4 under the covers.

I can confirm that once you apply the patch, APS starts correctly. Everyone can go and have a nice cup of tea.

EPM 11.2.x - Where did my Oracle Wallet Manager Go?

Recently I had to install EPM 11.2.x for a customer using MS SQL Server as their relational repository. The EPM installer package comes bundled with the option to install the Oracle Client. This customer didn't need the client so there was no need to add the Oracle clients to the installed options.

Big Mistake!

The Oracle Wallet Manager is used to create SSL certificate requests and to import new certificates for Oracle HTTP Server. In previous versions of EPM, the EPM stack would also install the Oracle Wallet Manager for you by default.  This is no longer the case in EPM 11.2.x, the Wallet Manager only gets installed if you install the Oracle Client:

Lesson: always install the Oracle Client as part of the EPM stack, even if you don't need it!

Fortunately the Oracle client installer can be found in the unzipped EPM installation extracts: <Extract Location>\db64\Disk1

Run the setup.exe and your Oracle Wallet Manager will appear by magic. Happy SSLing!

Oracle EPM 11.2 Installation How To

There have been some very helpful blogs and twitter posts out there commenting on the Oracle EPM 11.2 installation so I'd like to thank these lovely people:

• Dave Shay: http://www.epmonprem.pro/
• That EPM Bloke: http://hyperipwn.blogspot.com/
• AJ Martelino https://twitter.com/HyperionInfra

I’ve taken some of the info from the resources above and my own trial an error and come up with a condensed Oracle EPM 11.2 installation crib sheet. I’d recommend getting an Oracle EPM infrastructure consultant to do this for you as it’s quite involved. Oracle have had years to get this right so I really can’t understand how they’ve made such a mess of it. Multiple RCUs?!

Server Preparation

I used the following 3rd party software versions. Check the EPM Certification Matrix for full details

• Windows Server 2019
• SQL Server 2016

Windows Firewall

• Turn off notifications and blocking for Domain and Local Network.

Windows Defender

• Turn off on-access scanning.

Other Bits

• Create a new user with administrator privileges (using the 'administrator' user is not recommended although it worked for me).
• Turn off UAC. I also set these local policies to ensure UAC is completely turned off:
• User Account Control: Behavior of the elevation prompt for administrators in Admin Approval Mode (set to Elevate without prompting)
• User Account Control: Detect application installations and prompt for elevation (set to Disabled)
• User Account Control: Only elevate UIAccess applications that are installed in secure locations (set to Disabled)
• User Account Control: Run all administrators in Admin Approval Mode (set to Disabled)
• When launching any batch files right-click and 'Run as Administrator'.
• Check your install binaries. For some reason my V984509-01_part5.zip was unable to extract the fmw_12.2.1.3.0_odi.jar file.

RCU

For distributed installs you will need to run the RCU for each server node. I did try copying across the RCUSchema.properties file from my first node to other nodes but I got this error when trying to deploy the web app servers on other nodes: "The schema EPM_OPSS is already in use for security store(s)". So each node does need a separate RCU schema.

Having multiple RCU SQL databases per server node is a pain. To make things more manageable I used the same RCU database for each node and just changed the RCU prefix so I only needed one database.

Add your RCU database connection details manually to the E:\Oracle\Middleware\EPMSystem11R1\common\config\11.1.2.0\RCUSchema.properties:

sysDBAUser=UserName
sysDBAPassword=Password
rcuSchemaPassword=Password
schemaPrefix= e.g EPM1
dbURL= e.g. jdbc:weblogic:sqlserver://EPM1:1433;databaseName=RCU

EPM Configuration

Configure only the Foundation app server and database first. After configuring Foundation launch Weblogic admin server and start the Foundation Windows service.
Once you’ve done this you can then go ahead and configure the other components.
If you’re doing a distributed installation, make sure the Weblogic admin server is running on the first node before you configure the other nodes. On the second node I went through the same procedure of only configuring Foundation first, starting it, stopping it and then configuring the rest.

OHS

The config utility doesn’t create a OHS Windows service for us so we need to start it manually.

• To start OHS run E:\Oracle\Middleware\user_projects\EPM1\httpConfig\ohs\bin\startComponent.cmd ohs_component
• To stop OHS run E:\Oracle\Middleware\user_projects\EPM1\httpConfig\ohs\bin\stopComponent.cmd ohs_component

That EPM Bloke has kindly created a service wrapper here.

Rip it up and start again

The uninstallers are surprisingly good. If you need to ditch it all and start again then uninstall in this order. From the Windows menu:

• Run the “Oracle EPM System” -> “Uninstall EPM System”
• Run “Oracle FMW – 12.2.1.3.0 (2)” -> “Uninstall OracleHome2”
• Run “Oracle FMW – 12.2.1.3.0”  -> “Uninstall OracleHome”

The Oracle Client installs will prompt you to uninstall from the command line
Just to be sure I also deleted anything EPM related in the C:\Users\<USER>\AppData\Local folder.

I've now got a clustered EPM installation working nicely. Happy installing everyone.