Drillthrough in Vivid

Vivid provides the ability to do two things with drillthrough that are not supported by SQL Server Analysis Server (SSAS) or Excel: (1) Drillthrough on a calculated measure and (2) drillthrough in the presence of multiselect filters. In this entry you’ll learn about how Vivid successfully handles both of these.

Drillthrough on Calculated Measures

Drillthrough on calculated measures is difficult to get right for a variety of reasons (http://www.biblogs.com/2008/09/01/drillthrough-on-calculated-measures/). For example, assume the following calculation:

MEMBER [Measures].[Calculation1] AS  
   [Measures].[Internet Order Count] / [Measures].[Customer Count]

Doing a drillthrough on a calculated measure needs to return all of the rows that correspond to all of the measures that constitute the calculation. In this case we have the [Internet Order Count] and [Customer Count] measures. Vivid does drillthrough, on a calculation with a series of steps we outline here:

1. Parse and decompose the calculation, finding all of the measures in the calculation. Note that if there are calculations within the calculation then Vivid will recursively decompose the calculation.
   1. In this example there are two measures [Internet Order Count] and [Customer Count], with no calculations to recursively decompose.
2. Find the measure groups associated with each measure.
   1. [Customer Count] comes from the measure group Internet Customers and [Internet Order Count] from Internet Orders.
3. Perform a drillthrough on each distinct measure group, requesting as part of the drillthrough result set all of the measures that were used in the calculation (from that measure group).
   1. In the above example, Vivid will perform two drillthroughs, as there are two distinct measure groups.

Thus doing a drillthrough on a calculation can result in multiple drillthroughs performed, and therefore multiple sheets created (each drillthrough gets its own sheet). A single drillthrough will only be performed if all the measures in the calculation are from the same measure group.

While this works great much of the time, it does have some limitations.

1. Vivid parses the MDX expression, but for complex expressions it may be the case that a given measure doesn’t contribute any value for a given tuple. Vivid will still return a sheet to correspond to that measure/measure group.
2. Vivid always performs the drillthrough on the current coordinate. If the calculation is retuning data from a different coordinate, e.g., using ParallelPeriod, then Vivid’s drillthrough will return a potentially different set of data than expected. So be mindful when your calculations are internally manipulating coordinates. One useful feature in Vivid is that you can hover over a calculation in the pivot table editor to see its expression.

Drillthrough with MultiSelect Filters

Multiselect filters in Excel also block the standard SSAS drillthrough command. Vivid can handle them, but it is useful to understand how Vivid does them, in order to get the best performance.

First, it is important to note the inability to drillthrough on multiselect filters is an SSAS limitation, which effects Excel as a client. Given that the way Vivid performs a drillthrough with multiselect filters is by avoiding actually performing a drillthrough with multiselect filters. This is best demonstrated by explaining the steps Vivid goes through for mulitselect filters.

1. Find all of the multiselect filters and store the selected values. We will use them later.
2. Perform a drillthrough on the value, but first remove all of the multiselects from the query. Thus the drillthrough is on a query that has only single select filters (or no filters at all).
3. Filter the result from step 2 using the stored selected values from step 1.

Note that step 2 can result in a drillthrough result set that is much larger than the final result set. In theory, you could get back from SSAS a million rows (or more), but the final result set is empty (after filtering on the client). For this reason, do try to use as many single selects as possible, as each of them constrains the drillthrough on the server. Another ramification of this is, by default, Vivid does MAXROWS 10000 on drillthrough, so you may get back a dialog requesting to do a drillthrough that doesn’t cap MAXROWS, yet the final result set you receive might be smaller than 10,000.

It’s worth noting that Vivid treats slicers as a multiselect filters.

Lastly, the presence of multiselect filters does require that you specify the drillthrough columns the associated measure group. The reason for this requirement is that we need to return a custom set of drillthrough data, in order to properly filter (from step 3). Rather than underspecifying the columns that the user wants returned, we simply require the user to specify exactly what they’d like returned. Custom drillthrough columns is an extremely useful feature on its own, so the forced discovery is really serendipity.

Kang Su Gatlin