Create a Hierarchy in a Table (Tutorial)

Article
04/16/2014

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

In this lesson you will use PowerPivot to view and create hierarchies from table columns in Diagram View. A hierarchy is a list of child nodes that you can create from columns and place into any order you want. Hierarchies can appear separate from other columns in a reporting client tool, making them easier for client users to select and navigate the common paths of data. For more information about hierarchies, see Hierarchies in PowerPivot.

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For information about prerequisites and the first step in the tutorial, see PowerPivot for Excel Tutorial Introduction.

Why Create Hierarchies?

Tables can include dozens or even hundreds of columns. Because of this, client users might have difficulties finding and including data in a report. The client user can add the entire hierarchy (consisting of multiple columns) to a report in only one click. Hierarchies can also provide a simple, intuitive view of the columns. For example, in a Date table, you can create a Calendar hierarchy. Calendar Year is used as the top-most parent node, with Quarter, Month, and Day included as child nodes (Calendar Year->Quarter>Month->Day). This hierarchy shows a logical relationship from Calendar Year to Day.

Hierarchies can be included in perspectives. Perspectives define viewable subsets of a model that provide focused, business-specific, or application-specific viewpoints of the model. A perspective, for example, could provide users a hierarchy of only those data items necessary for their specific reporting requirements. For more information about perspectives, see Perspectives Dialog Box.

Create a Hierarchy

You can create a hierarchy by using the columns and table context menu or by using the Create Hierarchy button on the table header in the Diagram View. When you create a hierarchy, a new parent node appears with the columns you selected as child nodes.

When you create a hierarchy, you create a new object in your model. You do not move the columns into a hierarchy; you create additional objects. A single column can be added to multiple hierarchies.

In the PowerPivot window, switch to Diagram View. Expand the DimDate table so that you can more easily see all of its fields.

Press and hold Ctrl and click the CalendarYear, CalendarQuarter and CalendarMonth columns (you will need to scroll down the table).
To open the context menu, right-click one of the selected columns. Click Create Hierarchy. A parent hierarchy node, Hierarchy 1, is created at the bottom of the table, and the selected columns are copied under the hierarchy as child nodes.
Type Dates as the name for your new hierarchy.
Drag the FullDateLabel column under the CalendarMonth hierarchy child node. This creates a child node from the columns and places the node under the CalendarMonth child node.

To create a hierarchy from the button in the table header

While still in Diagram View, point to the DimProduct table, and then click the Create Hierarchy button in the table header. An empty hierarchy parent node appears at the bottom of the table.
Type Product Categories as the name for your new hierarchy.
To create hierarchy child nodes, drag the Product Category, Product Subcategory, and ProductName columns onto the hierarchy.

Recall from the previous lesson that you added Product Category and Product Subcategory by creating calculated columns that reference these fields from related tables. One of the benefits of using the RELATED function is that you can locate fields in the same table, allowing you to create hierarchies, such as Categories, that use values from other tables.

Edit a Hierarchy

You can rename a hierarchy, rename a child node, change the order of the child nodes, add additional columns as child nodes, remove a child node from a hierarchy, show the source name of a child node (the column name), and hide a child node if it has the same name as the hierarchy parent node.

To change the name of a hierarchy or child node

While still in Diagram View, in the Categories hierarchy, right-click the FullDateLabel child node, and then click Rename. Type Date.

Notice that when you right-click a child node in a hierarchy, you have several commands at your disposal to move, rename, or hide a source column name.
Double-click the parent hierarchy, Product Categories, and then change the name to just Categories.

Delete a Hierarchy

Keep the hierarchies in the workbook in order to complete the tutorial, but if you want to delete a hierarchy at some point, follow these steps.

To delete a hierarchy and remove its child nodes

While still in Diagram View, in the FactSales table, right-click the parent hierarchy node, Hierarchy Example 2, and then click Delete. (Or, you can click the parent hierarchy node and then press Delete.) Deleting the hierarchy also removes all the child nodes.
Click Delete from Model in the dialog box to confirm the action.

Next Step

To continue this tutorial, go to the next topic: Create a PivotTable from PowerPivot Data (Tutorial).