Today’s post is primarily going to focus on the ability to drill on visualizations in Power View, but before we get there I want to comment on really great feature of the next version of Office that has all sorts of possibilities, Apps for Office. While it is not directly related to Power View in Excel, in an indirect way, it actually is. On top of that, since one of the recently released apps for Office is actually a medal tracker that pulls Olympics data, I decided to check it out and see what was there.
Once you go through the process of getting the app installed it shows up in the ribbon here:
This inserts what is referred to as a task pane app which shows Live Medal Counts for the Olympics.
While this is a really nice presentation of the data in the task pane, the first thing that caught my eye were those bright buttons that read “Add to Spreadsheet”. Are you thinking what I’m thinking? If you click on that button, the data does get added to your spreadsheet like so. And once you have data in the spreadsheet, you are just one click away from being able to do a couple of other things:
- Insert the data into PowerPivot
- Create a Power View report in Excel
Technically, creating a Power View report in Excel will also insert the data into PowerPivot. Yes, you read that right. When you create a Power View report in Excel by selecting data from a table or a range, behind the scenes a PowerPivot model is generated in the workbook.
Here’s the spreadsheet with the data added.
One great piece of interactivity here worth noting. When you click on a row in the table in the spreadsheet, the task pane updates to show stats for just that country. Pretty cool.
If you go to the ribbon with the PowerPivot add-in enabled, you can click on the option to add this table to the data model. As I mentioned above, if you want to go straight to PowerView before doing this, you can do that as well and it will create the same table in the model. Once in the model, you can set the model table to update whenever the data in the spreadsheet table updates. Now if you look at the app in the task pane again, notice it has the option to “refresh the data”. So you can add this live medal data to a spreadsheet, add it to PowerPivot, hit refresh in the Medal Tracker app and now that data is up to date in PowerPivot and any Power View reports you create as well.
The Medal Tracker app doesn’t just have this one table of data though. It contains a whole set of data feeds for medal history, sports lists, athletes, etc. Pretty much everything we need to build out a complete PowerPivot model for the Olympics. We can add all of those tables to individual sheets, add them to the model, relate them together and we are well on our way to being able to make some interesting views of the data.
Fast forward through adding all of the data tables and let’s take a look at today’s Power View feature, drillable visualizations. After creating the model based on these tables, add a Power View sheet that shows the Count of Medals by Discipline and Event and convert the visual to a Matrix. Both of these fields are part of a hierarchy that is created in the model, but in Power View you can drill down on any field that you like by simply adding the field to a drop zone in the field well that accepts more than a single field. In our example right now, the drop zones look like this:
To enable drill, all you need is to do is to go to the ribbon and choose the option Design –> Show Levels –> Rows – Enable Drill Down One Level at a Time.
You can now drill up and down on disciplines.
Convert the matrix to a column chart and the drill feature is carried over. You can double-click on the columns in the chart to drill down to the next level.
When you want to drill up for the chart, the entry point is in the “floatie” area right above the chart next to the option that opens up the filter pane.
Lastly, you are not confined to just fields that are part of a hierarchy for drill. For example, you can add Team to the top of the drill path here and start the drill down from team instead.
Now I’ve got a bit of conundrum. In my previous posts I built up a workbook based on a different set of data, but the opportunity to check out the Medal Tracker app for Office was just too good to pass up and not include here. As a result, I have two separate workbooks based on Olympics data. I’m in the process of consolidating the two and I hope that by my next post, I’ll have that done and we’ll continue the tour based on a single workbook.
If you would like to download the workbook and try it out, I’ve placed it here. I’d also recommend that you check out Dan English’s latest blog entry on Analyzing SQL Server Saturday data with Power View over at http://denglishbi.wordpress.com/2012/08/06/sql-saturday-event-analysis-using-power-view-and-office-2013/