OBIEE 12c and Planning

Following on from my last post about configuring OBIEE 12c with HFM I thought I'd give EPM Hyperion Planning a bash.

Integration with EPM Planning was supported in OBIEE 11.1.1.9 and didn't require any configuration. You just had to make sure you used the html character code to replace the colon for the host:port and the Planning metadata imported successfully:

adm:thin:com.hyperion.ap.hsp.HspAdmDriver:ServerName%3A19000:AppName

You would expect that since this worked out of the box in 11.1.1.9 that it would also work in 12c right?

Wrong!

You'll get the following error when trying to import the metadata:

The connection has failed. [nQSError: 77031] Error occurs while calling remote service ADMImportService.
Details: Details: com.hyperion.ap.APException: [1085]
Error: Error creating objectjava.lang.ClassNotFoundException

Many thanks to Ziga who also encountered the same error and blogged the solution here:

http://zigavaupot.blogspot.co.uk/2016/02/obi-12c-series-connecting-obi-with.html.

The solution is hidden in the Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition:

"For Hyperion Planning 11.1.2.4 or later, the installer does not deliver all of the required client driver .jar files. To ensure that you have all of the needed .jar files, go to your instance of Hyperion, locate and copy the adm.jar, ap.jar, and HspAdm.jar files and paste them into
MIDDLEWARE_HOME\oracle_common\modules."

Copy the following files from your Planning install:

• E:\Oracle\Middleware\EPMSystem11R1\common\ADM\11.1.2.0\lib\adm.jar
• E:\Oracle\Middleware\EPMSystem11R1\common\ADM\11.1.2.0\lib\ap.jar
• E:\Oracle\Middleware\EPMSystem11R1\common\ADM\Planning\11.1.2.0\lib\HspAdm.jar

to this location on your OBIEE 12c server:

• E:\Oracle\OBIEE\Oracle_Home\oracle_common\modules

Restart the services and away you go:

OBIEE 12c and HFM

I've worked with several of the clever people from the Oracle CEAL team and I highly recommend their blog: https://blogs.oracle.com/pa/ . It's full of valuable BI and EPM information.

Their latest post https://blogs.oracle.com/pa/entry/integrating_bi_12c_with_hfm shows us how to import HFM metadata into OBIEE 12c. I had some teething troubles configuring this on Windows (mainly due to the direction of the slashes in my path statements) so I thought I'd share my configuration for OBIEE and HFM on Windows.

I followed the instructions in the document Olivier provided but I got the following error when trying to import the HFM metadata:

"Could not connect to the data source. A more detailed error message has been written to the BI Administrator log file".

Don't believe the error message, there isn't any extra info in the admin log file so no hint as to where I had gone wrong. I think my problem was two fold - I'm on Windows and had used back-slashes in my pathing statements and I used the bi java host port number that was specified in the CEAL document (9610). The default port for the java host is 9510 so I used that.

Here is my working config:

E:\Oracle\OBIEE\Oracle_Home\bi\modules\oracle.bi.cam.obijh\setOBIJHEnv.cmd :

Add the following variables. This is a windows command script so you should be using back-slashes:

SET EPM_ORACLE_HOME=E:\Oracle\Middleware\EPMSystem11R1
SET EPM_ORACLE_INSTANCE=E:\Oracle\Middleware\user_projects\FDNHOST1
SET OBIJH_ARGS=%OBIJH_ARGS% -DEPM_ORACLE_HOME=%EPM_ORACLE_HOME%
SET OBIJH_ARGS=%OBIJH_ARGS% -DEPM_ORACLE_INSTANCE=%EPM_ORACLE_INSTANCE%
SET OBIJH_ARGS=%OBIJH_ARGS% -DHFM_ADM_TRACE=2

E:\Oracle\OBIEE\Oracle_Home\bi\modules\oracle.bi.cam.obijh\env\obijh.properties

Add the following variables. This time we use the forward-slash:

#Added for HFM connectivity

EPM_ORACLE_HOME=E:/Oracle/Middleware/EPMSystem11R1

EPM_ORACLE_INSTANCE=E:/Oracle/Middleware/user_projects/FDNHOST1

Find OBIJH_ARGS as indicated in the CEAL document and append the below tags to the end (without carriage return):

-DEPM_ORACLE_HOME=E:/Oracle/Middleware/EPMSystem11R1 

-DEPM_ORACLE_INSTANCE=E:/Oracle/Middleware/user_projects/FDNHOST1 

-DHFM_ADM_TRACE=2

The text has been wrapped in the image above. Just add the text at the end of your OBIJH_ARGS setting.

E:\Oracle\OBIEE\Oracle_Home\bi\bifoundation\javahost\config\loaders.xml

Add the following lines to the <Name>IntegrationServiceCall</Name> Classpath:

E:/Oracle/Middleware/EPMSystem11R1/common/hfm/11.1.2.0/lib/fm-adm-driver.jar$;

E:/Oracle/Middleware/EPMSystem11R1/common/hfm/11.1.2.0/lib/fm-web-objectmodel.jar;

E:/Oracle/Middleware/EPMSystem11R1/common/jlib/11.1.2.0/epm_j2se.jar;

E:/Oracle/Middleware/EPMSystem11R1/common/jlib/11.1.2.0/epm_hfm_web.jar

For the Admin Tool client, edit the following file:

E:\Oracle\OBIEE\OBIEE_Client\domains\bi\config\fmwconfig\biconfig\OBIS\NQSConfig.INI

JAVAHOST_HOSTNAME_OR_IP_ADDRESSES = "SERVERNAME:9510";  

Restart your OBIEE and you can now import your HFM data into OBIEE:

adm:thin:com.oracle.htm.HsvADMDriver:HFMCLUSTER:APPNAME

You can see your HFM application, use the arrow to push it into the repository view:

You should now see it available in the physical layer:

Save the repository and start building your reports!