EPM Cloud - Import Hybrid Snapshot into Non-Hybrid Pod

If you’ve ever tried to import a EPM Planning hybrid-enabled application snapshot into a non-hybrid EPM pod you will have seen this error:

EPMLCM-14000: Error reported from Core Platform.

Unsupported migration of artifacts and snapshots for Hybrid PBCS application from (Service Type ENTERPRISE with Hybrid support) to (Service Type LEGACY without Hybrid support).

The import will fail completely and you won’t be able to pull in any artefacts.

Here is a simple hack which will enable you import the entire snapshot (excluding data of course).

Download the Snapshot

Download the Hybrid snapshot to your local machine and unzip the snapshot.

You’ll need to make changes to the 'Application Definition.xpad' and 'Application Settings.xml' files:

Edit the Application Definition.xpad File

File before changes:

File after changes:

Edit the Application Settings.xml File

File before changes:

File after changes:

Once you have made these changes zip up the folders of the snapshot. Zip up the files at this level:

You can now upload the modified snapshot and import the entire application (excluding data).

To load the data into your non-hybrid pod you will need to export the data to a text file on the Hybrid environment and then import into the non-hybrid pod.

EPM Cloud - Make the User Experience Simple & Intuitive with Direct Links

One great new feature which came out recently in the Oracle EPM Cloud is the ability to download direct links to any EPM Cloud navigation flow card or cluster. This includes links to both the default cards and custom cards and clusters which you have created. Task lists can now be super simple for the end user. Instead of a War and Peace list of instructions on how to navigate to User Preferences or how to open up Data Integration just give them a direct link!

How to access the EPM Cloud direct links?

Login to your EPM Cloud instance. In the top-right click on the down arrow next to your username and click on ‘Export URLs’:

This will download a .CSV file which is pipe delimited:

Adding the URL to a Task List

You can add any number of task list URL links to aid the end users. Some very useful ones could be User Preferences and Variables for a brand-new roll-out.

You can copy the URL and paste it into a URL type task list, here I’ll use the User Preferences card:

The task will look like this in their Task List:

Clicking on the task list will open up the cluster/card/tab in a new browser tab:

Here’s another example with Data Integration. Copy the Data Integration URL from the downloaded file:

Create your task list item:

Click on the task list and the Data Integration card opens up directly in a new browser tab. If you added instructions to the task list it also means the user can navigate back and forth between the tabs to read the instructions associated with the task.

Here is a link from the EPM Cloud youtube channel which demos how this functionality can also be used to streamline the user experience across the Oracle suite by embedding EPM content into Oracle ERP and Oracle NetSuite: https://www.youtube.com/watch?v=qJirlSZpTq4

In the next post we'll make things even slicker with a little touch of Groovy and the REST API.

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: com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user

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

mssql-jdbc_auth-9.2.0.x64.dll

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.0.0_enu.zip\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...

integratedSecurity=true

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:

jdbc:sqlserver://server:port;databaseName=dbname;integratedSecurity=true

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

Happy integrating :)

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!