OBIEE - Essbase

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics)

1 - About

Since OBIEE 10.1.3.3.2, Hyperion Essbase is supported as a physical data source.

At the time of this release, Hyperion Essbase version 7.1.6 and later can be used as a data source with Oracle BI EE 10.1.3.3.2. For the most current information regarding supported versions, see the System Requirements and Supported Platforms for Oracle Business Intelligence Suite Enterprise Edition.

See the System Requirements and Supported Platforms for Oracle Business Intelligence Suite Enterprise Edition document for the supported versions of the Essbase Client for connectivity to the Oracle BI Server.

Advertising

3 - Essbase Client Libraries

Oracle BI Server connectivity to Essbase is through the Essbase client libraries. The client libraries must be installed on the Oracle BI Server.

3.1 - BI Server Configuration for UNIX and Linux Platforms

Perform the following configuration steps to access the Essbase client libraries through the BI server on UNIX and Linux Platforms.

Example for Linux: Oracle BI 32 bit mode. For others platform, refer to this paragraph 9.1.2.2 BI Server Configuration for UNIX and Linux Platforms

  • Define the arborpath environement variable
Define ARBORPATH = <Essbase Client installation folder>

For example:

ARBORPATH=/export/home/Hyperion/AnalyticServicesClient
export ARBORPATH
  • Add the Essbase Client Libraries folder to LD_LIBRARY_PATH:
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:<Essbase Client Libraries folder>

For example:

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ARBORPATH/bin
export LD_LIBRARY_PATH

4 - Import Process

  • Essbase cube structures are imported via the “Import from Multi-dimensional” option in the Oracle BI Administration tool.
  • In the “Import from Multi-Dimensional” dialog, select Essbase as the provider type. Input the server and authentication information.
  • After selecting “OK”, the list of Applications and Cubes available on the Essbase Server will be presented
  • Select the Applications or Cubes to import.

Note that Applications will be mapped as Physical Catalogs.

  • Once the physical cubes are imported, they can be dragged to the Business Model and Mapping Layer to automatically create a Business Model that includes associated dimensions. The Business Model can now be customized to take advantage of Oracle BI Server capabilities such as aggregate and fragment navigation.
Advertising

5 - Guidelines and Limitations

This section describes some unique aspects to Essbase connectivity that the administrator or end user should be aware of when configuring and using Essbase as a data source.

5.1 - Measures

The default Aggregation Rule for imported measures will be set to Aggr_External. It is recommended that you change this setting to an explicit Aggregation Rule if the rule is known for the given measure and a corresponding rule is available for the BI Server. This will allow the BI Server to compute the aggregate when necessary.

5.2 - Measure hierarchies

Essbase supports the concept of . This enables end users to drill from a measure to components that make up the measure, for example, profit drills to revenue and costs. At this time Oracle BI EE does not support measure hierarchies. Oracle BI EE will import measures as a flat list of children to the cube itself independent of the measure hierarchy.

5.3 - Unique Member Names

When member names (including aliases) are unique in a given hierarchy, the Oracle BI Server can take advantage of specific MDX syntax to optimize performance. To enable this capability, select the “Use unqualified member name for better performance” box in the Hierarchy dialog.

The import process will not be able to identify that member names are unique for a given hierarchy, so it is the responsibility of the Administrator to confirm uniqueness. Note that query errors may result if a hierarchy is specified as having unique members when it does not.

5.4 - Time Series Functions

Oracle BI Server Time Series functions, "Ago" and "ToDate", are supported for use with Essbase.

5.5 - Evaluate Function

In Oracle BI EE 10.1.3.3, Evaluate functions were introduced to provide the ability to leverage unique source-specific functions. This capability was limited to relational sources. In 10.1.3.3.2, you now can leverage Evaluate functions for Essbase. Support for Evaluate does not extend across all multi-dimensional sources at this time.

Consider the following examples using the EVALUATE_AGGREGATE and EVALUATE functions. Note that expressions are applied to columns in the Logical Table Source that refers to the physical cube.

Advertising

5.5.1 - EVALUATE_AGGREGATE

EVALUATE_AGGREGATE is used to implement custom aggregations. For example, you may want to compare overall regional profit to profits for the top three products in the region. A new measure can be defined to represent the profits for top three products resulting in the logical SQL statement:

SELECT Region, Profit, EVALUATE_AGGREGATE('SUM(TopCount(%1.members, 3, %2), %3)', 
Products, Profit, Profit) Top_3_prod_Profit FROM SampleBasic

The Oracle BI Server will generate the following expression for the custom aggregation:

member [Measures].[MS1] as 'SUM(Topcount([Product].Generations(6).members,3,[Measures].[Profit]),[Measures].[Profit])'

5.5.2 - EVALUATE

Similarly, use the EVALUATE function to implement scalar functions that are computed post-aggregation. EVALUATE will change the grain of the query, if its definition makes explicit references to dimensions (or attributes) that are not in the query.

For example, if you would like to see the Profits for the top five products ranked by Sales sold in a Region, after creating the applicable measure, the resulting Logical SQL statement is as follows

SELECT Region, EVALUATE('TopCount(%1.members, 5, %2)' AS VARCHAR(20), Products, Sales), Profits FROM SampleBasic

The Oracle BI Server will generate the following expression to retrieve the top five products:

SET [Evaluate0]  AS '{Topcount([Product].Generations(6).members,5,[Measures].[Sales]) }'

5.6 - User-Defined Attributes

Essbase supports the concept of user-defined attributes (UDAs). A UDA is essentially any arbitrary textual string that can be associated with any member from a dimension. A member can have multiple strings associated to it. Oracle BI EE will suppress UDAs upon import.

Users can still query using UDAs by leveraging the EVALUATE function. Consider the following example where “Major Market” is a UDA:

After creating the applicable column, the resulting logical SQL statement is as follows:

SELECT EVALUATE('FILTER( %1.Members, isUDA([%2.Dimension, "Major Market"))', State, State), SalesFrom SampleBasic

The Oracle BI Server will generate the following expression for the custom column:

SET [Evaluate0]  AS '{FILTER([Customer].Generations(3).members,isUDA([Customer].Generations(3).Dimension,"Major Market")) }'

5.7 - Substitution Variables

Essbase substitution variables are automatically retrieved and populated into corresponding BI Server dynamic system variables.

Depending on the scope of the Essbase variable, the naming convention for the BI Server variable is as described below.

  • Server instance scope: <server name>:<var name>
  • Application scope: <server name>:<app name>:<var name>
  • Cube scope: <server name>:<app name>:<cube name>:<var name>

The refresh interval should be set appropriately to reflect anticipated update cycles for Essbase variables.

5.8 - Attribute Dimensions

Member attributes are not automatically associated to corresponding dimensions and levels during the import process. To manually create the association, map the member attribute to the appropriate Logical Table in the Business Model Layer Include the attribute in the appropriate Presentation Table in the Presentation Layer

5.9 - Unbalanced Hierarchies

Oracle BI EE 10.1.3.3.2 supports unbalanced hierarchies for Essbase sources. The following is an illustration of how unbalanced hierarchies will be represented to end users in the Answers interface.

Given a selection of Country, Region when we add “State” to the query. We know that for “Central America,” Regions do not have States.

The resulting Answers request is shown in the following image:

Note that for the unbalanced branch, for columns below the leaf level, nulls will be displayed.

6 - Support

6.1 - A descendent member of the expected level is returned

[nQSError: 43113] Message returned from OBIS.
[nQSError: 43119] Query Failed: A descendent member of the expected level is returned. 
Please check member selection in the report. (HY000)

Check that you don't have a hierarchy column side by side with a attribute column

7 - Documentation / Reference