Skip to main content

Hierarchies

Overview

Hierarchies are arrangements of data consisting of sets and subsets where every subset is of lower rank than the set. They can be created and configured in the cube.

Hierarchies are not created automatically but are created from fields in the pipeline and will appear in the cube under the dimension created from the pipeline.

Level-based hierarchies

Access the hierarchies pipeline tab

Access the pipeline with hierarchies you want to view or edit. Click the HIERARCHIES pipeline tab to reveal the hierarchies list.

Add a hierarchy

Add additional hierarchies by accessing the HIERARCHIES pipeline tab. Click the + Hierarchy button to add a new hierarchy.

Set hierarchy properties

When a hierarchy is selected in the HIERARCHIES pipeline tab, the properties for that hierarchy are displayed on the Properties panel.

Rename a hierarchy

Change a selected hierarchy name directly in the Properties panel.

Define a hierarchy description

Specify a description for a hierarchy using the corresponding text box in the Properties panel (which appears when a hierarchy is selected in the HIERARCHIES pipeline tab).

Use general settings

Specify generic settings for the selected hierarchy in the General area of a hierarchy's Properties panel.

Cube hierarchies may be divided into two types: natural and unnatural.

  • Natural hierarchy: Attribute relationships.

    Attribute relationships define relationships between attributes within a dimension in SQL Server Analysis Services. By default, they are defined between the key attribute of the dimension and each non-key attribute. A cascading set of attribute relationships may be defined manually.

    For example, in a table listing customers and their addresses, rather than relating each address part (e.g. city, state and country), with the dimension's primary key (e.g. customer ID), the parts are related sequentially (i.e. country to state, state to city, and city to customer ID).

    Attribute relationships created in this way improve performance by defining the aggregations for each level. Reports created using a natural hierarchy will return faster and require less memory to process.

  • Unnatural hierarchy: Without attribute relationships.

    At a superficial level, an unnatural hierarchy can be converted to a natural hierarchy by simply creating the appropriate attribute relationships. Often, the problem is more complex because the hierarchy's structure prevents the attribute relationships being created.

    In order to define an attribute relationship, there must be a many-to-one relationship between the members at the child level and their parent member. i.e., the parent attribute must be unambiguously determined from the child attribute.

    For example, in a natural product hierarchy, it must be the case that each product relates to exactly one product group (e.g. the "Hammer" product belongs only to the "Tools" product group).

    However, in many hierarchies, members in a particular level may belong to more than one parent member (e.g. a location hierarchy where a city called Springfield exists in more than one state). This many-to-many relationship will prevent the creation of attribute relationships.

    In this case, the problem can be solved by adding the parent attribute's key to the key of the child attribute. In the location hierarchy example above, this would result in a composite key for the city attribute containing the city and state (e.g. Springfield, MA and "Springfield, IL). This allows child members to be unambiguously associated with their parents.

    If you have an unnatural hierarchy, you can select the Force Natural check box in the General area of the Properties panel to automatically convert it to a natural hierarchy.

    Selecting the Force Natural check box defines the following:

    • A composite key for each attribute in the hierarchy (to ensure many-to-one relationships between the attribute and its parent attribute).

    • Attribute relationships between the attribute at each level and its parent attribute.

Note

  • The composite keys are not visible in the pipeline as they are only created in the cube. The Force Natural check box may be selected or cleared at any time. Changes will take place the next time the cube is processed.

  • If you are certain that the hierarchy is already natural, the composite key creation process is unnecessary. Instead, you may set the Property Of property on each column in the hierarchy to its parent for the equivalent performance improvement. This is more work, but results in a smaller cube, as the composite keys do not need to be stored.

Add a level to a hierarchy

You can add levels to a selected hierarchy using the properties in the Levels area of the Properties panel.

Levels must be in the order you require. You can add the primary key field(s) as the last (lowest) level if you wish, but this is not necessary.

Parent-child hierarchies

You can create a parent-child hierarchy using the Display Type option in a column's Properties panel.

For the parent-child hierarchy to work, you need to relate a column in the pipeline containing the ID of the parent row (known as a parent ID) to the pipeline's primary key.

For example, in an Employee pipeline, you might relate a parent ID column, ManagerID, to the primary key column, EmployeeID, in order to create a hierarchy of employees according to who they reported to.

If the pipeline's primary key consists of a composite key (containing a main key column and one or more additional key columns), you can create a parent-child hierarchy provided you have parent ID columns in the pipeline corresponding to the columns in the primary key. In this scenario, the number of columns must be the same.

For example, if the primary key consists of a main key column and one additional key column, the parent ID used by the parent-child hierarchy must also contain two columns.

In addition, the data type of each parent ID column must be the same as the corresponding primary key column, and the columns must be specified in the same order.

Note

Parent-child hierarchies cannot be created on the Initial pipeline step. Any step after that step can be used.

Create a multi-column parent-child hierarchy

Tip

In this example, the pipeline's primary key is modified to be a composite key, composed of more than one column (the MAINACCOUNTID main key column and one additional key column: DATAAREA, representing the company that the account is associated with).

To make a valid parent-child hierarchy in this case, the Parent ID in the parent-child hierarchy must also have the same number of columns under it. Presently, the parent ID has only one column (Account). Until an additional key is added, an error will appear in the pipeline, as demonstrated, and resolved, in the following procedure.

  1. Add an additional column to the primary key column to create a composite key.

    The DATAAREA column is now part of the primary key. Notice the yellow key icon in the list of columns as well as the entry in the Additional key columns text box (in the Properties panel).

    1. Several error icons (red exclamation points) are now present. These icons report the same error, caused by the parent-child hierarchy. Since MAINACCOUNTID and Account are part of a parent-child hierarchy, adding the additional key column to MAINACCOUNTID has caused it to have an additional key column, whereas Account still has the same, original number (one fewer key column).

    2. Hover over any of the error icons to see a message describing this issue.

    3. Columns in a parent-child hierarchy must have the same number of key columns. Therefore, a key column needs to be added to the Account column. A column representing the company associated with the parent account needs to be added to the Parent ID.

  2. Add an additional column (MAINACCOUNTRECID) to the Account column. An invalid column is purposely added to see the effect, as shown below.

    1. Click the Account column to select it.

    2. The column is highlighted.

    3. In the Additional key columns text box in the Properties panel, begin typing the name of the additional key column. All columns that match the name you type are displayed.

    4. Click the MAINACCOUNTRECID column to add it.

  3. The Account column now has the same number of key columns as the MAINACCOUNTID column. However, there is still an issue, and the error icons (red exclamation points) are still displayed.

  4. Examine one of the error icons by hovering over it and viewing the text that appears.

    1. For a parent-child hierarchy to work, the field types must match and be in the same order.

    2. In this example, MAINACCOUNTID and Account are already both string-type columns, so their data types match.

      1. The new additional key column, DATAAREA, is also a string-type column, so a string-type column must also be added to Account. However, the key column that was added, MAINACCOUNTRECID, is an integer-type column.

      2. This pattern is important. If the primary column was a floating-point number and the additional was an integer, the other column in the hierarchy would need to have a floating-point number main column with an integer as its additional column.

      3. Therefore, although the number of columns matches, the type does not match, which can be solved by adding a different, string-type column to the Account column.

  5. Remove the MAINACCOUNTRECID column.

    1. Verify that the Account column is selected (highlighted).

    2. In the Properties pane, hover over the right side of the MAINACCOUNTRECID entry in the Additional key columns text box.

    3. Click the small delete button that appears to remove the column.

  6. Add an appropriate column (in this example: ParentDataArea, which represents the parent account's company).

    1. In the Properties pane, begin typing ParentDataArea in the Additional key columns text box.

    2. All columns that match the name you type of displayed.

  7. Click ParentDataArea to add it.

  8. Save and close the pipeline.

  9. Reopen the pipeline.

  10. Click the BASIC (Data per company) step.

    1. Notice that columns have been reordered based on changes made to the additional key columns.

    2. DATAAREA is now shown under MAINACCOUNTID and is indented, as with all additional keys.

    3. ParentDataArea is now shown under Account and is also indented, as it is an additional key of Parent ID.

    4. The parent-child hierarchy is valid, as the column number and types are identical and in the same order.

In this section: