Oklahoma Power BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Here's how to see the actual query that is being created by a Pivot Table

  • 1.  Here's how to see the actual query that is being created by a Pivot Table

    Bronze Contributor
    Posted Mar 05, 2019 05:52 PM

    When a user connects to an SSAS Tabular or Multidimensional model via an excel pivot table to browse the model / cube, it creates an MDX query behind the scenes and sends that to the database engine. By adding a simple VBA office form to the workbook and the button with simple code below, you can then click on the pivot tale and run the script to return the MDX query behind the current state of the pivot table (highlighted text below). You can then copy and paste that into SSMS as an MDX query to run it manually and further investigate.

    Capture the MDX query behind a Pivot Table



    ------------------------------
    Abe Newton
    BI Consultant
    [Fourth Stripe Solutions]
    Guthrie OK
    4052265901
    Microsoft
    ------------------------------


  • 2.  RE: Here's how to see the actual query that is being created by a Pivot Table

    Silver Contributor
    Posted Mar 07, 2019 06:55 AM
    Fantastic tip, @Abe Newton! Thank you for sharing! Very helpful for anyone using Excel to connect either to a cube. I wonder, does it work if you are connected to the Power BI Service using the "Analyze in Excel" feature?​

    ------------------------------
    Brent Lightsey
    Analytics Consultant
    FirstLight Analytics
    Oklahoma, USA
    ------------------------------



  • 3.  RE: Here's how to see the actual query that is being created by a Pivot Table

    Bronze Contributor
    Posted Mar 08, 2019 12:14 PM
    Was wondering the same thing. Will need to test that out.

    ------------------------------
    Abe Newton
    BI Consultant
    [Fourth Stripe Solutions]
    Guthrie OK
    4052265901
    Microsoft
    ------------------------------