Working with Essbase Data Sources

Learn how Essbase data is modeled by default in the Physical layer of the Oracle BI repository, and describes the tasks you can perform to model the data in different ways.

This section contains the following topics:

About Using Essbase Data Sources with Oracle BI

When you import metadata from Essbase data sources, the cube metadata is mapped to the Physical layer in a way that supports the Oracle BI logical model.

Metadata that applies to all members of the dimension, such as aliases, are modeled as dimension properties by default. Level-based properties such as outline sort or memnor information, are mapped as separate physical cube columns in the dimension.

The following physical column types are used for Essbase metadata:

  • Member Alias: Indicates an Alias column.

  • UDA: Indicates the column is a User Defined Attribute (UDA).

  • Outline Sort: Indicates the column is of memnor type, used for outline sorts in the logical layer. Imported at the lowest level of each dimension.

  • Attribute: Indicates the column is of attribute type, for attribute dimensions.

  • Other: The type is different than those listed, or unknown.

  • Ancestor Reference: References the ancestor of a dimension.

  • Member Key: Indicates the column is a member key.

  • Leaf: Indicates that the column is the lowest member of the hierarchy.

  • Root: Indicates that the column is the root member of the hierarchy.

  • Parent Reference: References the parent of a dimension.

The column types Outline Sort, Ancestor Reference, Member Key, Leaf, Root, and Parent Reference are used internally by the system and should not be changed.

The image shows Essbase data that has been imported into the Physical layer.

Description of GUID-C95A117F-52E4-4EFA-95DF-10820BD75182-default.gif follows
Description of the illustration GUID-C95A117F-52E4-4EFA-95DF-10820BD75182-default.gif

There are different options in the Physical layer that let you control how you want to model certain types of metadata. Choose the option that best meets the needs of the user base. For example, many types of Essbase metadata are modeled as dimension properties by default in the Physical layer.

You can choose to flatten the Essbase metadata in the Physical layer for ease of use with the attribute-style reporting supported in previous releases of Oracle BI.

The following list summarizes some of these modeling options:

  • Aliases. Aliases are modeled as dimension properties by default, but you can also choose to flatten them using the Create Columns for Alias Table feature. See Working with Essbase Alias Tables for more information.

  • UDAs. UDAs are modeled as dimension properties by default, but you can also choose to flatten them using the Create Columns for UDA feature. See Modeling User-Defined Attributes for more information.

  • Alternate Hierarchies. Alternate hierarchies are modeled as separate hierarchies by default, but you can choose to view them in as a single hierarchy using the Convert to single hierarchy view feature. See Modeling Alternate Hierarchies for more information.

  • Measure Hierarchies. By default, measures are imported as a single measure column that represents all the measures, but you can also choose to view each measure as an individual column using the Convert measure dimension to flat measures feature. See Modeling Measure Hierarchies for more information.

Using Essbase data sources with Oracle BI repository includes:

  • Substitution variables. Essbase substitution variables are automatically retrieved and populated into corresponding Oracle BI Server repository variables. Depending on the scope of the Essbase variable, the naming convention for the Oracle BI Server variable is as follows:

    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

    A single initialization block is also created in the repository for the Essbase variables. Set the appropriate refresh interval in the initialization block to reflect anticipated update cycles for Essbase variables.

  • Essbase Generations. Essbase Generations are mapped to physical level objects.

  • Time series functions. The Oracle BI Server time series functions AGO, TODATE, and PERIODROLLING are sent to Essbase to take advantage of the native capabilities of the Essbase server.

  • Database functions. You can use the database SQL functions EVALUATE and EVALUATE_AGGREGATE to leverage functions specific to Essbase data sources. See << Examples Using EVALUATE_AGGREGATE and EVALUATE to Leverage Unique Essbase Functions- link to SQL Ref>> for more information.

    The EVALUATE_PREDICATE is not supported for use with Essbase data sources.

  • Gen 1 levels. By default, Gen 1 levels are included when you drag and drop an Essbase cube or dimension from the Physical layer to the Business Model and Mapping layer. However, because Gen 1 levels are not usually needed for analysis, you can choose to exclude Gen 1 levels when you drag and drop Essbase objects to the business model. To do this, select Skip Gen 1 levels in Essbase drag and drop actions in the General tab of the Options dialog. See Setting Administration Tool Options for more information.

  • Hierarchy types. For Essbase data sources, all hierarchies are imported as Unbalanced by default. Review the Hierarchy Type property for each physical hierarchy and change the value if necessary. Supported hierarchy types for Essbase are Unbalanced, Fully balanced, and Value.

About Incremental Import

You can incrementally import Essbase metadata, meaning, you can perform an initial import and then import again.

You might want to import incrementally when information in the data source has changed, or when the first import only included a subset of the metadata.

Note the following about incremental import:

  • When you re-import metadata that already exists in the Physical layer, a message appears, warning you that the Physical objects will be overwritten.

  • If you delete data in the source, re-importing the metadata does not automatically perform the deletion in the Physical layer. Instead, you must manually delete the corresponding Physical objects.

  • If you rename an object in the source, the renamed object is imported as a new object. In this case, both the old object and the new (renamed) object are displayed in the Physical layer.

  • In general, customizations that you have performed on the Physical layer data, such as determining the alias column to use for display, are retained after an incremental import. If you want to revert to the default imported view, you must delete the existing Physical layer objects and re-import the metadata.

Working with Essbase Alias Tables

Essbase cubes support the concept of aliases, which are alternate names for members or shared members. Members might have separate aliases for each user language to enable users to view member names in their own language.

Essbase cubes support the concept of aliases, which are alternate names for members or shared members. Often, members have separate aliases for each user language to enable users to view member names in their own language.

For example, the member name might be a product code (100), with a default alias for the product name (Cola) and an additional alias for the long name (Cherry Cola).

In the Essbase cube, aliases are stored in alias tables that map a specific set of alias names to member names. Typically, a Default alias table exists for each cube.

This section contains the following topics:

Determining the Value to Use for Display

When you import metadata from Essbase into the Oracle BI repository, the Essbase cube table object in the Physical layer has a property that determines which value to display for members.

The values are for the member name, the default alias name, or some other alias name. By default, the columns display the default alias name.

To change the value to display for members:

  1. In the Physical layer of the Administration Tool, double-click an Essbase cube table.
  2. In the General tab of the Cube Table dialog, choose the appropriate value for Display Column. You can select Member Names, or you can select Alias and choose an alias table name from the list, or you can select Variable and choose a variable that contains a valid display column name.
  3. Click OK.

Explicitly Defining Columns for Each Alias

Aliases are modeled as dimension properties in the Physical layer after import.

If you want to work with more than one alias, such as when you want to flatten attributes for reporting purposes or externalize strings for translation, you can explicitly define columns for each alias. You can define alias columns at the cube, dimension, or hierarchy level.

To explicitly define columns for each alias:

  1. In the Administration Tool, in the Physical layer, right-click the cube table, physical dimension, or physical hierarchy for which you want to define alias columns.
  2. Select Create Columns for Alias Table. Then, from the sub-list, select the alias table for which you want to create columns.

    Note that the Fetch button is not used.

  3. Click Create.
  4. Drag the new alias columns to the appropriate location in the Business Model and Mapping layer.

If you want to externalize strings for translation based on the alias columns, see Localizing Business Intelligence for more information.

Modeling User-Defined Attributes

Essbase supports the concept of user-defined attributes (UDAs). A UDA is any arbitrary textual string that you can associate with any member from a dimension.

A member can have multiple strings associated to it.

You can choose whether to import UDAs in the Import Metadata Wizard. If you choose to import UDAs, then by default, each UDA is modeled as a dimension property in the Physical layer of the repository.

You can also choose to model each UDA as a separate physical column. To do this, perform one of the following tasks:

  • To model all UDAs in a cube as separate physical columns, right-click the cube table and select Create columns for UDA. All UDAs in the cube are modeled as separate physical columns.

  • To model all UDAs in a dimension as separate physical columns, right-click the dimension object and select Create columns for UDA, then select All UDAs. All UDAs in the dimension are modeled as separate physical columns.

  • To model a particular UDA in a dimension as a separate physical column in each level, right-click the dimension object and select Create columns for UDA, then select the specific UDA you want to model. The selected UDA is modeled as a separate physical column for each level.

Associating Member Attributes to Dimensions and Levels

Member attributes are not automatically associated to corresponding dimensions and levels during the import process.

To manually create the association that maps the member attribute to the appropriate logical table.

  • Open the Oracle BI Administration Tool, drag and drop the columns from the attribute dimension in the Physical layer to the appropriate logical tables in the Business Model and Mapping layer.

Modeling Alternate Hierarchies

Alternate hierarchies are modeled as separate hierarchies in the Physical layer.

You can choose to view them as separate hierarchies, called the multi-hierarchy view, or as a single hierarchy.

To view alternate hierarchies as a single hierarchy, right-click the dimension object containing the alternate hierarchies and select Convert to single hierarchy view. To return to the multi-hierarchy view, right-click the dimension object again and select Convert to multi-hierarchy view.

For example, the image shows the multi-hierarchy view for an alternate hierarchy.

Description of GUID-00AD173E-D5F8-44F5-B681-D4E714C849A7-default.gif follows
Description of the illustration GUID-00AD173E-D5F8-44F5-B681-D4E714C849A7-default.gif

Figure 8-1 shows the single-hierarchy view for the same alternate hierarchy.

Figure 8-1 Essbase Alternate Hierarchy Displayed in Single-Hierarchy View

Description of Figure 8-1 follows
Description of "Figure 8-1 Essbase Alternate Hierarchy Displayed in Single-Hierarchy View"

Modeling Measure Hierarchies

Measures are imported as measure hierarchies. The cube contains a single measure column that represents all the measures.

Alternatively, you can choose to flatten the measure hierarchy to view each measure as an individual column. To do this, right-click the cube object and select Convert measure dimension to flat measures.

Improving Performance by Using Unqualified 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 Use unqualified member name for better performance in the Hierarchy dialog.

The import process cannot 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 result when a hierarchy is specified as having unique members when it does not.

Note:

If you find that the Oracle BI Server is generating incorrect queries for Essbase, check whether there are duplicate member names in a given hierarchy. If there are, ensure that the option Use unqualified member name for better performance is not selected for that hierarchy, or perform the following steps:

  1. From the Essbase outline, update each offending member variable by adding a prefix or suffix to make the member name unique.

  2. Update SQL queries as necessary, if references are made to data within SQL.

  3. Reload the data and members in the Essbase outline.