MB2-710 – Importing Data

As I revise for the MB2-710 exam I am creating posts covering all aspects of my revision. Hopefully collectively they may help others prepare for the MB2-710 certification. (Microsoft Dynamics CRM 2016 Online Deployment.)

In this post I will look at importing data.

The skills measured statement for importing data is as follows;

Import data
- Compare the methods for importing data; import data by using the Import Data Wizard; identify limitations of import processes

Import Methods

It will be common when deploying CRM to need to import existing data There are several methods available, including;

Data Import Wizard – From web application, perfect in simple situations.
Programmatically using SDK – A flexible approach but requires development effort.
3^rd 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 CRM Online provided as part of the lifecycle services for dynamics.

Note: If you are migrating a CRM on premise instance to Online you CANNOT take a backup of th on premise database and apply directly to Online.

Planning

When planning to import data there are multiple things to consider;

The volume of legacy data to import.
Options including;
- Import all legacy data.
- Apply a cut off. Do you really need 10 years of sales history? Will the last 12 months be sufficient?
- Import no data! It is not uncommon to start with a “clean sheet of paper”.
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!)
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.
Many of these will be CRM system entities. There are many examples a few are mentioned below;
- If you are migrating from CRM on premise too online. Some entities such as quote have a default status. All newly imported quotes will be in draft status, meaning you may only be able to easily migrate the draft quotes. If you require older completed quotes these may need special consideration.
- If you have time bound data such as phone call activity data, you will need to consider the implication of created on and modified on columns. Out of the box many views will be sorted by either created on or modified on date. These system fields may reflect when the data was imported not when the activity was originally completed.
- Record ownership, say an opportunity needs to be owned by the sales person who created it. When importing historic data, you may need to import data created by people who no longer work for the organization. Meaning you will need to consider how to assign ownership on these historic records.

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 is specifically mentioned in the skills measured, 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 an account 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.

Tip:
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 3^rd 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.

The 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 CRM.)

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 CRM. 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 contact that some fields were automatically mapped. This is because an exact match was found between the column heading in the source file and a CRM field. But there isn’t a field called “Busines 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. 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. Again this points to a limitation. If each row in the source file needs to be owned by a specified user, you will need programming options again.

Finally, 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 3^rd 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 a file that contained a duplicate primary contact record. 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 CRM 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 CRM I really encourage you to get some hands-on usage as part of your exam prep. Theory alone is rarely enough to pass a CRM exam you need practical experience.

Hopefully you have found this post useful for your exam preparation. J