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:


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:


[Thanks to Jure Jaklic for pointing this out]

5 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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

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