Thursday, 4 February 2021

EPM Integration Agent - MS SQL Server Windows Authentication

If you have tried to use Windows authentication in the database connection details for your Oracle EPM Integration Agent on-premises data source you may have come across these errors:

Error: This driver is not configured for integrated authentication Login failed for user

So, how DO you use a Windows username and password with an EPM Integration Agent target?


You need to add the mssql-jdbc_auth-9.2.0.x64.dll to your JRE\bin and JRE\lib folder. This is the library used by the MS SQL jdbc driver for Windows authentication. 

You should be able to find the library in the .zip file you downloaded containing the MS SQL jdbc driver (\sqljdbc_9.2\enu\auth\x64).

Restart the agent and it should now be ready to use Windows authentication. But WAIT, there's one additional step...


You'll need to add an additional parameter to your jdbc connection string to let the driver know that you are using Windows authentication and not native MS SQL authentication:


Et voilĂ ! The EPM Agent will now login to your MS SQL Server database using a Windows user account for authentication. 

Happy integrating :)

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!


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.


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.

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

  • 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:\Oracle\Middleware\oracle_common\common\bin\wlst.cmd E:\Oracle\Scripts\

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

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

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 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")



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 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 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 was unable to extract the fmw_12. file.


For distributed installs you will need to run the RCU for each server node. I did try copying across the 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\\

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.


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 – (2)” -> “Uninstall OracleHome2”
  • Run “Oracle FMW –”  -> “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.

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