EPM Workspace integration with OBIEE

The recent release of OBIEE 11.1.1.7 finally added in the ability to provide single sign-on integration between EPM Workspace and OBIEE, yes the functionality has been available for a while if integrating Workspace with 10.1.3.4.2+ though this was a complete hack and required installing Shared Services 11.1.1.4 plus the biggest drawback was that it was not available for OBIEE 11g.

Now I know there is also the option of installing a cut down version of Workspace when selecting the Essbase option with the OBIEE 11.1.1.7 installer but this is not EPM as you know it and from initial testing it is far from the finished product, interesting to see the EPM products operating in fusion mode and not Shared Services security mode but not pleasant if you are used to provisioning in Shared Services, definitely one to watch for the future though.

Anyway today I am going to go through the process of setting up the SSO integration between Workspace 11.1.2.2 and OBIEE 11.1.1.7, it is possible to integrate with previous versions of workspace with the process being the same for 11.1.2.1 and only slightly different for versions prior to that, though I don't it has been really tested on those versions.

If you are expecting to run an installer then sit back and relax you will be disappointed as the process is manual and is still a bit of a hack but at least you can keep with the same version of Shared Services, the process does involve updates to the Shared Services registry and anybody that has experience with the registry will probably be well aware that bad things can happen if incorrect entries exist.

Update 27/05/13 : If you plan to integrate with EPM 11.1.2.3 then make sure you carry out the steps of copying the two jar files which is explained in the following blog. 

Update 09/08/13: If you are running OBIEE with SQL Server then there is currently an issue which you can read about here.

There are a few prerequisites that need to be met before carrying out the integration.

• Supported versions of OBIEE and EPM Workspace are up and running, so this means OBIEE 11.1.1.7+ and I am going to say Workspace 11.1.2.1+ as it is time to upgrade if you are running earlier versions and are considering integrating.

• OBIEE and EPM including the registry relational database can be accessed from each instance.
 

• OBIEE and EPM are configured to use the use the same identify store such as Microsoft active directory which I will be using in my example, native security is not really a viable option.

The first step is 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.

To do this there is a utility available on the OBIEE instance in <BI_ORACLE_HOME>\common\CSS\11.1.2.0

The utility is contained in the file regSyncUtil_OBIEE_TO_EPM.zip which should be extracted to the same location.

In order for the utility to be able to extract the required information from the EPM registry it needs the database connection details and it does this by using the reg.properties file from the EPM instance.

reg.properties should be copied to the src folder of the Registry Sync utility.

Before the utility can be run a couple of variables will require updating.

ORACLE_HOME and ORACLE_INSTANCE should be updated to match the paths for the OBIEE instance.

Now the utility can be run.

If a report is run on the OBIEE version of the EPM registry before and after running the utility you will see the CSSHandlerKey2 property value is updated.

Basically the utility reads CSSHandlerKey2 from the EPM HSS registry and then writes a new encrypted key based on the EPM value to the OBIEE EPM registry so now there is a common link between them when handling SSO tokens.

An additional step is also required for the functionality to work and that is to remove the applicationID property from the OBIEE EPM registry

This can be achieved by running the epmsys_registry utility which if you have worked in the EPM infrastructure world you will know well.

The following command line should be run:

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

Running a registry report again confirms the property has successfully been removed.

Next step is to make sure the interop-sdk.jar file is the same version between the EPM and OBIEE environments.

The file should be copied from <MIDDLEWARE_HOME>\EPMSystem11R1\common\SharedServices\11.1.2.0\lib to <ORACLE_BI1>\common\SharedServices\11.1.2.0\lib

So you are thinking that is it then, don’t be silly life would be boring if it was.

The EPM registry has not yet been configured with any OBIEE information such as registering it with workspace.

It would be nice if you could run the EPM system configurator and select “Setup connection to Oracle BI and Publisher”

Go on give it a try and see :)

This option should only be used when integrating using the old method to OBIEE 10g though I am sure it will be updated in future releases.

The way to correctly register at the moment is to the HSSregistration utility located on the OBIEE instance.

Before running the utility the following variables will need to be set within the file: ORACLE_BI_HOME, ORACLE_HOME, JAVA_HOME

Also a file called registration.properties in the config directory requires updating with configuration information.

I don’t feel the properties really need any explanation except HIT (Hyperion Installation Technology) is the JDBC information for the EPM registry which can be found in reg.properties.

The HSSRegistration utility takes the following arguments:

• register — Registers Oracle Business Intelligence with both the Hyperion Installation and Hyperion Shared Services.


• view — Lists the Oracle Business Intelligence registration information from both the Hyperion Installation and Hyperion Shared Services.
 

• clean — Removes Oracle Business Intelligence registration information from both the Hyperion Installation and Hyperion Shared Services.

Running the utility with the view argument confirms that no OBIEE information has been registered with EPM Shared Services yet.

Running the utility with the register argument adds the OBIEE configuration information to the EPM registry.

Firing off another registry report confirms the OBIEE information has been added.

After restarting Foundation Services and logging into Shared Services new OBIEE provisioning roles are now available.

The next step is to run the EPM configurator and configure the web server again as the proxy information for OBIEE needs to be added.

Once completed you should be able to open up the http server configuration file and view the added proxy information, the configurator does not add proxy configuration for the BI office client, download link and composer so if these are required they will need to be manually added.

That completes the configuration on the EPM side and now for the SSO authentication configuration,

Within the fusion middleware control SSO should be enabled and the SSO provider set to custom, selecting custom stops the authentication section in the instance configuration xml file from being overwritten which is important as the Hyperion authentication information is now going to be manually added.

The instanceconfig,xml file is edited and “HyperionCSS” added to the <EnabledSchemas> setting.

Finally <MIDDLEWARE_HOME>\user_projects\domains\domain_name\config\fmwconfig\biinstances\coreapplication\bridgeconfig.properties requires editing to enable Hyperion authentication

The following properties should be added to the file:

oracle.bi.presentation.hyperioncssauthenticatorfilter.Enabled=true
oracle.bi.presentation.hyperioncssauthenticatorfilter.SetAuthSchema=true

Now the system can be restarted and tested.

Once a user had been provisioned with the roles in Shared Services then the OBIEE menu options should be available in workspace.

Selecting one of the menu options should then provide seamless integration with no additional login required and from then OBIEE security will take over.