Enable Dynamics CRM oData Query Filter for PowerQuery Performance

Recently I’ve been experimenting with the PowerBI and Excel self-service BI for Dynamics CRM, which has been discussed by CRM MVP Jukka Niiranen on: http://niiranen.eu/crm/2013/12/connecting-crm-online-odata-feed-excel-2013-power-query/ and PowerBI’s blog: http://blogs.msdn.com/b/powerbi/archive/2013/12/19/dynamics-crm-online-in-power-query.aspx last year.

Last Tuesday on Melbourne CRM User Group, Rowan Miller also excellently presented the functionality with localised content (Australian geographical data): http://www.meetup.com/MelCRMUG/events/170962872/

One thing that annoys me is the performance when connecting to CRM web service: https://(CRMServer)/xrmservices/2011/OrganizationData.svc where this endpoint will return all entities that can be queried, thus the performance of each refresh and initiation is absorbed.

Out of my experiment, theoretically if it could retrieve the data through the oData endpoint, it should be able to get the data after the filter. So, I tried to retrieve the data using the oData filter, e.g: 

https://(CRMServer)/xrmservices/2011/OrganizationData.svc/AccountSet?$filter=(StateCode/Value eq 0 and EMailAddress1 ne null) to get the Active accounts and has email address. Additionally, we could include which fields that need to be retrieved.

The performance of early initiation and data refresh is improved, as the query is handled through the oData query level, instead of pulling all data using PowerQuery.

As always, the best tool to build oData query is: oData Query Designer by CRM MVP Rhett Clinton: http://dynamicsxrmtools.codeplex.com/