Configuring “drill to details” behavior in PowerPivot: Part 1

It has been said there is no way to change the default drill through behavior in PowerPivot.  However, if we look closely we discover that we can actually exercise a bit more flexibility than normally assumed when using this feature.

The first thing to understand is that drill through retrieves data from whichever table the measure has been assigned to.

This is an interesting feature – yet, not very intuitive –:  as it is possible to assign a DAX measure to any table in the model (even disconnected ones) without affecting the output of the measure at all, one can allocate measures to tables specifically designed for drill through.

Lets look at an example using Adventure Works.

image

[Sum of SalesAmount] is an implicit measure.  As such, it was allocated to the table with numeric values (FactResellerSales). Notice this table is on the many-side of the context propagation.  When executing a drill through, we get an ‘exploded’ view of that table showing raw data:

image

Also notice that many technical columns are retrieved, which may not provide any value to the analyst (ResellerKey, for example) .  Instead, lets create a custom table to support a more customized drill through view, with only the columns we are interested in.  I will use the Table Import Wizard instead of writing straight SQL, as I believe this is something analysts will be able to configure on their own without the need to code:

image

By specifying the use of ‘Group and Aggregate’, we will get a list of the distinct product and order date keys in our fact table. As we might not interested in the original granularity as we are simply supporting a drill through action in order to show what dimension members make up a specific sales amount, a list of product and date members is sufficient in this case.

image

We can source the desired columns from the lookup tables by using RELATED. Or, to save memory space or for model usability, you may decide not to bring those columns on the visible lookup tables and only retrieved them from the source to populate the custom drill through table.

All columns should be hidden (do not hide the table, only the columns.  Hiding the table will hide the measure we will assign to it which is not what we want).

An explicit DAX measure is created at this point:

[Sum of Sales]:=SUM( FactResellerSales[SalesAmount] )

and assigned to the ‘drill through sales’ table:

image

As only the measure is visible on this table, the pivot table field list will not show the regular table icon but one indicating the summation sign:

image

When clicking on the cell we clicked before, this time we get a more ‘customized’ drill through:

image
Note the keys connecting to the related tables are hidden, they are never retrieved on the drill through view.  This is good, as they are technical and not needed for analysis.

The order of the columns depends on the order in which you added the RELATED columns on your custom drill through table.  Simply moving the column to another position won’t change it on the drill through view.  To change order, it is necessary to re-create the order you wish to see.

In order to custom drill through views to work, the default context propagation (from the one-side to the many-side) must be respected. As such, if you want to provide a small drill through experience from the visible lookup table you should snowflake the model instead of connecting both tables (visible lookup and custom drill through) to the fact table separately:

image      image

Note: in Excel 2013, there appears to be no way to change the default behavior of 1000 rows retrieved (this was not the case in Excel 2010).

About these ads

5 Responses to Configuring “drill to details” behavior in PowerPivot: Part 1

  1. prez02 says:

    Can you still right click on the table and Change the query code in order to change the DRILLTHROUGH MAXROWS 1000 parameter to a different value and then refresh?

    • javierguillen says:

      In Excel 2013, the drill through connection is not shown anymore, which effectively removes this possibility. I am not aware of a way to do it in that version.

      • Carsten Bieker says:

        That is very inconvenient, to put in nicely.

        I have read somewhere it its a bug and MS is looking into it. Apparently it is possible to open the file in Office 2010 and then change the default value,and this new value is kept when you open the file in 2013 again.

        For me, another (small) reason not to upgrade in the immediate future, somehow I feel MS has not got it right this time.

      • javierguillen says:

        Carsten,

        Yes, I totally agree: this is something I hope will be changed in the future.

  2. prez02 says:

    Just for the record, maybe this is already common knowledge.

    Apparently there is a way to change the drillthrough behavior. Rename the Power Pivot file to zip and look for connections.xml in the xl folder and change the value in there.

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

Follow

Get every new post delivered to your Inbox.

Join 67 other followers

%d bloggers like this: