Relationships, Pivots, and DAX: The Payoff, Part One

Recall that in yesterday’s post I linked my FactEvent table to the CleanPlayers table via a relationship.  Time for the payoff.

But you know what?  I’m tired of calling it the FactEvent table, and then constantly having to tell everyone that’s where the plays are stored.  It’s the Plays table, damnit, so I’m just gonna right-click and rename it before moving on:

              PowerPivot Rename Table   Becomes…  PowerPivot Rename Table 2

Which does NOT impact the relationship I made.

Just in case you doubt the value of Beta releases…

I’ve actually hit a bug here where the relationship I created yesterday prevents me from saving and re-opening the file.  I’ve filed the bug and expect it has something to do with either the data cleaning I did or the fact that CleanPlayers is a linked table, because I have not seen this before.  As Dennis Miller used to say, “Well at least you know it’s live…”

Time for Multi-Table Pivots!

Showing this feature is going to take discipline, because there are a number of fun things along the way to distract me (ooh!  shiny!).  I’ll try to note things in passing and promise to return to them later.

I want a PivotTable that shows me some aggregate data.  So I go to the ribbon in the PowerPivot ribbon, click on the old reliable PivotTable gallery button, and…

PowerPivot Offers Some Shiny New Pivot Options!

…we see a bunch of intriguing options there!  But even though I designed that feature and love it like a child, I’m just gonna stick to the basics and go with a Single PivotTable :P  (I promise, we’ll come back later!)

I choose to place this PivotTable in a new sheet, and now I see this:

PowerPivot Blank Pivot

Couple things to note:

  1. The PivotTable is on a normal Excel sheet – that’s important.  The PowerPivot addin does not render a single pixel of report or visualization.  That is ALL Excel, which is good, because we want everything to render to the browser via Excel Services later.
  2. This is not your father’s field list – there are definitely some new things happening over there on the right side…
    Let’s take a closer look at the top of the field list first:
                   PowerPivot Field List Top

    Every Table from the PowerPivot Window Appears Here

    Yes, all 40+ of them.  I’ve expanded a couple of the smaller ones so you can see their columns.

    This is a big departure from normal Excel PivotTables, and their “one table at a time” restriction – if that seems limiting to the BI Pros out there, well, it is.  See what your Excel friends have labored with all these years?

    Search Box

    And since that means a whole lot more fields than your average Excel field list, we’ve got a Search control at the top (great work team, getting that in!)

    The bottom of the field list also contains some new goodies:

                 PowerPivot Field List Bottom

    The bottom four dropzones should be familiar, but the top two – Slicers – are new.  I can’t resist, so I’ll give a quick preview of those in a second.  For now, though, I want to build a quick, simple report.

    First, I check the Yards field under the Plays table:

    Clicked the Yards Checkbox  which yields:

                                                                PowerPivot 1-Cell Report in the Excel grid.

    BI Pro sez:  “OK, What Happened There?”

    If you’re a BI Pro and you’ve been taking notes, you know that under the hood, PowerPivot is SQL Server Analysis Services, and SSAS doesn’t just fork over numbers.  It needs “measures” defined first.

    Well, the addin defined one on the fly.  The user checked a checkbox, the addin sees that the field in question (Yards) is numeric, then tells the PowerPivot engine that it wants a Sum of Yards measure defined.  Once that is complete, the addin then tells the Excel OM to add that measure to the current PivotTable.

    So… the Excel user gets the experience they are accustomed to (numeric fields default to Sum’s, in the data area of the pivot), and BI Pros can understand that a real OLAP measure has been created behind the scenes.

    Moving on, I then scroll the field list down to the CleanPlayers table and check the FullName checkbox (recall that I added this field yesterday using DAX):

    Clicked the FullName Checkbox  which yields:

                                         PowerPivot Yards by Player in the Excel grid.

     

    (Translation for BI Pros – in this case, FullName was just an attribute – all columns in the PowerPivot window are attributes actually.  And since the addin detected it was non-numeric, we just slapped it on rows without creating a measure.  Which again mimics Excel behavior).

    (Note for football geeks:  Aaron Gibson, –272 yards lifetime?  And look at Aaron Rodgers, still riding the bench behind Favre back in 2006, the last year for which I have data.  BTW, don’t trust any of these numbers yet – I think I have a lot of work left to do).

    What say you, Excel Pros?

    You impressed yet?  One little 4-box relationship dialog from the last post, and suddenly you are pivoting one table by the fields in another?

    Still letting it sink in, eh?  OK, let’s drive it home.  Every field in the CleanPlayers table is now a valid field for grouping/filtering/slicing the Plays table…

    The Grand Finale (for today)

    Remember those two mysterious extra dropzones for Slicers?

     

              PowerPivot Slicers Dropzones

    Now I’m going to grab the BirthStateName field from the CleanPlayers table, drag it into the Slicers Vertical zone, and I get this…

    PowerPivot Slice By BirthState

    Yeah, that’s called a Slicer.  A new feature of Excel 2010.  Works a lot like a Page Filter (aka Report Filter), but more graphical and “Fisher Price,” to quote a former colleague.

    Wanna see just players born in sunny states like FL, AZ, HI, and CA?  Single click to select one, ctrl-click to select multiple:

    PowerPivot Sunny States

    And then my personal favorite, single-clicking England:

       English Football Player

    I give you Osi Umenyiora, the only, ahem,  English Football Player in the NFL.

    One little relationship suddenly tells us quite a bit doesn’t it?

    (It even gives us cheesy jokes that equivocate on the term “English Football.”  Such power.)

    Next Football Post:  My First Video Post >>

    10 Responses to Relationships, Pivots, and DAX: The Payoff, Part One

    1. Rob, all great stuff. A few comments:

      “…we see a bunch of intriguing options there! But even though I designed that feature and love it like a child, I’m just gonna stick to the basics and go with a Single PivotTable”

      Um, what if I want 3 charts, or more than 4? Wouldn’t a more flexible selection option been better? No biggie…

      “Every Table from the PowerPivot Window Appears Here. Yes, all 40+ of them.”

      Which in some ways is unfortunate because tables that are used for lookup purposes and have nothing to do with user interactions show up here as well…and unlike columns, you can’t hide a whole table. Mixing design elements in the user interface is never a good thing.

      “And since that means a whole lot more fields than your average Excel field list, we’ve got a Search control at the top (great work team, getting that in!)”

      Thanks indeed! That’s obviously new to CTP3. Next task will be to remove that pesky “Gemini” label in the task pane name…

      “So… the Excel user gets the experience they are accustomed to (numeric fields default to Sum’s, in the data area of the pivot),” and

      “And since the addin detected it was non-numeric, we just slapped it on rows without creating a measure. Which again mimics Excel behavior”

      These two statements indicate to me that a major development goal was to mimic Excel’s PivotTable’s behavior. But it also means that users will be surprised and confused by equivalent functionality that *doesn’t* mimic Excel’s PivotTable behavior. That’s because there’s no obvious logic to which PowerPivot features may or may not work like Excel’s PivotTable.

      • Thanks Colin.

        “Um, what if I want 3 charts, or more than 4? Wouldn’t a more flexible selection option been better? No biggie… ”

        Yep, that would be better, no doubt. The options we offered were something we managed to basically jam into the development schedule at the last minute. We knew full well that we didn’t have time to build the complete solution – we’ll undoubtedly improve this next time we rev the addin. But we didn’t want to hold off on giving you something useful in the meantime.

      • Now for the “all my tables and fields show up in field list” point. Turns out there IS a way to hide fields in CTP3. I’ll post something shortly.

      • Last point, on “it also means that users will be surprised and confused by equivalent functionality that *doesn’t* mimic Excel’s PivotTable behavior”

        Use CTP3 for awhile and then let me know what you think. We put a lot of effort into making sure users won’t be confused. Two rules in particular we set for ourselves:

        #1 – Try not to replace/modify Excel features. *Adding* features is ok. Changing or removing is not.

        #2 – We didn’t override any Excel ribbon buttons or context menus. PowerPivot addin functionality is strictly confined to the field list and PowerPivot ribbon tab.

        Those rules might not be enough to dispell every problem, of course. It still IS an addin, and one that adds a lot of functionality.

        And one other thing I have learned in my time doing this line of work: users don’t always need a perfectly sharp mathematical model in their heads in order to succeed with a feature or product. Corollary: not all surprises are problems. Good surprises are totally ok.

        …but we still strive to make and follow rules, to keep it from being chaos.

        Anyway, try out CTP3 when it comes out and let me know.

        thanks,
        rob

    2. Oh, you can hide fields in CTP2 as well, and that’s great. My point was that, unlike fields, you can’t hide tables…and that’s not too good. Actually, you can pseudo-hide a table by hiding *all* the fields, but having table names hanging in the task pane looks a bit awkward.

    3. “Anyway, try out CTP3 when it comes out and let me know.”

      Yes, I certainly look forward to testing CTP3 when it becomes available to the underprivileged. :D

      Agree with your other comments. I wasn’t specific here about those features having differences of behavior because I listed most of them in a recent post in the Excel Team Blog (under “Using PowerPivot with Excel 2010″).

    4. Hi Rob,

      Another question: what if i wanted to sort the FullName of the player by his lastname or months by monthnumber instead of the month. Is that possible?

      Thanks,
      Kasper

      • Not possible in CTP3. But the feature that would enable this is still something we are trying to do. No assurances, but I will say that most last-minute features have been denied already, while this one is still active. So it’s got a good chance.

        For others reading this comment: the idea here is to control the sort order of a field in pivot reports – display “Jan,” before “Feb” for instance by sorting the “MonthName” column by the “MonthNumber” column. Also useful in slicers.

    5. Sorting in the PowerPivot PivotTable is one of these features that I’ve mentioned previously as working differently from Excel PivotTables. Not sure what changes have been made in CTP3, but in CTP2, this very common task is about as unintuitive as it can possibly be. Definitely worth sorting out (pardon the pun) for RTM.

    Leave a Reply