Wednesday, 16 December 2020

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!

Friday, 6 November 2020

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:

Data management, EPM Cloud, Error

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. 

Thursday, 24 September 2020

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.

Friday, 18 September 2020

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!



Tuesday, 25 February 2020

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:
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.

Wednesday, 4 December 2019

OAC Remote Data Gateway Part 2: The RPD



OAC Data Gateway 105.4: Now with added RPD goodness

In one of my previous posts (here) I went through the setup of the Oracle Analytics Cloud Data Gateway utility. I showed how this could be used to connect your OAC instance to your on-premises data sources without the need of opening firewalls etc.

One missing piece of the puzzle was how to get the OAC RPD semantic layer to connect to on-premises using Data Gateway. This is a key piece of functionality that all current OBIEE customers will need if they want to connect their existing dashboards to their on-premises datasources.  Thankfully with the release of OAC 105.4 it is now possible to connect your to your on-premises data sources via your RPD using Data Gateway.

Prerequisites:
  • Install the latest version of OAC Data Gateway from here
  • Install the OAC 105.4 Admin Tool on the SAME machine

Okay, that's it for pre-reqs :)

Alors mon cher ami, how do I get this to work?

Here are the steps to connect your OAC Cloud RPD to your on-premises data sources:
  • Make sure Data Gateway is running. Check here if you need to review the setup
  • Open up the obiee_rdc_agent.properties in the Data Gateway folder and note down the port number. (Beware, this number will change automatically each time you save the Data Gateway config so alter as necessary)

  • Launch the OAC Admin Tool
  • Before you open your RPD file go to File -> Load Java Datasources...


  • Enter the following details:
    • Hostname: localhost (Data Gateway is on the same machine as your Admin Tool)
    • Port: The port you found in the obiee_rdc_agent.properties earlier
    • User: Whatever. Literally enter anything you like for the username and password, there is no authentication but this field needs to be populated
    • Password: whatever


  • You should see the following message:


  • Open your RPD 'In the Cloud'
  • Duplicate the physical source as a backup (just in case)
  • Open the connection pool select JDBC as the call interface
  • Enter the jdbc string to connect to your local db in the Connection String (SQL Server in my example jdbc:oracle:sqlserver://MySQLHostname:1433;DatabaseName=MyDatabase)

  • In the Miscellaneous tab enter the path to the jdbc driver class. In my example I'm using SQL Server com.oracle.bi.jdbc.sqlserver.SQLServerDriver but you can find the full list of JDBC and JNDI templates and examples here

  • Save your RPD and publish to the cloud
  • To test the connection try to import the metadata by right-clicking on the Connection Pool:

And that's it. All being well you should be able to view your existing dashboards in OAC sourcing the data from your on-premises databases. When we tested response times were surprisingly fast.

Tuesday, 1 October 2019

OCI - Installing EPM Automate on Always Free Oracle Linux

Using Oracle's FREE Cloud VMs to Store your EPM Cloud Snapshots

Unless you have been living under a rock for the past month you will know that Oracle have released their "Oracle Cloud Free Tier". This consists of the following services FOR FREE!
https://www.oracle.com/uk/cloud/free/


I've enjoyed playing with ADW and APEX, an incredible combo which will enable us to build simple applications to enhance our existing EPM Cloud apps.

The two free Oracle Cloud Infrastructure Compute VMs also caught my attention. These could of course be used for a multitude of things, one of which could be a host for EPM Automate and a location to store EPM Cloud snapshots.

Installing EPM Automate on Oracle Linux OCI

There are several blogs out there on how to setup your instance. Here is a good one from APEX guru Dimitri:
https://dgielis.blogspot.com/2019/09/free-oracle-cloud-6-create-vm-instance.html
So, once you've got your VM running you can install EPM Automate.

  • Download the Linux EPM Automate installer. The link can be found in the PBCS user name drop-down:


  • Download the Java JDK for Linux from here for example (the JDK doesn't come bundled with the Linux EPM Automate version)
  • Copy both installers to your Oracle Linux VM using your favourite file transfer tool (WinSCP for instance)

Installing the JDK on Oracle Linux VM

JRE version 1.7 or higher is required.

This is nice and simple. The installer is just a compressed file so you can extract it to your preferred location:
tar -zxvf <filename>
e.g.
tar -zxvf jdk_ri-8u40-b25-linux-x64-10_feb_2015.tar.gz

I extracted mine to /GS_software/java-se-8u40-ri


Installing EPM Automate on Oracle Linux VM

This is again nice and simple. The EPM Automate is a compressed file, extract it to your preferred location:

tar -xvf epmautomate.tar

I extracted mine to /GS_software/epmautomate



Setting your JAVA_HOME

You'll need to set the JAVA_HOME in your user profile or as a shell environment variable.


Running EPM Automate

So, we've extracted the JDK and the EPMAUTOMATE archive, we should be good to go right?

WRONG!

I got the following error when trying to login:

EPMAT-7:Unable to connect to https://planning-blahblah.pbcs.em3.oraclecloud.com Root Cause: java.lang.RuntimeException: Unexpected error: java.security.InvalidAlgorithmParameterException: the trustAnchors parameter must be non-empty.
 1. Ensure that you are using the correct URL.
 2. If your organization requires the use of a proxy server to connect to the internet, verify that "proxyHost" and "proxyPort" environment variables are correctly set.
 Ex: setenv proxyHost <proxyhostname>,setenv proxyPort <proxyport>

The problem here is that we don't have the SSL certificate installed into our java keystore.

Importing the SSL Certificate into the Java Keystore

Open a browser and connect to your PBCS URL
https://planning-blahblah.pbcs.em3.oraclecloud.com/HyperionPlanning

Make sure you actually login to PBCS. If you download the certificate on the login page it will be the wrong certificate and you will get a bogus error like this: "EPMAT-7: Unable to connect. Unsupported Protocol: HTTPS".
  • Once you've logged in to EPM Cloud click on the browser padlock-> connection ->more information:


  • Click on 'View Certificate':
  • Click on Export and save the .crt file to your desktop
  • Upload it using WinSCP to your VM e.g. /GS_software/epmautomate/pbcs_em3_oraclecloud_com.crt
We now need to import the certificate into our Java cacerts keystore:
  • Change directory to jre/bin 
cd /GS_software/java-se-8u40-ri/jre/bin
  • Run the keytool command to import the certificate:
./keytool -import -file /GS_software/epmautomate/bin/pbcs_em3_oraclecloud_com.crt -storepass changeit -keystore /GS_software/java-se-8u40-ri/jre/lib/security/cacerts -alias pbcs_em3_oraclecloud_com


Now test EPM Automate again:


It works!

So you have a FREE Oracle VM on which you can run your EPM Automate commands and save your EPM Cloud PBCS snapshots for FREE! Zero installs required in your customer data centre.

Did I mention it was FREE?

If you're not comfortable with Linux or simply haven't got the time then our support team at Brovanture will gladly manage this service for you.

Thanks :)