Now that Power BI has hit the GA milestone (general availability), I decided to spin up a trial subscription for it and get familiar with the tools that it offers. My previous experiment with connecting to CRM Online OData feed with Excel 2013 Power Query seemed to be one of the very few blog posts that come up when you search for information on the topic. In that article I never bothered to go deeper into actually working with the CRM data as the big news really was that the latest version of Power Query was finally able to access data from CRM Online, thanks to the added support for Office 365 authentication on the OData feed.
I repeated the steps for connecting my Excel 2013 Power Query to a CRM 2013 organization hosted on CRM Online and started to think about a simple report I could build. Opportunities tend to be a nice entity for demonstrating your typical reporting needs on summing money values based on sales process stage, owner etc. so I selected the OpportunitySet to be included in my workbook query. Scanning through the columns showed that I had every field I needed, but there was one problem: I couldn’t see the actual data in them. Instead of the Estimated Revenue figures or Owner names all I had was a link that read “Record”.
“Hmm, well, a link’s a link so let’s click on it then. (Click) Okay, so now I’ve drilled down into an individual value. I no longer have a grid of opportunity records and columns, which kinda sucks. Oh, and also I can’t see any Undo button to take me back.”
Life would be so much easier if you just read the manual before starting to use new tools, but ain’t nobody got time for that in the fast paced IT consulting world, right? After a bit of trial and error I figured out what the procedure for turning that [Record] link into actual data values is. Since it’s not immediately obvious, I decided to write it down onto this blog post, so that anyone else experimenting with using Power Query and Dynamics CRM Odata feeds can move on faster than I did.
In all the columns that display the record link you can see a small icon with two parting arrows on the right side of the column header. This is where you can drill down to the column contents and choose which attributes for that field you would like to include in your query data. For example, when I click on the CustomerId column on the OpportunitySet query, the following menu opens up:
Once you click OK, the values for the chosen columns to expand will be shown on the query editor grid. Repeat this for each column you plan to leverage on your report. As you expand more columns, you’ll notice that the Applied Steps dialog in the query settings pane will list each of them as a step. You can also see that these fields will be included on the formula bar, which will read something like Table.ExpandRecordColumn and then a list of our chosen attributes.
After we’ve expanded all the necessary columns, we can then proceed with using this data in our report. While Power Query is the component in Excel 2013 that pulls the data into our Data Model, it doesn’t necessarily offer all the tools that we’d want to use for working with the data set. Move over to Power Pivot instead to define relationships between different tables, rename the columns, create calculated columns and do any other manipulation with the data before presenting it on a report layout. Then finalize your work on the Power View canvas and design the report that you wanted.
There’s an excellent tutorial available on the U2U Blog that covers all of the steps in more detail: Dynamics CRM 2013 and Power BI for Office 365 – Part 1 and Part 2. Wish I had also found that earlier on, but better late than never…
There should also be a Part 3 coming up that will talk about how to upload the report onto Office 365 Power BI for publishing it to the end users. I’m quite interested in seeing what will be the procedure here, because I’ve not seen much information about how to consume OData feeds in the Power BI portal. In fact, this tweet by Jamie Thomson implies that some OData features would have been dropped from the product:
When I click around in the Power BI Admin Center, I don’t see any options for adding a new data source that would be an OData feed. If I’d like my report contents to get updated after I’ve uploaded it, I’d need to set up the Scheduled Data Refresh feature. However, the list of supported data sources doesn’t mention anything about Dynamics CRM. This leads me to believe that the current version of Power BI doesn’t yet support using CRM OData feeds as data sources in reports published on the Power BI portal.
With an on-premises Dynamics CRM instance you could of course set up a direct SQL Server data source and publish it to Power BI via the Data Management Gateway component. However, the much more interesting cloud scenario of building Power View reports that leverage data from CRM Online directly seems to be beyond the current feature set offered by Power BI, unless I’m mistaken. Let’s hope that we get more information about the possibilities of Power BI for CRM Online customers when Microsoft presents their latest Dynamics product roadmaps at Convergence 2014 in a few weeks time.
Hi Jukka,
This and the blog articles it links too have been really useful recently thank-you once again.
There’s one particular thing I haven’t been able to work out yet – I wonder if you’ve figured it out already?
If you try to expand a column for an option set it only seems to have one possible attribute to display – the option set value. Any idea how you get the string that would be displayed in the CRM UI instead? The options set values are gibberish in reports…
Sorry, Simon, but I’m not aware of a way how the optionset label could be queried via the CRM OData feed.
Looking into it a bit more it sounds like you just can’t do it. That means (I think) that in order to display something meaningful you have to redefine the mappings between the values and the display strings in option sets a second time (outside CRM).
If that’s the case I think it severely limits the usefulness of PowerQuery with CRM – at least for adhoc reporting anyway…
You’re right, I think the CRM Online reporting story with OData feeds isn’t quite complete yet. In addition to limitations with the option sets, there are also challenges with retrieving data for custom entities (with errors like “Expression.Error: The field ‘t1715’ of the record was not found” appearing for me and many other users). Nevertheless, I’m pretty sure MS will invest in bringing Power BI and CRM Online closer together in future releases, so getting familiar with the existing tools at this stage will hopefully pay off in the long run.
I think that last one may be a problem with CRM 2013 – I’ve noticed OotB entity sets that work with CRM 2011 (admittedly on-premise) that do not with CRM 2013 Online.
The latest update for Power Query would seem to fix the custom entity issue now, based on my quick test: http://blogs.msdn.com/b/powerbi/archive/2014/05/06/new-updates-for-power-query.aspx
Thanks Jukka, I wasn’t expecting an update quite so soon – I’ll give it another go.
Thanks, you helped alot
The latest version of PowerQuery does indeed seem to fixed the errors querying custom records (and the other ones with certain OotB ones in 2013 and also in YKW).
I’ve been playing around with this some, and it seems like a nice option for small datasets from CRM Online, but I can’t seem to make it really workable with larger datasets, which is where the real gap is for CRM Online reporting.
For example, I’m looking at a table that has several hundred thousand rows. And even though I filtered it to show only data that was created last week (which should be perhaps 10,000 total records) it downloaded the entire table. Watching the download, it reached more than 500MB and took about an hour.
Am I missing something fundamental? Do I need to go read the manual? 🙂
Matt, I’ve run into similar problems with the data download performance when trying to do a simple report based on the ClickDimensions website visit data collected for our company website. No, we’re not that popular, but the amount of data available in any given entity’s database table can quite quickly become a challenge for even getting to the report design stage, since pulling down the OData feed from CRM Online isn’t exactly lightning fast.
Our fellow CRM MVP Andre K Margono recently posted a tip on how to apply a filter to the OData feed before the data is actually retrieved. I didn’t have a chance to try this out myself yet, but by the looks of things it should offer a way to limit the data set into something more feasible than a complete database dump of the tables. Here’s the article: Enable Dynamics CRM oData Query Filter for PowerQuery Performance.
Oh, and if you ever get to reading the actual user manual for PowerQuery, be sure to tweet the best bits so I can also catch up on those 😉
Great tip. I’ll try that out. And thanks for the fast response! Are you supposed to be off work at this hour??
I’m supposed to be on summer holiday for 2 more weeks, which means I’ve got more time than ever to spend on interesting stuff like CRM blogs 😀
Ahh – that explains a few things…
Actually Matt if you look at the U2U blog Jukka references they do something similar there also:
http://blogs.u2u.be/u2u/post/2014/02/05/Dynamics-CRM-2013-and-Power-BI-for-Office-365-Part-1.aspx
I started doing this at the beginning of my tests but dropped it because constructing the connection string was so cumbersome and error prone this way, (and then I forgot about it – running into simlar problems as you).
What I don’t understand is why the PowerQuery product doesn’t do this for you. Please forgive my ignorance but isn’t providing a simple to use and graphical UI to do this for you one of the main things users are going to expect this tool to do?
Jukka, this is what I found for the PowerQuery “manual”: http://office.microsoft.com/en-au/excel-help/microsoft-power-query-for-excel-help-HA104003813.aspx
The “hidden gems” that I found interesting and useful from the manual are:
Power Query Formula: http://office.microsoft.com/en-au/excel-help/learn-about-power-query-formulas-HA104003958.aspx?CTT=5&origin=HA103993115
Combine Query: http://office.microsoft.com/en-au/excel-help/combine-multiple-queries-HA103993872.aspx?CTT=5&origin=HA104003813
and Merge Columns: http://office.microsoft.com/en-au/excel-help/merge-columns-HA104138986.aspx?CTT=5&origin=HA104003813
The guide on how to connect to Dynamics CRM is really plain, I hope they are adding more useful functionality for Dynamics CRM: http://office.microsoft.com/en-au/excel-help/connect-to-dynamics-crm-HA104168136.aspx?CTT=5&origin=HA104003952
Thanks for sharing the links, Andre! Very useful for locating the Power Query features necessary for working with Dynamics CRM as the data source.
Jukka, extremely helpful as always.
Has anybody worked out how to deal with option set lookup values?
I can’t seem to get it to show the value label in power query. So my filtering is extremely difficult to decipher. I.e. I don’t know what “10000001” relates to?
Has anyone found a way of getting around this?
Peter, please check out my latest post on how to Access Option Set Labels in Dynamics CRM OData Feeds via Power Query.
Hello All, Thanks For the Good Information. I have a question though, How you filter data in power Query, MCode to bring only the data from last Friday. ( the Last Working Day)
Thanks
Jukka, Very nice article. I am having trouble figuring out if there is a way to access the attribute level custom option sets label text from odata query?
Hi Jukka! Have you gotten anywhere with the scheduled data refresh from CRM online to PowerBI? I’ve tried a few tricks but haven’t still found the answer if it is at all possible yet…
Kristina, so far I haven’t seen any official announcement from Microsoft on supporting scheduled refresh for CRM Online data source, nor any public indication on when this could be expected. We’ll just need to keep our eyes on the Power BI blog for updates on this one.
Is there a way to limit the number of rows returned when first creating a new Power Query with an OData source (CRM)? When first designing the Power Query, I have to wait a long time due to high volume of records, even when I select Load To Data Model. Thanks!
Douglas, you could filter the query at design time by appending the OData feed URL with a suitable filter criteria. See this blog post by CRM MVP Andre K Margono for more details: https://andz88.wordpress.com/2014/07/11/enable-dynamics-crm-odata-query-filter-for-powerquery-performance/
Thank you very much.. helped a lot…
Thanks! This was a great help :).