Power BI and D365 CE Connectivity – part 2

In last post, we have seen how to generate the fetchxml which can be used in Power BI for fetching the data from Dynamics 365 CE views. However the basic question of how to connect is still not discussed :). Let us do that connection in this post.

Let us again go to XRM tool box and open Power Query(M) Builder and click on “Generate Service Url”. As the plugin is already connected with the target CRM, it will generate the service urls which can be used in Power BI for connectivity.

Generate Service URl
Generate Service URLs

We can see the two tabs under Service URLs

Dyn365CEBaseURL: is the actual URL which we use in browser to open the CRM

Generate Service URl1
Dyn365CEBaseURL

ServiceRoot URL: is API URL as shown in  below snippet.

Generate Service URl2
ServiceRootURL

Once we have these URLs, let us create the blank queries in Power BI desktop. Click on Getdata and click on Blank query.

Blank Query
Creating Blank Queries

Go to Advanced Editor

advanced Query editor
Advanced Editor

Copy the Dyn365CEBaseURL generated above and paste in advanced editor

advanced Query editor1
Paste Dyn365CEBaseURL
advanced Query editor2
Added Dyn365CEBaseURL

Rename the Query as Dyn365CEBaseURL

advanced Query editor3
Rename the Query as Dyn365CEBaseURL

Similarly create one more blank query and name it as ServiceRootURL and paste the ServicerootURL created in above steps in advanced editor.

advanced Query editor4
ServiceRootURL

We have both the queries ready for the data connectivity. Now let us create few more queries for fetching actual data.

Create one more blank query and paste the fetchxml generated in earlier post in the advanced editor. I have named the query as Opportunity View.

opportunity view editor
Opportunity View

On Similar lines we can go ahead and add additional queries for different data , for ex Account data , I have added Account view.

Once we add these views, Power BI desktop might ask  to re authenticate and ask to enter the credentials. Once we connect and create these basic queries, we can go ahead and create various reports as usual in Power BI.

Refer my earlier posts to know more about data modelling, creating reports and dashboards

Power BI: Licensing Overview

Power BI: IPL Analytics- Dashboard

Power BI: Measures Vs Calculated Column

Power BI Jargons: DAX/M/Power Query

Power BI: IPL Analytics- Visualizations and Design Reports -Part 7

Power BI: IPL Analytics- Visualizations and Design Reports -Part 6

Power BI: IPL Analytics- Visualizations and Design Reports -Part 5

Power BI: IPL Analytics- Visualizations and Design Reports -Part 4

Power BI: IPL Analytics- Visualizations and Design Reports -Part 3

Power BI: IPL Analytics- Visualizations and Design Reports -Part 2 

Power BI: IPL Analytics- Visualizations and Design Reports -Part 1

Power BI: IPL Analytics- Data Modeling and Transformation-Part 2

Power BI: IPL Analytics- Data Modeling and Transformation-Part 1

Start with Power BI: Connect to Data Sources

Power BI : Deployment Models

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.