Image
Essbase-to-OBIEE-integration

Integrating ESSBASE to OBIEE

Santosh Jujgar
June 22, 2020

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. Learn more: Oracle EPM cloud services

Step to Connecting Essbase from OBIEE

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

Dimensions, Alias and UDA

  1. By default, Account dimension is treated as Measures hierarchy in OBIEE. syntax_image
  2. To switch from member names to alias, you can go to cube table options as shown below, appears after double clicking on cube name. syntax_image
  3. Also, below highlighted is an option if you wish to use UDA as columns. syntax_image
  4. Time dimension gets imported along with measures, as per the structure available in Essbase application
    Selection of Hierarchy types

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

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

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

Sample Report Snapshot in OBIEE with ESSBASE source syntax_image syntax_image

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

syntax_image

Benefits and limitations

Benefits:

  • The Essbase database is well readable by OBIEE so that existing Essbase application 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 Essbase outline.

Limitations:

  • Performance may degrade if data to be analyzed at 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 to limit one hierarchy in 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 structure aligned with the reporting calculations.
  • Not suitable for financial reporting. Read relevant blog here: Five Points to Overall OBIEE Performance Improvement

About the Author

Santosh Jujgar, EPM Technical Analyst, Jade Global

Santosh Jujgar develops, analyzes, and supports Jade Global’s Oracle EPM clients. He has 9+ years’ experience in technical support, analysis, and implementations. He brings his deep expertise in Oracle EPM(Hyperion) on-premise and cloud services, Oracle EPBCS/ PBCS solutions for our clients.

Add New Comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.