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 Smile). 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 Sad smile
“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 Smile).

    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.
    Power Pivot Home, Design and Advanced ribbon tabs

    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:-

    Power View behavior Without <setting>

    Power View behavior With <setting>

    sample

    sample

    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 Pivot right click to provide Description

    Power View behavior Without Description

    Power View behavior With Description

    Mouse hover merely shows you the name again
    Power View behavior without Description

    Mouse hover shows you the Description provided in Power Pivot

    Table Description
    Power View behavior with Description

    Column Description
    Power View behavior with Description

    Measure Description
     Power View behavior with Description 

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

    HOME TAB:

    Sort By Column
    Power Pivot 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…).

    Power Pivot Sort By Column

    Power View Without Sort By Column

    Power View With Sort By Column

    Probably not the way you want to Sort Month Name
    clip_image003

    clip_image004

    DESIGN TAB:

    Mark as Date Table
    Power Pivot 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.

    Excel Without Mark as Date Table

    Excel With Mark as Date Table

    clip_image006

    image

    ADVANCED TAB:

    Image URL
    Power Pivot 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.

    Power View Without Image URL

    Power View With Image URL

    The URL itself is displayed…ugh!
    image

    As Table
    clip_image012

     

    As Slicer
    clip_image013

     

    As Tiles
    clip_image014

    Default Field Set
    Power Pivot 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.

    Power Pivot Default Field Set

     

    Power View Without Default Field Set

    Power View With Default Field Set

    If you left click on the Table name (Items in our Example) => Nothing happens

    When you left click on Table name Items
    clip_image017
    The default field set is dropped on the canvas
    clip_image018

    Table Behavior
    Power Pivot Table Behavior

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

    Power Pivot Table Behavior

    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.

    Power View Without Table Behavior columns selected

    Power View With Table Behavior columns selected

    image

    image

    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.

    image

     

    Power View Without Keep Unique Rows

    Power View With Keep Unique Rows

    Only one row is shown which includes the total for ItemId 13 and 14 (Name = apple)
    clip_image023

    Notice the two Items are shown separately. This alerts you that there are different items with the same name.
    image

    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:

    Power View Without Default Label

    Power View With Default Label

    When displayed as card all fields have similar display
    clip_image025

    Power View emphasizes the field designated as ‘Default Label’. It also does not show the column name (Name in this case) giving a cleaner look.
    clip_image026

    Default Image:

    Power View Without Default Image

    Power View With Default Image

    clip_image027

    Image is displayed slightly larger and without the column name resulting in a cleaner look.
    clip_image028

    Summarize By (for numeric fields)
    Power Pivot Summarize By

    I explain it below, but per advice from a higher voice, this setting is dead to me, as are implicit measures in general Smile. 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.

    Power View Without Summarize By (Do Not Summarize) setting in Power Pivot

    Power View With Summarize By (Sum) setting in Power Pivot

    No icon next to the field.
    clip_image030

    When you drop the field it is not summarized.
    clip_image031

    clip_image032

    This would likely produce a very unexpected result.
    clip_image033

    Columns where you have explicitly specified a Summarize By appear with a å icon.
    Note that measures appear with the calculator icon.
    image

    When you drop the field it is summarized as chosen in Power Pivot.
    clip_image035

    Note below that you can still change the behavior of Summarize By as you wish on the Power View sheet.

    clip_image036

    Qty Served is now showing the Sum Total for each row
    image

    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

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

    1. cysiek says:

      Nice post :)

      PowerView looks nice, but it’s still beta application for me.
      I like PowerPivot (especially when I got SSD and 16gb of ram, and Excel crash only once-twice a day now), but our business required something more entertaining.
      Our company is MS partner, so we didn’t have to buy special licence and we use SharePoint a lot. I have recently created several PowerView reports so here are my fresh remarks:

      1. No quit button, you have to quit browser.
      2. Sometimes if you make change in your data model and click refresh on PowerView, you don’t see them. The only solution is reopen tab.
      3. No “View Only” option for viewers. You have to give “Read Only” right to audience and here is fun part. All viewers have “Edit Report” option enabled and can see measures, fields, chart etc. It is possible for them to change entire report but when click Save, beautiful exception with error appears saying, that you don’t have permission. Masterpiece of development.
      4. No normal aligning options like snap to something. You need to have good eye when arranging items. The only option is “Bring forward/backward”
      5. No custom colours/styles. You can only change background. Some random issues when changing theme.
      6. Changing a font is lottery. Sometimes it gets saved, sometimes not. Very frustrating.
      7. It’s not possible to have two series types on one chart. Like bar and line.
      8. Fonts looks different on different systems/web browsers causing reports not to be the same. Especially annoying when you placed everything on proper pixel location.
      9. When you have chart type Card Callout style, picture on report load gets smaller than they should. After you select some value it gets normal. It was blocker for me, since I wanted custom KPI icons and managed it by doing measure with filter on table with pictures.
      10. It’s slow. Our SharePoint farm contains 5 servers with about 100GB of ram.

      My conclusion is: PowerView is cool and nice only if you use for very simple reports, with standard themes. When you do something complex,you would be frustrated. If I have to pay for such a tool I will call for refund.
      I want to end with something optimistic, so: Scatter Chart rocks :)

    2. Avichal Singh says:

      Kudos on ending it on a happy note! Some comments/thoughts on some of the items:-
      1. The new version can do hyperlinks. Not quite the elegant way, but I suppose we could create a Quit or Return hyperlink in the report.

      2. I have not experienced this. If I just click the blue refresh button in Silverlight interface inside the window (next to Save, Undo and Redo buttons) then it just reloads the data not the model changes (that’s as per design). But if I reload the page I see the changes in the model without fail. We are using an SSAS Server to host our model. Are you using a PowerPivot Excel file uploaded to your SharePoint? I think I remember experiencing what you described when using PowerPivot Excel file.

      3. is indeed embarassing. I hope it gets fixed/addressed in some way.

      10. It is indeed slower than I would like. I have grown weary of the blue circle of death which shows up when waiting for Power View to render/refresh! At times the same table when pulled in Excel by connecting to the Tabular Model is snappy fast but the same PowerView report takes much longer to load. At one time I had run a trace on our SSAS Server to try to see what’s going on, and I found out that Excel Pivot and Power View when pulling the exact same table (row/columns/filters) were sending different queries back to the SSAS Server. That part is understandable, what is unfortunate is that somehow the queries generated by Power View seemed a lot more inefficient.

      The rest of the points are true and I agree that Power View does feel rough around the edges. However we have been able to have a successful rollout in spite of these challenges. And the newer version released with SharePoint/Excel 2013 has quite a few new features and fixes (we have not upgraded to the newer version yet). So I suppose there is still hope :-)

    3. Nero says:

      nice post. lots of useful info. Any idea how we can deliver a ‘canned’/’static’ report so that users cant make any changes to the filters?

    Leave a Comment or Question