Binding Excel to a custom MDX query supporting PivotTable filters

Chris Webb wrote this post: Binding an Excel table to the results of an MDX query.  In a comment at the bottom, Scott Gall suggested using VBA.  I want to take this one step further to allow custom MDX to be filtered by the filters from the Pivot Table Field List, while not actually using a pivot table (similarly to formulas mode).

This addresses the following limitations of pivot tables/formulas mode …

  • Performance.  MDX generated by pivot tables can be up to 10 times slower than custom MDX.  I haven’t got any hard and fast numbers, but I think the discrepancy is greater in tabular than multidimensional – especially when crossjoining large hierarchies (or nesting in the Pivot Table Field List).
  • Custom calculations that are relevant only to a single report, and therefore don’t warrant being built into the cube/tabular model.  We can tackle this using the OLAP Pivot Table Extensions, but there are still some cases where we might want calculated members on hierarchies, or custom layout of members on axes, etc. for which it would be nice to use custom MDX.
  • Excel formulas on the results.  Formulas mode often works well in this scenario (that’s what it’s for), but
    • Large data sets in formulas mode perform worse than pivot tables, let alone custom MDX.
    • Formulas mode doesn’t work with a variable number of rows depending on filter selection.  Sometimes Excel formulas can be great for things like what-if analysis, etc. in this situation.
  • Multiple charts/grids sharing the same filters.  We can use slicer buttons for this, but we may still be on Excel 2007, or it might just be a report that is better suited to filters than slicer buttons.
  • Endless other customizations.

This Excel file does the above.  Things to note …

  • Don’t get too excited about the code.  It is VBA.
  • It supports multi-value filters.
  • It gets the connection info from an existing connection (Data tab > Connections) called “AdventureWorks”, which you will have to configure.
  • Place custom MDX in the Query tab with the <<Filters>> placeholder in the where clause as per the example.  You can then hide the Query tab from the users.
  • The sample query provided was written against the tabular Adventure Works.  You will have to change it if using the multidimensional Adventure Works or a different database.
  • Check the RefreshButton_Click() macro and change the constants at the top as required.  For example, you will need to change the intFirstReportRow constant if you add more filters.
Advertisements

3 Responses to Binding Excel to a custom MDX query supporting PivotTable filters

  1. Thanks for your sample file! it saved me quite a bit of time!

    One question: I’m using Multidimensional Cube and my query returns column names such as [Measures].[Actuals Y-0] which is how I called the Query scoped calculated member with the “WITH MEMBER” statement. Any idea how I could have it return just “Actuals Y-0” as your sample does (with your measure names) ?

  2. I forgot to ckeck the “notify me by email” so please respond to this comment (I checked the checkbox this time). Sorry about that.

    • Christian says:

      Hi David,

      I would have expected the VBA code to reformat the column headers even if they are calculated measures using WITH MEMBER.

      I’m not sure if this is the problem, but I believe the code depends on the measures using full square brackets like [Measures].[xxx] instead of Measures.xxx or Measures.[xxx], etc.

      You probably need to step through the VBA code just under
      ‘ Reformat column names

      I hope this helps,
      Christian

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

%d bloggers like this: