Art of BI: Configure OBI 11g for Essbase Metadata Import Integration OOTB

By | In 11g, Art of BI, OBIEE | March 29th, 2011

With the aim to provide a fabulous integration between Oracle BI/EPM, i.e.: OBIEE and Oracle Essbase / HFM , etc Oracle has made some wonderful strides.  OBIEE 11g is a testament to that integration’s roadmap.  This post is all about just getting OBIEE 11g  configured for interaction with Oracle Hyperion Essbase as close to right out of the box as possible.  Although the Essbase integration doesn’t completely work directly OOTB, Oracle has at least set the stage for the infrastructure.  What do I mean by that? Let me explain.

OOTB the Oracle BI 11g instance already installs/contains the following items for integration with Oracle Essbase 11.1.2:

  1. Essbase JAPI files
  2. Essbase C API files

What is missing for this OOTB Oracle Essbase integration is:

  1. Configuration of PATH variables pointing to the location of the Essbase API files
  2. Configuration of the Administration Tool to know where to point for the Essbase API files
  3. Oracle BI Server (now managed by OPMN) to know where to point to the Essbase API files
    • Actually this is done OOTB but one will want to confirm the location is indeed set propertly
    • This has to do with the machine where the OBI Server itself is installed and is not an issue if just running the OBIEE 11g Client Tools

The Essbase API files are required for both the Admin Tool and the Oracle BI Server are installed during the default installation(s).  These have to be on both machines, period!   The location for these files is, $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC and $ORACLE_HOME/products/Essbase/EssbaseServer respectively.  Take a look in those locations and understand what exists for this integration to take place.  Please note that these are the libraries, etc. for the latest 11.1.2 version.  To see the version of Essbase these libraries relate to, expand the /bin/ folder and right-click and library (DLL)  > Properties. Select the Version tab and look the “Other Version Information” box and click on “File Version” for the detail information and you should see 11.1.2 … bla, bla, bla.  If you are using a different version of Essbase there is a little bit more work involved to get the integration going.  Let me know if you need some assistance and I’ll see what info I can pass along.  Anyways…

Full Configuration on Windows

This will quickly outline the steps for a OBI 11g Windows Server configuration so just adjust slight when your OBI Server is on Linux.

The Oracle BI Server uses the Essbase client libraries to connect to Essbase data sources. You must ensure that the Essbase client libraries are installed on the computer running the Oracle BI Server before you can set up a connection to or import metadata from Essbase data sources. You also need to ensure that the Essbase client libraries are installed on any computer where you want to run the Administration Tool or the nqcmd utility. See “System Requirements and Certification” for information about supported versions of the Essbase Client for use with Oracle Business Intelligence.

The OBI 11gR1 install already includes these files in this main directory, $ORACLE_HOMEclientsepmEssbaseEssbaseRTC

After you verify that the Essbase client libraries are installed on the appropriate computers, you must ensure that the PATH environment variable on each computer includes the location of the Essbase client driver (for example, EPM_ORACLE_HOME/products/Essbase/EssbaseClient). See “Configuring Essbase Data Sources on Linux” for information about setting the PATH variable on Linux.

[simage=516,400,y,center]

In addition, you also need to ensure that an additional environment variable is set appropriately for each computer (either ESSBASEPATH or ARBORPATH, depending on your client version). For more information, see Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide (or the equivalent title for your client version).

[simage=515,400,y,center]

Updating Essbase Information in opmn.xml

To enable connectivity, on each computer running the Oracle BI Server, you need to verify that information in opmn.xml is correct for your Essbase client version and installed location.

To include the location of the Essbase client libraries in opmn.xml:

1.       Open opmn.xml for editing. You can find opmn.xml at: ORACLE_INSTANCE/config/OPMN/opmn/opmn.xml

2.       Locate the ias-component tag for the Oracle BI Server process. For example: <ias-component inherit-environment=”true”>

3.       Under the Oracle BI Server ias-component tag, locate the line that identifies the variable ESSBASEPATH. For example: <variable id=”EssbasePath” value=”$ORACLE_HOME/products/Essbase/EssbaseServer” />

4.       If necessary, update the value to point to the appropriate location for your installed client libraries. Change the variable name to ARBORPATH if necessary, depending on your client version.

[simage=517,400,y,center]

Per the above screenshot, the default is already setup and pointing to the correct location.

5.       Locate the line that identifies the variable PATH. For example:

[sourcecode language=”xml”]

[/sourcecode]

6.       Locate the portion of the entry that shows the location of the Essbase client libraries (shown in bold in the preceding example), and update the location if appropriate for your installation. Change the variable used in the path from ESSBASEPATH to ARBORPATH if necessary, depending on your client version.
[simage=519,320,y,center]

Make sure that you update the PATH variable as you can see partially in the above screenshot.

7.       Save and close the file.

8.       Restart OPMN.

9.       Repeat these steps on each computer that runs the Oracle BI Server process. If you are running multiple Oracle BI Server instances on the same computer, be sure to update the ias-component tag appropriately for each instance in opmn.xml (for example, ias-component id=”coreapplication_obis1″, ias-component id=”coreapplication_obis2″, and so on).

Test to see if Admin Tool Allows Essbase Metadata Import

Aafter setting the env vars, updating OPMN.xml [not really need in most basic installations], and restarting opmn does the Admin Tool allow you to import Essbase Metadata?

Of course, it doesn’t work, the OPMN piece is for the OBI Server itself.  With the exception of the environment variables (ESSBASEPATH or ARBORPATH) and the LD_LIBRARY_PATH if using linux as your OBI Server OS, OBI Server communication with Essbase 11.1.2 is ready to go out of the box.  So, keep reading.

Adding Essbase Information to bi-init.cmd

On Windows systems, to enable connectivity, you need to update bi-init.cmd on each computer running the Administration Tool.

See “Configuring Essbase Data Sources on Linux” for information about setting up the environment on Linux systems.

To add Essbase environment variables to bi-init.cmd:

1.       Open the bi-init.cmd file for editing. You can find bi-init.cmd at: ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup

2.       Add a line setting the ESSBASEPATH or ARBORPATH environment variable, as appropriate for your version and installed location of the Essbase client libraries. For example:

set ORACLE_INSTANCE=C:mw_homeinstancesinstance1 set ORACLE_BI_APPLICATION=coreapplication set ESSBASEPATH=C:productsEssbaseEssbaseClient call %ORACLE_INSTANCE%bifoundationOracleBIApplication%ORACLE_BI_APPLICATION%setupuser.cmd

3.       Locate the line that sets the PATH environment variable, and add the location of the Essbase client binaries. For example:

set PATH=%ORACLE_HOME%bifoundationserverbin;%ORACLE_HOME%bifoundationwebbin;%ORACLE_HOME%bin;%ESSSBASEPATH%bin;%PATH%

4.       Save and close the file.

Test the Admin Tool and Presto

Now, open the Administration Tool and attempt to import Essbase Metadata.  Be sure that you have closed and re-opened the Administration Tool using the Start > All Programs … method of launching the OBI Admin Tool as I describe in this post to ensure that the BI-init.cmd changes are picked up during the launch of the tool.

[simage=520,400,y,center]

Conclusion

No one ever set setting up the integration with OBI 11g and Essbase was easy, but hopefully this post allowed you to see that it is actually fairly straightforward.  Please post any comments, questions, or issues you may be experiencing.  I would be interested to know how you and your team are using this integration in a large scaled-out environment as well.

References

http://download.oracle.com/docs/cd/E14571_01/bi.1111/e10540/datasource.htm#CHDEDBCE

http://download.oracle.com/docs/cd/E14571_01/bi.1111/e10540/deploy_rpd.htm#CHDEGHBJ

Contact Us
Christian Screen
Christian is an innovator in analytics and data warehousing design, best practices, and delivery. With more than fifteenyears of decision support and data warehousing with key experiences at Office Depot HQ, Sierra-Cedar, and Capgemini, he oversees the Oracle Analytics Practice which includes the technical development and delivery of Oracle BI collaboration software, data warehouse solutions, Oracle BI/EPM projects, and packaged analytics solutions at Datavail.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

10 thoughts on “Art of BI: Configure OBI 11g for Essbase Metadata Import Integration OOTB”
  1. Hi there – great post. You write that if I use version 9.3.3 of Essbase there are additional steps. Can you describe these?

    Regards, Jacob

    1. Jacob,

      It looks like in OBIEE 11.1.1.5 this is no longer an issue and the client that comes with it should be backwards compatible. I haven’t tested it yet so I may do so for a future blog post. The guidance is of course just to upgrade your Essbase version to the at least 11.1.1.3 if not 11.1.2.

      1. I have OBIEE 11.1.1.5 ans Essbase 11.1.1.3 installed. I’m unable to connect from OBIEE to essbase. I’m not able to get EssbaseRTC. Do I need install Oracle DB in my machine? Please help me how to connect OBIEE & Essabse.

      2. Dear All,

        Installing the proper essbase clients solved the problem, in which ‘the proper essbase clients’ means 11.1.2. Any other version results in error.

        Regards, Theo.

  2. Dear All,nnI have checked all prerequisities, but can’t get the admin tool to connect using essbase 11.1.1.4. The documentation states that the version should be compatible, but stil I get the same useless error message.nnIs there any way to debug so I know where the error is? Any logs, debug option when starting the admin tool et cetera?nnBtw, I am using OBIEE 11.1.1.5, and essbase clients 11.1.1.4 form the Edelivery installer.nnRegards,nnTheo.

  3. Hello,

    Nice Article Thank you. I have installed the OBIEE 11G – 11.1.1.5 ADMIN TOOL only client version installed on my windows 7 – 64 bit computer and when I am trying to connecte to the Essbase I get an error that says “CONNECTION FAILED” Any tips will be greatly appreciated.

    Thanks,
    Venky

    1. Venky,

      This is hard to say as there are several issues that could cause this error. If you’ve followed the instructions in this article then you should be good to go. There may possibily be an issue with the 64-bit version but it is hard to say for sure. Did you follow all steps outlined in this post?

      1. thanks for your quick response. Since I have installed only the Client version I do not have the OPMN.XML file.. I am also suspecting it is because of the 64 Bit.

      2. Venkya,
        I breezed over the fact that you had installed only the client tools. So, if you are in offline mode attempting this it will not work. If you connect online to the RPD and the Essbase configuration has been successfully configured on the server then all should work correctly. Ultimately, the best practice is not to simply use the client tools on a workstation when working with Essbase, HFM, Oracle OLAP, MSAS or SAP data sources. For example, HFM makes use of the Java Host which only gets installed with the full Oracle BI installation. Take a look at my previous post with the PDF diagram of how you should set up your developer environment for OBI 11g, http://www.artofbi.com/index.php/2011/11/oracle-bi-11g-multiuser-development-workstation-configuration/