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]
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…
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.
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
Hi Nick, as the post says this trick of using the presence of the cube icon only works for Analysis Services data sources, not OData data sources. The easiest way you can tell if query folding is working for OData data sources is to use the new query diagnostics functionality: https://blog.crossjoin.co.uk/2019/11/17/troubleshooting-web-service-refresh-problems-in-power-bi-with-the-power-query-diagnostics-feature/
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