ESSBASE integration with OBIEE

Objective:

In this blog I will explain how to create & load data into a Cube in EAS console and import this Cube into OBIEE to build Dashboards on that Cube.

Note: Assuming that the reader having basic understanding on Hyperion and OBIEE Tools.

I’ll cover the following topics:

A.      Overview of ESSBASE and OBIEE integration

B.      Creation of a Cube in EAS Console

C.      Importing & building Subject Area this Cube in BI Administration tool RPD

D.     Generate Reports & Dashboard

A.    Overview of ESSBASE and OBIEE integration

ESSBASE

•  Multidimensional database storage system (MOLAP).
•   For complex business modelling.

OBIEE

•  Converts Multidimensional Online Analytical Processing (“MOLAP”) data into Relational Online Analytical Processing (“ROLAP”).
•  With ESSBASE as a middle layer, OBIEE is very easy for End users to generate reports and design dashboards in less span of time.

Overview of ESSBASE and OBIEE integration Architecture

Oracle ESSBASE as a Data Source for OBIEE:

• BI Server translates Logical SQL queries against RPD into MDX.
• Combine (federate) with other datasets (relational, flat file etc.).
• Include planning data with actuals in BI dashboard.
• Make use of ESSBASE calculations and forecasts for BI data, Oracle ESSBASE as a Data Source for OBIEE.

Supported Versions

ESSBASE: ESSBASE 9.3.3+, 11.1.1.3.500, 11.1.2.1+

OBIEE: OBIEE 11g

B.    Creation of a Cube in EAS Console:

• ESSBASE Database is also commonly referred to as Cube (as in ESSBASE Studio console)
• An ESSBASE database is a data repository that contains a multidimensional data storage array.
• Note that all databases are created under an application (ASO/BSO). You may have one or many databases under an application.
• Files that are related to databases are called artifacts (or objects).

 Understanding the Process for Defining and Building the ESSBASE Cube

Loading data is the process of adding data values to a database from a data source such as a spreadsheet or SQL database.

Now, we try to demonstrate the various ways to load data into ESSBASE cube.

a)      Free form data Loading

b)      Data Load Using Rule File

c)      Excel lock and send (Smart View)

Free form data loading:

If the source file format is 100% matching with the outline format, then only we will go for Free form data loading. Free form loading doesn’t require any rule file when source file contains at least one member from each dimension and data appears at last column. The free form loading can be done manually or we can create an import script to load data at scheduled time.

Data Load Using Rule File

Rules define operations that ESSBASE performs on data values or on dimensions and members when it processes a data source. Use rules to map data values to an ESSBASE database or to map dimensions and members to an ESSBASE outline. Rule file use for filtering data and then load in to metadata. Rule files use for mapping text file or source file with outline structure. The particular data will go to a particular field. First let’s clear all available data. Just right click on database and clear all data.

There are 3 types of methods of building dimensions

a)      Level References

b)      Generation References

c)      Parent Child References.

Excel lock and send (Smart View)

Lock & Send is used to insert new data/update the existing data into ESSBASE using Excel add-In/Smart view.

After you have drilled to an intersection that you would to update/load/change data in, you enter it directly in within Excel. Then perform a Lock operation using the add-in or Smart View. This tells ESSBASE that you would like to update data that is currently being shown on your spreadsheet. Then perform a Send operation. This will upload all of the data on your sheet back to the database.

After sending the data, it will automatically be unlocked. Then just retrieve the sheet to verify that the data you uploaded.

B.1. Creation of a Cube and loading Data:

1.      We need to connect with ESSBASE server.

EAS Console URL: http://<servername>/easconsole/console.html

After using this console, we need to provide credentials.

2.       We need to create an application, and then a Database (here using ESSBASE servers).

We can create either ASO or BSO applications (ASO: Aggregate Storage Option and BSO: Block Storage Option)

Here, I’m creating BSO application.

3

.      After creation of application, we need to create database.

In outline we can create different dimensions like Account, Entity, Period, Scenario and Product dimensions.

In Account dim we may have as many child’s as we want, like sales, profit, inventory or margin.

In Entity dim we have location like India, USA.

In scenario we have Actual, Budget and Forecast data.

4.      Each dimensions can have many child members.

This is how outline has database.

6.      After creation of outline database we need to connect with smart view in excel.

7.      After creating smart view in excel, we need to connect with ESSBASE.

  By clicking on panel, we get shared connections, In that we need to choose either creation of ESSBASE or creation of Hyperion planning. 

8.      We need to enter data.

B.    Importing & building Subject Area on this Cube in BI Administration tool RPD

1.      Now we need to connect with OBIEE to build RPD.

·         Click on BI Admin tool, open with online.

Note: 1.If you do offline, you have to deploy this RPD in EM Console.

            2. In 12 C, we don’t use deploy in EM as an option.

·         In that, click on file and import metadata.

·         Select connection type: ESSBASE.

Provide ESSBASE credentials.

ESSBASE server: <your ESSBASE servername>

We need to import ESSBASE database in OBIEE.

Create three layers using some sample name.

10. Finally, do check consistency to whole file, weather it was created correctly or not.

11. We can create reports based on given ESSBASE data.

 Created one sample report based on ESSBASE data.

12. If any Substitution variables of ESSBASE are there will automatically convert into Dynamic variables in OBIEE.

Things to Remember:

·         UDAs(User Defined Attributes) can be used as filters

·         ESSBASE outlines are imported as defined in ESSBASE to provide OLAP type drill down functionality.

·         ESSBASE cubes use hierarchal columns by default

·         Have your ACCOUNT OR MEASURE DIMENSION tagged as MEASURE DIMENSION and VALUE AS HIERARCHY type.

·         UNCHECK NULLABLE

·         Uncheck Cacheable.

·         Check HIERARCHY TYPE.