Tag: migration

Configuration Migration Tool: Working Smarter with Metadata Entities

If you mostly work with configuring the Microsoft Dynamics CRM solutions via the customization UI with its graphical point & click tools instead of writing custom code in Visual Studio, you may not keep such a close eye on the changes introduced in the CRM SDK. After all, the term SDK stands for “software development kit” and you may not consider yourself as the target audience if you don’t actually develop any new software that would interface with Dynamics CRM.

The What’s New page in the CRM SDK documentation is a place that would be very useful for also non-developers to have a look at whenever updates to the product are released. Apart from the end user guidance material published on CRM Customer Center, the SDK is pretty much the official place where changes to the platform functionality are introduced. With the latest CRM 2013 Service Pack 1 release (a.k.a Leo) now available, there’s a long list of new and improved features to review. One new addition that didn’t receive a mention on that page, though, is the Configuration Migration Tool, which is a very handy tool if you need to transfer metadata entities across different CRM organizations.

What Are “Metadata Entities”?

In theory the CRM solution files should contain all of the configuration data about your CRM organization. This is what is often referred to as metadata, which could be described as “data about data” in short. As an example, the list of possible values for the Relationship Type attribute on your account record would be considered metadata, whereas the actual account records referring to one of these values would be “real” data stored in the business entities. To illustrate the difference, a blank CRM organization containing only your customizations would not contain any records for the business entities (accounts, contacts, opportunities), but it would contain all of the records in the metadata entities that would be necessary for executing the customized business processes, since otherwise the configuration of that environment would be incomplete.

In practice there are often valid reasons for why it makes more sense to store this type of metadata in a custom entity of its own, instead of using option sets. This provides a whole different level of flexibility in terms of what type of metadata and how much information you can store. Maintaining the list of values can be done without the need to update the customizations. Filtering available records to the end user can be done via standard security roles and BU’s. Building dependent lists on the form can be achieved via the filtered lookups feature. You can leverage the new Quick View Forms to show data from related parental records. And so on.

One problem with using these type of metadata entities is that the data cannot be packaged into a CRM solution file. While you can easily export any changes to the standard customization components and move them to a different CRM organization, getting the metadata entity records from one org to another requires a bit more effort. As a result, keeping the different development, test and production environments in sync requires specific attention to be paid on these metadata during the release process.

Introducing the Configuration Migration Tool

The CRM 2013 Service Pack 1 release includes the new Unified Service Desk (USD) application that relies heavily on storing its configuration data into these metadata entities. In order to support the systematic deployment of USD solutions configured for the customer’s environment, Microsoft has introduced new tools in the version 6.1 (a.k.a. CRM 2013 SP1) SDK. These are the Configuration Migration Tool and the Package Deployer tool. While the latter one is surely useful in deploying larger ISV solutions, I believe it’s the Configuration Migration Tool that can benefit a far wider audience. Also, since you don’t need to run Visual Studio to work with the tool, I’m personally more comfortable discussing its features in my blog as a non-developer who generally steers clear from anything that isn’t close to point & click.

In short, what the Configuration Migration Tool (from here on referred to as “CMT”) allows you to do is 1) choose the entities that you want to migrate data from, 2) export all the records from those entities as a .zip file and 3) import it into a different CRM organization. There’s a great illustration available in the SDK article describing CMT, presented below:

“Awesome, now we have a built-in tool for CRM data migrations!” Well, actually, no. While CMT can move a set of records from several entities in one package and maintain their relationships, it has not been designed for the purpose of business data migration. For example, you cannot filter out any of the records to be transferred from the source organization, nor are there any guarantees for the tool’s performance when moving very large sets of data. Here’s what the Dynamics CRM product team has to say about the tool:

Check out this CMT feature overview video on YouTube for more information on the above points.

Moving Configuration Data with CMT

Let’s see how the tool works in practice. To test the features, I created two custom entities for storing my configuration data: “Category” and “Subcategory”. A category can have many subcategories (1:N relationship), but in order to make the experiment more interesting, I also added a lookup on the category form that allows selecting the default subcategory to be used. So, we have both a 1:N and N:1 relationship between our entities, as shown on the Category form below. For the purpose of testing I added category A with subcategories 1*, 2, 3 and 4, then category B with subcategories 1 and 2*, with the asterisk determining the chosen default subcategory (notice the duplicate names in the subcategory data, we’ll get to that later on).

After launching CMT, choosing to define the schema (step 1) and entering the connection information to log into my own development CRM 2013 server via IFD (hint: enter the server URL with the org name as “org.domain.com” and the user name as “user@domain.com, no other connection option worked for me), we’re presented with a screen for choosing the components to be included in the schema. We can pick from entities included in a specific solution. After selecting the entity, we can either choose which fields we want to transfer data from or just be lazy and add the whole entity into our schema definition. Save and Export will give us an XML file with the contents from the included entities, fields and relationships.

The next step is the data export. Here we’ll choose the schema XML we just created and a target file for the data export. Hitting the Export button will start the process, report the progress for each entity in the CMT app window and finally produce a .zip file containing the data for all the components defined in the schema file.

The last step is to connect to a different organization that already has the same schema. So, if you’ve not yet transferred your solution files into the target organization, now’s the time to do that, since CMT will not modify the organization schema, but rather just assume that it is identical to the source organization as far as the selected entities and fields go. (more…)

June 9, 2014
How to Import Primary Contacts

The Import Wizard in Dynamics CRM can do a lot more than what may initially seem possible. I’ve covered some of these features in a previous article called CRM 2011 Data Import Wizard in Practice. Among these capabilities is the possibility of importing records to different entities that have multiple relationships connecting them in both directions, not just the simple parent-child relationship pointing from one entity to the other.

A typical example of such a relationship would be the Primary Contact of an account. The account is a parental record to all the child contacts, but one of these contacts may however have a 1:N relationship back to the account and be presented in the Primary Contact lookup field on an account form. If you are importing both the account and contact columns in a single file (such as an Outlook contacts export) then mapping these relationships should only be a matter of mapping the right fields. If you have two separate files, then simply zipping them up into a single file will allow you to map both entities in the same import process.

One of the problems that you may encounter during such an import process is that there are multiple matching records for the mapping fields. If you have more than one account record called “Litware, Inc”, to for example represent different regional offices of the company, mapping the primary contact by account name won’t work. Similar problems will arise if several people have the same first and last names. Therefore it’s a good practice to always generate a unique ID for each record before importing it. You can construct the identifier field in Excel with the Concatenate function and combine several fields into a single Import ID string (account name + address 1 city, for example) which you then map into a temporary field in CRM. You can use this field as the lookup reference to the related entity instead of the standard primary field when importing data.

Appending Existing Records

In a recent import task I was once again faced with the Primary Contact issue. Only this time the account data that I needed to map the contacts into was already in the CRM database. As these were new contact records being imported, my first thought was to create a workflow rule to be triggered from the create event of a contact record. Using some temporary contact field to store the primary contact flag into, like “governmentid = PrimaryContact”, and then searching for this value in the workflow rule would have allowed me to start a record update step for the parent account of the newly imported contact. In the update step I could just state that the account’s primary contact would be the contact that the workflow process instance has been initiated on.

Fortunately I looked through the source data once again before proceeding any further, as that revealed a flaw in the assumptions behind the above workflow rule logic. A single contact was sometimes the primary contact for more than one account. Also, there were occurrences where the contact wasn’t the primary contact of its own parent account. The relationships were therefore more complex than what a single workflow rule could cover.

This doesn’t mean that leveraging workflows was out of the question, though. To enable the creation of multiple relationships from a single imported contact record I just needed to have an intermediate stage in my process, to store the data in CRM. What this requires in practice is that you first import the data into a different entity, then trigger the update step from that record into the actual record you want to append with new information.

One option would have been to create a new temporary entity just for the sake of getting the data imported correctly. However, since these were account and contact records for which we wanted to link the imported data, there was already a logical place available in the default data model of Dynamics CRM: connections. It’s in fact the perfect entity for importing any relationship data into, as the two parties of the connection can be references to any entity type that has connections enabled for it, meaning several default entities and any custom entity you’ve created. Therefore we could cover several different import scenarios with connections and not have to go into system customizations to add relationships to other entities.

The Import Process

First I had to add a new connection role for “Primary Contact” (step 1) to identify the connection records that I want to run my workflow process on. As this role will be used exclusively between account and contact records, I specified them as the available record types for the role (step 2). Also, I always tend to put the same role value on the “other side” of the relationship to keep the data consistent and simple to view/search for, so I set this new role to be its own matching connection role (step 3).

Then I proceeded to creating a new workflow process that would be triggered on the create event of a new connection record. In the workflow rule I specified it to run only on connections that have the Primary Contact connection role. I also wanted to validate that the Connected From and Connected To entities are mapped the right way around in the connection record, so I simply check that the account and contact records behind each relationship contain data. Without these conditions being met, the update step wouldn’t produce any meaningful results anyway.

In the update step I mapped the Connected To contact record into the Primary Contact field of the Connected From account record.

Now we were ready to start the actual import work. Since the primary contact relationship data was handled with a separate entity, I first imported the contact records through the normal process. (more…)

July 9, 2013
CRM 2011 Data Import Wizard in practice
Data migration typically isn’t the most joyful part of a CRM implementation, but you really need to pay attention to carefully importing all the relevant customer data it if you want the users to adopt the CRM system as an integral part of their day to day activities, rather than yet another business application searching for its purpose. When implementing Microsoft Dynamics CRM, the logical place to start planning the import process is having a look at what tools are available in the application itself.

The Data Import Wizard used to be a curse word among the Dynamics CRM crowd for a long time, but you shouldn’t ignore this option right away, just because of its bad reputation. Sure, there are many limitations with the built-in tool, but it has come a long way since the previous versions. Having recently spent some hands-on time with the CRM 2011 Import Wizard, I decided to put together some of the useful links and pieces of information I discovered during the process. There’s plenty of great blog posts out there on individual data import features, but perhaps this can serve as a “getting started” tookit for planning on how to import data into Microsoft Dynamics CRM 2011.

Mapping related records based on custom ID fields

The CRM database by definition is primarily a place for storing information about how different objects relate with one another. This means you will almost always be dealing with source data that needs to reference another set of data once imported into the system. In Dynamics CRM these relationships manifest themselves as lookup fields that point from a child record to its parent.

When you are mapping the lookup fields from a child entity into a parent entity that’s already in the system, you always need to consider the possibility of duplicate values in the list of parent entities. Contact names are not unique, and neither are account names in many cases. Yes, you could import lookup references by using a CRM GUID instead of the primary field (most often the name attribute) of the parent entity, but how often would you have that available in your source data to be imported? Exactly.

The first and in my opinion the best improvement with the Import Wizard in CRM 2011 is the possibility to reference the parent entity with an alternative field. Yes, if you have a reliable unique value available in your data, such as customer number or contact email address, you’re free to use that to link your records together. Alternatively, you can construct specific import ID’s out of your data that you first import into a hidden field, then later on use that as the reference which connects your related child records into exactly the correct parent record without the risk of import row failure.

For step by step instructions, check out this blog post by MVP Leon Tribe: Changing the Lookup Reference When Importing Related Data.

Importing data for multiple entities in one go

A common format for customer data coming from non-relational systems is a flat file that contains both account and contact data on the same “table”. In these cases you will have multiple instances of the account’s information repeated on each line where there is an individual contact related to that account. You first reaction might be “oh well, guess I’ll have to split that into an accounts file and a contacts file, then remove the duplicates“. Well, good news: you don’t have to anymore!

Nowadays CRM comes with a built-in data map called “For Generic Contact and Account Data”, which will allow you to import a file that has data intended for both account and contact records. First of all, you can map some of the source fields into both the target entities. Address information is a good example, as it’s typically stored separately on both accounts and contacts (yeah, data redundancy, but often it’s just more convenient for your everyday CRM usage).

Secondly, you will not get duplicate account records from each of the rows, as the Import Wizard is smart enough to detect the distinct parent accounts needed for the child contacts. Now, in order to get the expected results, it’s also up to you to be smart with your source data and field mapping. If any of the fields you’ve mapped to the parent account have any variation in their contents (such as phone numbers with different spacing formats), you will get duplicates, simply because the system will not throw away any unique data rows. Additionally, your child record imports to those accounts will result in failures, as the parent account lookup field will point to a non-unique value in the database (unless you used the aforementioned method to specify an alternative lookup reference). You should also take into consideration if the source data actually has intentional duplicate values for account names, such as branch offices with only a different address.

Check out this article for step-by-step instructions on how to import accounts and contacts from a single file. But what if you need to perform the same type of import, only you’re not dealing with accounts and contacts? Say, importing data to custom entities with a parent-child relationship, like “event” and “event attendee”? No problem, you can build a data map just like the “For Generic Contact and Account Data” one, by leveraging the multi-entity data file import mapping feature.

How much data is too much?

Even if you are importing only into a single target entity, there’s a good chance that you’ll cross the line of allowed maximum size of the import file for the Import Wizard, which is 8 MB. While the XML data import templates available for download from the CRM UI provide very nice features for ensuring the input data is in the correct format, they have the downside of increasing the file size considerably. Compared to an Excel file (xls, xlsx) the size of a file saved in the Office 2003 XML file format can easily be tenfold.

One potential way to get around this limitation is to zip up your import files. You can read the requirements for the zip file contents here, but in general your everyday import files should be zip compatible without any extra tricks. This is actually how the multi-entity data file imports are also handled, as there will only be the possibility of uploading a single file into the Import Wizard to be processed, so you’ll need to package your import files into a zip archive.

In addition to the source data files, you can also include attachments into a zipped import file. Yes, the Import Wizard does support attachment import as well. You’ll need to be careful with the data structure, so have a look at this article for specifications on how to Import attachments with notes. Keep in mind that the 8 MB file size limit does still apply here, so a large number of big file attachments may not be a fun task to perform throug the Import Wizard.

Field and value mapping made easier

If you are working with a data set that contains several picklists with lots and lots of values, mapping them could potentially consume a lot of your time. The first thing you want to make sure is that there are matching values in the CRM picklist fields (nowadays known as option sets) for all the distinct values available in your source columns. Auto mapping will do the heavy lifting for you and match the source and target values as long as they are identical in both. One thing you may not initially notice while mapping the data, though, is that the Import Wizard will also automatically append the list of values in the option sets if it encounters new source values. While it sounds like a neat feature, this may mean you end up with an unexpected set of values, duplicates with slight differences in spelling, breaking workflows or plugins due to mismatch of value ID’s etc. In my opinion, it’s much better to plan ahead and be in the driver’s seat of how your CRM is customized, even if the Wizard offers powerful but dangerous new features that can extend the schema with new fields or even entities.

When you’re working with development, test/QA and production environments, performing the same data mapping procedures time and time again could quickly become a very tedious task. Not only that, but the chances of making a mistake in the process of mapping the fields and values becomes ever more likely if you have to repeat a manual task like that. Luckily Dynamics CRM allows you to save your data maps after you create them (and before you start the actual import job), so be sure to take advantage of this feature. Of course, saving your data map into a test server won’t provide you with that data once you move to production. That’s where the export/import feature of data maps comes in handy. Just create your field mapping once and then take it with you to the next organization you’re working on.

More handy improvements in the Wizard

There used to be limitations on some of the entity fields which you weren’t allowed to update in previous versions. A common pain point was the inability to directly set the record owner, so you had to import this information in a temporary field and perform bulk updates on the records after the import. This limitation has now been removed and you’re free to assign the records directly to users or even teams.

Another caveat of the Import Wizard was that you weren’t allowed to set the state of the imported records, meaning you couldn’t easily import inactive records for historical purposes. Well, now you can, so no more need to leave out information on past activities with your customers, just because you don’t want to re-send all your emails to get them appear as closed activities. Just set your activity status as completed, import opportunities as won/lost or whatever status it is you require.

One thing to note while importing records is that the status change will actually take place after record creation. Why is this important? Well, the closing event will trigger workflows you may have in the target system. Also with the new Activity Feeds functionality introduced in the Q4 2011 update, there’s a chance you may have activity feeds rules in place that will spam your import actions all over the personal wall of your CRM users. Since no one wants to see hundreds of “activity X closed” notifications in their activity feed, be sure to remember to deactivate all rules which could wreak havoc on your brand new internal collaboration channel.

How about update existing?

While creating new records with the CRM 2011 Import Wizard is supported, updating existing records isn’t. In case you would like to only import some new fields for existing customers, by using an identifier field like email address or customer number to locate the records to be updated inside the CRM database, you’ll need to look for alternatives to the Wizard.

It is supported to perform an “export for import” extraction of data from Advanced Find that provides you an Excel sheet you can import back to update records (by selecting “make this data available for re-importing by including required column headings” option). However, unless you’re willing to dump all your records into this Excel and then match them against your import file with your custom ID field by using a tool like Access, to get the corresponding GUID’s, this won’t be the tool you are looking for.

I guess you could also create a temporary child entity for the target entity, then import new records here with the required lookup reference linking them to the parent, followed by a set of workflow magic that would transfer the required values from child to parent. It all depends on how much effort you’re willing to put in working with the out-of-the-box data import tool.

Beyond the Import Wizard: ISV solutions

There will always be many data migration needs that simply cannot be covered with a wizard like application, no matter how much Microsoft would improve the feature set of the Dynamics CRM Import Wizard. At some point it would have so many parameters and options that it would no longer resemble a wizard at all. Since the out-of-the-box functionality has to remain approachable for the “normal” user who just wants to get a simple Excel list uploaded into the system, I’m pretty certain that the market for 3rd party solutions is not going to go away anytime soon.

Instead of rooting for one particular vendor, I’m going to provide a list of the data import solutions that I’m aware of and let you evaluate which one best fits your needs.
As further reading, I recommend everyone to have a look at this awesome article by Joel Lindstrom on Lessons Learned Migrating Data to Microsoft Dynamics CRM 2011. In it Joel lists the most important gotchas to be aware of before starting a data migration to Dynamics CRM, even when using a 3rd party tool like Scribe, such as activityparty data handling, record status, removed users etc.
November 8, 2011