Blogs

Power BI > How to fetch Dynamics 365 CE data

Inkey, September 20, 2019 7331 Views

Power BI provides several different ways to fetch data from Dynamics 365 CE and today I will share my know-how on them.

1. Dynamics 365 Online Connector

This method is mostly used to fetch data from Dynamics 365 CE. Initially, this was the only method to fetch data from CE.
To use this, go to Get Data > Online Services > Dynamics 365 (online)

Selecting the above option, you will get the following dialog box:
Please visit the following link to know how to get the above URL –
https://carldesouza.com/dynamics-crm-odata-web-api-urls/

Provide your credentials here:
And you will get a list of entities, where you can either select all the entities or you can select specific entities and you will get all the data from those selected entities.

Pros:

• Direct way to fetch data from Dynamics 365 CE; you just need to provide URL and credentials.

Cons:

• It does not fetch Option-set labels. It just fetches value for that Option-set field.
  To surpass this, you need to use Power BI Option-Set Assistant in XRM Toolbox.
• It fetches all the data of a particular entity for the lookup fields. There is an additional effort to either remove those columns or extract data from those records.
  Below is an example of such columns:
  
• Need to rename columns according to their Display names.

2. Common Data Service Connector 

This connector was made generally available from June 2019.
To use this, go to Get Data > Online Services > Common Data Service.

You will get the following dialog box where you need to provide the URL(as retrieved in the earlier approach) for Dynamics 365 CE:

By default, ‘Reorder columns’ and ‘Add display column’ is set to true.
Click on OK and you will see two options, ‘Entities’ & ‘System’.Both these options have same number of entities but expanding Entities, you will see entities with their Display names and expanding System, you will see entities with their Schema names as below:
The difference between these two is that the previous one provides labels with values for Option-set and Status fields. Columns with the labels of Option-set fields are suffixed with “_display”. And for Lookup fields, it just brings Id for the Lookup value entity. While the latter option works exactly similar to Dynamics 365(Online) Connector. Hence, using Common Data Service connector > Entities, looks a good option.

Pros for Common Data Service connector > Entities:

• No need for the extra step of fetching label values for Option-set fields using XRM Toolbox.
• It removes extra effort to remove all the columns that fetch all the data for the lookup fields. Hence, number of columns fetched using this option will be less than what we get using System option or Dynamics 365(Online) Connector is used.

Cons for Common Data Service connector > Entities:

• Need to rename columns according to their Display names.

Cons for Common Data Service connector > System:

• It works exactly similar to Dynamics 365(Online) Connector. Hence, cons for this option are similar to those of Dynamics 365(Online) Connector.

3. Using Power Query (M) Builder in XRM Toolbox

To use this, you need to add Power Query (M) Builder plugin in XRM Toolbox.
Connect your organization in XRM Toolbox, and select the plugin. You will see the following screen:
Select Load Entities > select the entity for which the data is needed > select required fields.
After selecting the required fields, click on Update FetchXML. You will proceed to the following screen:
Select Generate FetchXml option and you will get Power Query for the selected fields.
Copy-paste the query and paste it in Blank Query and the data will be loaded along with labels for Option-set and values for Lookup fields. It also queries for the renaming the columns as Display names.

Pros:

• No extra efforts for removing the columns or renaming the columns.
• No extra efforts to bring labels for Option-set fields and Lookup fields.

Cons:

• If you want to fetch data from multiple entities, it is not possible. You can select columns and get M Query only for a single entity at a time. So for multiple entities, you need to repeat the process.
• If there are frequent changes of removing or adding columns, you need to select the columns all over again to generate FetchXML.

Please note that you can also use Generate OData and use OData query but this option just eliminates efforts for renaming and removing columns. You would still need to retrieve labels for Option-set fields and expand Lookup fields.

A lot depends on which approach needs the least efforts to satisfy your requirements.

Please feel free to comment down if you are aware of any other method or if you know any other limitations regarding the above-mentioned approaches.