Microsoft CRM Online is a solution for customer relationship management, allowing organisations to manage information and use it to streamline sales and marketing activities. Microsoft Power BI is a solution for visualising information and analysing it to gain insight.
Now wouldn’t it be great to get these two working together? You can use the analysis tools of Power BI to understand what’s going on in your sales information.
Clearly, Microsoft thought so to, because from within the Power BI portal you can create a data connection to a number of services out of the box, and one of those services is Microsoft Dynamics CRM.
When you select the CRM option, there’s a simple wizard to point Power BI at your CRM Online tenant and add the authentication information. This creates the data connection, pulls in some tables of data, and creates a set of reports and dashboards for you, showing things like average deal size and opportunity count by status. Magic!
You also have an option to explore the data.
When you select this option, you get presented with a blank canvas. You can choose from an array of tables of data and select from a range of visualisation options to create your own interactive reports showing the information you care about in the format that makes most sense to you.
This all sounds wonderful... but there is a challenge here.
The standard data connection and reports are based around a set of standard data tables and expectations. They are built around the expectation that the users will be managing sales pipeline and suchlike. Given that this is a major reason why people invest in a CRM solution, there’s a lot of sense to that. However, Microsoft also have a concept of XRM – where X is a variable. Essentially, the idea is that you can use the capabilities of Dynamics CRM to manage all sorts of other information, customising the platform to suit your needs.
For example, at Core we have an offering for membership organisations. This offering is built on the foundation of Dynamics CRM but tailored around managing members, membership subscriptions, and suchlike, rather than customers and sales opportunities. The solution uses other tables and custom fields to manage the information. This means, in the context of Power BI, that the information people are most likely to want to analyse isn’t included in the out of the box data connection to CRM.
So does this mean you can’t do reporting on your CRM data in Power BI? No. It just makes it a little more difficult interesting.
You need to create your own data query. There are a couple of options for this depending on how deep you want to go into the world of BI. The route I went was to use Power Query. This is a free add-on to Excel that again lets you design queries – without needing to learn how to write SQL queries. You can choose to connect to a range of data source types, including an OData Feed. Once you choose the OData option, you can add the details and login for your CRM account. It then lets you choose from all the available data tables, and you can then edit your query – applying filters or choosing which columns you want to include.
You can get more complex if you want to bring in data from multiple tables by merging and appending different queries or pulling in connected data.
Through this visual interface, you can build up some sophisticated queries to pull in precisely the data you’re interested in. Once you’ve done, load your query and now you have your data set loaded to an Excel file (if you’ve got a lot of data, this may take a while depending on the performance of your computer).
Then you simply need to upload your Excel file to Power BI and you can start creating your custom reports and dashboards.
So, if you’re working with Microsoft CRM Online, you can use native integration to start analysing your data in Power BI. However, if doing something more custom with your CRM deployment, your data connections might need a little more planning. If you want more information about reporting on CRM or creating data connections between different systems, contact us to arrange a discussion.