locked
Pulling data from SSAS - via Power Query is slow, via PowerPivot is Fast RRS feed

  • Question

  • When I try to pull data from a SSAS Data Model via Power Query, the Performance is significantly worse than when I try to pull data directly from Powerpivot. 

    PowerPivot seems to use MDX whereas Power Query I suppose uses M (or maybe something else behind the scenes) but pulling via PowerPivot is just significantly faster. 

    Why is that?

    Friday, October 7, 2016 8:20 PM

Answers

All replies

  • Power Query also uses MDX to pull data from SSAS, but it's possible that we're not generating optimal MDX. To understand the difference, we'd probably need to know the MDX query emitted by PowerPivot, the MDX query emitted by Power Query and the actual text of the M query itself.
    Tuesday, October 11, 2016 5:46 PM
  • Power Query also uses MDX to pull data from SSAS, but it's possible that we're not generating optimal MDX. To understand the difference, we'd probably need to know the MDX query emitted by PowerPivot, the MDX query emitted by Power Query and the actual text of the M query itself.


    I've tried to copy the MDX generated by PowerPivot into Power Query as well but even that seems to be significantly slower than Power Pivot. 

    Also, how do I see the MDX generated by Power Query?

    Tuesday, October 11, 2016 7:10 PM
  • If you enable tracing from the diagnostics menu, it should be written into the trace output. Otherwise, you could use a profiler against SSAS to see it.

    I know of no specific reason why PowerPivot's MDX query run from inside Power Query would be noticeably slower. Here, too, a profiler against SSAS would be useful and/or interesting.

    Tuesday, October 11, 2016 7:24 PM
  • I've just come across the same issue

    50,000 rows,  2 minutes using Power Query,  12 seconds using MDX into Power Pivot

    Wednesday, October 19, 2016 7:25 AM
  • When filtering SSAS sources in Power Query, there are 2 different types of filters: Text filters and member filters. I found that member filters will (almost) always fold, while text filters could be the cause for slow performance.


    Imke Feldmann TheBIccountant.com

    Wednesday, October 19, 2016 9:49 AM
  • The member filter will result in a filter expression that uses: Cube.AttributeMemberId

    Table.SelectRows(PrevStep, each (Cube.AttributeMemberId(...

    and will fold, as it sends MDX statements to the source.

    If you've used a text filter instead, your filter expression will look like an ordinary column expression:

    Table.SelectRows(PrevStep, each [ColumnName] = ...

    In this case query folding cannot be guaranteed, as during the lazy evaluation process a complicated optimization algorithm will run which sometimes decides to fold and sometimes decides that it would be better to pull the whole table into PQ and do the filtering there. This is probably what you're experiencing at the moment.

    There is a nice solution that helps you create dynamic member filters, that will fold:  https://social.technet.microsoft.com/Forums/en-US/b8f3d6be-ed1e-4eb7-82e7-bd3bab3c7b47/ssas-source-query-how-to-filter-with-multiple-members-from-a-table-?forum=powerquery

    You might find this article helpful as well: https://blog.crossjoin.co.uk/2015/01/13/a-closer-look-at-power-queryssas-integration/


    Imke Feldmann TheBIccountant.com

    Wednesday, October 19, 2016 2:27 PM