Query Folding with Salesforce

Background

First some background to both Salesforce and Query Folding. Salesforce is a popular cloud enterprise software provider. Which, more importantly for this post, has a Power Query connector. It actually has two, the object and the report connector. However this only applies to the object connector. The object connector allows connection to the underlying tables that sit behind salesforce. Querying salesforce is done with a SQL-like language called Salesforce Object Query Language [SOQL]. You can see easily with the Salesforce Workbench.

Now for the folding, I’ve mentioned query folding before where you can see it with a more traditional query to a SQL server. A major difference between a SQL server datasource and Salesforce is that you cannot see the native query. This means it doesn’t look like folding is taking place and it is difficult to investigate with just Power BI alone.

However it is not it impossible to see if has been folded or to see the queries being sent. We can do this with either the Power BI query trace, or through analyzing the packets sent to Salesforce with a tool like Fiddler. I prefer using the query trace and will do so for this post. To enable this go into options then diagnostics and tick enable tracing. Keep in mind that this setting resets when you close Power BI.

Once this setting is enabled the next time you load data using the query editor several files will appear in the Power BI traces folder. Within that folder there will be some log files, open the ones that start with “Microsoft.Mashup.Container.NetFX45” then search for “Engine/IO/Web/Request/GetResponse” in those files. You should be able to see some lines that contain something like

https://eu16.salesforce.com/services/data/v29.0/query?q=

This is the SOQL query that is being sent to Salesforce. The number and region code before salesforce.com will depend on your salesforce instance. From this we can see if changes made in the Query Editor are being folded to the source.

The folding

Only a small set of functions can be folded back to Salesforce, basic operations such as filtering rows, selecting columns. As well as sorting, limiting and grouping rows. Some functions such as creating new columns which do get folded with SQL server do not get filtered for Salesforce.

For an example I have loaded the Salesforce sample data that comes with new development environments and then constructed a query that takes advantage of a lot of query folding. I have reduced the number of columns, filtered and sort rows, kept the top 5 and then grouped by two columns with two aggregations. All these operations have gotten folded into the query, this can be seen in the logs in the following line.

This is the SOQL statement and you can clearly see the folded elements. I have tested other functions which fold with SQL server such as new columns, joins, and simple transformations. However none of these appear to fold and may actually stop folding happening on functions where it works.

Thank you for reading this post, I realise it is longer than what I have written before but hopefully it will help you out.

If you want to read more about the query traces and what can be done, I recommend this blog post by Kasper on BI and this one by Chris Webb.

• Power-Bi
• Power-Query
• Query-Folding
• Salesforce