Integrating ESSBASE to OBIEE

OBIEE is Oracle Business Intelligence Enterprise Edition, which is a suite of OLAP products that provides enterprise reports, scorecards, dashboards, ad-hoc analysis, and OLAP analysis. Essbase is an Extended SpreadSheet Database is a multidimensional database from the Oracle EPM suite. Let us see how we can integrate the existing Essbase cube in OBIEE. Click to Learn more: Oracle EPM Cloud.

Steps to Connecting Essbase from OBIEE

1. To connect Essbase from OBIEE, open the OBIEE Administration tool and select “File” -> “Import Metadata” option and provide details of the Essbase server as below. Provide only the hostname of the Essbase server without the port number if the Essbase agent is running on the default port; otherwise server name should be appended with a colon and the specific port number.
   
2. Once successfully connected, the next step will be the selection of an application to import into the Physical layer of OBIEE Repository (RPD).

Dimensions, Alias, and UDA

1. By default, the Account dimension is treated as a Measures hierarchy in OBIEE.
2. To switch from member names to alias, you can go to the cube table options as shown below, which appears after double-clicking on the cube name.
3. Also, below highlighted is an option if you wish to use UDA as columns.

4. The time dimension gets imported along with measures, as per the structure available in the Essbase application
   Selection of Hierarchy types 

   Multiple hierarchy types can be selected based on the nature of the hierarchy. E.g., Value type can be selected if it is Parent-Child nature and the levels frequently change in the outline.

   

5. Once imported, drag the application to the Business Model and Mapping (BMM) Layer.
6. You may need to manually associate member attributes with corresponding dimensions and levels during the import process. (required until OBIEE 11.1.1.6).
7. Drag the cube from the BMM layer to the Presentation layer.

After this step, you are good to start with creating analysis and dashboards in OBIEE based on the subject area that appears in the presentation layer. An example is below in the dashboard where Revenue is analyzed at totals, with prompts at the top.

Sample Report Snapshot in OBIEE with ESSBASE source

A more detailed analysis can be built for lower levels as below, which is at the Business and Product level, with prompt as required:

Benefits and limitations

Benefits:

• The Essbase database is well readable by OBIEE, so that existing Essbase applications and metadata can be fully leveraged in building OBIEE analysis and dashboards, which are highly graphical and intuitive.
• Essbase data can be presented nicely using graphs automatically.
• As the Essbase application already exists, it helps in reducing reporting implementation time and additional ETL in between, unless there are major changes required in the Essbase outline.

Limitations:

• Performance may degrade if data is to be analyzed at the lowest granularity for multiple dimensions, in cases where there is large dimensionality and number of levels in the application, for e.g. very large ASO cube. We should limit one hierarchy to one row of the grid.
• Time dimension and calculations may require a lot of customization depending on the calculations to be done. For e.g., YoY%, variance analysis. Alternatively, Essbase outline needs to have a structure aligned with the reporting calculations.
• Not suitable for financial reporting. Read relevant blog here: Five Points to Overall OBIEE Performance Improvement