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
- 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.
- Once successfully connected, the next step will be selection of application to import in Physical layer of OBIEE Repository (RPD).
Dimensions, Alias and UDA
- By default, Account dimension is treated as Measures hierarchy in OBIEE.
- To switch from member names to alias, you can go to cube table options as shown below, appears after double clicking on cube name.
- Also, below highlighted is an option if you wish to use UDA as columns.
- 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.
- Once imported, drag the application to Business Model and Mapping (BMM) Layer.
- You may need to manually associate member attribute to corresponding dimensions and levels during the import process. (required until OBIEE 22.214.171.124).
- Drag the cube from BMM layer to Presentation layer.
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
A more detailed analysis can be built for lower level as below which is at Business and Product level, with prompt as required:
Benefits and limitations
- 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.
- 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