Create a #PowerPivot report filtered by the Top X Users by <Logic> (Part 1 of 3)

Background

I was recently working with a customer to help them solve an interesting PowerPivot problem where they had wanted to get the Top 10 users within their data set and create reports based on that.  One approach is to create a rank function within PowerPivot as noted in a Rank function thread on the SQL Server 2008 R2 PowerPivot for Excel forum?  Another way is to create a Pivot Table where the UserID was placed in the [rows] and the measure in question in the [data] area; and sort the data from largest to smallest.

image

Figure 1: Events by UserID sorted by Events Largest to Smallest

You can even do value filter so you only see the Top 10 by right clicking on the Row Labels icon > Value Filters > Top 10

image

Figure 2: Choose Row Labels Icon > Value Filters > Top 10 to only view the top 10 values

In this hospital scenario example, the business analyst was trying to understand what are the most commonly accessed systems by the top 10 users of the system.  To do this, the analyst simply put the systems category into column labels and voila, she could identify that the ICU and the Lab systems were the most commonly accessed systems.

 

image 

Figure 3: Adding systems category, could see that the top 10 users typically accessed the ICU and the Lab systems

While this is great, there are a number of issues that come to mind.

  • It’s not a very clean looking report
  • If you have a lot (e.g. millions) of users in your data set, it would take a long time for the native Pivot Table to render this – if at all.
  • You cannot easily apply these Top 10 users by any other criteria or combination of criteria to have better insight on what is going on.

 

Solving Using Excel Named Sets

So perhaps there is another way to do this? 

Thanks to Siva Harinath (from the Analysis Services Team) for reminding me about Excel Named Sets which allow you to input your own MDX statement to query your VertiPaq database.  A great blog posting on the topic can be found at PivotTable Named Sets in Excel 2010

To do this, create a simple pivot with the measure only.

image

Figure 4: Simple Pivot with only the measure added to the PivotTable.

Leaving the cursor within the PivotTable Data area, go to the Office Ribbon and choose PivotTable Tools > Options > Field, Items, & Sets

image

Figure 5: Accessing Excel Named Sets

From here click Manage Sets… > New to create a new Named Sets.  Below is the new “Top 10 Users by Events” Named Set that I have created.

image

Figure 6: Creating an Excel Named Set to query my VertiPaq database within my workbook

The red oval call outs is for the MDX statement that I used to create my TopCount statement.  The statement in this example is

TopCount(

     {[vAuditLog_ServerActions].[UserID].[UserID].members},

      10,

      [Measures].[Measures].[Sum of event_count]

)

You will want to modify it for your scenario, you can find more information at TopCount (MDX).

Once you have created this new Named Set, it will show up under the “Sets” in the PowerPivot Field List.  Click on the checkbox for your newly created set and then you will have just to the top 10 Users by Event Count listed in your PivotTable workbook.

image

Figure 7: Top 10 Users by Events using Named Sets

What is great about using Excel Named Sets is that it will only show the top 10 users.  So if you have millions of users, Excel will not be spending its time to render this information.  It will simply show you those Top 10 users and you’re good to go.

But if you look at Named Sets, one of the issues with them is that you can only put them in the Column Labels and Row Labels.  That is, you cannot filter by them so you can look at the most common departmental systems that the top 10 users are accessing.  Another business problem that may be easier to relate to (with the similar business logic to this hospital departmental scenario) is what are the most watched TV programs of the top 10 markets (i.e. cities). 

Discussion

Now, you have the option to filter all the data by these top 10 users.  Since the most recent PivotTable (Figure 7) has the top ten UserIDs, you could create a calculated column using the DAX IF function to exclude all UserIDs that are not listed in the above PivotTable.  e.g.:

if(

      ([Table].[UserID].members = 52159102 || …

       [Table].[UserID].members = 54159102),

      1,

      0

)

This DAX statement would create a new column in your table where 1 indicates the user is in the “Top 10” and 0 – the user is not in the “Top 10”.  This would be a new column within the PivotTable Field list that you could then choose and filter by 1 so that all queries to the pivot table are now filtered by the Top 10 users.

The other options include

  • To query the original source system to get your Top 10 users, import it in as a new table in the PowerPivot window, and then join to that table.
  • Use a CUBESET function to query the Top 10 out of the VertiPaq database directly.
  • Another option is to use VBA to create your named set and then you can filter on the VBA function

The reason I haven’t blogged either of these techniques more in-depth is because in the next blog posting, we will cover how to “filter” by using the Named Set you just created.  In the third posting of this series, we’ll also cover how one can dynamically change the criteria for the Top 10 Users (e.g. also filter by state, year, other columns in your data source, etc.).

The next blog posting is: Create a #PowerPivot report filtered by the Top X Users by <Logic> (Part 2 of 3) which will be available shortly.

HTH!

12 Comments

  1. Hi Denny,

    Looking forward to your continued discussion on Named Sets.

    I’d like to know how you got TopCount to order the set. Back in December, I posted a question here about TopCount in Named Sets:

    http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/58b0e35e-131b-494a-a0c8-375dbdca3806

    Essentially, unless I ordered manually in the PivotTable, I couldn’t get TopCount, with or without ORDER (which I added to TopCount in desperation), to order the set appropriately.

    A pity that Excel can’t create a dynamic TopCount set (or any dynamic set) automatically. It’s unlikely that target Excel users
    will find much use for Named Sets. Static sets aren’t particularly useful (even something “static” like a core product group can change over time), and there aren’t many Excel analysts or experts that know much about MDX. Then again, Named Sets weren’t designed explicitly for PowerPivot users, so the true beneficiaries are BI Pros.

    I’ve found Named Sets useful also for simulating user hierarchies.

    Colin

    1. Hey Colin,
      I haven’t figured out how to order it either – I can get the TopCount with the named set but its unordered until I tell it to sort manually. The good news is with the TopCount, it is truly the top 10 values. But yeah, I do agree with all your points on this, eh?!
      Denny

  2. […] blog posting is a continuation of previous post, Create a #PowerPivot report filtered by the Top X Users by (Part 1 of 3), where I had created an Excel Named Set based on the Top 10 Users by Events as noted in Figure […]

  3. […] Leave a Comment Tags: DAX, Excel This blog posting is a continuation of previous post, Create a #PowerPivot report filtered by the Top X Users by (Part 1 of 3), where I had created an Excel Named Set based on the Top 10 Users by Events as noted in Figure 1.  […]

  4. […] blog posting is a continuation of previous post, Create a #PowerPivot report filtered by the Top X Users by (Part 1 of 3), where I had created an Excel Named Set based on the Top 10 Users by Events as noted in Figure […]

  5. […] Leave a Comment Tags: DAX, Excel This blog posting is a continuation of previous post, Create a #PowerPivot report filtered by the Top X Users by (Part 1 of 3), where I had created an Excel Named Set based on the Top 10 Users by Events as noted in Figure 1.  […]

  6. […] esteemed colleague, Denny Lee, has found his way into the deep end of the pool lately with a series of posts about Top N PowerPivot […]

  7. […] Lee’s series of posts on Top N reports goes a different direction than what I have here.  He defines a Named Set in […]

  8. […] a lot of digital ink lately on the wonders of cube formulas, an alternative to pivots.  And Denny’s recent series of posts may suggest to you that you can’t really use Pivots effectively in Top N scenarios without […]

  9. […] about the need of named sets for Top 10 calculations within Excel?  Well, as noted in the posting Create a #PowerPivot report filtered by the Top X Users by <Logic> (Part 1 of 3), my concern here […]

  10. […] Create a #PowerPivot report filtered by the Top X Users by <Logic> (Part 1 of 3) […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s