Query Folding in Dynamics 365 CRM

[IanR]

Hi,

Is query folding possible with an ODate connection to Dynamics 365 (CRM Online)? All my queries have the View Native Query right-click option disabled, which leads me to believe that query folding is not happening. My model takes a long time to refreesh so I would like to enable query folding, if possible. 

Thanks

Ian

[v-yuezhe-msft]

@IanR,

Custom SQL queries cannot be run against the Dynamics 365 online data source, thus query folding is not possible and the view native query option is disabled.

In your scenario,  multiple steps in Query Editor can make the refresh slower. I would recommend you uncheck the following options and check if it helps.



Regards,
Lydia

[IanR]

Hi Lydia,

I'm afraid that unchecking those settings didn't work. If anything it took longer; previously the refresh had taken around two hours, after unchecking those settings it took somewhere between 2 hr 20min and 2hr 40 min (hard to be exact because unless your watching the screen when the refresh finishes there's no way to tell the exact time that it finished).

I am astonished that it takes this long. Power BI is the recommended reporting platform for Dynamics 365 and my main data table is just over a million records, which I keep reading is not supposed to be a problem for Power BI. And in some ways it's not. Once the data has been downloaded the measures calculate very quickly so having a million plus rows IN in the model isn't a problem - it's getting them there that is so painful. The model fails to autorefresh when uploaded to the PBI Service and I've been told by two support engineers from Microsoft that this is because it takes more than 58 minutes. After that, apparently, unless you have the Premium license your security token expires and the refresh fails. How can Microsoft recommend a reporting platform that isn't going to work for people who can’t afford Premium – which is most of the target market for Dynamics 365 and Power BI?

Very unhappy!

[20belowgump]

Hi Ian,

I'm having this exact issue: No query folding for OData connections/Nav web services. 

Did you ever find a solution?

It is already slow with one year of data. I need query folding. 

Thanks in advance,

Nick 

[IanR]

Can I just confirm something?

At moment my first steps for each table that I import are to remove the columns that I don't need and filter out the rows that I don't need. I am doing this promarily to try and speed the refresh up. Is this pointless? In the absence of query folding does does everything have to be downloaded, all columns and all rows, before antything can be removed? Which means that I can be squeaky clean with best practice but it won't make any difference to the refreesh time?

[v-yuezhe-msft]

@IanR

It will reduce the refresh time after you remove unwanted columns and rows. And yes, you need to import all columns and rows , then remove the columns and rows you don't need.


Regards,
Lydia