Import Dynamics CRM or Salesforce into Microsoft Common Data Service

The first post in this series provided an overview of Microsoft PowerApps and the Common Data Service (CDS, or Common Data Model, CDM), and how to import data into the underlying database using Excel. Currently, Excel is the only mechanism to import large volumes of existing data into the CDS database.

If you want to import hundreds or thousands of accounts and contacts from Dynamics CRM or Salesforce, you can export the data from CRM into an Excel file but then have to make sure the file is correctly set up. If you want to refresh the data, you have to go through the process again.

This second post provides an overview of how to automate importing account and contact data from Dynamics CRM or Salesforce into an Excel file in a format ready for import into the CDS. This process is repeatable, so can also be used for bulk updates.

The process described uses Inaport, a full featured ETL engine from InaPlex with connectors for Dynamics CRM, Salesforce, Infor CRM, Sage CRM, and others. InaPlex will provide a FREE 30 day Inaport license until the end of 2016 for organizations wanting to import CRM data in their PowerApps environment. InaPlex will also provide maps and supporting files at no chargeIns.  Contact InaPlex to take of advantage of this offer.

Overview

PowerApps and the Common Data Service (or Common Data Model) provide a mechanism for importing data from Excel spreadsheets, but there are some important issues to consider, such as:

• The worksheet must have the primary key of the table, and matching appears to be just on the primary key. If there is a match the record is updated, otherwise created.
• The field names should match the field names in CDM exactly. If the field names in the Excel file do not match the field names in CDM exactly you will have to manually map them during the import. You will want to avoid doing this because there is currently no way to save a mapping which means you have to do the mapping again if you do another import.
• All required fields must be mapped, even if you do not have data.
• Several fields have default values that may not be appropriate for your data. For example, the country fields default to Afghanistan unless you map a different default value.
• Picklist fields need to have the correct values.

The process can be automated using Inaport, the integration engine from InaPlex. In brief, the steps are:

1. Export a template file or files from the CDS.
2. Use the template files to create an Excel worksheet that will hold the Dynamics data.
3. Use Inaport to pull account and contact data from Dynamics CRM into the data file. In the process, the data will be standardized to meet the CDS import requirements.
4. Import the data sheet into the CDM database.

Once set up, the process looks like this:

Setup

Setup is extremely straightforward as InaPlex provides all the map profiles and supporting spreadsheets needed for this import for no charge. If you do not already have them please contact us. We will also provide a special build of Inaport and a free 30 day license.

The files supplied include:

• README – detailed instructions for setup and running.
• CDS_Template – a file with the exported templates from Account and Contact in the CDM database. These templates were exported early November 2016; you may need to refresh them. If you have added customization’s you will need to refresh.
• CDS_Data – a copy of the template file, ready for data to be loaded from you CRM system. Again, this may need to be refreshed if the
• CDS_Maps – an Excel file used to set up maps of data values. For example, mapping country names to the two character country code required by the CDM database.
• 01_DynamicsCRM_to_CDS_Contacts – the Inaport profile used to pull the contact data from Dynamics CRM into CDS_Data
• 02_DynamicsCRM_to_CDS_Accounts – the Inaport profile used to pull the account data from CRM into CDS_Data.
• 01_SFDC_to_CDS_Contacts – the Inaport profile used to pull the contact data from Salesforce into CDS_Data
• 02_SFDC_to_CDS_Accounts – the Inaport profile used to pull the account data from CRM into CDS_Data.

It is suggested that all files be installed into a single working directory.

Instructions on creating connectors to to Dynamics CR, Salesforce, and the CDS_Data Excel file are included in the ReadMe document.

Once Inaport is correctly set up, it will connect to the CRM system and pull the data into the CDS_Data worksheet in the format required for import into the CDM database.

Implementation

Which CDM Entities Should You Use?

In both Dynamics CRM and Salesforce the standard entities to use are account and contact.

In the CDM database there are several entities that might be considered for mapping account and contact data. Candidates include:

• For accounts: Organization, Business Unit, and Account
• For contacts:  Contact, Worker, Application User

At the time of writing it is not obvious which entities to use because the relationships are not clear. The obvious choices are Account and Contact, but the Contact does not have a lookup to Account. The Worker and Application User entities have a lookup to Business Unit. Case, on the other hand, does have lookups to both Account and Contact, but not to Business Unit.

InaPlex has chosen to use Account and Contact entities because they are the most natural for CRM and our expectation is that Contact will be modified. It’s easy, however, for you to customize these mappings, for instance, to add a lookup to Account or to modify the Inaport profiles to use Business Unit and Worker. Contact InaPlex if you need any assistance with this.

Handling Default Values and Required Fields

Several of the fields in the CDM database have default values, or are required to have a mapping.

The Inaport profiles handle this by creating new fields that are then mapped to the target, as shown in the image below.

For example, the PartyType is set to “Person”. For the IsEmailAllowed field, the incoming data is checked and the correct value allocated.

In addition, Inaport has the ability to do conditional tests on data and make changes as required.

Matching

An important part of this process is enabling you to re-run the import as many times as necessary to get the data correct.The profiles are set up to match on existing records in the spreadsheet and update them if they exist, or create new if necessary.

When the data is imported in the CDM database, it follows the same procedure.

Mapping

The Inaport profiles are set up to map the Dynamics CRM or Salesforce fields to the correct CDM fields.These maps can be modified by you to meet your particular requirements or customizations.

Conclusion

Using Inaport to populate the Excel spreadsheet for importing to the Common Data Service provides significant advantages:

• better control over data standardization and normalization
• easier mapping to the CDM fields
• a more robust and repeatable process
• little to no work for subsequent imports
• access to data transformation tools

If you would like a free license and copy of the maps to import data from your CRM system to the CDS, please contact InaPlex today.