Power BI and its configuration with Microsoft Dynamics CRM

Making a revolution in Business Intelligence and Analytics, Microsoft introduced Power BI with a vision to provide improved & impactful live data visualization. Power BI, today, is a well-recognized application in the market with its extensive and powerful capabilities for business intelligence.
Power BI lets you connect to hundreds of data sources such as SQL Databases, Excel, Dynamics CRM, Facebook.

It extracts data from these data sources, easily transforms to related data structures and loads it from which you can create your own reports and interactive dashboards.

1

Figure 1: Example dashboard generated by Power BI

 

Advantages of using Power BI in Business context:

  1. Connects to a wide range of data sources.
  2. Helps getting the deepest business insights to boost your business productivity.
  3. Verbal ask for data provisioned by using Cortana Intelligence.
  4. Provides variety of content pack or service pack which automatically fetches data and creates interactive dashboards.
  5. Provides 360 degree Analytics for Organization, helps in keeping track of sales, gaining new customers and making strategies.
  6. Real time dashboard updates over secured live connectivity with data sources.

connect power BI with Microsoft Dynamics CRM Online

It is quick and easy to connect Power BI to a Dynamics CRM Online instance. Sign up on Power BI online and follow the steps given below to connect to Dynamics CRM online.

As soon as you sign up, you can see Power BI logo on Office 365 panel.Click on it to navigate to Power BI online.

2

Figure 2: O365 Home Page

 

From Power BI Online page, as displayed in the next screenshot, click on Services to get Dynamics CRM service analytics pack.

3

Figure 3: Power BI Online

Point to remember: This service pack will automatically take data from your CRM instance and  will automatically create pre-populated dashboard similar as in below image:

4

Figure 4: Pre-populated Dashboard

Now, the dashboard is ready and we can embed it in Dynamics CRM, however first we need to enable Power BI embedding in Dynamics CRM settings.

Navigate to Settings->Administration->System Settings->Reporting in Dynamics CRMcontext using admin credentials.

Enable Power BI visualization embedding.

Navigate to Dashboards section and click New. Power BI Dashboard is now listed & available for use.

5

Figure 5: Dynamics 365 Dashboards section

 

Click on it and choose the dashboard you want to be displayed.

If as an Admin you wish to create a report of your own with your desired visuals, Power BI desktop version can be downloaded & installed on local box for further usage

Lets quickly walk through desktop version of Power BI.

Similar as online version, all you have to do, is to:

  1. Get the data from data source
  2. Create a visual, publish it to Power BI service (Online) and create a dashboard from there.
  3. It would ask for the Web API URL, that you change in the developer resources on Dynamics CRM.

6

Figure 6: Power BI data source selection

As seen above, Power BI provides a number of data sources to connect with.As of now, we will get the dynamics online data. It would ask for specific entities data, select that and start creating your report.

Select the entities; note that you can load as many entities as you want. It will also display the table of that entity.

7

Figure 7: Power BI entity selection

Note: If you load more than two entities and if those two entities have any relationships then it will automatically identify the possible relationships present in it.

After loading select the visualization and fields from entities and then publish it to power BI online.

8

Figure 8: Visualization creation screen

 

You can edit query as well from the data that you received, merge it, create a conditional column, add column, count it, group by it, create relationships etc.

You can maintain the gateways and schedule the refresh of datasets. You need to go to settingsàdatasets

The refresh history is also visible with their specific date &timestamps.

Enable Cortana Intelligence, one of the coolest features, by selecting “Allow Cortana Intelligence” under the option of Q&A.

 

It allows you to ask questions; so that whatever you ask for, you get the data you want in span of few minutes.

9

Figure 9:Power BI datasets

 

We can also make reports in Power BI online version same as that of Power BI Desktop.

Power BI is also available for mobile as “Power BI Mobile” and while creating reports and dashboards you get an option to enable it for mobile.

 

Published by

Seshan Dixit

Dynamics CRM Developer

seshan.dixit@pragmasys.in

Pragmasys Consulting LLP, Bangalore, India