Connect to the Power BI Using Common Data Service (CDS)

By | October 19, 2018

Introduction:

The Common Data Service is an integral part of the Business Application Platform, providing secure cloud storage for your app data and integrating with Office 365 and Dynamics 365. Common Data Service (CDS) is one of the way to pull data from Dynamics 365. Common Data Service (CDS) enables us to easily build and extend applications with their business data. Using CDS we can directly get option set labels and lookup values without applying any query.

We will see how we can get data from CRM using Common Data Service for Apps (CDS) in Power BI.

Now, open your power BI desktop and select “Common Data Service for Apps (Beta)” under “Get Data”.

Connect to the Power BI Using Common Data Service

Now specify your Dynamics 365 URL as Server URL in CDS Contents. If you specify “Add display column” as false then you will not be able to get status labels and option set labels.

Note: You will not get option set labels for Multi Select Option Set.

Connect to the Power BI Using Common Data Service

If you are not already signed in then you need to sign in using your Power BI Credentials.

After successful signed in, the Navigator window will open showing the list of entities in “Common Data Model”, “Custom Entities” and “System”.

Common Data Model contains commonly used OOB entities such as accounts, contacts, invoices etc.

Custom Entities contains custom entities created and entities from project service.

System Entities contains all the entities from the CRM.

Connect to the Power BI Using Common Data Service

Note: You will get status field labels and option set labels only if you have selected entities from “Common Data Model” as well as from “Custom Entities”.

Now select the entities that you want to pull.

Connect to the Power BI Using Common Data Service

The entities selected will be loaded in your power BI desktop. Now you can use entities for making your report. When using Common Data Model and Custom Entities to pull the entities from CRM we get additional fields for status labels as well as for option sets labels. So we can directly use status labels in power BI reports without need of writing a query.

Connect to the Power BI Using Common Data Service

Entities can now include calculations and roll ups of related records to allow you to create Excel like formulas on both number and text-based fields. Hence you don’t need to use query in custom column to get status labels and option set labels from CRM.

Conclusion:

Common Data Service Provides another way to connect to the CRM and pull data from Dynamics CRM.

Marketing4Dynamics – Mailchimp and Dynamics 365 CRM integration to plan effective sales strategies, increase sales and improve ROI

  • Sync Audiences, Members and Tags from Mailchimp to CRM
  • Sync CRM Marketing List (Contacts/Leads) to Mailchimp
  • Sync Campaigns and Member activities from Mailchimp to CRM
  • Monitor and analyze Mailchimp campaign statistics through Dashboards in CRM

2 thoughts on “Connect to the Power BI Using Common Data Service (CDS)

  1. javier

    Is it possible to use CDM from Office 365?. Using Power BI and Excel files?

    1. inogic

      Hi,

      You can connect to Common Data Service using Power BI. There is a connector for Common Data Service but it is in preview. It is not recommended to use for production use.

      Regarding connecting to Common Data Service through excel, we are not sure whether you can directly connect to Common Data Service through Excel. Power BI add-on for excel enables you to connect to Power BI dataset and reports. If you have report or dataset in Power BI connect to Common Data service then you can use those reports and dataset in your excel.

      But as mentioned Common Data Service connector is in preview. So you should not use for production use.
      Not sure whether we have understood your question correctly.

      Thanks!

Comments are closed.