When you work with Dynamics 365 and manage a team or sell projects, you need to have a full overview of your situation for your company to thrive. Without writing a single line of code, I’ll show you how fast you can change the way you analyze data from Dynamics CRM using Power BI reports.
In this article, you will also find out how to:
- Get API from your CRM properly and establish a connection with Power BI.
- Analyze data with Power BI directly from your Dynamics CRM.
- Embed specific tiles from Power BI reports into Dynamics CRM.
Dynamics 365 is the perfect tool to store information about your customer relationship, sales performance, or service management. For my last project, I also integrated Dynamics 365 with IoT and other cloud services. Consequently, it enabled me to create a fully managed system for IoT devices.
You’ve covered the data, user interfaces, and management aspects, but what about data visualization? Power BI could make data more useful and provide greater insights. Reading this article will give you a clearer picture of how to combine Dynamics 365 and Power BI. Then, you can decide how you can implement this solution in your own organization.
“Dynamics CRM 365 works great as a data source but for analysis and data visualization we use the Power BI.”
Predica delivered a project for a big mall in Abu Dhabi. The client had decided to use a cloud-based solution and combine Dynamics 365 with another tool for data visualization. Based on our experience and the tools we use internally in Predica, we proposed using Power BI. We found it to be a helpful and easy-to-implement analytical solution.
When we talked to the customer, they had some concerns regarding the proposed solutions:
Client: What about database infrastructure for reporting layer?
DD: No problem! We are connecting Power BI directly to your Dynamics CRM system without external databases and additional maintenance.
Client: Sounds interesting…
Client: How will you organize my workspace for reporting?
DD: If you want to scroll down reports on your mobile phone, you can do that. If you need to embed accurate charts into your Dynamics CRM, you can do that, too. Power BI gives you a wide range of possibilities to deliver knowledge of your data stored in Dynamics…
When I realized that customers had these types of questions, I decided I needed to shed some light on this area.
Let’s go back to the nature of the project!
The project’s goal was to build a service management system based on Dynamics 365 online with the following functionalities:
- Service management platform where a user can assign and manage tasks via the web portal.
- The quick overview of KPIs and trends, nested in the portal.
- Reports available on mobile.
The above requirements are selected and very general.
I’ll answer the first problem mentioned by my client about connectivity between Power BI and Dynamics CRM without any external database structure for reporting layer.
How to connect to Dynamics
The first way to connect Dynamics data is to work with ready-to-use, pre-defined dashboards, and reports, wrapped in a thematic Content Pack. If you’d like more information about this, visit the Power BI blog.
The second way is to use Web API. Depending on the region where Dynamics is located, you can receive different access links to the source. The best and most reliable way to find a dedicated link to your Dynamics is to get it directly from your Dynamics CRM. The full path to reach the Web API is shown in the short video below.
You can use one of the six available methods for authentication, but at Predica, we usually use authentication with an organizational account through oAuth2. If you want to know more about available authentication methods, and which one you should choose, please contact our subject matter expert Tomasz Onyszko.
How to analyze data from CRM
The same solution we implemented for our client, we also use at Predica. It is intended to analyze and monitor the process in the sales department. Here’s how we did it.
The customer asked us the following questions before preparing the solution:
- How many leads have my salespeople generated in the last few months?
- How many opportunities are converted into a real project?
- What is the trend of sales?
- Do I have enough resources to cover the needs of sold projects?
And many, many more…
The solution we have implemented has allowed our customer to find answers to all their questions. Additionally, they gained a wider insight into the development of sales.
For efficient data analysis with Power BI, you can organize workspace in three different ways:
- Analyze data on your desktop via Power BI Desktop.
- Access your reports through Power BI Service.
- Embed report titles into Dynamics CRM (I’ll describe this in the next section).
For the first two methods, you can use content packs, or create your reports in Power BI. Predica’s BI team and I prepared a “ready-to-use” set of reports. I’ll show you two of them as an example in the next step.
When you analyze data from the Dynamics CRM, you will encounter the problem of data transformation. You will need to choose entities appropriate to your problem analysis. Let me give you a little hint about the entities you should focus on.
For sales analysis, the entities you should analyze are:
- Account – Filtered by active in the last year
- Activity – All activities added or modified in the last 365 days
- Business Unit – Only active BU
- Lead – Added in last 365 days
- Opportunity – Added in last 365 days
- Opportunity product – Added in last 365 days
- Product – All active products
- Territories – For spatial analysis
You should be able to create and gather useful insights from your Dynamics data based on the entities mentioned above. Now, I would like to show you how to analyze sales information, effortlessly and intimately, using the same entities discussed.
On the screen below, you can see our approach to sales performance. It applies to new leads, opportunities, and projects. The last tile in the bottom-right corner applies to information about closed leads and opportunities. It is beneficial because it could help you with resource planning and avoiding oversales.
The next figure of sales reports refers to the detailed opportunity view, broken down by project owner role, and with specified clients.
You can quickly check how many leads you opened and how many specific salespeople acquired points. This is an easy-to-read report that measures the effectiveness of sales representatives.
If you want to find out more about how to perform the most thorough sales analysis, I invite you to contact me for a private conversation. I’ll try to find the best possible solution tailored to your organization.
Embedding Power BI reports in CRM
Responding still to the question about organizing workspace, Dynamics 365 has advantageous features. In particular, they allow you to publish chosen tiles from Power BI dashboards, directly into your Dynamics panel.
Power BI and Dynamics 365 run on the same o365 account, thanks to oAuth2 authentication. It’s possible to nest reports without providing additional credentials. Moreover, low-level security and other functionalities are also migrated and enforced in nested reports.
What you should know is that we do not use Power BI Embedded reports, and instead get tiles directly from the Power BI service. More precisely, we get tiles that pin to dashboards. The conclusion from this is that you should first publish reports to Power BI Service and pin chosen elements to the particular panel. Then, you’ll be ready to start nesting your tiles in Dynamics CRM.
Before you start, you should also enable the possibility for nesting Power BI reports into Dynamics 365. To enable and configure this, go to your Dynamics CRM, click on Setting > Administration > Reporting, and then tick the checkbox.
The final step is to create a new dashboard in Dynamics 365 and select Power BI from the grid. Now you can choose interesting reports or tiles to embed from the drop-down list.
- Enable Power BI tiles in Dynamics 365
- Publish report to Power BI Service or connect to ready-to-use content pack
- Choose specific tiles and pin to dashboard
- Go to Dynamic 365 and create new dashboard
- Select Power BI from the grid and select tiles to embed from drop-down list.
In the video below, I’ll show you how to do this step by step.
I believe this short article can help you choose the right tool for data analysis from your Dynamics CRM and gives you a quick overview of how to customize it for your company’s needs. If you want to know more about Power BI and best practices in data visualization, check out Paweł’s blog post.
If you have any questions, just leave a comment below or contact me directly.