Siebel Analytics Server Administration Guide > Creating and Administering the Physical Layer in a 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. It is not possible to set up ragged or parent-child hierarchies in the physical layer.
• 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.

  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.

• Set up hierarchies for large cubes (those with many members on the lowest levels of the hierarchy) to minimize performance issues. It is recommended that you do not set up hierarchies and levels unless they are required to run queries.

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

• Adding a Hierarchy to a Physical Cube Table
• Verifying Hierarchy Levels
• 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

Each 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 Analytics 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 include in a hierarchy, you must select them in hierarchical order, starting with the highest level. After adding columns to the hierarchy, you can change the sequence.

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. You can change the order of the columns in the Browse dialog 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.
3. In the Hierarchies tab, click Add.
4. In the Hierarchy dialog box, in the Level tab, type a name for the hierarchy in the Name field, and click Add.
5. In the Physical Level dialog box, in the Columns tab, type a name for the level in the Name field and click Add.
6. In the Browse dialog box, in the Name list, locate the columns that you want to add to the hierarchy.

   For more information about the Browse dialog box, see Using the Browse Dialog Box.

7. Add columns in top-down order as explained in the following steps:

   NOTE:  In a hierarchy, levels should be added from the top down, although they can be reordered later. The first column assigned to a level is the key. Using the correct hierarchical sequence allows your queries to return accurate information and avoids errors.

   1. Add the column for the highest level in your hierarchy by clicking that column and clicking Select.
   2. Select subsequent columns in top-down order and click Select.

      In the Physical Level dialog box, the Columns tab lists all the columns selected for that level.

8. In the Physical Level dialog box, click OK.
9. In the Hierarchy dialog box, in the Levels tab, the Default member type ALL check box should not be selected for non-ALL default members.

   If you imported the cube, the Default member type ALL check box should be automatically set. If you build the hierarchies manually, ask your multidimensional data source administrator if a non-ALL default member has been defined. For example, for the Year level, it is possible to define 1997 as the default member.

10. To change the order of levels in a hierarchy, perform the following steps:
    1. In the Hierarchy dialog box, in the Levels tab, select a hierarchy, and then click Edit.
    2. In the level tab, select a level, and then click Up or Down.

       The Levels tab lists all the defined levels for the selected hierarchy. You must select a level for the buttons to be available.

    3. In the Levels tab, click OK.
11. In the Hierarchy tab, 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 a 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, in the Level tab, verify the levels are correct.

   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.
6. When the levels are correct, click OK.

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 and from the cube table in the Physical layer.

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, click to select the columns that you want to add.
      • Click Select and then click OK three times to return to the Physical layer in the Administration Tool.
   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.

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 to remove objects that are not accessed. Perhaps the business model does not reference any of the elements in that hierarchy. For example, you 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. This is different from deleting a hierarchy in the Business Model and Mapping layer.

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.