Oracle^® Business Intelligence Server Administration Guide > Creating and Administering the Physical Layer in an Oracle BI Repository > Creating and Setting Up Physical Tables >

Setting Up Hierarchies in the Physical Layer for a Multidimensional Data Source

The following are some guidelines to follow when setting up hierarchies in the Physical layer.

• Hierarchies that are ragged or have a parent-child hierarchy are not imported. You can set up unbalanced hierarchies in the physical layer by changing the hierarchy type.
• To change the column from a measure to a property or a level key, you need to set up a hierarchy and associate the cube column with the hierarchy. If you delete a property or level key column from a level, the column will change back to a measure under the parent cube table.

  CAUTION:  You will need to build a matching hierarchy in the Business Model and Mapping layer. If you do not do this, queries may appear to work but might not return the correct results.

To create and maintain hierarchies in the Physical Layer, perform the following tasks:

• Adding a Hierarchy to a Physical Cube Table
• Verifying Hierarchy Levels
• Updating Member Counts
• Viewing Members in Physical Cube Tables
• Adding or Removing a Cube Column in an Existing Hierarchy
• Removing a Hierarchy from a Physical Cube Table
• Associating a Physical Cube Column with a Hierarchy Level

Adding a Hierarchy to a Physical Cube Table

Most hierarchies are imported into the physical layer. Columns associated with a hierarchy that is not imported will not be imported. If users need access to columns that are not imported, first add these columns to the physical layer and then associate them with a level in a hierarchy. This section contains instructions for adding 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 data source cube table cannot set one column as a level key and the Oracle BI physical layer table set a different column as a level key. The icon for the column that you select first changes to the key icon after it is associated with the level of a hierarchy.

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

If a query does not explicitly refer to a member of a hierarchy, a default member must be used. Therefore, every hierarchy must be associated with a default member, typically the ALL member. The Hierarchy dialog box contains a check box (Default member type ALL) that you use when you want to designate the ALL member as the default. The following list contains some guidelines about selecting the check box:

• If you import the cube, the Default member type ALL check box should be automatically selected. The ALL member is identified during import.
• If you build the hierarchies manually, the check box will not be automatically selected. Before selecting the check box, ask your multidimensional data source administrator if a non-ALL default member has been defined. For example, for the Year level, 1997 might be designated as the default member. In this case, you should not select the Default member type ALL check box.

To add a hierarchy to a physical cube table

1. In the Physical layer of the Administration Tool, double-click the table to which you want to add a hierarchy.
2. In the Physical Cube Table dialog box, click the Hierarchies tab and click Add.
3. In the Hierarchy dialog box, complete the fields using Table 14 as a guide.
4. To create a level, perform the following steps:
   1. In the Hierarchy dialog box, click Add.
   2. In the Physical Level dialog box, complete the fields using Table 14 as a guide.

      NOTE:  In a hierarchy, levels should be added from the top down (you can reorder them later). Using the correct hierarchical sequence allows your queries to return accurate information and avoids errors.

5. To add one or more columns to the level, in the Physical Level dialog box, click Add.

   NOTE:  You can also add columns to a physical level by dragging and dropping physical columns on the level object. The first column you add will be a key. Subsequent columns will be properties.

6. In the Browse dialog box, perform the following steps:
   1. In the Name list, locate the columns that you want to add to the hierarchy.
   2. Select the key column first, and then click Select.
   3. In the Physical Level dialog box, click OK.
7. To add more columns, repeat Step 5 through Step 6.

   NOTE:  You can add multiple columns by pressing Ctrl on your keyboard while clicking each column, and then clicking Select.

8. When finished adding columns, in the Hierarchy dialog box, click OK.

Verifying Hierarchy Levels

It is strongly recommended that after setting up a hierarchy containing more than one level, you should verify the order of the levels in the hierarchy.

To verify the levels in a hierarchy

1. In the Physical layer of the Administration Tool, double-click the table you want to verify.
2. In the Physical Cube Table dialog box, click the Hierarchies tab.
3. In the Hierarchies tab, select a hierarchy, and then click Edit.
4. In the Hierarchy dialog box, verify the levels are correct.

   The Hierarchy dialog box lists all the defined levels for the selected hierarchy. The highest level in the hierarchy should be the first (highest) item in the list.

5. If you need to reorder the hierarchy levels, select a level and click Up or Down to correct the order of the levels.

   There must be multiple levels and you must select a level for the buttons to be available.

6. When the levels are correct, click OK.
7. In the Physical Cube Table dialog box, click OK.

Updating Member Counts

You must open the repository in online mode to update member counts.

To determine if counts need to be updated, move your mouse over the hierarchy or level name. A message appears to let you know that the counts need to be updated or when they were last updated.

When you update member counts, the current number of members are returned from the selected hierarchy. After successfully updating the member count, the updated member count appears in a message when you move the mouse above the hierarchy or level name. The message appears in the following syntax:

<hierarchy name> (<x> members, last updated <time stamp>)

To update member counts

1. In the Administration Tool, in the Physical layer, move your cursor over a hierarchy or level.

   If the counts need to be updated, a message appears.

2. Right-click one or more hierarchies and levels.
3. In the menu, select Update Member Count.

   An updated message appears if the update was successful.

Viewing Members in Physical Cube Tables

To view members, the repository must be opened in online mode. This is available for physical cube tables from Analysis Services and SAP/BW data sources.

You can view members of hierarchies or levels in the physical layer of repositories. The list of members by level in the hierarchy can help you determine if the XMLA connection on the server 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).

To view members

1. In the Administration Tool, in the Physical layer, right-click a hierarchy or level.
2. 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.

3. Click Query to display results.
4. When finished, click Close.

Adding or Removing a Cube Column in an Existing Hierarchy

After setting up a hierarchy you may need to add or remove a column. You might want to remove a hierarchy if it has been built incorrectly and you want to start over.

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.

To add a cube column to or remove a cube column from an existing hierarchy

1. In the Physical layer of the Administration Tool, double-click the table that you want to change.
2. In the Physical Cube Table dialog box, click the Hierarchies tab.
3. Select the hierarchy you want to change, and then click Edit.
4. In the Hierarchy dialog box, select the level and click Edit.
5. In the Physical Level dialog box, perform one of the following steps:
   1. To add a column, click Add.
      • In the Browse dialog box, in the Name list, select the columns that you want to add.
      • Click Select.
   2. To remove a column, select the column and click Remove.
   3. To change the sequence of the levels in a hierarchies, select the level and click Up or Down.
   4. Click OK.
6. In the Hierarchy dialog box, click OK.
7. In the Physical Cube Table dialog box, click OK.

Removing a Hierarchy from a Physical Cube Table

You might want to remove a hierarchy if it has been built incorrectly and you want to start over or if you want to remove objects that are not being used. For example, you might import an entire physical multidimensional schema and only want to keep parts of it in the business model.

NOTE:  When you delete a hierarchy in the Physical layer, you remove the hierarchy and the columns that are part of the hierarchy.

To remove a hierarchy from a physical cube table

1. In the Physical layer of the Administration Tool, double-click the table that you want to change.
2. In the Physical Cube Table dialog box, click the Hierarchies tab.
3. Select the hierarchy you want to remove, and then click Remove.

Associating a Physical Cube Column with a Hierarchy Level

Attributes are used in the physical layer to represent columns that only exist at a particular level of a hierarchy. For example, if Population is an attribute that is associated with the level State in the Geography hierarchy, when you query for Population you are implicitly asking for data that is at the State level in the hierarchy.

There can be zero or more attributes associated with a level. The first physical cube column that is associated with a level becomes the level key. If you associate subsequent columns with a level, they become attributes, not level keys.

Example of Associating a Physical Cube Column with a Hierarchy

You have a level called State and you want to associate a column called Population with this level.

• Create the hierarchy and the State level.
• Create the physical cube column for Population.
• In the Physical Cube Table dialog box, in the Hierarchies tab, select the State level and click Edit.
• In the Hierarchy dialog box, click Add.
• In the Physical Level dialog box, click Add.
• In the Browse dialog box, select the Population column and click Select.

  The measure icon changes to the property icon.