Work with Essbase Data Sources

About Using Essbase Data Sources

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's a User Defined Attribute (UDA).
Outline Sort: Indicates the column's of memnor type, used for outline sorts in the logical layer. Imported at the lowest level of each dimension.
Attribute: Indicates the column's of attribute type, for attribute dimensions.
Other: The type's different than those listed, or unknown.
Ancestor Reference: References the ancestor of a dimension.
Member Key: Indicates the column's a member key.
Leaf: Indicates that the column's the lowest member of the hierarchy.
Root: Indicates that the column's 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 shouldn't be changed.

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

Description of essbase_phys_layer.gif follows

Description of the illustration essbase_phys_layer.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 Work with Essbase Alias Tables.
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 Model User-Defined Attributes.
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 Model Alternate Hierarchies.
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 Configure Oracle Analytics Server to Use DataDirect.

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.

The EVALUATE_PREDICATE isn't 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 aren't 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 Set Administration Tool Options.
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 import Essbase metadata incrementally by performing an initial import and then performing another import.

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

When you re-import metadata that already exists in the Physical layer, a message appears, warning you that the Physical objects are overwritten in the import operation.
If you delete data in the source, re-importing the metadata doesn't automatically delete the same data in the Physical layer. 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. The old object and the new (renamed) object are both displayed in the Physical layer.
Customizations in the Physical layer data such as creating an 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.

Work with Essbase Alias Tables

Essbase cubes support 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.

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).

Aliases are stored in alias tables that map a specific set of alias names to member names. A default alias table exists for each cube.

This section contains the following topics:

Determine 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.

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

Explicitly Define 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.

In the Model Administration Tool, in the Physical layer, right-click the cube table, physical dimension, or physical hierarchy for which you want to define alias columns.
Select Create Columns for Alias Table. Then, from the sub-list, select the alias table for which you want to create columns.
Click Create.
Drag the new alias columns to the appropriate location in the Business Model and Mapping layer.

To externalize strings for translation based on the alias columns, see Localize Oracle Analytics Server in Administering Oracle Analytics Server.

Model 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, do one of the following:
- Right-click the cube table and select Create columns for UDA. All UDAs in the cube are modeled 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.
- 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.

Associate Member Attributes to Dimensions and Levels

Member attributes aren't automatically associated to corresponding dimensions and levels during the import process.

Open the Model 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.

Model 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 ess_alt_hier_1.gif follows

Description of the illustration ess_alt_hier_1.gif

Description of ess_alt_hier_2.gif follows

Description of the illustration ess_alt_hier_2.gif

Model Measure Hierarchies

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

You can choose to flatten the measure hierarchy to view each measure as an individual column.

In the Administration Tool, right-click the cube object, and select Convert measure dimension to flat measures to view an individual column.

Improve 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.

The import process can't verify that member names are unique for a given hierarchy. You must confirm member name uniqueness. Query errors result when a hierarchy is specified as having unique members when it doesn't.

Do one of the following:
- From the Essbase outline, update each offending member variable by adding a prefix or suffix to make the member name unique, update SQL queries, and reload the data and members in the Essbase outline.
- In the Hierarchy dialog, select Use unqualified member name for better performance to enable this capability.