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

5 comments

Posted on 12th August 2014 by Jukka Niiranen in Tips

, , , , , , , , , ,

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.

CRM_OData_Excel

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.

CRM_OptionSet_Value_Label

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.

PowerBI_CRM_Odata_2

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.

PowerBI_CRM_Odata_3

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.

PowerBI_CRM_Odata_4

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.

Similar to filtering the rows, we can also choose which columns in the query are of interest to us. Since we’re essentially building a dimension table for our fact table (to use data warehousing terminology) that needs to convert numerical ID’s into text labels, all we need is two columns in that table. Highlight the SourceValue and TargetValue columns, then from the query editor ribbon click Remove Columns – Remove Other Columns.

PowerBI_CRM_Odata_5

Now the query is starting to look a lot more manageable. As the icing on the cake, let’s add the final step by renaming the query into “LeadSource”. If you plan to share the document with someone, or suspect that your memory might fail you as the number of queries in your workbook grows, consider adding a description of the query’s purpose, too.

PowerBI_CRM_Odata_6

We’ll want to still make sure that our counter part for the LeadSource table, the LeadSet table, has some values available where we can link our ID’s to, so open up the query editor window for it next. As we move to the LeadSourceCode column we notice that it’s again got that darn “Record” value there instead of actually useful data. Oh well, that’s not a big issue since we’ve now discovered how to drill into ‘em, so just add an expand step for that column and Close & Load to refresh the query.

PowerBI_CRM_Odata_8

2. The Data Model: Power Pivot

So, we’ve got some nice data pulled into our workbook, but how do we go about defining the logical connection through which our new dimension table is related to the actual leads table? This is where we jump from Power Query to Power Pivot territory. Both PQ and PP (as well as Power View) are Excel add-ins that you should have enabled to properly work with CRM OData feeds and build reports. You don’t need a paid Power BI subscription to access these client components, just a supported version of Excel.

Just like Power Query has its own tab on the Excel ribbon once it’s installed, so does Power Pivot. (It used to be known as “PowerPivot” btw, hence the spelling on the screenshots. What will they come up with next? “Power Point”?) Click on the very first icon to open up a window where you can manage the data model.

PowerBI_CRM_Odata_7

Enable the diagram view from the View tab of the ribbon, to see a collection of disconnected database tables. To map our Lead Source option set ID values to the labels in the dimension table, create a join between the LeadSourceCode.Value and TargetValue fields by dragging one over the other. Now we have an arrow pointing from the lead table to the lookup table for the option set labels. If we had created similar lookup tables for any other ID fields in our entity tables, this is where we would map them all up nicely.

PowerBI_CRM_Odata_9

3. The Data Visualization: Power View

OK, we’re almost at the finish line now. Our data model is complete so we can close the Power Pivot window and start thinking about data visualization. To get a nice, modern looking chart from our data we can go to the Insert tab of our “tabulous” Excel client and click the Power View icon to add a new report sheet into our workbook.

PowerBI_CRM_Odata_10

You’ll find our previously created tables on the Power View Fields pane (twice, if you’ve added them both into the Excel data model as well as the workbook while configuring your data sources in PQ). Since we now have a relationship between the LeadSet and LeadSource tables, we can drag a measure field into the Values box (such as Count of LeadId) and use the SourceValue field from our dimension table to group the data by the Lead Source option set values, in plain English. Hurrah!

5 Comments
  1. Peter Page says:

    Hi Jukka, Great post and glad you managed to find a way around this!

    Have you noticed that the picklistmappingset doesn’t show all of the optionsets?

    Is it that the optionsets have to be global to be in this set? Or should optionsets created in the set up of individual fields be shown here as well?

    Thanks

    22nd August 2014 at 10:49

  2. Simon Hetzel says:

    Fantastic Jukka,

    I had a quick look at the PicklistMappingsSet some time back now but I gave up because I couldn’t find the particular (OoTB) picklist I was looking for. I didn’t have time to check properly but at the time like Peter I wondered if they were actually all there. I also hesitated thinking perhaps this table was actually really for something else like optionset mappings for import jobs (or something?!?). Like most things nowadays I soon forgot all about it so thanks for the reminder!

    If optionset labels are never going to be simply expandible in PowerView and this is the right place to get them I guess it would good if the optionset “Record” column could be expanded to give a .ID, .Name etc. as well a .Value? (So people can be more certain they are querying labels for the right options sets – I’m guessing the names aren’t unique?) Also wondering what happens in this table with the whole MUI thing

    Hmm… I must try and find some time to give it another go with the more recent versions of CRM and PowerQuery…

    23rd August 2014 at 13:27

  3. bob mick says:

    My company is using CRM Online, so we must use Power Query to retrieve information for analysis and reporting in Excel/Power Pivot. PickListMapping is missing several list that I need, especially those for custom entities – though it seems like global option sets for customs are in the list.

    In Dynamics CRM 4.0 some of this sort of info was in a “StringMap” table, so I spent an hr digging through all the other data sets offered in Power Query. Did not find anything I needed (could have missed it).

    Going to learn a little more about all those “relationship” columns Power Query offers at the end of each edit table. Maybe there is something hidden there?

    23rd September 2014 at 17:01

  4. bob mick says:

    … The relationship columns are nice in that they provide a way to perform “joins” of two or more related tables. For example: Select “Projects” and get a sum of hours spent from a related “Timesheet” table (both custom entities we created).

    But they do not help with pick lists.

    23rd September 2014 at 17:40

  5. Ariel Plon says:

    Great post. I had been unable to retrieve picklist data from CRM for the reports. Has anyone found a way to bring picklist for custom entities?

    I am not a fan of picklist and try to avoid them whenever posible. However we still have some legacy and would be great if we can import the picklist for custom entities data.

    Regards, Ariel

    15th October 2014 at 15:25

Leave a comment