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:
- Essbase JAPI files
- Essbase C API files
What is missing for this OOTB Oracle Essbase integration is:
- Configuration of PATH variables pointing to the location of the Essbase API files
- Configuration of the Administration Tool to know where to point for the Essbase API files
- 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 theOBIEE 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_HOME\clients\epm\Essbase\EssbaseRTC
After you verify that the Essbase client libraries are installed on the appropriate computers, you must ensure that the
PATHenvironment 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.
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).
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.
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:1.<variable id="PATH" value="$ORACLE_HOME/bifoundation/server/bin$:$ORACLE_ HOME/bifoundation/web/bin$:$ORACLE_HOME/bifoundation/odbc/lib$:$ORACLE_ INSTANCE$:$ORACLE_HOME/lib$:$ESSBASEPATH/bin$:$PATH$:$/bin" append="true"/>
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 toARBORPATH if necessary, depending on your client version.
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_home\instances\instance1 set ORACLE_BI_APPLICATION=coreapplication set ESSBASEPATH=C:\products\Essbase\EssbaseClient call %ORACLE_INSTANCE%\bifoundation\OracleBIApplication\%ORACLE_BI_APPLICATION%\setup\user.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%\bifoundation\server\bin;%ORACLE_HOME%\bifoundation\web\bin;%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.
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.
