[OBIEE11g] Essbase As A Source For OBIEE11g

Essbase as a source for OBIEE11g

Objective: Import the Essbase Cube Data from Essbase Administration services console (EAS) to OBIEE and generate the dashboards with the presumption that the reader having basic understanding on Hyperion and OBIEE Tools

1. Overview of Essbase and OBIEE Integration

2. Create a Essbase Datasource into Repository

3. Create Business Model and Presentation for the Essbase Cubes

4. Generate Reports in OBIEE Analytics Tool 



Overview of Essbase and OBIEE Integration

Hyperion Essbase is MOLAP where as OBIEE is ROLAP- MOLAP as we store the cube data, analysis is much faster than ROLAP.

Many users work with Essbase data using as their interface an add-in for Microsoft Excel. Hyperion places the entire cube in front of you in excel and you can drill down, add or remove dimensions, apply filters, pivot data, slice and dice and much more and also the write back capability which makes it the first choice for budgeting and forecasting applications. With Essbase as a middle layer, OBIEE is very easy for End users to generate reports and design dashboards in less span of time.

From OBIEE 10g, Essbase can also be one of the Datasources for OBIEE Repository as well as Oracle BI Repository can be a Datasources for Newly Emerging Essbase Studio Tool(11.x).

Supported Versions of Essbase Cubes: 9.x, 11.x

When Essbase is used as an OBIEE data source, you have the capability to combine Essbase sources with other OBIEE Server supported data sources, and present relevant information to your users in one place, from multiple sources. OBIEE is a relational Query tool so it makes Essbase behave that way too.

 Pre-Requisites:

 The Essbase Metadata import and its related queries require installation of the Essbase Client API on BI Server for OBIEE 10g.However I observed that for 11g it’s not mandatory. Let’s try to import Sample. Basic cube which comes with Essbase 11.x installation into OBIEE Repository.

Integration Architecture:

Also Ensure that all the Hyperion services are up and running fine along with OBIEE services.

Steps that needs to be followed:

1.  Importing Multidimensional Essbase Datasource into OBIEE Repository

Click on BI Administration to create a new rpd in repository

Start -> All Programs ->Oracle Business Intelligence-> BI Administration

Before precede further, need to define some user-defined options.

Since Essbase Cube Gen1 names contains only Dimension names, we can skip that levels before importing the cube as follows

Go to Tools-> Options and select the required checkbox

Click on file-> New Repository to create a repository

Provide the user-defined name and repository password

Path: C:\OBIEE\instances\instance4\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository

It’s the default location where OBIEE Repository will store. Click Next

Select the connection type as “Essbase”, Provide Valid Server name, Username and password.

In this scenario, it’s my local system hence the Essbase Server is “local host”.

Best practice is using System name or IP address while Essbase Installation to make the Server name unique.

Once you click on Next, It will displays the Essbase Cubes under the server

Click on the Sample Application and click on “Imported Selected” button on the middle pane window to import the Essbase Cube to OBIEE Repository as following image

2.  Changes that will perform in Physical Layer

Once the cube got imported, Dimensions and facts appears as follows in the physical layer

To display all the Measures or facts in Essbase Cube.

Right Click Database [Basic] of Sample Application -> “Convert Measure dimension to flat measures”;

Once more advantage here is no need to create again star schema, OBIEE will brings all join relationships along with tables (This is where I impressed and of course lot of re-work got reduced for developersJ)

As you can see from the screenshot below, the Market physical dimension has three UDAs associated with it; Major Market, Small Market and New Market.

Another good point, Even the hierarchy is ragged or Skipped, Essbase+OBIEE can handle it.

We can also observed here it will display the Introdate, Caffeinated attribute dimensions as hierarchies

3. Now Drag the Sample Cube from Physical to BMM layer

Double Click on Essbase Cube Symbol and uncheck the disabled button to display in Subject area of OBIEE Analytics tool

4. Create a new Subject area in Presentation layer by dragging the logical cube from BMM to presentation layer

 Now click on “Check global Consistency” for verification of warnings or errors

If there are no errors, it will display as rpd building is successful as follow

5. Before Upload Ensure that OBIEE Services are up and running fine. This can be verified in Enterprise Manager link

Link: http://ServerName:7001/em/

Logon with valid credentials

Here is the path where we can observe the OBIEE services status

Now upload the repository to BI Server

From the left window, Click on 

Farm_bifoundation_domain->Business Intelligence-> Coreapplication

In the middle window, Click on “Deployment tab”.

Click on “Lock and Edit Configuration”

Then it displays the message as follows

Now click on “Browse” button to upload the repository

Path: 
        C:\OBIEE\instances\instance4\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository

Provide the repository password and click on “Activate changes”

Now click on “Restart to apply recent changes” to upload rpd

For any rpd changes, we need to restart the services to make rpd from offline to online mode

Automatically it will take you to the “Overview” tab and click on “Restart” button and say “Yes”

It will take for a while for services to be up and working fine

Once the services started, Open the Analytics link to generate the reports

Link: http://servername:7001/analytics/

Give the valid credentials  

Click on New-> Analysis

Select the SA as “Sample”

With this we can create reports and dashboards