Work with Oracle Essbase Data Sources

This topic provides information about how Semantic Modeler models Oracle Essbase data by default in the physical layer. This topic also describes the different ways you can model Essbase data.

Topics:

About Importing Metadata from Essbase Data Sources

When you import metadata from Essbase data sources into a semantic model, the cube metadata is mapped to the model's physical layer in a way that supports the model's logical layer.

By default Semantic Modeler models metadata that applies to all members of the dimension, for example aliases, as dimension properties. Semantic Modeler maps level-based properties, for example outline sort or memnor information, as separate physical cube columns in the dimension.

Physical Column Types

Semantic Modeler supports the following types of physical column for Essbase metadata:

  • Ancestor Reference - References the ancestor of a dimension.

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

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

  • Member Alias - Indicates an alias column.

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

  • Other - Indicates that the type is different than those listed, or unknown.

  • Outline Sort - Indicates the column is of memnor type and used for outline sorts in the logical layer. An outline sort column is imported at the lowest level of each dimension.

  • Parent Reference - References the parent of a dimension.

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

  • UDA - Indicates the column is a user-defined attribute (UDA).

The system uses the column types Outline Sort, Ancestor Reference, Member Key, Leaf Indicator, Root Indicator, and Parent Reference internally. Don't change these column types.

Essbase Components

The Essbase data sources that you use in your semantic models include:

  • Substitution variables - Essbase substitution variables are automatically retrieved and populated into corresponding Oracle Analytics repository variables. Depending on the scope of the Essbase variable, the naming convention for the 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 Analytics 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 with Essbase data sources.

  • 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 type if necessary.

About Connecting to an Oracle Essbase Data Source

This topic provides information about connecting to an Oracle Essbase data source for use in your semantic model.

What is the System Connection Requirement?

Data source connections with the System connection checkbox selected are available for semantic model development. By default, these connections are displayed in a semantic model's Connections tab.

If you create an Essbase data source connection from the Oracle Analytics Home page's create Connection option, be sure to select the System connection checkbox to make the connection available when you develop semantic models. You can't edit the data source's System connection option after setting up the connection.

See About Semantic Model Data Source Connections.

How Do I Create the Connection?

Use this topic to create your Essbase connection, making sure to select the System connection checkbox: Connect to Essbase Data Sources.

About Adding Essbase Cube Tables to the Physical Layer

When you drag and drop an Essbase cube table into a physical database, Semantic Modeler prompts you to specify how you want the import to handle the items in the table.

See Add Oracle EPM Cloud Cube Tables to the Physical Layer.

Be sure to understand these important points before you import an Essbase cube table:

  • After import the cube table, you can't change the settings you chose for the import.
  • If you need to adjust the settings for a cube table, then reimport the cube table.
  • When you import more than one table, the import options you select are set for all cube tables. If the cube tables require different import settings, then import them individually.

You can choose the following Essbase cube table import options. Remember that you can't change these settings after you import a cube table.

  • Create columns for UDAs - Select to flatten user defined attributes rather than model them as dimension properties.
  • Convert measure dimensions to flat measures - Select to include each measure as an individual column rather than a single measure column that represents all measures.
  • Convert alternative hierarchies into a single hierarchy - Select to include alternative hierarchies as a single hierarchy rather than modeled as separate hierarchies.
  • Create columns for alias table - Select to flatten aliases dimension properties.

Add Essbase Cube Tables to the Physical Layer

From the Oracle Essbase connection, you can drag and drop cube tables to add them to the semantic model's physical layer.

You need to create one physical database in the physical layer for each cube or set of cubes.

Dragging and dropping a cube table from the data source connection to the physical database populates some of the database's features, the query features, and connection pools. You can adjust these settings as needed. You can add query limits for application roles to the database.

For Essbase, a cube table must be located in a catalog. When you drag and drop a cube table from an Essbase connection into the physical database, Semantic Modeler adds a catalog.

See About Adding Essbase Cube Tables to the Physical Layer.

See About Connecting to an Oracle Essbase Data Source.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Physical Layer Physical layer tab contains objects representing physical data.
  4. In the Physical Layer pane click Create Create icon and then click Create Database.
  5. In Create Database, go to the Name field and type a name. In the Database Type field select Oracle Essbase. Click OK.
    The new database's Tables tab is displayed.
  6. In the Tables tab, click Connections Connections icon and in the connections pane, expand the Oracle Essbase connection and browse or search for the cube table to add to the physical database.
  7. Drag the cube table to the physical database and drop it into the Tables list.
  8. In the Cube Table Options dialog, specify how you want Semantic Modeler to add the cube table to the physical layer.
    Semantic Modeler creates a catalog and adds the cube table to it.
  9. Click the Connection Pools tab and verify that your database and connection pool settings are correct.
  10. Click the Tables tab to inspect and confirm the imported physical columns and hierarchical levels.
  11. Click the Tables tab, double click a hierarchy to open it, and go to the Hierarchy Type field to inspect and adjust its type. Note that by default Semantic Modeler imports all hierarchies as Unbalanced.
  12. Click Save.

Add or Remove Cube Columns in a Hierarchy Level

Dragging and dropping a cube table into the physical layer includes all of the table's objects. You can add or remove associated columns in the imported hierarchies.

You can choose to add any of the dimension property columns to a dimension hierarchy level. When you remove a hierarchy level column, Semantic Modeler converts it to a dimension property column.

In some cases you might need to create the column that you want to add to the hierarchy. When you create a column, Semantic Modeler adds it as a dimension property column that you can then add to a hierarchy level.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Physical Layer Physical layer icon.
  4. Browse for and double click to open the physical cube table and then from the Dimensions and Hierarchies pane, browse for and double click the hierarchy level where you want to delete or add a column.
  5. To delete a column, go to the Associated Columns field, hover over the column to remove, and click Remove Associated Column Delete Key button.
  6. To add a column, click Add Associated Columns Create icon to browse for and add a column.
  7. Click Save.

Modify a Hierarchy Level's Key

When you add an associated column to an Essbase cube table hierarchy level, by default Semantic Modeler uses the first column that you add as the level's key. You can choose a different column to use as the level's key.

For Essbase cube tables, each hierarchy level has a level key. The level key must match with the data source definition of the cube. In the physical pane, the icon for the column that you select first changes to the key icon after it's associated with the level of a hierarchy.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Physical Layer Physical layer icon.
  4. Browse for and double click to open the physical cube table and then from the Dimensions and Hierarchies pane, browse for and double click the hierarchy level containing the key you want to change.
  5. Go to the Associated Columns field and confirm that it displays the column you want to set as the key. If the column you need is missing, click Add Associated Columns Create icon to add the required column.
  6. In the level's properties pane, click the Key field and select the column you want to use as the level key.
  7. Click Save.

About Working 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.

Specify How to Display the Cube Table's Columns

You can choose which value you want to use to display an Essbase cube table's columns.

You can choose from the following options:
  • Member Names - Uses the names from the Essbase data source cube members.
  • Alias - Uses the selected Essbase alias table name to map alias names to the cube's member names.
  • Variable - Uses a variable that contains valid display column names.

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.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Physical Layer Physical layer icon.
  4. In the Physical Layer pane, browse for and double-click the cube table where you want to specify how you want columns to display.
  5. Click the General tab and then in Display Columns specify how you want the table's columns to display. If you select Alias, click the field and choose an alias table. If you choose Variable, click Select and pick a variable from the list.
  6. Click Save.

Use Unqualified Member Names to Improve Performance

When member names (including aliases) are unique in a given hierarchy, the Oracle Analytics query engine can take advantage of specific multidimensional expression (MDX) syntax to optimize performance.

After you import a cube table, you must confirm that member names are unique for a given hierarchy. It is important that you confirm this setting because query errors can result when a hierarchy is specified as having unique members when it doesn't.
You can fix this issue in the Essbase outline or in the physical hierarchies properties in Semantic Modeler.
To fix this issue in the Essbase outline, go to the outline and 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.
To fix this issue in the physical hierarchy:
  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Physical Layer Physical layer icon.
  4. Browse for and double click to open the physical cube table, and then from the Dimensions and Hierarchies pane, browse for and double click the hierarchy level where you want to set the property.
  5. Click the Use unqualified member name for better performance field to select it.
  6. Click Save.