How To Tell Whether Query Folding Is Taking Place When Importing Data From Analysis Services In Power BI And Excel Power Query

As a quick follow-on from last week’s post on how to detect whether query folding is taking place when importing from OData data sources, if you’re importing data from Analysis Services you have a similar problem: how do you know whether query folding is taking place? Ensuring that query folding takes place for as many of the steps in your query – especially those that filter or otherwise reduce the amount of data returned – is very important for data refresh performance.

Although the Power Query engine generates MDX queries when importing from Analysis Services in the same way it generates SQL queries when it imports from a relational database, the View Native Query option doesn’t work for Analysis Services data sources. You can of course use a Profiler trace or xEvents to see the MDX, but for most users that will not be an option for security reasons. The UI does tell you in another way though. If query folding is taking place for a step, then the Cube Tools menu on the ribbon (with the Add Items and Collapse Columns buttons) will be available, and in the top right-hand corner of the table in the results area there will be a cube icon:

image

If query folding is not taking place for a step (even though it might be taking place for previous steps in the query) then the Cube Tools menu will not be visible, and the cube icon will be replaced by a table icon. For example, in the following screenshot an Index column has been added to the query shown above, so query folding is not taking place from this step on:

image

[Thanks to Jure Jaklic for pointing this out]

8 responses

  1. When importing from a multidimensional cube there is a 2.0 MDX engine that can increase performance by 50%. Source = AnalysisServices.Databases(“cube”, [TypedMeasureColumns=true, Implementation = “2.0”]).

    Any news on when Microsoft will provide basic MDX Calcs on live connections without having to import?

    • I know – in fact I think you told me a few months back. However, having talked to the dev team, it sounds like it’s not finished and isn’t properly tested, which is why I haven’t blogged about it. I think it’s mean to be NDA too 🙂

      No news on MDX calcs on live connections I’m afraid…

  2. Pingback: Is Query Folding Taking Place? – Curated SQL

  3. Is there a way to connect Excel’s PowerQuery to Power BI online model (Dataset), so you could hava a excel tabel report from Power BI models?

    Maybe use the same connection as Analyze in Excel from Power BI Pro uses?

    • It should be possible using XMLA endpoints, but they are only available in Power BI Premium. It might also be possible with the “Analyze in Excel” connection too but this won’t be supported.

  4. Hi Chris,

    None of my Odata feeds seem to allow query folding. I have the table icon, not the cube icon. There is
    no step that “breaks” the query folding, it just isn’t there right from the start. The “View Native Query” option is greyed out for every step.

    I’ve even tried some sample Odata feeds and none of them seem to allow query folding.

    I’ve tested with an ODBC connection and the “View Native Query” option is not greyed out, so query folding appears to be working.

    Could there be any setting that prevents Odata query folding? Any other ideas? I’m tearing my hair out.

    Thank you in advance,
    Nick

  5. Hi Chris,

    None of my Odata feeds seem to allow query folding. I have the table icon, not the cube icon. There is
    no step that “breaks” the query folding, it just isn’t there right from the start. The “View Native Query” option is greyed out for every step.

    I’ve even tried some sample Odata feeds and none of them seem to allow query folding.

    I’ve tested with an ODBC connection and the “View Native Query” option is not greyed out, so query folding appears to be working.

    Could there be any setting that prevents Odata query folding? Any other ideas? I’m tearing my hair out.

    Thank you in advance,
    Nick

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: