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


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.


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. (more…)

Our New Book: CRM 2013 QuickStart


Posted on 21st July 2014 by Jukka Niiranen in News and events

, , , , , , ,

A few people have asked me for recommendations on what books they should get if they want to learn about the ins & outs of the current Microsoft Dynamics CRM 2013 version. Even though there’s a wealth of blog articles out there that study specific features and an ever growing library of content produced by Microsoft themselves, there’s not been a whole lot of material in traditional book format that would have covered the latest CRM 2013 functionality. Well, now there is a great title available that I can recommend: the CRM 2013 QuickStart.

CRM_2013_QuickStart_cover_smallHow do I know the book is any good? Because I wrote a part of it! Aside from shameless self promotion, I can honestly say that the writing team behind this book is quite an extraordinary league of CRM experts:

If that’s not a group of CRM MVP’s you’d trust for advice on how to work with the platform then I don’t know who you would!

What exactly does the book cover then? As the title suggests, it’s not a complete A-Z of each individual feature included in the Dynamics CRM 2013 platform. Neither is it meant to be “my first Dynamics CRM manual” for people who are unfamiliar with any version of the product. Let me borrow some of the official intro text for the book here to explain the reason for its existence:

The CRM 2013 Quick Start is a first look at Microsoft Dynamics CRM 2013 and all the new features that have been included.

In the CRM 2013 Quick Start you will find details that can help administrators, customizers (functional consultants) and developers; not to mention power business users wanting to know all the details the admin never tells them. If you run CRM in the cloud or sitting in a server room at your office the information is useful.

This book is targeted to someone who has some CRM prior experience. By that we simply don’t spend any time explaining the basics of Microsoft Dynamics CRM from a beginner’s point of view. That said, the information in this book would still be useful on your journey to become proficient.

Imagine that you’re someone who’s started their Dynamics CRM journey with an earlier version like 4.0 or 2011 and you’re now faced with the upgrade project for moving the solutions onto the latest CRM 2013 version. Is this the right book for you to gain an understanding of what’s new & what has changed in the platform? Absolutely! How about if you’re a system administrator or a customizer that has some exposure to the new version (via CRM Online perhaps) but are looking to ramp up your knowledge about the platform for future projects, enhancements or admin tasks. Will the CRM 2013 QuickStart help you get up to speed faster than searching for random articles online? You bet! If you want to know more about the book’s contents, then go ahead and review the summary for each of the 11 chapters.

Now, this is actually the first book that I have ever written content for and it makes me immensely proud to have managed to make my debut in such a prestigious crowd of co-authors. My personal contribution to this title focused on describing the founding principles of how to design a great user experience for the CRM solution that you wish to deliver to your end users. While some of the topics I covered in the book are specific to the latest CRM 2013 version, many of the solution design guidelines are actually universally applicable to any Dynamics CRM version, representing best practices that I’ve personally learned over the past decade of working with the product. I’m really glad to have been given the opportunity to present them in a format that allows for a different type of discussion than your typical blog post.

Being a newbie in authoring content for books, it was also a valuable learning experience for me. Although I’ve been writing down my thoughts  on all things Dynamics CRM on this blog for six years now, the project of producing close to 50 pages of content on a given topic to create a coherent book chapter that can stand on its own was still a very different kind of assignment. Let’s just say that I have new found respect for authors that have managed to create entire books for new software products on their own.

The great thing about my writing project was that it provided me a really concrete reason to dig into the details of the various new customization options that Dynamics CRM 2013 offers and experiment with different scenarios that I’m likely to encounter in real life customer projects. As they say, the best way to learn a new topic is to teach it to someone else. If this content that I’ve put together as a part of my own learning process then ends up helping also other members of the Dynamics CRM community to discover better ways to solve customers’ problems with the application, then I consider that a win-win result.

So, as a reminder, please go and check out the CRM 2013 QuickStart book’s website at, where you can purchase the PDF version of the book. It’s also available in Amazon Kindle format, with a printed version due for release very shortly.

Special offer! Please see this page for instructions on how to access a 20% discount code for the ebook. Thanks for spreading the word!

Update Rollups, Service Packs, Major Releases: Understanding Dynamics CRM Versioning


Posted on 17th July 2014 by Jukka Niiranen in News and events

, , , , , , , , ,

When it comes to commercial software, there’s often times a difference between what the engineering department calls their product versions and how the marketing department decides to label the product for the end customers. For example, after Windows 3.1 the commercial name of the product changed to “Windows XP” while the underlying version number progressed to 4.00. After various changes in both product and naming strategy of the OS, we’re now using a product called “Windows 8.1″ when its actual technical version number is 6.3…

Just like with Windows, Dynamics CRM also has version numbers that are different from the names you’ll see in marketing materials and end user documentation. These numbers are relevant to anyone who needs to either maintain and administer a Dynamics CRM environment or customize and develop solutions for the product, since you need to be aware of the changes introduced by various updates to Dynamics CRM.

Before CRM 2013 there wasn’t a convenient way through which you could have determined the installed updates by just looking at the version number, since each Update Rollup just had a seemingly random four digit build number assigned to it. Luckily the latest releases have made the version numbers much more user friendly, by starting to follow the standard “” pattern. There’s still a few CRM specific things you need to be aware of, especially with the very latest releases, which is why I thought now’s a good time to draw some attention to the topic.

CRM_version_numberStarting with CRM 2013, the version numbering scheme follows a pattern like this (notice the bold numbers):

  • Major Release
    • Name: Microsoft Dynamics CRM 2013
    • Version number: 6.0.0.xxxx
  • Service Pack:
    • Name: Microsoft Dynamics CRM 2013 Service Pack 1
    • Version number: 6.1.0.xxxx
  • Update Rollup:
    • Name: Microsoft Dynamics CRM 2013 Update Rollup 1
    • Version number: 6.0.1.xxxx

The build numbers are of course not “xxxx” in reality but I left them out since they’re not something you should actively need to remember. Just bookmark this page and reference it whenever you need to know the detailed number of a particular release: Microsoft Dynamics CRM 2013 and 2011 Update Rollups and Service Packs: Release Dates, Build Numbers, and Collateral.

I’ll go through these levels in a reversed order, since I think that makes up for a more exciting story line here. Also, I think it’s more likely to resemble the real life process via which you’ll encounter each of these versions of Microsoft Dynamics CRM after your initial deployment. You’ll certainly need to be aware of the different versions even prior to setting up your very first demo/test/development environment, but keeping up with the Dynamics CRM releases is a job that never ends!

Update Rollups

These are collections of hotfixes to existing product functionality. When you open a support ticket with Microsoft for an issue you’ve encountered with the software, sometimes you might receive an individual hotfix package from them that fixes your specific issue (and nothing else). Normally you wouldn’t need to install each and every hotfix separately, however, as most of these fixes will eventually be released in an Update Rollup package. You’ll see a list of “issues that are resolved” in the Microsoft Knowledge Base article of each Update Rollup, which is normally your best indication of whether the software bug you’ve encountered has been squashed.

<rant>Since the official name of these releases is “Update Rollup”, the official acronym for them would logically be “UR”. However, some people seem to happily forget about the first word and instead call them “RU”, presumably as a shorthand from “RollUp”. Fine, call them whatever you want, even give each of the releases your very own pet names if you wish, but you’ll never, ever see me use anything but “UR” on my blog.</rant>

At the time of writing, the latest released version for CRM 2013 is Update Rollup 3, version number It was released on July 15th and contains over 200 hotfixes as listed on the KB article. Normally there would be a UR3 package released for all the server and client components, but this time there’s no Outlook version available, since apparently none of the hotfixes required the client bits to be updated. Update Rollups are usually cumulative, so CRM 2013 UR3 also contains the fixes from UR1 and UR2.

If you don’t manually download and install the UR’s, they will also be distributed via Windows Update later on once Microsoft makes them available via this channel. Since you should always test the effects of each UR prior to production deployment, it’s usually never a good idea to just let things run on autopilot. For example, letting your Outlook clients get the UR’s via the Windows Update schedule without keeping your server components up to date will soon land you in unsupported territory.

Service Packs

During it’s first 11 years of existence, there weren’t any official Service Packs released for Dynamics CRM, unlike many other Microsoft products. This changed a couple of months ago when CRM 2013 Service Pack 1 was released on May 28th. Even though MS is mostly referring to it by the name Spring ’14 release in their marketing messages, based on the naming policy of CRM Online, the SP1 version is essentially Spring ’14 for on-premises customers and the Outlook client components (even for CRM Online customers).

While the CRM 2013 SP1 KB article looks like any UR article we’ve come to know, with a list of resolved issues, this isn’t just a collection of hotfixes. Service Packs are the delivery mechanism for new product features as well as changes to existing functionality of the product. An example of this would be the case creation and routing features introduced in SP1. For a more complete list of SP1 contents, refer to the “What’s New” page on CRM Customer Center. Note that in order to access some of the new features, you’ll not only need to install the SP1 bits onto the server but also go to the CRM organization’s settings menu and apply “Install Product Updates” from there.

Service Packs are cumulative in the sense that they contain the previously released Update Rollup hotfixes. So, while CRM 2013 UR1 and UR2 were released prior to SP1, you don’t need to install them separately when setting up a new Dynamics CRM 2013 environment. Just grab the Service Pack and you’re all patched up to that point.

Update Rollups for Service Packs

Now this is where it really gets interesting. Remember that latest CRM 2013 Update Rollup 3 I linked to a few paragraphs earlier? Based on our discussion so far, would you assume it to contain all the fixes, updates and new features released for CRM 2013 so far? I see quite a number of nodding heads out there and I’m not at all surprised if your initial assumption would be “of course”. The correct answer, however, is “no”. Due to the counter-intuitive nature of this situation for any Dynamics CRM veterans, an explanation is surely in order here. (more…)