How to enable query folding from a salesforce object

[Anonymous]

My goal is to enable incremental refresh on our data that is being imported using the power bi salesforce connector.  To enable incremental refresh, query folding must be happening.  Currently, “View Native Query” is greyed out even in the first steps of the query.  This indicates query folding is not happening.  How do I enable "View Native Query"?  Is it possible?

[Syndicate_Admin]

Good afternoon
That is, if I connect Power BI to Salesforce Objects there is no way to fold queries?
Or is it produced but partially so it would not work either?

Thanks a lot

Slds

Juampi

[lbendlin]

See my earlier response. Query folding is attempted, but you have no control over the produced SOQL code.  Like with other sources  - if you apply certain transforms then you can prevent even the attempt from happening.

[jeffrock]

Hi @lbendlin,

I am Just Loading the salesforce object and then applying filters using manage parameters for start_Date and End_Date. my enable query folding for that step is disabled. if I move further for incremental refresh will it still apply and work?

Thank you

[lbendlin]

See my earlier reply.  Use Query Diagnostics to examine the produced SOQL query.  It may actually "fold"  - ie it may produce custom-ish SOQL.

As mentioned earlier, this is not absolutely required for incremental refresh, more of a nice to have. Of course it depends on the size of the object you are accessing.

[v-henryk-mstf]

Hi @Anonymous ,

For your description, the idea behind query folding is to push the logic built into the Power BI query back to the data source server, and then execute the logic in its native language instead of client-side conversion of the data.

After clicking it, it shows the native SQL statement that satisfies the query folding rules. If a conversion that does not support query folding is introduced, such as capitalizing each word, then the native query option is disabled.

You can check the following related soltion and blog:

Solved: Re: View Native Query always disabled - Microsoft Power BI Community

Power BI - Checking Query Folding with View Native Query (pragmaticworks.com)

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

"Currently, “View Native Query” is greyed out even in the first steps of the query.  This indicates query folding is not happening."

You would think that, but it is incorrect. Query folding is happening in some limited fashion, specifically for column selection and column filtering. Custom SOQL will be generated for some of the Power Query transforms.  You can check this in the Power Query Diagnostics output - it will show the custom SOQL query HTTP call.

Since SOQL is quite different from SQL I think Microsoft decided not to use the term "Query Folding" and not to show the custom SOQL in the "View Native Query"  part.

Note that the custom SOQL is limited to in-object queries.  Relationship queries are not currently supported, so you cannot reproduce SFDC Report Types in Power BI just yet.