Deep Dive into the Common Data Service Data Integration

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…

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.