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.

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.

Have you been able to embed a Power BI Preview dashboard into a CRM Online dashboard via an IFrame? It was a bit fiddly but possible with Power BI v1.0, but I’m wondering if you’ve tried to do it with the Preview version (being no longer SharePoint-based)?

Adam, I believe embedding the new Power BI v2 reports isn’t supported yet. At least this article states that “it simply is not possible for the moment to embed Power BI Preview at all into Dynamics CRM – a feature which is available in Power BI for Office 365”. Also, if I search for Power BI suggestions on their UserVoice site containing the term “embed” then it appears most of the ideas are marked as “under review” currently.

We can certainly use Power BI to display CRM data via Power BI dashboards (app.powerbi.com). However, our biggest need is to display Power BI charts, reports, and dashboards from within CRM. Any news on if and when this will be possible? Thanks!

Douglas, I’m not aware of any public statement from MS on when/if they would be making making Power BI v2 content available for embedding into CRM.

Aside from making it technically possible to show Power BI dashboards inside CRM, one aspect that makes this a bit of an awkward topic is that the security model in CRM could not be in any way reflected with the Power BI content, since that is controlled via sharing to Office 365 Groups. Of course now that Office Groups is also being provided as something you can view via CRM with the latest 2015 Update 1 (v7.1, a.k.a. “Carina”), perhaps this is laying the groundwork for familiarizing users with the concept that not everything you see within CRM is necessarily controlled via CRM security roles.

Is it possible to build reports in Power BI with on-premise CRM data?
Is there any article that explains this?
Suppose we have different users with different security roles in CRM. How can we make sure that each user can view the Power BI report based on his/her data? How does this filtering occur?

Alan, for on-premises Dynamics CRM there are no specific tools in Power BI at the moment (add your vote for this feature here). You would therefore need to access it via the SQL Server database as the source and configure the Power BI Personal Gateway to push the data into the cloud for reporting and analysis.

As mentioned in my post, when you push your data to an external reporting database like Power BI, you lose the ability to filter the content of reports based on the original Dynamics CRM security roles and business units. A single user account will be used for collecting the data and shipping it to PBI, so any records that this user has access to in CRM will be included in the dashboards and reports built from this data. This is very different from the traditional SQL Server Reporting Services approach, where you do live queries to the filtered views of the CRM database and therefore get exactly the same data as you would via the CRM application UI or webservice API.

With an external reporting database like Power BI, you’ll have to use some other strategies for controlling access to the contents of the reports. As mentioned, Office Groups is the new foundation for sharing content inside Power BI (read this article for details). If you have divisions, teams or other organizational units that share the same Dynamics CRM organization but require restrictions on not seeing each others’ data, then you could create multiple copies of both the dashboards as well as the data sources (including the data snapshots, so watch your storage quota), then share them with the corresponding Group only. Nowhere near as convenient as when working within a single CRM database of course, but not an entirely new concept either when looking at how separate BI systems have previously been managed. Of course Groups is an Office 365 feature that exists only in the cloud (at least for now), so you’ll need to set it up alongside Power BI.

As always, thanks for your post and your prompt responses to the great questions. I added my votes (3) for on Premise OData access via Power BI but I don’t think that’s what most who deploy on prem *really* want. What I really want is the new Power BI Desktop experience to render within our on prem SharePoint 2013 BI Services with our on prem CRM 2015 just like it does for the Cloud versions. We build our CRM and SharePoint on prem for various reasons not the least of which is to keep our Clients confident in our control over their data so I’m hoping that Microsoft will be addressing that in short order. I know that “Power this, that and the other thing” is available via the Excel conduit but the Power BI Desktop seems so, so much easier, if only it was available on prem!

Larry, I’m not sure if that type of functionality would be in the short term roadmap for Microsoft. The v2 release specifically unbundled Power BI both from Excel (via the Power BI Desktop app) and SharePoint (via the new site). To me it seems like the major driver for this new direction has been the leap in product agility for Power BI development, as it no longer requires any specific version of Office or a particular SharePoint configuration. While I don’t believe Microsoft is planning to abandon the on-prem BI market, it’s obvious that the latest tools and features will become available first for the cloud environments. What exactly will be their BI product offering in the future is somewhat unclear at this stage, as we’re seeing brands like Power Query being phased out as Excel 2016 arrives.

Featured Post

Watch out: the Citizen Developers are coming! They are armed with easy to approach GUI tools like Flow, PowerApps and PowerBI, and they aren’t afraid to connect to any of the 160+ cloud apps that you may or may not know your organization is using to solve everyday business problems that the traditional IT projects […]