Access Option Set Labels in Dynamics CRM OData Feeds via Power Query

Access Option Set Labels in Dynamics CRM OData Feeds via Power Query

If you’re using Dynamics CRM Online without direct access to the underlying SQL Server database, there may have been a few occasions when you’ve wished for more flexible options for reporting and customer data analytics than what is possible via configuring CRM charts or developing Fetch XML based reports in Visual Studio. Late last year Microsoft enabled support for accessing the secured OData feeds from CRM Online via the Excel Power Query tool, which opened up some interesting options for building modern BI solutions on top of the CRM data up in the Microsoft cloud.


For a non-developer who hasn’t leveraged OData before it might come as a bit of a surprise that not all of the business data is necessarily readily available via the feeds when examining a particular CRM entity. One crucial thing that’s missing is the option set values. More specifically, it’s the label values that are not accessible via the entity tables retrieved via the OData feed, as all we have access to are the numerical values representing the labels (1, 2, 3, 4… 10001), but not the human readable versions.


From a reporting perspective, it’s very likely that any chart or table that you wish to build is going to leverage one or more option set fields. Formerly known as picklists, these are basically the dropdown fields on CRM entity forms that allow the user to select one value from a list of predefined values. Much more convenient for reporting purposes than free text fields, as I’m sure you’d agree.

So, what are our options then? We could of course manually create new tables into the Excel workbook that store the mapping of ID values and labels, but that just doesn’t sound like a fun exercise at all. More importantly, that would only give us a static list of option set values that couldn’t adapt to the changes in CRM customizations. Nope, not a good approach from report maintenance perspective, so let’s not go there.

As a bit more efficient workaround we could be adventurous and import a copy of the CRM solution file containing the entity customizations as an XML data source into Power Query and then pick out the necessary mappings from there. Due to the power of Power Query, this would actually technically work, and we could even set it to reference a file location from where the latest customizations would dynamically be imported upon workbook refresh. Still, that would leave us the burden of setting up an automated export system that would produce the customizations.xml file to reflect the latest changes.

After a bit of poking around in the OData feed data source, it turns out the optionset labels are actually included there. The tricky part is that they’re not simply a [Record] link that you can drill into and expose the values from your existing entity data set. Nope, they reside in a specific table of their own, called PicklistMappingSet. In this tutorial I’ll show you how to retrieve the data for a “Leads by Source” chart created with Power View, taken from an OData feed data source pointing to Dynamics CRM Online, using Power Query to pull the data into a data model built with Power Pivot.

1. The Data Source: Power Query

PowerBI_CRM_Odata_1To follow the steps you should have the latest Power Query version installed in your Excel client. I’ve already covered how to access CRM OData feeds from Power Query in a previous article, so please refer to that one if you haven’t done the exercise before. After connecting to the OData feed URL we should select the tables that we want to work with from the data source navigator pane. By minimum you should grab the LeadSet and PicklistMappingSet to build the chart.

The problem with CRM and OData is that by default the feed will pull down each and every record in the table. The query performance is less than stellar with CRM Online and if you have a high number of leads (status doesn’t matter, also the closed ones will get downloaded), you might be waiting for a while before the query is completed. If you want to move on a bit faster then check out this great tip by Andre Margono on how to set up a query filter for the Dynamics CRM OData query (for example, only active leads).

The real beauty of Power Query is in the query steps you can use for manipulating the workbook queries. Before we go there, though, let’s create a duplicate of the PicklistMappingSet query. This will make it easier for us to map the values into our actual leads table later on, as well as preserve the original option set value table available for further queries.


Open up the new duplicate query you’ve added into the edit mode by double clicking on it, which launches the Power Query query editor (yes, query is the word of the day). The first step we’re going to add for the query involves expanding a column that only shows a green “Record” value by drilling into it from the small icon next to the column label. Do this to the ColumnMappingId column and just load up all the columns found from behind it.


You’ll see the Record column transformed into three new columns as a result of drilling down into the data. The column we’re interested in is ColumnMappingId.Name, which has the names for all option set fields in our source CRM system. For this example we want to see the LeadSource field, so add a new filter for this value, just as you would in a normal Excel data table.


By now you might have noticed that the Applied Steps box in the Query Settings pane is collecting all of the actions that we’re performing on the query and storing them as steps. If you make an error in your selection, just click the delete symbol next to the steps to get rid of it. [Read more…]

CRM 2013 SP1: Case Creation and Routing – The Details

In my previous post about the new functionality included in CRM 2013 SP1 / Spring ’14 release I laid out the big picture of how case creation and routing rules relate to cases and queues in Dynamics CRM. Now it’s time to take a more detailed look at how you would actually configure these rules to automate your case creation process. There are a few limitations that it’s good to be aware of before you jump into applying these new tools in your service management scenarios.

Case Creation Rules

As illustrated in the big picture of queue and case management in my previous article, Case Creation Rules are specific to a single queue. Also, you can only have one Case Creation Rule per queue – per channel. It is nevertheless a 1:N relationship between queues and rules, since a queue can have a Case Creation Rule both for email and social activities (the latter of which are not yet leveraged in this release). The Command Bar buttons on the updated queue form, labelled “Email To Case Settings” and “Social To Case Settings”, take you to the respective rule record.


The Case Creation Rule form allows you to configure predefined conditions for case creation. Emails from unknown senders can be filtered away from case creation. Also the existence of a valid entitlement for the sender (contact) or the senders company (parent account) can be used as a filter. Finally, email related to an already resolved case can be set to generate a new case record, with a configurable “quarantine” time period. So, if you resolve a case today and the customer replies “thanks for your help”, this probably shouldn’t generate a new case, but a reply sent after 3 days to the same email thread might warrant opening up a whole new case record.


That’s all the conditions you can apply for the automatic case creation. There’s an additional entity called Case Creation Rule Item that’s found in the “Specify Case Details” subgrid. What this feature allows you to do is specify a condition on the activity record (email or social activity) and set values for the newly created case’s fields. As an example, if the email subject contains word X, you could populate the case subject lookup field with value Y. So, you can’t use these Rule Items to determine whether a case will be created or not, but you can pass along some variables from the originating activity.


The entity fields you can access in the Conditions box are limited to those directly related to the email (or social) activity. There is however one welcome exception and that is the Senders Account. This means that when the email is coming from a known contact, there’s a way to reach into the fields of the account related to the contact (related to the activity), to check variables like relationship status, customer category or other important pieces of information in a B2B service scenario. [Read more…]

CRM 2013 SP1: Case Creation and Routing – The Big Picture

The latest Dynamics CRM Online Spring ’14 release is now rolling out to existing and new customers (starting from the US data centers) and the on-premises equivalent of CRM 2013 Service Pack 1 will soon follow is now available from MS Download Center (here’s the KB article for more details about SP1). The quickest way to check if your CRM Online organization is already updated to the latest release is on the About screen, accessible via the gear icon in the top right corner. If your version reads (or 6.1.anything) then you’ve got the Spring ’14 release available and you can proceed to the Install Product Updates menu to enable the new features.


This release, previously known by the codename “Leo”, focuses on enhancing the service management capabilities of Dynamics CRM. There’s a great “What’s New” page on CRM Customer Center that provides a detailed listing of the new features launched now, including an eBook of the changes in service management. Instead of repeating all of this information, I’ll try and provide an overview of how the features align with one another and specifically how they could be applied in real world scenarios for managing incoming service cases from customers.

Enhancements in Case Creation and Queues

I guess we’ll still need to first list the new options we need to be aware of when configuring the service module in CRM 2013 SP1 to handle emails and cases  via queues. First off, there is now support for server side synchronization of emails (and other activities) between CRM Online and Exchange Online, without having to use the old Email Router technology (no support for hybrid deployments, though). Then there’s a new feature called Case Creation Rule that allows you to automatically convert an email message or a social activity record placed in a queue into a new case record. Finally, we have Routing Rules that can be leveraged for moving items into queues.

The following is my own interpretation of how these three areas are aligned in CRM 2013 Spring ´14 Update / Service Pack 1. The picture illustrates how an email message from the customer would flow through the system automatically based on the configuration of the aforementioned features. It also includes a few bullet points about the supported actions for each component. (Feel free to click on the image to view a bigger version that won’t stress your eyes so much.)


When going through the Leo release features I found it a bit challenging to get a clear view of the logical order in which the different functional areas found under the new Service Management settings menu should be applied. Also the relationships between them and the restrictions imposed on the number of records was something I only learned through trial and error. Hopefully this illustration makes it easier to identify the roles of case creation rules and case routing rules in the new release.

Rules vs. Workflows & Plugins

Looking at the picture, someone who has previously configured Dynamics CRM to be used in an email, queue and case based support process will surely find many familiar actions from the list. At the end of the day, pretty much everything here has already been possible with previous CRM versions. With those you just needed to leverage the workflow engine in the CRM platform to configure the case creation and routing activities. So, what’s really new here and why has Microsoft built this into the latest product release?

Behind the scenes, what the case creation and routing rules do is they create the workflow processes for you. This can be seen from the release documentation where the administrator of those rules is reminded about the requirement to have sufficient security roles for performing the corresponding actions via workflows. So, taking a very simplistic view, you could think of these new features available in the Service Management as a dedicated UI for configuring common process automation actions for customer service scenarios.

There’s definitely value in having these new features available right inside the core product. In previous versions, it has been far from trivial to build the necessary functionality for frequently encountered requirements, such as “email to case”. Several ISV add-ons have been developed to deliver such functionality and system customizers have surely spent a ton of time pushing the CRM workflow editor to its limits in an effort to automate the common tasks that a service organization would need to perform when managing cases in Dynamics CRM. Now there’s a new standard way to implement these processes via a method that is fully supported by Microsoft, which in turn will lead to far more customers taking a serious look at these case management capabilities in their business application platform.


It’s important to keep in mind that these new features don’t replace any of the existing CRM platform functionality. They offer a default method to configure common features, but they will not cover every possible scenario that you’ll come across in real life implementation scenarios. That means you can still use workflows and plugins to extend the process automation for service case management. For example, while a case creation rule provides the possibility to set an auto response email to be sent to the customer upon case creation, there’s nothing stopping you from doing this via familiar workflow process if more complex business logic is needed than what the new Service Management UI in CRM makes available.

In the next blog post I will take a more detailed look at how the case creation and routing features can be leveraged in practice, so stay tuned!