Hierarchies

To build a hierarchy, start out by adding the first level of the hierarchy in the same manner as you did with a single-attribute dimension.

The next level of the hierarchy is then created by adding it to the same dimension. In this example, we first added Name from the Country_Region table to the Shared dimensions folder – that would create the ‘Name’ dimension. Next, we added City to the new Name dimension – and that would become the second level of the hierarchy.

In this way, we now have a two-level hierarchy. The current Name level actually contains country names.

Now we want to add a third level to this hierarchy: Customer names, which is the Name column from the Customer table. However, when we try this, we will probably see this error message:

So, the problem seems to be that we already used Name in this hierarchy – even if that was from a different table.

To overcome this problem, we must include a work-around in our definition of the Name level from the Country_Region table.

The work-around is to include the full [table.column] references in the Namecolumn and Keycolumn properties of the existing Name level:

In this way, we should now be able to add the Name column from the Customer table as the third level of this hierarchy.

Notice, in this screenshot, property settings have been changed to reflect the names on the dimensions, hierarchies and levels.