Work with Cube Dimensions and Hierarchies

This topic describes what you need to know to understand, create, and modify a cube's dimensions and hierarchies.

Topics:

About Physical Dimensions and Physical Hierarchies

Most of time you'll import multidimensional data source dimensions and hierarchies into the physical layer rather than create them manually. In some cases you might need to add or modify a cube table's dimensions, hierarchies, and dimension properties.

In the physical layer, you can open a dimension to perform tasks such as modifying its name and description, and adding, removing, or modifying the hierarchies for that dimension. You can also add, remove, or edit columns that represent the dimension properties.

If a hierarchy isn't imported, any columns associated with that hierarchy are also not imported. If users need access to columns that aren't imported, you can either reimport the cube table from the data source, or you can add those columns to the physical layer by manually creating them and associate them with a level in a hierarchy.

The Oracle Analytics query engine supports unbalanced hierarchies for all multidimensional data sources. In most cases, you can configure unbalanced hierarchies in the physical layer by opening the hierarchy, selecting the hierarchy, and changing the hierarchy type.

Create a Cube Dimension

You can add a dimension to a cube to contain hierarchies and dimension properties.

A dimension is the top-level member of a cube hierarchy, representing the highest level of consolidation. Dimensions are hierarchical representations of descriptors that users are familiar with, such as a product hierarchy.

If you're creating a dimension in an Oracle EPM Cloud cube table, use the Default Member field to include the data source's Point of View (POV) value. This value provides customized information for different users.

On the Home page, click Navigator and then click Semantic Models.
In the Semantic Models page, click a semantic model to open it.
Click Physical Layer .
In the Physical Layer pane, browse for and double-click the cube where you want to add a dimension.
Click the Dimensions tab, click Create, and then select Create Dimension.
Optional: In the new dimension's properties, go to Name and enter a dimension name.
Click Save.

Create a Dimension Property Column

You can add a dimension property to a cube dimension. A dimension property is metadata that applies to all members of the dimension, for example aliases.

On the Home page, click Navigator and then click Semantic Models.
In the Semantic Models page, click a semantic model to open it.
Click Physical Layer .
Click the Dimensions tab, click Create , and then select Create Dimension Property Column.
Optional: In the New Dimension Property Column, go to Name and enter a dimension column name.
Click Locations to browse and select the cube where you want to add a dimension property column.
Click Create.
Click Save.

About Working with Physical Hierarchies

This topic helps you understand how to set a physical hierarchy's properties and add levels and level columns to a dimension hierarchy.

For more information about setting the physical hierarchy's properties, see What Are a Physical Hierarchy's Properties?

When you import a cube table, Semantic Modeler sets the hierarchy type based on the multidimensional data source. For example, Essbase hierarchies are set to Unbalanced. After you import the cube table, be sure to review each hierarchy's type and change it as needed.

A business transaction, or a cube refresh, can change the number of levels in a parent-child (value) hierarchy. Typically, after you import a cube table, you need to set the hierarchy type for a parent-child (value) hierarchy to Value before you drag metadata to the logical layer. The hierarchy type in the logical layer is set automatically based on the physical hierarchy setting. For all other hierarchies, you can change the hierarchy type later without needing to rebuild the logical model.

When you select columns to add to a hierarchy, be sure to select them in hierarchical order, starting with the highest level. If you select multiple columns to add to the hierarchy as a group, the import adds the group's columns in the order that you selected them. After you add columns to the hierarchy, you can change the order of the columns.

In the physical hierarchy's properties, you can add, remove, or modify the level's columns. By default, the first column you add to the level is the level's key. But you can change the order of the columns and which column is the level key.

What Are a Physical Hierarchy's Properties?

This topic provides information about the properties that you assign to your imported, added, or aliased physical hierarchies.

Property	Description
External Name	Essbase and Oracle EPM Cloud Displays a user-friendly name assigned to a cube table in the Essbase or Oracle EPM Cloud application. This is the name used when referencing the cube table in physical SQL queries. If you add or change this value it must match the external name defined in the data source's application.
Dimension Name	Essbase and Oracle EPM Cloud (Dimension Unique Name) Displays the dimension that the hierarchy belongs to.
Dimension Type	Essbase and Oracle EPM Cloud Identifies if the hierarchy belongs to a time dimension, measure dimension, attribute dimension, or other type of dimension.
Hierarchy Type	Essbase only Identifies the type of hierarchy: Fully balanced - A level-based hierarchy with no unbalanced or skip characteristics. Corresponds to a level-based hierarchy in the logical layer. Unbalanced - Also called ragged. A hierarchy where the leaves (members with no children) don't necessarily have the same depth. Corresponds to a level-based hierarchy with the Ragged option selected in the logical layer. Ragged balanced - Also called skip. A hierarchy with members that don't have a value for a particular ancestor level. Corresponds to a level-based hierarchy with the Skipped Levels option selected in the logical layer. Network - This hierarchy type isn't used. Ragged stretched - A hierarchy with members that don't have a value for a particular ancestor level, and not all members at the same level have the same parent level directly above them. 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 logical layer. For level-based hierarchies with both unbalanced and skip-level characteristics, choose either Unbalanced or Ragged balanced as the physical hierarchy type. Then make sure that both Ragged and Skipped Levels are selected for the corresponding logical dimension in the logical layer.
Parent Key	Oracle EPM Cloud only The column in the hierarchy that identifies the member's ancestor.
Member Key	Oracle EPM Cloud only The column in the hierarchy that identifies the member.
Default Member type ALL	Oracle EPM Cloud only This option isn't used.
Use unqualified member name for better performance	Essbase and Oracle EPM Cloud Select this option when member names including aliases are unique in the hierarchy so that the Oracle query engine can take advantage of specific multidimensional expression (MDX) syntax to optimize performance.