Today's blog post is written by Brendan Landers, VP of Consulting at Sonoma Partners.
My last blog post covered some basic information on Power BI Desktop connected to SFDC. In this blog post, I will discuss the various ways you can connect to Microsoft Dynamics CRM from Power BI and the pros and cons of each approach.
If you are using an on-premise version of Microsoft Dynamics CRM, you can connect to the SQL tables directly or write queries to massage the data in the way you need. You have the ultimate flexibility here. I prefer to write queries to limit the data set to only fields I care about, rename columns, and do some basic transformations (although this is all possible in Power BI). As an example, on one project we had a requirement to add columns flagging opportunities closing in the next 90 days. In Power BI we could do this with DAX, but the resources who would be supporting this long term were much more comfortable writing SQL, so we had the import SQL query handle this.
If you are using CRM online, the good news is that you have options. The bad news is that they have limitations.
As you probably know, you cannot connect directly to the database with CRM online so Microsoft offers some other approaches.
One option is to leverage one of the two Power BI content packs Microsoft has made available on the Power BI service. They are the succinctly named Microsoft Dynamics CRM Online Sales Manager and Microsoft Dynamics CRM Online Service Manager content packs.
To access the content packs, follow the instructions found here. Once the data is loaded, assuming you have data in the environment, the results are pretty impressive looking. For example, the Sales Manager content pack includes a Dashboard and a Report with 8 tabs of detail. These reports include details on Sales Performance, Win/Loss Analysis, Pipeline, Activities, and detail on Leads and Accounts.
In addition to cool reports, the content pack includes many features in the data set you may not first notice. For example, they’ve added some calculated columns which are very useful (behind the scenes using DAX). For example, on the Opportunity data set they have columns for Average Deal Size and Average Deal Age. Also, they include a Date object in the data set which allows similar date functionality as advanced find (i.e. filter to "Last 30 Days").
These content packs are great for inspiration and demo purposes, but you are limited to the content they provide – you cannot add to the data model. You can, however, edit or create reports from the Power BI service, but this is much more limiting than the Power BI Desktop tool. Herein lies the problem. It’d be great if we could start with the Content Pack, download the pbix file and leverage the Desktop tool to build what our customers need, but Power BI doesn’t allow this – you can’t take content published to the service and edit (Sidenote: please upvote this request from our friend Jack Bender.). The consequence is that unless you want all native fields and only want data from the objects provided by the content pack, you are handcuffed. Unless that changes, from our perspective, it’s only realistic to build your reports using the desktop and publishing to the service.
With the desktop, you have two other options to connect to CRM online, again each with limitations. First, from within Power BI Desktop, you can click on Get Data and select the Dynamics CRM Online source.
Once selected, you are asked to provide the URL for the CRM OData service.
Enter the URL and click OK. Next, you may be asked to authenticate. Select the OAuth2 option and enter your credentials for the CRM environment. Next you will see a list of CRM objects you can pull into Power BI. These look similar to the tables available in the database. For this example, let's select the following objects:
Click Load. You will now see the three data sets on the right side (under fields), and you can start interacting with them. You’ll notice some things missing though, which again renders this approach very limiting. For example, on the OpportunitySet, I don’t see an Estimated Revenue field (schema name is estimatedvalue). I can’t build any sales reports without this field, so again I am stuck. That said, based on the URL example they provide, I know I am hitting the old version of the OData service. So, let’s try this again with the updated URL.
So if we follow the same steps with the new URL, I see all the data I’d expect to see in Power BI. Great news…seems usable. I see my three objects in the right navigation pane. Not done yet, though. Before we can build the reports we want, we have to create relationships between the data sets. To do so, click on Manage Relationships in the ribbon.
You will see that no relationships have been defined. Simply click Autodetect, and Power Bi will analyze the data sets to attempt and relate the data.
In this example, Power BI detects three relationships and marks two as active. Unfortunately, they are not quite what we want. I want opportunities related to both accounts and systemusers (i.e Name of the Salesperson) so I can pull data in a single report from all three. Easy to rectify though. Simply uncheck the top relationship and check the second relationship and click Close.
Now we have what we need to start building reports from CRM Online. For example, if you simply select estimatedvalue in the opportunities dataset, and fullname in the systemusers data set you will see a report of opportunities by owner.
From here, you can start to build complex reports and publish to the Power BI Service for other users to consume either via the web or the Power BI mobile application.
Please note, rather than use the Dynamics CRM Online option when you are getting data, you could simply select OData Feed option and get the same result. The big difference you will find when you connect via OData is that you are working with schema names and you don’t have any of the additional niceties that the Content Pack provides, but you have the flexibility you will likely need. Power BI has some light ETL functionality that allows you to do thinks like change field names, apply simple transformations etc. It saves the rules so when you refresh the data set, you don’t have to worry about losing the work.
In summary, with a little elbow grease, you can get Power BI connected to any CRM 2016 online or on-premise organization and start to build really cool reports. Our advice is that, like most reporting projects, the most challenging part will be getting your data how you need it for reporting.
As I said earlier, some of our clients are more comfortable with SQL server queries and therefore, for a variety of reasons, some of our clients elect to synch their online database to on-premise for reporting purposes, at which point you’d have all of the above options with the exception of the content pack (which is specific to online). I am hopeful Microsoft will someday soon let us download datasets and reports from the online service as pbix files so we can use the content pack as a starting place but add into it what we need. At that point, things will get very interesting…