Establishing connection between OBIEE and Essbase

Due to the feature of drilling to the detail reports in OBIEE, it would be an excellent thought to navigate from Hyperion reports to OBIEE reports and then to drill into the details of Hyperion reports.

But despite providing all credentials establishing a connection with ESSBASE from OBIEE is a nightmare and can consume a considerable amount of time in resolving the issue:

Perform the below steps sequentially to establish the connection with ESSBASE:

• Install ESSBASE client both at SERVER and LOCAL machine
• Set variables with path values ‘ARBORPATH’ and ‘ESSBASEPATH’ in both SERVER and LOCAL machine

         ARBORPATH = C:\oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient

         EssbaseClient = C:\oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient

         Path = %PATH%;%ESSBASEPATH%\bin;%ARBORPATH%\bin

• Restart OBIEE Server
• Update bi_init.bat in Local Machine as below:

@echo off

rem overwrite JAVA_HOME

set JAVA_HOME=C:\OBIEE\jre

if exist "%JAVA_HOME%\bin\java.exe" goto:doneJAVAHOME

rem abort

echo *******************       

echo ****** ERROR ******

echo ******************* 

set /p prompt=[Invalid JAVA_HOME: %JAVA_HOME%] Enter return to abort

exit 100

:doneJAVAHOME

set ORACLE_HOME=C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orahome

set ORACLE_INSTANCE=C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orainst

set ORACLE_BI_APPLICATION=coreapplication

set ARBORPATH=C:\oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient

set ESSBASEPATH=C:\oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient

rem call %ORACLE_INSTANCE%\bifoundation\OracleBIApplication\%ORACLE_BI_APPLICATION%\setup\user.cmd

if NOT "%1" == "" set COMPONENT_NAME=%1

set PATH=%ORACLE_HOME%\bifoundation\server\bin;%ORACLE_HOME%\bifoundation\web\bin;%ORACLE_HOME%\bin;%JAVA_HOME%\bin;%windir%;%windir%\system32;%ESSSBASEPATH%\bin;%ARBORPATH%\bin;%PATH%

if "%2" == "" goto:end

%2

:end

• Open RPD in online mode, save it to local machine and close it
• Open RPD in offline mode (not online), mind it this is very important point due to which RPD does not connects to ESSBASE Database
• Establish the connection and import the required objects
• Save the RPD and Close it
• Upload it to the OBIEE Server
• Open the RPD in Online Mode, now imported database objects are ready to use