Pages

Monday, February 14, 2011

MDX Extraction of Excel Pivot Table (Excel Add-in)

In today's BI world, Microsoft Excel is the most common & widely used BI tool for reporting and analysis because of its rich and easy to use features, which most users know better than me. So lets discuss about one Excel feature which is being used by many SQL Server Analysis Services users and other BI front end users.

Microsoft Excel provides compatibility with various data sources (i.e. SQL Server, SQL Server Analysis Services,  Microsoft Query Wizard & ODBC ...). Out of these, SQL Server Analysis Services which is commonly known as SSAS, is widely used data source to browse OLAP Cube data and perform powerful analytics provided by SSAS and Excel.

When we browse olap data, excel pivot table engine creates and sends MDX query to SSAS server. In most cases, users want to see what query is being used to perform this action because of number of reasons:

  1. To debug & test data consistency issues with cube/reports.
  2. To understand query which returns complex data sets.
I have created an Excel add-on which returns MDX behind every pivot table result set. This can be installed on both Excel 2007 & 2010. Add-in is available on below link:

Download: MDX Extract Add-in

Installation & Configuration Guide

 Table of Content:
  • Installation Guide
    • Installation by using Excel add-in wizard
    • Installation by copying to Excel Installation directory  
  • Configuration/Working Guide 
  • Usage
Section 1. Installation Guide:
There are 2 ways to install this add-in in Excel.

Installation by using Excel add-in wizard:
  • Open an existing Excel workbook or create a new one.
  • Go to the office button and select Excel Options. 
  • In the Excel Options dialog box click on "Add-Ins", it will show all the existing Add-ins. In the "Manage" option choose "Excel Add-ins" and click "Go".
  • Add-Ins dialog box will open, which will show all the available add-ins that can be used in the current Excel. If required add-in available in list, then select the check box and press OK. 
  • Otherwise, browse "MDX Extract Add-in.xla" file and press OK.
  • Now "MDX Extract Add-in.xla" will be available in the available Excel Add-Ins list. Select this add-in and press OK to make it available in the Excel workbooks.
  • You may get a security warning while adding it, select "Enable".
  • Now this add-in will be available for all existing Excel workbooks and for new ones as well. 
Installation by copying to Excel Installation directory:
  • Copy "MDX Extract Add-in.xla" to "C:\Program Files\Microsoft Office\Office12\XLSTART" folder in case of Excel 2007 and respectively to the Excel 2003.
  • Now create a new Excel or open an existing one, this add-in will automatically be get added to that excel.
  • To check whether add-in is available in the current working excel workbook/sheet, select Developer tab, and click on Visual Basic Tab.
Section 2. Configuration/Working Guide:
 After the successful installation, you can now work on the tool which has very simple interface but powerful usage.
  • Open an excel workbook, and create a Pivot table report on Analysis Services cube.
  • Right click on any pivot table cell; you will get an added option as "MDX Query". 
  • Click on "MDX Query" option, 1 new sheet will get added to the existing workbook named as "MDX" with the corresponding underlying pivot table MDX in the first row and first column. After first run, whenever you will try to generate underlying MDX of excel pivot table, MDX will get added to the next empty row in the existing "MDX" sheet.
Section 3. Usage:
  • It can be used to extract underlying MDX of Excel Pivot Table which can further be used for testing and debugging the existing issues in reports/cubes.
  • It can extract MDX of protected pivot table Excel reports. (I.e. Excel pivot table has a limitation that if there is any protected pivot table then pivot table cells become read only and user cannot make any changes to it and so cannot extract MDX as well).


Readers: Kindly share your comments & suggestions.                                                      

2 comments:

  1. Hi
    I liked your addin so much

    I'm doing a addin and I'm using the property pivot cell.mdx to extract mdx formula to use in a drill down solution
    It works in excel 2010 but don't exist in excel 2007
    How can you do? There is another property?

    ReplyDelete
  2. thanks so much for posting this - really easy to use. cheers, ben

    ReplyDelete