Microsoft’s Common Data Service (CDS) has a feature called Data Integration, and this blog post is a deep dive into this feature; its capabilities and limitations.  As a Microsoft Partner that provides consulting services for Dynamics 365, and since Dynamics 365 database is now built on CDS, I was intrigued to find out if the Data Integration feature of CDS could improve the way we integrate data from other systems into Dynamics 365 for our customers.  In any Dynamics 365 implementation the integration portion can be challenging and expensive, is the CDS Data Integration feature a new simple way in which Citizen Developers can integrate data into Dynamics 365?

Summary

This has turned into long blog post, so for those readers who just want to get a sense of what CDS Data Integration is like, without going into the deep dive, here is the summary.

Personally, I would not recommend using the CDS Data Integration at this stage unless you are doing a one off initial import of data into an existing entity from one of the available connectors.  There are too many limitations of the feature at this stage and you are likely to run into issues and bad practices if you use it for creating entity schemas in the CDS or refreshing data.

However, it is a great idea and has huge potential in the future.  I for one can see use cases for it if Microsoft keep developing it and address the following:

  • Get the PowerQuery tool up to speed with the same functions that you get with Excel or Power BI desktop
  • Allow data to be refreshed into the CDS without completely overwriting the existing data. i.e. only update the fields that need updating and also not replacing the record’s primary key
  • Allow data to be refreshed automatically on a schedule
  • Add support for adding and populating relationships

I believe that some of these items are on Microsoft’s roadmap for CDS.

Now for the detail behind my recommendation…

What is CDS Data Integration?

It is the found in the PowerApps (https://web.powerapps.com) menu here…

PowerApps Data Integration Menu

The CDS Data Integration feature uses Power Query to extract data from a source system, prepare the data and then load it into CDS.  This is the same technology that is used for Power BI Query Editor and Excel’s Get & Transform Data feature.

CDS Data Integration is in its infancy and does not have the range of data preparation tools and data source connections as for Power BI and Excel.  But Microsoft are working on this.

The data sources available to CDS are currently limited to the ones below.

PowerApps Data Integration Sources

Import Data to Create a Custom Entity in CDS

One of the features of CDS Data Integration is the ability to import data from a data source and have it automatically create custom entities based on that data.  For more information you can follow the steps for doing this here.

This feature works OK but there are a couple of pitfalls, which I will explain with an example.  I have a list of machines in an Excel spreadsheet that I would like to add as a new custom entity to my Dynamics 365 environment and relate them the Account entity.

Here is the sample data that I used.  Note that the data types for Installation Date is Date and Version Number is Number.  All other columns contain Text data types.

Sample Machine Data

I went through the process described in the article Add data to an entity in Common Data Service for Apps by using Power Query and the following is what I found.

Change the CDS Default Publisher

The CDS uses a special Solution to hold all of the updates made to the CDS metadata.  This Solution comes with a Publisher called CDS Default Publisher and it is assigned a random 5 character prefix for your custom entities and fields.

Before you even start the custom entity creation process through CDS Data Integration, you will probably want to change the prefix to something more meaningful or create your own Publisher.

CDS Publisher

Be Aware of Data Types

Inside of the Power Query editor the data types are not recognized from Excel so you need to transform them into the data types that you want created in Dynamics 365.  But where do you change the data type, there’s nothing in the menu bar like there is with Excel or Power BI desktop?  It took me ages to find this and I thought it was a major flaw for a while but you can change the data type by clicking on the data type icon in the column header.

Editing Data Types

In the list of data types you will notice that the Dynamics 365 \ CDS data types for Lookup (relationships) and OptionSet do not exist.  They are not currently an option, so keep this in mind.

I also tested using the sample Northwind OData service as described in the article Add data to an entity in Common Data Service for Apps by using Power Query and Power Query was able to pick up the data types from the OData source.

Once I had completed the process, I validated the field data types that had been created in Dynamics 365.  Everything looked as expected except every column that I had set to Text in Power Query was created in Dynamics 365 with the data type of Multiple Lines of Text, which have a max length of 2,048 characters.  This is far from ideal, especially if users are going to be entering data into this field as the data entry will not behave like the user expects of a Single Line of Text field.  If you are a Dynamics 365 configurator you will know that once a field is created, you cannot change its data type.

Multi Lines of Text

Entity Settings that can’t be changed

The general entity settings that were set by default by the Data Integration were also interesting as some of them cannot be changed once the entity is created.

For example, the Ownership type defaults to User or Team.  If you want an Organisation owned entity then you cannot create it through the CDS Data Integration.

Another example is the Define as an activity entity option.  By default this is unchecked and you will need to create activity type entities outside the CDS Data Integration process.

Out of these two defaults I see the Ownership type being a problem as the CDS Data Integration could be a great tool for creating lookup tables, e.g. Country codes, in Dynamics 365.

EntitySettingsCreatedByCDS

Import Data into an Existing Entity

Using the same Machine Excel file, I tested out the process of importing data into an existing entity.  The following is what I found.

Lookups (relationships) are not Supported

If you have an entity with a lookup field via a many to one relationship and you want to populate that field then it is not currently supported by CDS Data Integration.  I tried this by setting the Account ID field on the Machine entity to the GUID of the associated Account entity.  The following was the result when I tried to do the field mapping in CDS Data Integration.

Field mapping for lookup not supported

I have not tried loading data into an existing OptionSet field but I imagine that there might be some complications there too.

Refreshing Data

Once a project has been set up in CDS Data Integration you can Refresh the project and it will pull the latest data from source and load it into Dynamics 365.

In the article Add data to an entity in Common Data Service for Apps by using Power Query the following warning is given.

Overwritten data warning

Now this is a major problem for Dynamics 365 and Model Driven PowerApps because when the data is refreshed and over written the unique identifier (GUID) for each record is overwritten too.  Dynamics 365 and Model Driven PowerApps use these identifiers for the lookup fields and so when data is refreshed the lookups are removed.  Dynamics 365 started as a Customer Relationship Management (CRM) system and most records are related to another record in some form or another, so this is a major issue.

Let me explain with an example using the Machines data.  On the Account entity I added a lookup field which is a many to one relationship to a Machine record.  If I populate the Machine lookup field with a Machine record and then refresh the Machine data via CDS Data Integration then the lookup field loses its reference and become blank.

Lookup field refresh implications

I have posted an Idea on the PowerApps Community forum to fix this issue.  Please vote here if you would also like this addressed.

Integrating Data

So far we have looked at just uploading data on one-off occasions using a manual trigger, but the article Add data to an entity in Common Data Service for Apps by using Power Query mentions that you can use CDS Data Integration as an integration method.  However, I do not see any options for scheduling data integrations.

After posting on the PowerApps Community forum I got a response from Microsoft that the Scheduled Refresh is something that they are actively working on and should be out within the next few months.  I hope that they have factored in not completely overwriting of data for Dynamics 365 and Model Driven PowerApps.

Conclusion

Even though this blog post has pointed out a few issues and missing components with CDS Data Integration, I am excited by this feature and think it has some huge potential in bringing disparate data from external systems into CDS to be used by Dynamics 365, PowerApps and Power BI.  The kinks just need to be ironed out.  I am looking forward to future releases of this product.

4 replies on “Deep Dive into the Common Data Service Data Integration

  1. Hi Hamish, great post.
    Did you revisit this topic with the latest release? I think some of the flaws you mentioned are resolved now. Still trying to figure out how to import records that are related. My gut fealing says that records that have a relationship in the source, can be linked so that during import eg. the contact can be linked to the account, based on a key field present in the source.
    If you have any thoughts or input on that please tell me.

    Like

    1. Hi Marcel, I haven’t got around to updating the content in this article since the October release. The release makes a few of my articles out of date. However, I do plan to revisit them. Will keep you posted.

      Like

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