DEEN
Home › Blog › Optimizing Power BI query performance with CRM 2016 Online OData v4 service

Optimizing Power BI query performance with CRM 2016 Online OData v4 service

Nils Pelzer
Sep 28, 2016 | Last updated: Dec 16, 2022
Expert articles | 3 min read

Recently, we were tasked with migrating a customer’s Power BI report for CRM 2016. Although the old OrganizationData.svc still works as a data source, we decided to use the new OData v4 service of CRM 2016 Online. We also looked into how to further optimize the performance when updating the data model. This is especially important for reports with a dynamic data source. As time passes, your data source grows and it consequently takes longer to update your report. An update that used to take just a few minutes, may very well take a couple of hours a few months on.

Unfortunately, Power BI isn’t very clever if you design your queries with the query editor’s GUI. Instead, connect to CRM Online and select an entity.

You will get a notification to reduce the number of columns in order to improve performance:

img-four

After selecting the columns you need for your report, you end up with a table that contains only those columns. But if you take a look at the actual query in the Advanced Editor, you can see that you always start with a table that contains every column. In the next step, you remove every column that you didn’t select.

let
 Source =
 OData.Feed("https://<tenant>.api.crm.dynamics.com/XRMServices/2011/OrganizationData.svc/Entity")

#"Removed Other Columns" = Table.SelectColumns(Source,{"Column"})

in

#"Removed Other Columns"

Thankfully, it is possible to use a filter in your OData source query using the Advanced Editor. Modify your query accordingly:

let

  Source =
 OData.Feed("https://<tenant>.api.crm.dynamics.com/XRMServices/2011/OrganizationData.svc/Entity?$select=Column")

in

  Source

The result of both queries is exactly the same, except that the latter is potentially a lot faster than the first.

Unfortunately, filtering the initial OData query doesn’t work as expected if you are using the new OData service of CRM 2016 Online.

let

  Source =
 OData.Feed("https://<tenant>.api.crm.dynamics.com/api/data/v8.0/Entity?$select=Column")

in

  Source

This query results in a table that contains the data of every column that you selected, and additionally every other existing column without any data. We opened a Support Call for this issue with the Power BI team, and they confirmed that this is a bug in Power BI v. 2.31.4280.661. However, modifying the source query and filtering for the required columns is still a lot faster as the returned columns contain no data.

Until this bug is fixed, the Power BI team recommends using the following syntax to start your queries:

let

  Source = 
OData.Feed("https://<tenant>.api.crm.dynamics.com/api/data/v8.0/ Entity?$select=Column"),

#"Removed Other Columns" = Table.SelectColumns(Source,{"Column"})

in

#"Removed Other Columns"
⨯

Answering