Welcome back to part 2 of my review of tips and gotchas for using Power Query with Dynamics CRM OData feeds.
Let’s continue our review and talk about how ugly the option set numeric values are, and how you can improve your query times by tweaking (filtering more specifically) the OData URL used in Power Query.
Now this was a fun one. If you’re used to customizing Dynamics CRM, you have likely created an Option Set (also called a pick list). The user picks from a list of available choices for a field, but CRM stores the text value along with a corresponding integer value. When you load an entity via the OData feed in Power Query, you get in the integer value, not the corresponding text field. That’s not very helpful. This goes for not just picklists, but statuses as well. Here’s an example of a picklist called Class:
How do we get the friendly text label? There are two main methods I’m aware of:
Dynamics CRM keeps a record of all of the option sets in this entity. While it’s old, there is an MSDN reference for this entity.
Now first let me say that I did not think of this myself, but I saw a blog from Jukka Niiranen (CRM MVP from Finland). I’m doing something similar, but going a slightly different route. Basically he keeps the mappings and the main data in separate tables, creates a mapping in Power Pivot, then in Power View he chooses to display the text value. That works of course, but I found a few cases like in pivot tables I couldn’t get it to display the text. So I decided to take the separate queries for the picklistmappings and merge them into my main table in Power Query. This allowed me not to have to do any mappings in Power Pivot, and ensured that no matter where I chose to display the data, I would never see the integer values.
In my example, I have two fields I wanted to map:
Billing Option was either Billable or Not Billable, and Class had values of Products like CRM, SharePoint, etc. The problem was there were some duplicate integer values in Class and Billing Option. So when you look at the PicklistMappingSet entity, I couldn’t just use one query for both sets of values since they were not unique:
You could either use the integer in CRM, or do what I did which is make a query for each field we need to map (one for Billing Option and one for Class). To do this:
Load the PicklistMappingSet
Merge the Query
Now we can take our picklistmapping values and merge them into our main content table. Basically what we’re doing is a join between two data sets like the VLOOKUP function in Excel. More specifically, we are going to do an Inline Merge. This means we are doing the merge the fields from the picklistmapping table as columns into our main Time table. You can read more details on here on support.office.com.
At this point, we need to do the same thing for the Class field.
That’s it! Now you’re left with no integer values and only text values. So every time you refresh the main query, it will get the latest values and automatically map them in your table. Sweet! Here’s what my queries looked like:
In the very latest release of Power Query, they changed the text for load only queries to say “Connection only.”. I forget what it used to say. These were the relevant steps in the main query:
As we saw in part 1, we know that to load any data from Dynamics CRM we need to use the OData service URL (this is for CRM Online):
You load this in Power Query, you get the entire huge list of every entity in CRM, and you choose from there. You choose an entity, and you can’t help but notice that some entities might have over 100 columns. If I load the OpportunitySet entity, there’s 191 columns (count in the lower left corner)! Yikes!
Now sure you can just remove all those columns after the fact, but think about it. Power Query has to load ALL those columns, then apply your filters. Normally you only need to work with 5 – 15 columns in an entity, so that’s definitely not very efficient. Maybe you know you will NEVER need to worry about closed or inactive records? Power Query has to load all of those too, them remove them. Building a query this way can give you refresh times at 10+ minutes. How can we improve this? Luckily, the OData REST API supports us doing a little filtering via the URL. That way, Power Query won’t even TRY to load all the records you don’t want.
How do we do that? In my case, we are using CRM for time tracking. I am loading all time tracking records and these go back many years. I only care about from Jan 1 2014 and forward. Also I want to limit the columns I bring in. We can modify the URL. In my main query, I only want to load one entity, only 10 columns I choose, and only records dating from Jan 1 2014 and forward with a default sort applied. This would be my URL:
https://<tenant>.crm.dynamics.com/XRMServices/2011/OrganizationData.svc/OpportunitySet()?$select=c5_Date,column2,column3,column4,column5,column5,column6,column7,column8,column9,column10,&$filter=(c5_Date gt DateTime'2014-01-01T00:00:00')&$orderby=c5_Date desc
You can know if this works by pasting that in your web browser. It should return an XML type of response. Some notes about the URL:
There’s a codeplex solution you can install that will give you an OData query designer that will help with the syntax. You can also find a few good examples of the filters here and other examples from MSDN. And don’t try to get fancy and use $select to try and bypass the above methods to get the Display of an option set. Say if you try like $select=Class/Value in Power Query, it will throw this:
Can’t you just hear the Pricing is Right losing horn? You can use the values on filtering, just not select. So on a filter, you could do this:
$filter=(StateCode/Value eq 0)
Wouldn’t that make life so much easier if $select let you get the display name instead of the integer? Oh well. Regardless, using these methods, my query went from about 12 minutes to about 2 minutes to refresh. AND, the very latest Power Query update apparently includes some performance improvements as well. From their blog:
“Query load—Performance of loading queries has improved by about 2x-3x in this release, according to our benchmarks. Queries that used to take ~10 minutes before this update, now only take between 3-4 minutes. We encourage you to try your own scenarios and let us know if you’re hitting these Performance levels.”
I hope this has made your job a little easier or made you aware of a few other issues you may not have encountered yet or offered some solutions when working with Power Query to get Dynamics CRM content. Power Query is an amazing tool, and I strongly encourage you to try it for yourself. This is an exciting space, especially when the new announcements in the new Power BI Dashboards Preview automatically generate awesome dashboards with CRM Online data.
For more information about this blog, Power Query, Power BI, Dynamics CRM or C5 Insight, contact us here!
Trackback from C5 Insight Blog
If you have been following the Excel business intelligence space lately, you are familiar with all of ...
The complementary paper includes over 12 years of research, recent survey results, and CRM turnaround success stories.
This 60-second assessment is designed to evaluate your organization's collaboration readiness.
Learn how you rank compared to organizations typically in years 1 to 5 of implementation - and which areas to focus on to improve.
This is a sandbox solution which can be activated per site collection to allow you to easily collect feedback from users into a custom Feedback list.
Whether you are upgrading to SharePoint Online, 2010, 2013 or the latest 2016, this checklist contains everything you need to know for a successful transition.