Working With Hierarchies and Drill Down in Power View for Excel 2013

A whole slew of improvements to Power View have arrived today with the preview release of Office 2013. It’s not just that Power View is now component of Excel on the desktop but that Power View itself can do many more things than in its original release as part of Reporting Services 2012. Here’s just one example: drill down.

Drill down (and drill up) is the ability to negotiate a vertical hierarchy. For example, it’s very easy to understand a calendar as a hierarchy – a year inherits four quarters, each of which inherit three months, each of which inherit around thirty days. Generally a dashboard tool allows a user to visualise data grouped by the top of the hierarchy and then use some simple mechanism like a click to drill down through any path. Power View now offers this facility – one step on the road to an analytic/dashboarding tool maybe?

Here’s how to achieve it.

For a start we need to create something that actually is a hierarchy*.  This can be done in PowerPivot’s Data Model designer.  It’s easy to do:

  1. After you’ve imported a table that contains hierarchical data, switch to Diagram View.
  2. Click the Create Hierarchy button on the table’s title.
    Create a hierarchy in a PowerPivot table
  3. Drag each attribute from the table to the right place in the hierarchy.
  4. Give the hierarchy and its attributes a useful, meaningful name.
    A PowerPivot hierarchy

Power View (and any other client tool you use this model with) will now be able to use this hierarchy.

Back in Excel, select Power View from the Insert ribbon.
image

This causes a new tab to appear in the workbook, with the Power View interface. For this example I have just added some of the tables from the Adventure Works DW sample database and created a SUM measure on the [InternetSales].[SalesAmount] field.  With this measure checked, the visualisation changed to a column chart, and the Calendar hierarchy ticked, Power View builds this view for me:
Power View chart with Drill Down

To drill down through the data you just double-click on whichever column you want to drill into:
Power View drill down in a chart

Power View automatically (and instantly because this is PowerPivot data) redraws the chart at the next level down. You can keep going to until you reach the bottom level of the hierarchy.

To drill up, hover your mouse pointer over the chart and three tiny icons appear in the top left corner.  The first of these is the drill up command. It only needs a single click.

Drill down also works with other types of object.  With a matrix, a double click on a row or column header goes down one level. A drill up is done by single-clicking the up arrow icon on the left hand side of the first header cell.
Power View drill down in a matrix

All this functionality works with Power View in SharePoint 2013 as well as in Excel on the desktop.

* OK, you don’t really need to define a hierarchy in your data model because Power View will treat any attributes placed on the axis as a hierarchy.  But it’s good practice to create hierarchies in your data source for consistency.

3 thoughts on “Working With Hierarchies and Drill Down in Power View for Excel 2013

  1. Pingback: Building a Simple BI Solution in Excel 2013, Part 1 « Chris Webb's BI Blog

  2. I am using a SSAS Tabular model to provide data to Excel 2013 PowerView. There are multiple hierarchies defined in the source data, but I don’t see any of the hierarchy functionality that you described. Is there something else that I need to do to make it work?

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>