Power BI with Microsoft Dynamics CRM

Download Power BI Desktop , it comes with both 32 bits and 64 bits. Gets this installed.

After the Power BI installation is completed it launches with the Welcome screen.

Figure 1: Power BI welcome screen

For establishing a connection with any data source, you will need to click on the Get data icon which is visible on the welcome screen. Else you can click on the Get Data button which is visible on the untitled page as shown in the below image:

Figure 2: Connect with Data Source

Click on More –> Online Services –> Dynamics 365 (online)

Figure 3: Connect with Dynamics CRM

Clicking on Connect button, a screen will appear asking for CRM Web API URL.

Figure 4: Power BI Web API URL

Login to CRM –> Go to Settings –> Customization –> Developer Resources

Figure 5: CRM Setting (Customization) page

Copy the link from Service Root URL which appears under Instance Web API section.

Figure 6: CRM Web API

Enter the copied CRM Web API link in Power BI, under Web API URL field. Click OK button. This required for establishing a connection between Power BI and your CRM Organization.

Figure 7: Power BI Web API

The below screen will appear when the connection gets established between Power BI suite and Dynamics CRM.

Select the CRM entities by clicking on the check box which appear next to them. This is required for pulling data only for the selected entities, then click Load button.

Figure 8: Connection established

This would take some time depending on the number of entities selected for loading.

Note: If you don’t want the entire fields from the entity to get loaded. You can narrow down the fields by click on Edit button.

If user clicks on Edit button, Query Editor Page will open. Click on “Choose Columns” button under Home tab.

Figure 9: Option to choose require columns for report

Select the require columns/fields which are required for reporting. You can search the field directly by entering the name of the field under Search Columns.

Figure 10: Select required fields for report

Clicking on OK button, the Query Editor will have only the selected field.

For reflecting the changes Click on “Close & Apply” button then select “Close & Apply” option.

Figure 11: Close & Apply for reflecting the changes.

Finally, the data gets Loaded. I didn’t filter the column while loading the entity as shown in Figure 8 therefore all the columns got populated as shown under red box.

Figure 12: Data Loaded for Entity

You can now start with your Power BI Report by selecting any option under Visualization.

Visualization brings our data into life in the form of amazing visual representations.

Starting with Power BI

What is Power BI?

Power BI is a cloud based Data Visualization and Business Intelligence tool which help the users to read the data from different data source. It is a collection of services, apps, and connectors that work together to turn your unrelated sources of data into a unified logical format, visually immersive, and interactive insights.

Why Power BI?

Power BI allows quick and easy insight into the data which helps in developing interactive dashboard and reports. User can connect to the data whenever and wherever they want for analyzing and visualizing data.

Ways to use Power BI

  1. Power BI Service (Interactive Data Visualization BI Tools)
  2. Power BI Desktop (Desktop Version)

Let’s get started with Power BI using its desktop version. The reports created within Power BI Desktop can be publish directly from the Desktop application to Power BI Service.

Tasks which can be performed using the Power BI suite:

  1. Can get the data from different data sources.
  2. User can establish a relationship between the data for enriching their data model.
  3. Data can be visualized in many different format like Bar and column charts, Cards, Area charts, Gauge charts, Funnel charts, KPI’s, Maps, Tables, Matrix etc.
  4. Publish the report.