Wednesday 24 April 2013

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.

17 comments:

  1. First of all I wish to thank you for your great posts, there are always very helpful and a great source for new ideas.

    I am following your guide on integrating EPM 11.1.2.3 and BI 11.1.1.7 and I have the following reoccurring error when running HSSRegistration.

    ----
    !Starting Registering Product:BIEE-11.1.2.0, Filename:biee-11.1.2.0.Product, Pat
    h:config/biee-11.1.2.0.Product Exception in thread "main" java.lang.NoSuchFieldError: ESSBASE_STUDIO_PRODUCT
    at com.hyperion.interop.lib.helper.RegistryHelper.(RegistryHelper.java:62)
    at com.hyperion.interop.lib.helper.RegistryHelper.getInstance(RegistryHelper.java:94)
    at com.hyperion.interop.lib.helper.CMSClientHelper.(CMSClientHelper.java:58)
    at com.hyperion.interop.lib.helper.RegistrationHelper.(RegistrationHelper.java:46)
    at com.hyperion.interop.lib.helper.CMSHelperLocator.getRegistrationHelper(CMSHelperLocator.java43)
    at com.hyperion.interop.lib.CMSClient.register(CMSClient.java:659)
    at oracle.bi.server.hssregistration.HyperionSharedService.doRegisterProduct(HyperionSharedService.java:339)
    at oracle.bi.server.hssregistration.HyperionSharedService.register(HyperionSharedService.java:93)
    at oracle.bi.server.hssregistration.BIEERegisterer.execute(BIEERegisterer.java:60)
    at oracle.bi.server.hssregistration.BIEERegisterer.main(BIEERegisterer.java:138)
    ------

    I am trying to integrate the products for demo purposes on the same server. I have already used the same and separate Middleware Homes, and I have tried to add the missing ESSBASE_STUDIO_PRODUCT on the registry of OBI.

    Any help or guidance will be appreciated, as I am stuck and I cannot start my demo.

    ReplyDelete
  2. Hi,

    It looks like the Oracle documentation is missing some steps or is out of sync with jar files when using EPM 11.1.2.3.

    I will add a blog entry for this problem with a resolution.

    ReplyDelete
  3. Hi,Really good piece of information, and let me tell you, your site gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a lot once again.

    ReplyDelete
  4. Thanks for great post.

    I followed your steps and stuck quite in the beginning:

    When trying to call

    epmsys_registry removeproperty SHARED_SERVICES_PRODUCT/@applicationId

    I receive the error:

    An error occured during the operation. Detailed Message is - java.lang.NoClassDefFoundError: weblogic/jdbcx/sqlserver/SQLServerDataSource

    It seems that the sqlServer driver is not available.

    On EPM server the registry Report executes fine.

    Can you tell me how I can check if applicationId is contained in OBIEE registry.

    When I try to run a registry report I get an empty page.

    regards

    ReplyDelete
  5. Sorry for the delay, not had to time to write it up but it looks to be a bug when using SQL Server as the database type, have a read of : http://john-goodwin.blogspot.co.uk/2013/08/epm-workspace-integration-with-obiee.html

    ReplyDelete
  6. Hi John,



    Thanks a lot for the great posts. I confronted an issue, and appreciate your guidance.



    After I run the HssRegistration.bat utility, in Shared Service I was able to see OBIEE provisioning roles. Can I assume the register was successful? Then I configured the web server (IIS) in the EPM configuration utility. However, there is no BI related sites (analytics, xmlpserver etc) were created in IIS. As a result, they were not present in EPM workspace either.



    Regards,

    Steven

    ReplyDelete
  7. Hi Steven,

    I tested using IIS as the web server and it created the app pools and applications with the proxy configuration within IIS.

    ReplyDelete
  8. Hi John,

    Great post, it has been really useful for me. I havve one question: I managed to meka this work following your instructions in one environment, but in the following two, the SSO configuration fails. I get an EPMCSS-00301: Failed to authenticate user at bifoundation.log that I cannot get rid of. Do you have any ideas? I've tried all documents I found about this particular error in Oracle Support but still no luck.

    Best Regards!
    Andrew

    ReplyDelete
  9. Hi John,

    I have EPM installed on Linux, none of the utilities you mentioned exist. Is the integration possible on a linux installation, do you have any tips for this?

    Thanks
    Ben

    ReplyDelete
  10. It should be the same process for Linux, which utilities don't exist?

    ReplyDelete
  11. Hello John,

    I've followed your instructions and at the end when I go to workspace, over applications, the OBIEE logo doesn't appear, instead aI got this string:

    ${/analytics, workspace/resources/??/global.js, BIEE}

    Also when i select BI Catalog, BI Home or BI Interactive Dashboards I got these messages.

    Warning:
    An error occurred processing the result from the server.Description: Invalid or could not find module configuration 'biee.home'.

    Error:
    Required application module biee.home is not configured


    Did you have any idea what did I miss?

    Thanks, Lucas.

    ReplyDelete
  12. Hi John,

    THanks for your post. I tried all the steps. BIEE Application group is added in my Shared Services Console. But when I click on the Catalog under BIEE Application group I get an error saying ERROR: LCM is not supported for this application.

    Also, I do not see the applications link under Navigate in my workspace.

    Could you please throw some thoughts here?

    Thanks,
    RK

    ReplyDelete
  13. LCM is not supported for OBIEE so you will not be able to select it under application groups

    ReplyDelete
  14. Thank you for the very detailed information. I've tried a few times, and I've got everything working with EPM 11.1.2.3.500 (windows) and OBIEE 11.1.1.7 on SQL server, HSS registration completes without error, registry entries are there, I can provision users in shared services - but when I run the OHS config it doesn't show the BIEE logical web app, and when I log into workspace the BI Menu is not there. any ideas of what i may have missed?

    ReplyDelete
  15. hello, thank you for your tutorial.
    but i have a problem, i can't seem to find my reg.properties file


    i navigated to C:\Oracle\Middleware\user_projects\epmsystem1\config\foundation\11.1.2.0

    but there's no reg.properties file like the screenshot showed. there were only 3 files : .oracle.instance ,epm, and instance.version

    can you help me in this? is there something wrong with my oracle installation?

    already googled a while and still no luck.

    ReplyDelete
  16. Hi John!

    Is this possible to have a OBI report pointing to Essbase with SSO enabled? Client doesnt want to integrate the "portals", just access.
    Without SSO it works perfect with :USER and :PASSWORD, but whe we turn on SSO, stops working.
    Any idea?
    Followed all the steps up to before registering the project into HSS.

    Thanks,
    Rafael Marques

    ReplyDelete
  17. Hi John,
    Im trying to integrate 11.1.2.3.500 with 11.1.1.7.x At one of the steps during HSSConfiguration.bat register, it get the following error.

    I have followed all the other steps ( and checked ) my variables and jars. It writes to the HIT registry fine then errors out when trying to apply to the HSS side.

    !Starting Registering Product:BIEE-11.1.2.0, Filename:biee-11.1.2.0.Product, Pat
    h:config/biee-11.1.2.0.Product
    ! Done Registering Product with HSS

    ! Registering Application

    Product: BIEE-11.1.2.0, Application:Dashboards, Filename:dashboards.Instance, Pa
    th:config/dashboards.Instance, ProjectName:BIEE
    Exception in thread "main" java.lang.NoSuchMethodError: com.hyperion.css.CSSUser
    ProvisioningAPIIF.registerApplication(Ljava/util/Map;Lcom/hyperion/css/common/CS
    SPrincipalIF;Ljava/lang/String;Ljava/lang/String;Ljava/lang/String;)V
    at com.hyperion.interop.lib.helper.RegistryHelper.createApplication(Regi
    stryHelper.java:388)
    at com.hyperion.interop.lib.helper.RegistrationHelper.register(Registrat
    ionHelper.java:558)


    Any suggestions on where to follow ?

    ReplyDelete

Note: only a member of this blog may post a comment.