[OBIEE 11g] OBIEE Integration With Essbase

OBIEE Integration with Essbase

OBIEE Integration with Essbase

 Essbase Cube Import Process

Select “Start => Programs => Oracle Business Intelligence => Administration”.  Select “File => New” from the menu to create a new repository.  In the “New Repository” window enter a file name of “Essbase.rpd” and press “Save”.  A blank repository is displayed

Blank Repository

To import the Essbase cube, select “File => Import => from Multi-dimensional...”.  The “Import from Multi-dimensional” window appears.  Enter the following values: Provider Type: “Essbase”; Essbase Server: “<name of machine hosting the Essbase server>”; Username: “<Essbase user>”; Password: “<Essbase password>”:
 
Essbase Metadata Import

In the “Select Source” window expand the Essbase server node to list the available 

Essbase applications

Essbase Source Applications

In the present case, the set of default Essbase applications is listed.  Select the “Demo” application and then press “Import” followed by “Close” when the import has been completed.  Expand the nodes that appear in the Physical layer of the Administration Tool.

Repository Physical Layer

Database  Connections in Physical Layer

connection pool (automatically created by the import process)

Connection Pool in Physical Layer

 “Demo” is  the Essbase application name:

Catalog in Physical Layer

 “Basic” is the Essbase database name:

Cube Table in Physical Layer

Under the “Basic” node there are four hierarchies, for example, “Year”:

Hierarchy in Physical Layer

Note, that the three levels have been given generic names, such as “Gen1,Year”.  The levels can be renamed within the OBIEE Administration Tool or they can be renamed before import in the Hyperion Essbase Administration Services Console (edit the outline corresponding to the Essbase database, right-click on the relevant dimension, and select “Generations”).

Also under the “Basic” node there are ten physical cube columns, for example, “Total Expenses”:

Cube Column in Physical Layer

  

Business Model and Presentation Layers 

One of the key functions of the Administration Tool is its ability to create cubes (in the Business Model layer) from relational sources.  However, in this case we already have a cube in the Physical layer, so all we need to do to create a default Business Model is to drag the cube across to the Business Model layer:

Business Model Layer Added

The Administration Tool automatically maps the Physical layer hierarchies onto Business Model layer logical tables and dimensions; and it maps the physical cube columns onto logical columns within the “Basic” logical measure table.

To create the Presentation layer drag the “Basic” Business Model across to the left hand pane:

Presentation Layer Added

Save the repository (“File => Save”), check for global consistency (“Yes”), mark the Business Model available for queries (“Yes”).  Close the “Consistency Check Manager” window that appears (ignore the missing Administrator password warning).

Reports

The repository can now be used to generate reports using any reporting tool that can connect to the BI Server by setting Essbase as the default repository in the “NQConfig.INI” configuration file (add “Star = Essbase.rpd, DEFAULT;” in the “REPOSITORY” section, after commenting out the current default repository).  For example, here’s a simple tabular report created with BI Answers

BI Answers Report

OBIEE 11g Admin Tool Essbase Connectivity Issue


Depending on your system setup and Essbase version, there could be  two possibilities of missing variables: ESSBASEPATH and/or ARBORPATH. These need to be set before the Administration Tool starts. ( Essbase 11.1.2 uses ESSBASEPATH whereas version 9.3.3 requires ARBORPATH).

Solution:

Update the Environment Variables 

<mw_home>\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup\user.cmd
Add these lines after checking the paths apply in your environment, save and restart the Administration tool.
set ARBORPATH=C:\middleware\Oracle_BI1\clients\epm\Essbase\EssbaseRTC
set ESSBASEPATH=C:\middleware\Oracle_BI1\clients\epm\Essbase\EssbaseRTC
set PATH=%ARBORPATH%\bin;%PATH%