Power Pivot Hierarchies – Part 2 of PowerPivot 2012 Series

Last time I introduced some of the new features in PowerPivot 2012.  This week I will continue to explore the new features in PowerPivot 2012 by looking at building hierarchies.

In the previous version of PowerPivot, I could drag multiple dimension attributes to the columns or rows of a pivot table to create a hierarchy on the fly.  I still can.  However, the latest version of PowerPivot lets me pre-define hierarchies that users are most likely to use.  Using the Contoso 2012 data sample , I’ll show you how to build a hierarchy for products starting with category and drilling down into subcategories and finally products themselves.  But first, let’s clean up the data in my model to streamline processing.

Open the Contoso 2012 data in PowerPivot 2012 from last week.  While I could manage the data from the traditional data view (tab view to some), the new diagram view is so much easier.  Click the Diagram View button in the View group of the Home ribbon.

Since I want to focus on the tables: DimProduct, DimProductSubCategory, and DimProductCategory, I reorganize the tables in the diagram view to bring them together as shown in the following figure.

Notice that by default, I loaded all fields in these tables.  However, many of these fields are not needed for my pivot table project.  I could go back and reload these tables and define which fields I wanted to load into my model.  A best practice is to review the data during load to select only the columns needed and to filter the rows for only the data needed.  While I can always filter later by using slicers to remove rows that I do not want, it places an extra burden on the pivot table user to know what slicers to apply.  Removing columns that I later decide that I do not need is a simpler process.  In fact, I have two options.  I can simply hide a column from the client tool (Excel pivot table) side or I can delete the column entirely.  Of course, hiding a column means that if I change my mind later, I can unhide the column and begin using it again.  Let’s see how these options work.

Suppose I decide that I may not need the StopSaleDate column in the DimProduct table.  Right click on the field to display the popup menu shown below and select the Hide from Client Tools.  This removes the column from the Field List when displaying the pivot table or chart, but does not remove the field from the model.  I use this option to hide columns that I use to build calculated columns but which the users do not need to use as dimensions or slicers.  Often I only need the calculated column and not the source fields used to build it.  However, those columns must remain in the model to support the calculation.  I just do not want to display them to the user.  Hiding the columns used in building calculated columns is often a best practice.

I can also hide multiple columns at once by clicking on the first field and then by holding down the Shift or Ctrl key, then clicking on other fields.  Using the Shift key lets me select a range by clicking on the first and last field in a contiguous range.  Using the Ctrl key lets me select individual fields that may not be adjacent.  Then with the fields selected, I can right click on any selected field and click Hide from Client Tools just as before.

On the other hand, if I want to permanently remove a column from the current model , I could select one or more fields, right click the selection, and click Delete from the popup menu as shown below.  Just remember that when you delete a column, the only way to get that column back is to reload the table unless it is a calculated column in the first place.

Note: Deleting a column from the model does not affect the source data.

The following figure shows my ‘cleaned’ set of product and category tables.  Notice also that I clicked on the arrow line that relates the DimProduct and DimProductSubCategory tables.  When I do that, PowerPivot highlights the fields involved in the relationship.

Now with my tables cleaned, I can return to the Data View and open the DimProduct table.  I need to add two fields to the product table to create the hierarchy for products.  The first field I want to add is the ProductSubCategoryName.  If you remember from some of my earlier posts, to add a column to a table, just navigate to the right side of the table and click Add Column to select a new column.  I will rename the column to something more appropriate like SubCategory, then apply the formula:

= RELATED(DimProductSubcatgory[ProductSubcategoryName])

I hope you remember that to reference a field in another table, you must use the RELATED() function.  The resulting column looks something like the following:

Similarly, I add a Category column and use the equation:

= RELATED(DimProductCatgory[ProductCategoryName])

Now I can return to the Diagram View.

I no longer need to display the DimProductSubcategory and DimProductCategory tables to the user because my product table now includes the information I need, the names of the product category and subcategory.  Therefore, I can right click on the header of each of these tables and select Hide from Client Tools from the popup menu.  These two tables will no longer appear in the Field List box of the pivot table.

Next, within the DimProduct table, I can right click on the field that forms the top level of the hierarchy, Category in this case, and select Create Hierarchy from the popup menu.

A default hierarchy named Hierarchy1 is added to the DimProduct table.  I’ll rename it later.  First, I’ll right click on SubCategory and select Add to Hierarchy.  The fly-out shows the names of all current hierarchies in the table.  Since I only have one, I can simply click Hierarchy1 to select it.

Similarly, I also add the field ProductName to the hierarchy as shown in the figure below.

I could hide these three fields from the Client View as described earlier by selecting them then right clicking on them and picking Hide from Client View in the popup menu.  However, I’ll tell you later why this might be a bad idea.

However, we do want to rename the hierarchy.  Right click the hierarchy name and select Rename from the popup menu.

If we now go to our Excel pivot table window and refresh the PowerPivot Fields List by clicking the Refresh button that automatically appears at the top of the list when Excel recognizes that something has changed in the underlying data, you should see something like the following figure.  Notice that DimProduct now includes an entry for the hierarchy which I’ve renamed to Product.

If I build a simple pivot table to sum the SalesAmount and use the Product hierarchy as my row dimension, the resulting pivot table should look like the following:

By clicking on the box before each of the tables, I can expand and collapse each level drilling down to the level of detail that I need.  If I right click on any of the levels, I can select an option to expand or collapse all of the members of that level at one time.

Ok, so what is the problem with removing the fields from DimProduct that appear in the hierarchy?  First, I cannot create a pivot table using one of the sublevels as a column or row.  For example, I cannot simply drag over SubCategory from the hierarchy list to be my row dimension.  That may be a minor issue compared to the second problem.  The big problem as I see it is that I cannot use Category or SubCategory as slicer fields if I remove the individual fields from DimProduct.  I cannot drag the individual field names from the hierarchy definition into the slicer boxes.  For that reason primarily, I prefer to keep the individual fields that define the hierarchy in the table in most cases.

That’s it for this week.  Next week I’ll look at some more exciting features from PowerPivot 2012.

C’ya next time.

2 comments on “Power Pivot Hierarchies – Part 2 of PowerPivot 2012 Series

  1. Pingback: Populating Your Data Model with A Query | SharePointMike's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.