Previewing the New Power BI Experience with Dynamics CRM

Around one year ago Microsoft introduced the capability to access CRM Online data via Power Query, one of the client side components of the Power BI suite. More precisely, the capability to authenticate with the already existing CRM OData feeds via Office 365 credentials was introduced into PQ, which then allowed us to use CRM Online as one data source in our Power Pivot data models driving Power View visualizations. No Dynamics CRM specific feature had really been developed, but it was the first step on the road of unlocking the cloud data from the analysis limitations that the previous tools (CRM charts & dashboards, SQL Server Reporting Services reports with Fetch XML queries) had imposed on CRM Online customers.

There was a lot left to be desired still. Building reports from the raw OData feeds can be a cumbersome process, since no built-in tools existed for filtering the amount of data pulled from the entity tables. Also, not all the components needed in basic reports, such as optionset labels, were available to be retrieved dynamically from the OData feed. The biggest gap, however, has been the inability to automatically refresh the reports published onto Power BI portal, which means that each report end users would have needed to perform this refresh manually. As a result, Power BI has so far not been a something that I would have suggested to CRM Online customers as a company wide reporting solution, since it’s mainly been suitable for fairly advanced CRM power users building their own reports via Excel 2013.

The Next Generation of Power BI

Just before Christmas Microsoft announced the new Power BI Public Preview, which has a number of impressive looking enhancements to the service offering:

• Power BI Dashboards
• Power BI Designer application
• Apps for iPad and iPhone
• On-premises SQL Server Analysis Services support

Include in this announcement was also the statement: “In addition to the existing seamless connection with Microsoft Dynamics CRM Online, with today’s release customers can also connect to their data in Salesforce, Zendesk, Marketo, SendGrid, and GitHub with many more to come in the months ahead.” Upon first read, it was easy to interpret it as “nothing new for you Dynamics CRM folks out there right now, better luck in the next update!” This, however, isn’t the case.

When browsing through the new support site for Power BI I came across a posted idea on supporting Dynamics CRM as a data source, and a very recent comment from a Power BI product manager that the new Public Preview actually included native support for Dynamics CRM. Here’s the article where you can find the exact steps needed for connecting your Power BI Preview instance to Dynamics CRM. As this Preview is only available in the US for now, I decided to spin up a new CRM Online trial org from across the pond and try it out.

What’s in Store for Dynamics CRM Cloud BI?

After going through the process of enrolling for the Power BI Public Preview program with my Office 365 US trial tenant, I completed the steps for connecting to CRM Online. This really is quite a simple process with not too many options. You only have to dig up the OData feed URL from your CRM Online instance. After you’ve added this Microsoft Dynamics CRM app into your Power BI environment, the data retrieval process will start uploading your CRM Online records from one cloud to another.

As my trial CRM Online tenant only contained the standard demo data, this was a quick operation. I was soon taken to the “Dynamics CRM Sales Manager” dashboard. This page shows a selection of KPI tiles and charts reflecting the various standard fields from CRM opportunity records, alongside some custom calculated fields like deal age. Naturally there’s a Bing Maps component available as well, to visualize the geolocation data from the accounts.

The Power BI CRM app also includes a 10 page report called “Microsoft Dynamics CRM Dashboard”. This is where you’re taken if you click on the components in the above dashboard. It’s essentially a big Power View report consisting of pages like Sales Pipeline, Win – Loss, Lead Analysis and so on. You get the interactive filter options that you can use to narrow down the data to specific time ranges or record status values, for example.

I’m assuming some of this report’s content is the same as with the CRM 2015 workbooks advertised in this video: Using Power BI with Microsoft Dynamic CRM 2015. However, since I’ve yet to come across these workbooks (not sure if they’re published yet), that’s a topic we’ll need to visit some other time.

Report Authoring Experience

Configuring the Dynamics CRM for the Power BI Public Preview site also gives us a dataset that points to our CRM Online organization. This means we can create a brand new dashboard that uses the same data, so let’s explore these capabilities further.

By clicking on the CRM dataset we’re taken onto a design canvas that looks familiar to anyone who’s worked with Power View in Excel. On the right side we see a list of fields grouped by table, from where we can drag and drop any field onto the designer area. This automatically creates a new chart, with a default type selected based on the field’s data type. So, geolocation fields like Address 1: City will bring up a Bing Maps visualization, but we can turn it into a bar chart like in the image below. The values, axis attributes, legends and multiples can be edited, just like in traditional Power View, but this time right inside the browser window.

After we save this as a report, we can pin the charts onto our custom dashboard. You’ll need to create your dashboard first, then navigate to a report you saved from the dataset page, then hover over a chart to see the “pin to dashboard” option. You can’t choose which dashboard, though, so be sure you follow this somewhat unintuitive navigation path to make the data visualizations appear in the place you expected to find them. Once on the dashboard, you can move and resize the components, to adjust the layout to your liking.

The really cool features of Power BI are the ones that provide a user interface to access the underlying data in ways that you don’t see on too many other platforms. The Q&A bar with the “ask a question about your data” is definitely one of them. With the Dynamics CRM dataset loaded into Power BI Preview you don’t need to even manually drag & drop the fields into the right boxes to build a chart like you would in the Power View editor inside Excel. The Q&A UI lets you just type in a statement in natural language, see real time suggestions for possible questions to ask and immediately presents a chart composed of the underlying data. For example, to create a bar chart to show estimated opportunity revenue by account city, all we need to do is type in the following question. If we like what we see, we can then pin the chart into our dashboard.

Stepping away from the browser window and back to the desktop apps, the Power BI Designer application looks like a simplified version of the tools already available inside Excel 2013. This is not necessarily a bad thing, since I for one have found myself lost in the application Ribbon quite a few times when navigating between Power Query, Power Pivot, Power View and native Excel features when trying to build a report. The new authoring tool can hopefully offer a more streamlined process for turning data sources into charts. However, the Designer version released as a part of the new Public Preview doesn’t appear to offer any new data sources compared to the generally available Excel tools, so no specific connect type exists for Dynamics CRM (only Salesforce).

From Preview to Practice

As mentioned, previously the inability to automatically refresh reports in the Power BI portal with the latest data from CRM Online was the showstopper for publishing real life reports via this method. What the Power BI Public Preview now delivers is an option to schedule the refresh of a number of data sources, including the dataset for the Dynamics CRM Sales Dashboard. The maximum frequency offered is daily, with the choice of four possible time slots, to schedule the refresh to occur during the night and offer fresh new charts on the next morning. If needed, you can also click on Refresh Now on the dataset to force the data update process to start. How long this refresh would take in reality for a large CRM database is something that remains to be seen, but certainly you can’t expect real-time updates.

So, what about then going beyond the standard opportunity and account data in CRM? How would you manipulate the scope and contents of the preconfigured dataset for Dynamics CRM Sales Dashboard that you get after you connect Power BI Public Preview with your CRM Online? For the time being, my understanding is that you don’t. Essentially it’s just a pre-built configuration of a dataset like the one you could build with Power Query, but with the added special skills that this one can actually be refreshed automatically in the cloud. You cannot download this dataset nor the report from the Power BI site and open it in the Designer app, so we’re limited to working with what the browser UI offers.

While you can build new reports and dashboards from the preconfigured CRM dataset, I don’t see a way of including any custom fields into it, let alone custom entities. It also suffers from the same handicaps as raw OData feeds when it comes to option set fields, meaning that you may only see the numeric ID value instead of the human readable label. Now, because there is no editor available for this dataset where you could add lookup tables into it and further manipulate the data, there’s probably no way you could fix these in your dashboards.

This leads us back to studying the original wording of the Power BI Public Preview announcement. The SaaS connector part read as follows: “With an existing subscription to one of these services, customers can login from Power BI. In addition to establishing a data connection, Power BI provides pre-built dashboards and reports for each of these applications.” Well, both of these statements are true – just not at the same time. What we get for Dynamics CRM is a “Power BI app” that is a packaged sample using a subset of common default fields in the CRM data model. Alongside that, we could of course build reports in Power Query with the traditional way mentioned at the beginning of this blog post. Review the Get Data page on the Power BI Public Preview support site to see what type of data sources you can currently combine onto the dashboards.

Takeways for Dynamics CRM Users

What can we learn from this new preview version of Power BI from a CRM perspective? The great news is that there definitely appears to be planned methods through which CRM Online can become a first class citizen in terms of data source for Power BI reports hosted in the cloud. There are new visualization methods, authoring tools and client apps in place that will make Power BI a much more attractive destination for business intelligence information delivery. Also the extensibility of the platform is improving, with the announcement of Power BI REST API and the promise of support for custom built connectors leveraging Azure Stream Analytics to push data into Power BI.

As with all technology, before committing to any particular BI solution you should define what exactly are you planning to do with it. Which metrics do you need to be able to produce and what data sources must be combined in order to provide the answers to your questions? Pretty much any modern application comes with some level of data charting and summary view capability, Dynamics CRM being one of them. If you have requirements from the business to produce visually appealing insights on data that’s primarily managed inside CRM, then do make sure that you’ve fully investigated the capabilities of the source system before exporting it into a different reporting app.

It’s somewhat disheartening when I see customers replicate sales opportunity dashboards in tools like QlikView when the exact same charts could have been built inside Dynamics CRM. Likewise, when I do a demo of the latest application version to an existing Dynamics CRM customer and hear comments like “these dashboards sure have evolved dramatically since the CRM 2011 version”, when in practice all the features have already been available to them in the current system, that doesn’t make me a happy CRM consultant. Sure, it’s nice to be able to impress the customers with what the application could do for them, but lack of awareness on the capabilities of current solutions is not the right reason.

Contrasting this with the new features we see in the Power BI Preview, what should a Dynamics CRM customer keep in mind when planning the future use cases for these new dashboards? For starters, there’s most likely not going to be a convenient way to filter the available data based on the CRM security model, on the same fine grained level of business units, security roles, record sharing, access teams, field level security and all the other features that are built into the functionality available within the Dynamics CRM application. Also, even though Power BI allows you to drill down into the data (and also filter several dashboard components based on the single selection, which is something CRM can’t do), the ability to actually open up the individual CRM records to review their contents and perform updates right on the spot is unlikely to become available very quickly. Oh, and speaking of updates, don’t forget that this is not going to be real-time data like the one you’re used to in your Dynamics CRM dashboards.

Having said that, I’m very much looking forward to the time when we can build similar CRM apps for Power BI that the current preview demonstrates. With the level of investments that Microsoft is making into their cloud BI platform development, there is bound to be a wealth of new features and client applications that will help Dynamics CRM users get more value from their customer data.