How to Generate, Populate and Integrate an Essbase Cube into an OBIEE 12c Environment Using the Essbase Business Intelligence Acceleration Wizard

August 17, 2016

Author: Doug Ross, Performance Architects

One of the lesser known and (mostly) undocumented features of Oracle Business Intelligence Enterprise Edition (OBIEE) 12c is the Essbase Business Intelligence Acceleration Wizard.  This new component provides a very simple way to generate, populate, and integrate an Essbase cube into an OBIEE environment to boost query performance through aggregate redirection.  Utilizing the Essbase server that can be optionally installed with OBIEE, a wizard interface walks a developer through the steps of selecting the measures and dimension levels to aggregate and deploy.

A video showing an example of using the wizard is included at the end of this blog post.

For this example, a basic analysis is built using the Sample Application in OBIEE 12c.  The analysis shows “Revenue by Year.”

DR1

To ensure that the cache is not used when the query is executed, the DISABLE_CACHE_HIT variable is set to 1 on the Advanced tab.  The LOGLEVEL variable is set to 3 to capture details about the query execution in the session log.

DR2

The results are then displayed.

DR3

Examining the session log shows that the query is accessing the XML data files to return the results.

DR4

This then demonstrates how the Essbase BI Acceleration Wizard is utilized to generate an aggregate cube for query redirection.  Using the URL servername:port/cds/view, a login page is displayed. After entering the OBIEE credentials, the wizard’s main screen will display.   The CDS in the URL is intended to stand for “Cube Deployment Service.”

Begin the aggregate creation process by clicking on the “Launch Essbase BI Acceleration Wizard” link:

DR5

The wizard accesses the online RPD for the OBIEE environment.  The first step in the process is to select the “OBIEE Business Model” that will be used as the source for the cube.  Click on the “Populate Business Models” dropdown to show the list of business models.

DR6

Then, choose a business model from the list.

DR7

Next, select the connection information for an existing Essbase server or enter the name of a new application.

DR8

The next step in the wizard will present a list of all the available fact tables in the business model.  Choose the appropriate fact table and then select the measures that will be aggregated in the cube.

DR9

In this example, we aggregate the “Revenue” metric only.

DR10

Next, the list of associated dimensions for the metric(s) chosen are displayed.   Choose one or more dimensions to use in the aggregate and move them to the “Selected Dimensions” box.  In this example, we only select the “Time” dimension and then move on to the next step in the wizard.

DR11

The list of levels in the selected dimensions will be displayed.  From this screen, you can select which levels to aggregate.   Note the selected dimension cannot be a ragged hierarchy.  In this example, we only roll up at the year level.

DR12

The final step of the wizard provides some advanced options, but they are not well-documented at this time.

DR13

Leave the “Run in Background” option checked and click on the “Deploy” button to begin the process of creating the aggregate cube and wiring it into the online RPD.

Returning to the main screen of the BI Acceleration Wizard allows for monitoring the progress of the cube deployment.  Click on the “Actions” dropdown on the left side and select “Refresh.”

DR15

The new application is displayed in the list on the left.  Click on the application name to show the details of the cube deployment.  The four steps in the process will be displayed and can be expanded to see the details of the execution.   The steps are: “Clear RPD” (if there was an existing application); “Deploy Cube;” “Load Metadata;” and “Load Data.”

DR16

Once the cube is deployed successfully, we then return to the analysis we built earlier and re-execute it.  The results should be exactly the same.

DR17

Now, we will examine the new session log generated for this query and see that the query has been redirected to access the new Essbase cube.

DR18

Finally, we can open the RPD in online mode to see what has been modified by the Essbase cube deployment service.  On the “Physical Layer,” the new Essbase cube has been configured in the co-located Essbase server.

DR19

In the “Business Model and Mapping Layer,” a new “Logical Table Source” or “LTS” has been added to the logical fact table.  This LTS points to the Essbase cube and will have the mapping for the “Revenue” metric to the cube measure column.

DR20

In summary, the Essbase BI Acceleration Wizard offers a very simple interface to improve query performance using Essbase cubes with minimal configuration effort.

The entire process of generating the cube can be seen in the video below:

 


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Leave a Reply