Guest Post: Our Power View Story (and Power Pivot Settings Cheatsheet)

By Avichal Singh www.linkedin.com/in/avichalsingh

You read about my Power Pivot journey in my first blog post and in my subsequent blog post I elaborated on migration to Analysis Services Tabular Model (SSAS Tabular). I realize now though, that I did things out of order and need to address that in some way. As my journey outlined, before we switched over to SSAS Tabular, we moved our Power Pivot workbooks to SharePoint and started using Power View Reports. And Power View has been a key element of our success. For this post I’ll go back to the future and speak about

– Our success with Power View

– All the settings in Power Pivot related to Power View

p.s.: When I refer to Power View I am referring to Power View on SharePoint. I am not referring to Power View functionality built in to Excel 2013, since that is a fairly different experience than Power View on SharePoint.

Power View Success Story

I love Power View, except when I don’t. It can feel limiting at times and frustrating, especially to an excel user (which is all of us ). After demonstrating a really slick Power View report with all the bells and whistles (check out a sample from Microsoft BI at Power View Demo. Mine don’t look as good as this), the first question I often get from the user is, “Great, now how can I export this to Excel?” And my answer is – you can’t
“Export to Excel is the third most common button in data/BI apps…after Ok and Cancel” (click for a real fun post!), and Power View does not have it. Yet! If the powers that be are reading, I think it’s feasible that an icon appears when you hover over Power View report elements, to export the underlying data in excel in a simple table format. Please consider that for the next release. Now that I am in begging mode might as well ask for – ability to re-label measures/column names in Power View Report and show numbers as Percentage of Total (like in Excel Pivots). The latter is doable using DAX but not easily so.

Okay, now let’s move on to some love ©©©

• Power View reports are easy to build, maintain and use
• Shared Power View report give you a Single Version of Truth (kinda)

Power View reports are easy to build, maintain and use

Build: Regardless of comments earlier, the best thing you can do is approach Power View with a fresh set of eyes and leave your Excel goggles behind. I had done many a report in Excel and struggled initially with Power View. But once you accept it for what it is, it’s a joy to build reports. I love building reports for our users on the fly. I would schedule a 30 minute meeting with them – in person or share my screen if they are remote. And build the report right in front of their eyes as they guide me. This is radically different than the traditional BI world (gather requirements, spec out design…blah blah) – and our users love it! There are many sophisticated features in Power View – Vertical/Horizontal Multiples, Scatter Chart Play Axis, Tiles – to name a few and many more in Excel 2013 and SharePoint 2013. However, most of our reports use the simplest elements – table, bar chart, line chart and slicers. I’m a fan of Stephen Few and his data visualization books, see his Graph Selection Matrix or better yet his book on Information Dashboard Design. In essence he makes a case for keeping things simple and that works for us.

The fact that building reports is so simple, liberates you and the BI team to focus on other elements; the ones that really matter. Spending time with users to really understand their business not just their “requirements” (read post The Ideal PowerPivot Professional), focus on your Tabular Model and measures (read book The Data Warehouse Toolkit) and bridge the last mile to make sure the reports are really being used and provide users exactly what they need.

Maintain: How many times have you opened an Excel Dashboard that you created a year or even a month ago and struggled to understand all the links, formulas and dependencies? You will leave all that behind with Power View. Another of my favorite parts is that the Power View Reports refresh automatically, no user action or excel macros involved! (As long as you schedule a refresh of the underlying tabular model, which is easily done)

Use: Users like being able to load a Power View report in the browser. They can easily open multiple copies and compare different filters on each. It feels more light weight than opening Excel. With Excel Services you can view an Excel file in your browser, but it does not feel quite the same, plus it comes with it’s limitations. Same with Excel 2013 where you can embed Power View in an Excel sheet, but it just feels clunky. Eventually it may come down to personal preference. Of course you do not have to pick one, you can pick both. We still create a few Excel reports besides all our Power View reports.

Shared Power View report give you a Single Version of Truth (kinda)

Single Version of Truth – is one the biggest clichés in BI. And I do not believe in it. It does not exist. Just like in real life, we all have our versions of reality (IMHO, nowhere does that apply more, than in marriage; but that’s a whole different blog post ).

In our group, we cannot even agree whether sales figures are up or down (okay, I am exaggerating…a little). Working with users I have often found that they each have either different sets of filters or use a different context. Some user may filter out some product or product categories. Some may look at amount including shipping and taxes while other might exclude that. Users may have legitimate need to view data in a specific way for their own use. You want to retain this flexibility, while also providing a common view that the team or organization can agree upon.

We do this simply by creating just a handful of standard reports that apply to the whole organization. For team meetings and such, these are the ones that are used. The filters and context for these reports have been agreed upon by all stakeholders.

Then we have unlimited customized reports for different teams, even for specific users. Users also have the ability to edit and save their own reports. These are tailored to the filters and context required by users for their own use. But if they need to have a discussion with a different team they need to use the standard reports instead of the custom ones – to make sure they speak the same language.

It is possible to approach this with Excel alone, but you would not get the ease and accessibility of Power View; which is a powerful combination.

Power View Settings in Power Pivot

There are tutorials abound for Power View and it’s fairly easy to just learn by doing it yourself. There is the HelloWorldPicnic Tutorial that I use in this post. MicrosoftBI TryIt page is also a good resource as it lets you explore sample Power View reports or create your own based on models provided. (However, if you do feel that a Power View 101 video tutorial would be helpful, then please leave a comment to let me know.)

Instead I will focus on helping you understand all the various settings in Power Pivot and how they relate to Power View. This I found, was much harder to piece together from all the material interspersed on the web. HelloWorldPicnic Tutorial prerequisite section would let you download the files I use.

I will cover settings in the Home, Design and Advanced tabs on the Power Pivot ribbon.

For each setting I would show you how you change the setting in Power Pivot and then show you side-by-side views of how Power View behaves Without and With the setting:-

Let me start by covering a general setting which can provide extremely useful documentation to the model and help your users in creating their own Power View reports.

Description

You can provide Description for a Table, Column or Measure; simply by right clicking the element and selecting ‘’Description”.

Power View behavior Without Description	Power View behavior With Description
Mouse hover merely shows you the name again	Mouse hover shows you the Description provided in Power Pivot Table Description Column Description Measure Description

Let us go ahead and cover the Home, Design and Advanced tabs.

HOME TAB:

Sort By Column
:

In an Excel Pivot you have a lot of flexibility as to how to sort your labels, from simple options to complex ones using custom lists. Power View (or really Power Pivot) at one point only offered alphabetical sorting. That changed with Sort By Column feature in Power Pivot v2. Simply it lets you sort a column based on ordered values specified in another column. Typical scenario would be sort MonthName (January, February,…) using a Sort By Column MonthNumber (1,2,3…).

DESIGN TAB:

Mark as Date Table

This turns on the “Date Filters” in Excel Pivots. You also need this in order to use Time Intelligence DAX Functions. This does not have a direct impact on Power View but is an important setting and is mentioned here for completeness. You need to have at least one field of Date Data Type to be able to use this setting for a table.

ADVANCED TAB:

Image URL

This can be used to display an image fetched from a URL, in your Power View Reports. This certainly adds some visual pizzaz, but we haven’t found many scenarios where we could effectively use this. You can also use an Image URL field as Tiles or Slicer.

Default Field Set

You can use this to specify the most important fields in a table so that when users click on the Table Name in Power View these fields automatically get dropped on the report. Could save you and your users quite some mouse clicks and can also be useful to guide users to the more important fields.

Table Behavior

See image below for an example of how you can specify these settings. Detailed description follows.

For the columns selected in Table Behavior, Power View displays a special icon next to the column name. I am not sure if this is helpful or confusing to the users. It would have been more useful perhaps to display such an icon next to each column in the Default Field Set, thereby giving user a visual hint as to the more important columns.

Let us discuss each setting in further detail.

Row Identifier: You can set this to the column that has a unique value for each row and is never blank or null. In database terms this would be the Primary Key for the table. As far as I understand this setting in itself does not affect anything discernable, besides enabling you to make a selection in “Keep Unique Rows”.

Keep Unique Rows: Best explained by an example. In our Items table, we have ItemID 13 and 14 which both have the Name as ‘apple’, one with Color=green and the other with Color=red.

Another typical example of keeping unique rows would be the Customer Name. You would not want to club together the data of two distinct customers just because they have the same name. To achieve this, after you specify a Row Identifier, you can select the Customer Name column for Keep Unique Rows.

Default Label:

Default Image:

Summarize By (for numeric fields)

I explain it below, but per advice from a higher voice, this setting is dead to me, as are implicit measures in general . For more, see ‘Implicit Measures’ at bottom of post When to Use Measures vs. Calc Columns or DAX Formulas for Power Pivot (Chapter 6 Introduction to DAX Measures, “Implicit” versus “Explicit” Measures).

Note that the setting you chose in Power Pivot can be over ridden from inside Power View by clicking on the field and changing the Summarize By.

If you have not been using Power View, hopefully this article would give you some inspiration to give it a try. If you have been using Power View, you should now have a better understanding of how all the settings in Power Pivot affect Power View.

Disclaimer: The opinions and views expressed in this post are those of the author and do not necessarily state or reflect those of Microsoft