EPM Workspace integration with OBIEE issues when using SQL Server

I wasn’t expecting to write another update to the Workspace/OBIEE integration process but I recently saw a comment on my blog which highlighted issues when configuring with SQL Server, I must admit that I did neglect to test the configuration against SQL Server and I usually base my testing on Oracle.

Now I could have left it but it would just niggle in the back of my mind until I knew if the process does have problem with SQL Server and if it is possible to succeed with the integration.

The problem was that I didn’t have access to any OBIEE environments which were configured using SQL Server so that was the first task to complete, if only my blog postings took the time it does to write them up.

I am not going to go through the Workspace to OBIEE integration process again as it exactly the same as I covered here.

The first step of the integration process is to allow OBIEE to accept the EPM SSO token when integrating with Workspace which basically means that both the EPM registry and the OBIEE version of the registry need to keep a shared encryption key, this is achieved by configuring and running the register sync utility

I didn’t experience any issues and the configuration ran through successfully.

The next step is where I started to encounter problems.

The documented step is to run the epmsys_registry utility to remove the applicationID property from the OBIEE EPM registry but before removing the property I usually run the utility to produce a registry report. 

The utility runs but when the report opens it is blank.

The utility produces registry.log which is located in:
<BI_ORACLE_INSTANCE>\diagnostics\logs\registry

[2013-07-30T22:42:52.618+01:00] [EPMREG] [ERROR] [EPMREG-11043] [oracle.EPMREG.REG] [tid: 10] [ecid: 0000K0lY9ziFc5WFLzyGOA1Hy3DS000000,0] [SRC_CLASS: com.hyperion.hit.registry.Registry] [SRC_METHOD: lookupComponents] An exception when looking up components of type SYSTEM_TASKS_CONFIGURATION.
[2013-07-30T22:42:52.618+01:00] [EPMREG] [ERROR] [EPMREG-11099] [oracle.EPMREG.REG] [tid: 10] [ecid: 0000K0lY9ziFc5WFLzyGOA1Hy3DS000000,0] [SRC_CLASS: com.hyperion.hit.registry.Registry] [SRC_METHOD: lookupComponents] Exception occurred.[[
java.lang.NoClassDefFoundError: weblogic/jdbcx/sqlserver/SQLServerDataSource
    at com.hyperion.hit.registry.datasource.SQLServerSharedServicesDatasource.getInstance(SQLServerSharedServicesDatasource.java:83)
    at com.hyperion.hit.registry.datasource.SharedServicesDataSourceFactory.getDataSource(SharedServicesDataSourceFactory.java:101)
    at com.hyperion.hit.registry.datasource.SharedServicesDatabaseConnectionBuilder.getConnection(SharedServicesDatabaseConnectionBuilder.java:77)
    at com.hyperion.hit.registry.datasource.SharedServicesDatabaseConnectionBuilder.getConnection(SharedServicesDatabaseConnectionBuilder.java:28)
    at com.hyperion.hit.registry.RegistryUtils.createNewConnection(RegistryUtils.java:399)
    at com.hyperion.hit.registry.Registry.lookupComponents(Registry.java:1058)
    at com.hyperion.hit.registry.ui.Console.getComponents(Console.java:269)
    at com.hyperion.hit.registry.ui.Console.generateReport(Console.java:251)
    at com.hyperion.hit.registry.ui.Console.main(Console.java:148)
]]

So basically the epmsys_registry utility can’t find required java classes when it is being run.

The java classes the utility is looking for are the SQL server JDBC ones which can be found in:
<BI_ORACLE_HOME> \common\JDBC\Oracle\11.1.2.0\lib\ wlsqlserver.jar

 

If you edit the epmsys_registry script there is no reference to the jar file.

I updated the script to include the jar file in the classpath.

Time to run the utility again.

This time it must have got further because there is additional message but still the report produced is blank.

The log was full of error messages but didn’t really indicate clearly where the issue was.

[2013-07-30T22:52:12.956+01:00] [EPMREG] [ERROR] [] [oracle.EPMREG.REG] [tid: 10] [ecid: 0000K0l^IiaFc5WFLzyGOA1Hy3MC000000,0] [SRC_CLASS: com.hyperion.hit.registry.utils.JDBCTemplate] [SRC_METHOD: executeQueryOptimized] EPMREG-51067

[2013-07-30T22:52:12.956+01:00] [EPMREG] [ERROR] [] [oracle.EPMREG.REG] [tid: 10] [ecid: 0000K0l^IiaFc5WFLzyGOA1Hy3MC000000,0] [SRC_CLASS: com.hyperion.hit.registry.utils.JDBCTemplate] [SRC_METHOD: executeQueryOptimized] EPMREG-51068

I then took a look at the epmsys_registry script on an EPM environment and found a slight difference.

In the EPM environment the classpath contains epm_j2se.jar but in the OBIEE environment it references epm.jar

I commented out the line with epm.jar and added in epm_j2se.jar then ran the utility again.

Success the registry report was generated successfully so on to the next step to remove “applicationId” property from the OBIEE EPM registry.

<BI_ORACLE_INSTANCE>\config\foundation\11.1.2.0\epmsys_registry removeproperty  SHARED_SERVICES_PRODUCT/@applicationId

Once again successfully though you may get the following error which can be ignored:

An error occured during the operation. Detailed Message is - java.sql.SQLSyntaxErrorException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Invalid object name 'CONFIG_HISTORY'.

The error is due to a table which does not exist but this is only required to logs entries in an EPM configurations so need to worry.

Generating another registry report confirms the property has been removed.

I followed the rest of the steps which I documented in my previous blog and I did not encounter anymore issues.

So with a few additional updates it is certainly possible to integrate Workspace with OBIEE when SQL Server is the chosen database type.

After writing up this blog I noticed that Oracle have also just confirmed the issue which can be found in the following support document:

“OBIEE: Configuring SSO for Essbase and Hyperion Financial Management: Error Running epmsys_registry.sh removeproperty " java.lang.NoClassDefFoundError: weblogic/jdbcx/sqlserver/SQLServerDataSource" (Doc ID 1572650.1)”