Oh the Pain!
Creating data models in PowerBI can be a tedious experience when your data sets are large and are only available via a web feed such as an OData API, as is the case with Dynamics 365. Making even the smallest change to a query take an age to preview and then even longer to apply. To make matters worse, you encounter the same long wait times every time you need to refresh your data.
You've added filters to reduce the set size, you've removed all but the necessary columns and none of it has had any impact at all. Why? Because all the filtering and column removal you've done it done client side, after the entire data set has already been fetched and fetching the initial data set is what is slowing you down. This situation could be improved if the PowerBI odata query builder was a little more intelligent and gave you more control but the current UI based implementation is very basic indeed.
Save me Doctor
Luckily there is quite a simple solution that can dramatically reduce the time it takes to fetch your data.
Behind the scenes, PowerBI is simply making a call to the Dynamics WebAPI. By default it will issue the following:
Which gets all account, with all columns. But we can control this. The following shows you how to create a new, more targeted odata query.
- Open up the query editor.
- Right click in the query pane and choose odata.
- Specify a filtered odata query with a subset of columns. e.g
https://inty.crm4.dynamics.com/api/data/v8.1/accounts?$select=name,createdon,accountid&$filter=customertypecode eq 5 and createdon gt 2016-12-31
You can enter any supported odata url here, including expand. I would suggest you test your query in a browser. For more information on the what is querying options are supported see: https://msdn.microsoft.com/en-gb/library/mt593051.aspx
- You will now get back a table with lots of null columns. PowerBI first queries the metadata for the entity, creates the columns and then populates them with the data supplied by the query url you entered.
- "Choose columns" and choose only the columns you specified in the select clause of your query.
- You've now got a tailored, neat and tidy table with only the data you really need.
I've found that using this technique dramatically improves model building and refresh performance. It's not direct SQL quick and you still have to carefully consider your filters and selects but it makes something that was barely usable far easier to live with.