Working with Multidimensional Sources in the Physical Layer

Learn about physical cube tables, dimensions, and hierarchies from multidimensional data sources.

This section contains the following topics:

About Physical Cube Tables

Each cube from a multidimensional data source is structured as a physical cube table, a type of physical table.

It has all the capabilities of a table, such as physical cube columns and keys (optional) and foreign keys (optional). It also has cube-specific metadata such as hierarchies and levels.

When you import the physical schema, the Oracle BI Server imports the metadata for the cube, including its metrics, hierarchies, and levels. Expanding the hierarchy object in the Physical layer reveals the levels in the hierarchy. In the Physical Cube Table dialog, the Hierarchies tab lists the dimensional hierarchies in the cube.

Each multidimensional catalog in the data source can contain multiple physical cubes. You can import the metadata for one or more of these cubes into the Oracle BI Repository. Although it is possible to create a cube table manually, it is recommended that you import metadata for cube tables and their components.

If you do create cubes manually, you must build each cube one hierarchy at a time and test each one before building another. For example, create the time hierarchy and a measure and test it. When it is correct, create the geography hierarchy and test it. This helps ensure that you have set up each cube correctly, and makes it easier to identify any setup errors.

About Measures in Multidimensional Data Sources

You need to select the aggregation rule for a physical cube column carefully to make sure the measures are correct.

Setting it correctly might improve performance.

Always verify aggregation rules after importing cube metadata. Typically, aggregation rules are assigned correctly when you import cube metadata. However, if a measure is a calculated measure, the aggregation rule is reported as None. Therefore, you must examine the aggregation rule for all measures after importing a cube to verify that the aggregation rule has been assigned correctly.

For all measures assigned an aggregation rule value of None, contact the multidimensional data source administrator to verify that the value of the aggregation rule is accurate. If you need to change the aggregation rule, you can change it in the Physical Cube Column dialog.

Use the following guidelines to assign the correct aggregation rule:

If the generated physical queries to the database should send an aggregation function, such as SUM(revenue), then set that function as the aggregation rule. With this setting, the Oracle BI Server typically sends the aggregation to the database in the query, but might also perform aggregations itself in certain situations.
If the data for this measure should not be aggregated in the query or by the Oracle BI Server, use the External Aggregation rule. It is important to choose this setting when the measure uses a more complex calculation inside the data source than the Oracle BI Server can replicate with a simple aggregation rule such as calculations for ratios, consolidations and allocations. This option is also useful when the cube persists a full set of pre-aggregated results.

About Externally Aggregated Measures

In a multidimensional data source, some cubes contain very complex, multi-level based measures.

If you assign an aggregation rule of External Aggregation, the Oracle BI Server bypasses its internal aggregation mechanisms and uses the pre-aggregated measures. When imported, these measures are assigned an aggregate value of None.

The following are some guidelines for working with pre-aggregated measures:

External aggregation only applies to multidimensional data sources such as Essbase, Hyperion Financial Management, Microsoft Analysis Services, and SAP/BW that support these complex calculations.
You cannot assign external aggregation to measures from non-multidimensional data sources. If the required aggregation rule is supported by the Oracle BI Server and can be mapped to a relational data source, then it is not complex and does not require external aggregation.
There is only one aggregation rule for a logical measure. Therefore, a single logical column cannot federate a non-complex aggregation rule for a mapping to a non-multidimensional source, with a complex aggregation rule for a mapping to a multidimensional source. Instead, you need to create one logical measure for each source, and create a third logical measure that derives from the first two.
You can mix non-complex measures from non-multidimensional data sources with non-complex measures from multidimensional data sources if they are aggregated through the Oracle BI Server.

About Working with Physical Dimensions and Physical Hierarchies

Most dimensions and hierarchies are imported into the Physical layer from multidimensional data sources, rather than created manually.

If a particular hierarchy is not imported, any columns associated with that hierarchy are also not imported. If users need access to columns that are not imported, first add these columns to the Physical layer by manually creating them and associate them with a level in a hierarchy.

Each level in a hierarchy has a level key. The first cube column associated with (added to) the level of a hierarchy is the level key. This must match with the data source definition of the cube. The icon for the column that you select first changes to the key icon after it is associated with the level of a hierarchy.

Oracle Business Intelligence supports unbalanced hierarchies for all multidimensional data sources. In general, you can configure unbalanced hierarchies in the Physical layer by changing the hierarchy type.

You can view and edit properties for physical dimensions and hierarchies by double-clicking physical dimension and physical hierarchy objects in the Physical layer of the Answers. You can also view and edit these objects from the Dimensions and Hierarchies tabs of the Cube Table dialog.

This section contains the following topics:

Working with Physical Dimension Objects

In the Physical Dimension dialog, you can view and edit the name and description of the dimension.

You can also add, remove, or edit hierarchies for that dimension, and add, remove, or edit columns that represent dimension properties.

Working with Physical Hierarchy Objects

When you select columns to add to a hierarchy, it is recommended that you select them in hierarchical order, starting with the highest level.

If you select multiple columns and bring them into the hierarchy at the same time, the order of the selected group of columns remains the same. After adding columns to the hierarchy, you can change the order of the columns in the Browse dialog.

In the Physical Hierarchy dialog, you can view and edit the name and description of the hierarchy, along with the properties described in the table. For level-based hierarchies, you can add, remove, edit, or reorder levels. For value-based hierarchies, click the Column tab to add, remove, or edit columns. To specify a key column, double-click a column name.

In the Physical Level dialog, you can view and edit the name, external name, and description of the level. You can also add, remove, or edit columns for that level. To designate a column as a level key, double-click a column name.

Always review the hierarchy type after import to ensure that it is set appropriately. The way this parameter is set upon import depends on the data source. For example, all Essbase hierarchies are initially set to Unbalanced. Review the hierarchy type for each hierarchy and change it as appropriate.

Typically, you always need to manually set the hierarchy type for parent-child (value) hierarchies, except for Hyperion Financial Management hierarchies, which are always set to Value by default upon import. Review the hierarchy type and change the type to Value as appropriate. Parent-child (value) hierarchies are those in which a business transaction, or a cube refresh, can change the number of levels.

For parent-child hierarchies, you must manually set the physical hierarchy type to Value before dragging metadata to the Business Model and Mapping layer. The hierarchy type in the Business Model and Mapping layer is set automatically based on the physical hierarchy setting. For all other types, you can determine the hierarchy type later, without needing to rebuild the logical model.

You must also ensure that the corresponding logical dimension properties are correct for queries to work. See Working with Logical Dimensions.

For SAP/BW data sources, all hierarchies default to fully balanced hierarchies on import. The hierarchy type for two-level hierarchies (which typically correspond to characteristic primary hierarchies) should not be changed. Review all SAP/BW multi-level (external) hierarchies to determine whether any are parent-child hierarchies, and set them to Value as needed.

Property	Description
External Name	The physical name that is used when referencing the hierarchy in physical MDX queries. This value must reflect the external name defined in the data source.
Dimension Name	(Dimension Unique Name) Dimension to which the hierarchy belongs.
Dimension Type	Identifies whether this hierarchy belongs to a time dimension, measure dimension, or other type of dimension.
Hierarchy Type	Identifies the type of hierarchy, as follows: Fully balanced: A level-based hierarchy with no unbalanced or skip characteristics. Corresponds to a level-based hierarchy in the Business Model and Mapping layer. Unbalanced: Also called ragged. A hierarchy where the leaves (members with no children) do not necessarily have the same depth. Corresponds to a level-based hierarchy with the Ragged option selected in the Business Model and Mapping layer. Ragged balanced: Also called skip. A hierarchy where there are members that do not have a value for a particular ancestor level. Corresponds to a level-based hierarchy with the Skipped Levels option selected in the Business Model and Mapping layer. Network: This hierarchy type is not used. Value: Also called parent-child. A hierarchy of members that all have the same type. This contrasts with level-based hierarchies, where members of the same type occur only at a single level of the hierarchy. Corresponds to a parent-child hierarchy in the Business Model and Mapping layer. For level-based hierarchies with both unbalanced and skip-level characteristics, choose either Unbalanced or Ragged balanced as the physical hierarchy type. Then, ensure that both Ragged and Skipped Levels are selected for the corresponding logical dimension in the Business Model and Mapping layer.
Default member type ALL	This option is not used.
Use unqualified member name for better performance	Select this option when member names, including aliases are unique in a given hierarchy so that the Oracle BI Server can take advantage of specific MDX syntax to optimize performance.

Adding or Removing Cube Columns in a Hierarchy

After importing a hierarchy, you may need to add or remove a column.

If you remove a cube column from a hierarchy, it is deleted from the hierarchy but remains in the cube table and is available for selection to add to other levels.

In the Physical layer of the Administration Tool, double-click the physical hierarchy to add or remove a cube column.
For level-based hierarchies, double-click the level for which you want to add or remove columns. Then, in the Physical Level dialog, you can add, remove, or edit columns. When you are finished, click OK in the Physical Level dialog.
For value-based hierarchies, click the Columns tab. You can add, remove, or edit columns, and designate member key and parent key columns.
Click OK in the Hierarchy dialog.

Working with Cube Variables for SAP/BW Data Sources

In SAP/BW data sources, cube variables are used as a means of parameterizing queries. Cube variable objects are imported into the Physical layer when metadata is imported from Querycubes/Bex Queries in SAP/BW data sources.

Typically, you do not edit these objects directly except to keep them synchronized with the Bex queries in the data source, and except to specify overrides for key characteristics values.

The Cube Variables tab of the Cube Table dialog lists the cube variables for the given cube table, along with the cube variable caption. Double-click a cube variable for more detailed information, or click the Add button to define a new cube variable.

The table describes the properties of cube variables for SAP/BW data sources. See the SAP/BW documentation for additional information.

Property	Description
Name	Name of the cube variable.
Caption	A description (label or caption) associated with the cube variable, mainly used for display purposes.
Variable Type	The type of cube variable. Variable types include: SAP_VAR_TYPE_MEMBER: A placeholder for a selection for MEMBER_UNIQUE_NAMES. SAP_VAR_TYPE_HIERARCHY: A placeholder for a HIERARCHY_UNIQUE_NAME. SAP_VAR_TYPE_NUMERIC: A placeholder for a numeric value in formulas.
Selection Type	The selection type of the cube variable, for cube variables of type SAP_VAR_TYPE_MEMBER. Selection types include: SAP_VAR_SEL_TYPE_VALUE: The variable is replaced by a single value. Cube variables of type NUMERIC must have this selection type. SAP_VAR_SEL_TYPE_INTERVAL: A placeholder for an interval. SAP_VAR_SEL_TYPE_COMPLEX: A placeholder for a complex selection.
Entry Type	Indicates whether replacing variables is optional or mandatory. Entry types include: SAP_VAR_INPUT_TYPE_OPTIONAL: Specifying a value is optional for this variable. SAP_VAR_INPUT_TYPE_MANDATORY: You must specify a value for this variable. SAP_VAR_INPUT_TYPE_MANDATORY_NOT_INITIAL: You must specify a value for this variable. An initial field is not a valid entry.
Reference Dimension	This column contains a DIMENSION_UNIQUE_NAME for the parameter type SAP_VAR_TYPE_HIERARCHY.
Reference Hierarchy	This column contains a HIERARCHY_UNIQUE_NAME for the variable type SAP_VAR_TYPE_MEMBER.
Default Low	This property contains a default value for the variable or is zero.
Default High	This property contains a default value for the variable or is zero. This property is only important for variables with the selection type SAP_VAR_SEL_TYPE_INTERVAL and SAP_VAR_SEL_TYPE_SELECTION.
Override Default Low	Provide a default value for the cube variable in this field if the Default Low is zero. You must specify a value for this property for mandatory variables that do not specify a default value.
Override Default High	Provide a default value for the cube variable in this field if the Default High is zero. You must specify a value for this property for mandatory variables that do not specify a default value.

Viewing Members in Physical Cube Tables

You can view members of hierarchies or levels in the Physical layer of repositories.

Viewing the list of members by level in the hierarchy can help you determine if the connection pool is set up properly. You might want to reduce the time it takes to return data or the size of the returned data by specifying a starting point (Starting from option) and the number of rows you want returned (Show option).

Open the Administration Tool in online mode.
In the Physical layer, right-click a hierarchy or level.
Select View Members.

A window opens showing the number of members in the hierarchy and a list of the levels. You might need to enlarge the window and the columns to view all the returned data.
Click Query to display results.
When finished, click Close.