As I revised for the MB2-715 exam (Microsoft Dynamics 365 Customer Engagement Online Deployment) I am creating blog posts detailing all aspects of my revision. I hope these posts will aid anyone who is also revising for this exam. In this posts I will review importing data.
As I mentioned in the post I did covering deployment planning, most implementations of Dynamics 365 will involve importing some data. We also commonly import additional data, maybe as new lists of leads are purchased or we wish to bring data into Dynamics 365 from other 3rd party systems.
I often like to refer back to the skills measured statement! This is because it helps us be aware of the extent of the information that needs to be covered by any revision. The skills measured statement for importing data is as follows;
There are several data import methods available, including;
- Data Import Wizard – From web application, perfect in simple situations.
- Programmatically using SDK – A flexible approach but requires development effort.
- 3rd Party Tools – There are several third-party tools to support data migration. (Such as Scribe or KingswaySoft.)
- Microsoft Data Loader Service – A bulk data loader service from Microsoft for Dynamics 365 Online makes use of Azure to stage the data.
- Microsoft On-Premise to Online Conversion Service – new service to support the migration from on-premise to online. Including all of your data and configurations.
Note: If you are migrating a CRM on premise instance to Online you CANNOT simply take a backup of the on premise database and apply directly to Online. But with Microsoft Dynamics CRM 2011 on-premise and later we can use the on-premise to online conversion service.
The data loader service is mentioned in the skills measured statement, you can read about it here. The idea of the data loader is to import bulk data without needing to write custom code. The data loader is a free service provided as part of Lifecycle Services from Microsoft. Note, this is currently a preview feature only available in North America. (Therefore the details I can offer are limited!)
When planning to import data there are multiple things to consider;
|The volume of legacy data to import.||Options including;
|The effort involved in designing the import routines.||In my experience it is very common for the time required to import data to be under estimated. (Or in some projects missed as a task completely!) This is in terms of the actual import runtime and also the design, development and test effort to create the import jobs.|
|The time required to conduct the import.||With large databases the actual import can literally take days!|
|Data cleanse.||Most (no all) legacy databases will contain inaccurate or redundant data. The purchase of a new solution might be the ideal opportunity to cleanse some or all of this data but again don’t under estimate the effort! However often times the cleanse process can start whilst the data still resides in the legacy application. I like to compare data cleanse to moving house. You can clear out all your “rubbish” before you move. All of those books you’ve bought and haven’t read and never will read! Or you can pack everything up shift it to the new house and trash stuff once in your new home.|
|Specific record types may need special consideration.||
|Storage Requirements||Don’t forget you will need to consider the expected final size of your database. (I did mention the typical space allocation based on licenses purchased in a previous post!)|
Data Import Wizard
The data import wizard isn’t specifically mentioned in the skills measured! But it is a common abd simple method to import data, so let’s look at how it works.
Often you will import one entity at once but it will also be common to need to import related data. In this example I am going to import accounts and contacts. With the contacts being related to the account and vice versa. As a contact can be related to a company and an account can have a primary contact.
First of all, I have created a couple of comma separated spreadsheets and a zip file, as shown below.
My zip file is simply the two spreadsheets (CSV files) combined into a single zip file.
My Account CSV file looked like this. I simply entered account name, business phone and primary contact name. Obviously in the real world you would have many more columns.
One limitation of this approach is that the data will be imported exactly as shown. If you need to do any transformation on your data this should be done in the spreadsheet prior to import or alternatively done programmatically. (Using SDK or 3rd party tools.)
My contact spreadsheet looked like this, notice that the name on the contact is split into first name and last name columns. Mirroring the way the data is held in the CRM contact entity. Also notice that I have added the related account name to link the contact to the account.
Having prepared the data we are now ready to use the import wizard, for this navigate to data management in settings.
Then I select the imports option. In the imports option you will see details of any previous imports, including the number of records successfully loaded and the number of errors. From this screen you can select the import option to start the data import wizard.
Firstly, the wizard will prompt you for the name of the file to import. See that I have selected the zip file mentioned earlier.
Notice that the supported file types are xml, csv, txt, xlsx and zip. With a zip file needing to contain one or more of the preceding file type.
Having selected the file type you click next to progress in the wizard. Now you will be presented with the file or files you are about to import.
Optionally you can control the delimiter settings. Valid options include comma, colon, semi-colon and tab. Plus if text fields have a data delimiter of double quote, single quote or none. (In my case I created the csv file using Excel and the defaults were just fine.)
Clicking next will now allow me to select a data map. If this import has happened before you may have created a data map. (FYI: A data map simply contains meta data to describe which columns in the source file to map to which entities and columns in Dynamics 365.)
Also, some default maps exist to help with importing data from Business Contact Manager (an Outlook add-in) or Salesforce.com.
In my example I selected the default option of automatic mapping.
Clicking next allows me to map the record types. Here we are going to map each file in the import to an entity in Dynamics 365. Below you can see that I linked my accounts file to the account entity and my contacts file to the contact entity.
Click next will the allow me to map all of the fields on each entity. The required fields are listed first followed by the optional fields. You don’t have to map every field in the source file, I have often found I’ve been given columns of data in the source file that I simply don’t want to import!
Notice on my account some fields were automatically mapped. This is because an exact match was found between the column heading in the source file and a Dynamics 365 field. But there isn’t a field called “Business Phone” on the account entity so that couldn’t be mapped automatically. This is easily resolved by manually selecting a CRM field, such as “Main Phone” in this example.
Having mapped all of the fields for account I turned my attention to the contact entity. In this one see how the related account field in my source has not been automatically mapped. This one is a little different as the account field is a lookup.
See how in my example I have linked the related account field in my source to customer name in the contact entity. With customer name being a lookup onto account or contact. When I do this I have opted to say I am going to match to account using the Account Name field.
With all my fields mapped I click next. On the next screen I am simply presented with a summary of the import. Assuming you are happy with everything just click next.
Now before starting the import I have a few options that govern how it operates.
Firstly, I can allow duplicates or not. In my example to avoid any errors I said “yes” to allow duplicates. In a real world scenario, you are likely to opt to not allow duplicates! Now this does hint at a limitation of the data import wizard. If a duplicate record is found, then the row in the import will error. If you actually want to update the existing record when this happens then again you’d need to resort to programming options.
Next we select the owner of the records. Notice that all of the record will be owned by the same named user. Alternatively I would need an owner column in my import file that would contain the owner information.
If this data import is to be repeated then you can save the mapped as a data map for later re-use. One example of when I have done this is when you purchase lead data from a 3rd party. It is likely that you may need to import another set of leads in the same format so saving a data map will save you time next time around.
Finally click submit will start your import.
The imports happen in background.
As your import is progressed you can fresh the screen and see the status change from “Submitted”, to “Transforming”, to “Parsing” and finally to completed “completed”. Assuming your import goes well you will see that the total processed and successes columns will contain the same row count.
However, if you are unlucky and you see rows have errors or had partial failures you may need to review these failures.
Below I have simulated an error by importing the same file again. But this time I told it not to allow duplicates. Meaning all the rows will fail as duplicates! By opening the import and looking at the failures option I can see which rows errored and why.
It is not usual to get several (or many) errors when you first attempt an import. Often this will be because a column is incorrectly mapped or duplicates exist etc. To help avoid these errors you can download a template spreadsheet for imports. If you go to any entity in Dynamics 365 you will find an import option. You can import files directly from here and you can also use the download option to create a template.
So that is the data import wizard. Simples! Well yes, it is simple but like most things with Dynamics 365 I really encourage you to get some hands-on usage as part of your exam prep. Theory alone is rarely enough to pass a Microsoft exam, you also need practical experience.
Hopefully you have found this post useful for your exam preparation.