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:
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.
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.
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.
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.
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.
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:
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
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_AGGREGATE to leverage functions specific to Essbase data sources.
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.
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.
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 does not 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.
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:
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.
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.
Note that the Fetch button is not used.
To externalize strings for translation based on the alias columns, see Localizing Business Intelligence.
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.
Member attributes are not automatically associated to corresponding dimensions and levels during the import process.
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.
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.
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 cannot identify that member names are unique for a given hierarchy, so it is the responsibility of the administrator to confirm uniqueness. Query errors result when a hierarchy is specified as having unique members when it does not.
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:
From the Essbase outline, update each offending member variable by adding a prefix or suffix to make the member name unique.
Update SQL queries as necessary, if references are made to data within SQL.
Reload the data and members in the Essbase outline.