Denny Lee

Background

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.  What is handy about using named sets is that it will only render the top 10 rows as opposed to the millions of rows of users this data set has.  While it is easy to show the Top 10 users, it is not straightforward to filter by those Top 10 users because Excel Named Sets cannot be used as a filter.  Example reports with  this type of business logic include “most common hospital departmental systems that the top 10 users are accessing” or “what are the most watched TV programs of the top 10 markets (i.e. cities)”.

Figure 1: Top 10 Users by Events using an Excel  Named Set

In the second blog posting of this series, Create a #PowerPivot report filtered by the Top X Users by (Part 2 of 3), I showed how to filter by this same named set in a static fashion.    In this third part of this blog series, let’s figure out how to filter by a named set in a dynamic fashion.

“Filtering” by the Named Set (Dynamic)

So the ask here is how can I “filter” by the named set that I just created.  One potential idea would be to create a linked table using the PivotTable containing the Named Set which you can then join to and filter by.  Alas, for SQL Server 2008 R2 PowerPivot, creating a linked table against an existing PivotTable is not supported.  But while you cannot create a direct link table, you can create an indirect link table.   That is, use Excel expressions to create a new regular table based on your PivotTable and then create the linked table based on the former. Thanks to Bradley Ouellette from the Analysis Services for reminding me of that trick!

Figure 2: PivotTable created based on the Top 10 Named Set with slicers and regular table (red).

As you can see in Figure 2, a PivotTable based on the Top 10 Users by Events Named Set has been created and a number of slicers have been included like year, state, database and department (server_group).  Any time I click on a slicer, a different listing of UserIDs are shown in my PivotTable.

The red oval is a regular table that uses Excel expressions to copy the data from the PivotTable.  The Excel expression for the first row of the UserID table with the value 112103102 is simply the Excel expression:

=D12

This is repeated for all ten rows of the PivotTable.

Figure 3: PivotTable, Regular Table, and PowerPivot window all noting the same set of UserIDs

Now I have a regular table which I can then create as a Linked Table within the PowerPivot window in Figure 3.

Figure 4: Department / Database Report filtered by Top 10 Users (of all users within the data set)

Based on the top 10 users within the entire data set (all states, all years), these ten users managed to create almost 28.6 million events for the Lab department and General database.  But now I want to view the top 10 users for only Washington State.  To do this, I can go back to my PivotTable created based on the Top 10 Named Set and click on the State slicer.

Figure 5: Altering the PivotTable created based on the Top 10 Named Set to include only Washington State.

Notice how clicking on the WA state code updated both the PivotTable and the linked regular table with a different set of UserIDs. Now that you have the new UserIDs in your Pivot Table, click on the Update All button

Figure 6: Click on the Update All button to refresh the linked data set within the VertiPaq database.

The linked table within the PowerPivot window will now be updated with the new values.

Figure 7: Linked table within the PowerPivot window updated based on the regular table, based on the PowerPivot table for your Top 10 users named set.

Now you can go back to your original Department / Database Report filtered by Top 10 Users (of all users within the data set).  Note you may need to refresh the Top 10 users filter so that way you will only include the ten users.  Note the last checkbox is not checked which represents the users that are not in the top 10 list.

Figure 8: Top 10 Users Named Set within the PivotTable Filter

As well, it may be necessary to click on PivotTable Tools > Options > Refresh to ensure any schema changes to the pivot due to the change of the Top 10 are taken into account.

Once these steps are done, you will notice the same Department / Database report has been updated for the top 10 users of Washington state.

Figure 9: Department / Database Report filtered by Top 10 Users (of Washington State users)

Discussion

While not the most straightforward way of doing this by making use of named sets and regular tables linked to a PivotTable, you can dynamically change your report to filter by different set of Top 10 users by clicking on the slicers (and the extra few steps). 

Enjoy!

Background

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.  What is handy about using named sets is that it will only render the top 10 rows as opposed to the millions of rows of users this data set has.

Figure 1: Top 10 Users by Events using an Excel  Named Set

While it is easy to show the Top 10 users, it is not straightforward to filter by those Top 10 users because Excel Named Sets cannot be used as a filter.  Example reports with  this type of business logic include “most common hospital departmental systems that the top 10 users are accessing” or “what are the most watched TV programs of the top 10 markets (i.e. cities)”.

“Filtering” by the Named Set (Static)

As noted in the figure above and the previous blog, with your named set you have a list of the Top 10 Users by Events.  Now you want to filter your data by those ten users.  A quick way to do this is to simply copy/paste the top ten values from the PivotTable you created to a new PowerPivot Table.  To do this, copy the entire table and then open the PowerPivot window.  Because there is data in a tabular format within the clipboard, the Paste from Clipboard panel will be active and you will be able to click the To New Table button

Figure 2: Copy and Paste the PivotTable data into a new PowerPivot table

Once you’ve clicked on the To New Table button, you will be then provided the Paste Preview dialog (right side of Figure 3) so you can create a new table within the PowerPivot Client window.

Figure 3: The original PivotTable of Top 10 Users (left) and creating a new PowerPivot table (right)

Don’t forget to create your relationship between the UserIDs within the pasted table.  In this example, this is the Row Labels column within your “Top 10 All Users by Events” table which contains the top 10 UserIDs and the UserID column within your fact table.

Figure 4: Creating the relationship between the fact table and the related lookup table.

Note: You probably want to actually rename the “Row Labels” column to UserID so its more easy to understand but I kept the “Row Labels” column here for the purpose of this example.

Now that you’ve created the relationship, let’s go back and create the report of interest – in this case this is a department / database report as noted in Figure 5.

Figure 5: Department by Database Report

So how do I filter this by my Top 10 users Named Set?  Drag the Row Labels column Top 10 All Users by Events to the Report Filter pane of the PowerPivot Field List.  Open up the filter and note the last checkbox is not checked which represents the users that are not in the top 10 list.

Figure 6: Top 10 Users Named Set within the PivotTable Filter

Once these steps are done, you will notice the same Department / Database report has been updated for the top 10 users of Washington state.

Figure 7: Department / Database Report filtered by Top 10 Users (of Washington State users)

Discussion

So now you have the ability to filter by your Top 10 users for your reports.   This will get you a long way but every time you have a new Top 10 set of users, you’ll need to update the original table and/or create a new table and perform more relationships to do this.

In the next blog posting Create a #PowerPivot report filtered by the Top X Users by <Logic> (Part 3 of 3), I’ll go over how to setup the Top 10 named set so it is dynamic in nature so that way you can change the top 10 criteria and with a few button clicks automatically update the report based on that criteria as well.

Enjoy!

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.

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

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.

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.

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

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.

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.

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!