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

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”.

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.

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.

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.

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.

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