-
Discussion Regarding CRM Data Import Strategies
I sometimes spend way more time than I should working on projects that allow me to increase my understanding and knowledge. My current CRM installation is no different. I have invested a great deal of non-billable time on the data import process so that I can better gauge the requirements for future projects of a similar nature. I thought I'd post some of my insights should they prove useful to others in the CRM Community.
Background
This customer has a Business Operations System (BOS) written in Microsoft Access that runs their entire company. We are moving their customer data out of this system into Microsoft Dynamics CRM in order to make better use of the functionality that CRM provides. The main operational features will remain inside of the Access application which has been modified to utilize CRM as the data provider for the customer and contact information.
Note: My customer uses the term Customer for Account and I will that term for this article.
Requirements
The main task was to move the customer and contact information into CRM. A secondary task was to provide the data back to the BOS in a manner that would not require retooling of that application or if modification was necessary, make it a minimal-cost change
Relationships, Relationships, Relationships
The main challenge I faced was moving from a fairly flat database schema into the highly relational CRM schema. The BOS has two tables that store both Customer and Contact information. The two tables are linked to provide a parent-child relationship between Customers.
Strategy
I had to develop a plan for moving this data into CRM, creating the relationships between Customers and Contacts and Parent and Child Customers. After much thought and more than a little trial and error, I finally arrived at a multi-step operation that allowed me to import the data then later establish the necessary relationships.
Tools
Instead of using the native CRM import utility or going with a commercial product like Scribe Insight, I decided to use the Bulk Import sample application that Microsoft released a few weeks ago. I chose this route because I wanted to gain experience with the tool and to better understand its capabilities. I have a couple of upcoming projects that will probably utilize customized versions of Bulk Import so I wanted to know what I was getting myself into.
The tool itself if very well written and contains a variety of interesting programming techniques. I had to introduce modifications that would allow me to move additional data types not supported in the base product, but that was a fairly minor detail.
Bulk Import supports threading so the operations it performs are actually fairly quick. On one import, for the Contacts I think, I was using four threads and was obtaining 22+ creates per second. This allowed me to move roughly 8,000 Customers and 8,000 Contacts into CRM in much less than an hour.
Data Preparation
As I mentioned in a previous article, when you import data into CRM using the CRM Web Services, you can either allow CRM to assign an ID ( GUID ) or programmatically assign it yourself as data provided during the import process. I chose the latter approach and the following techniques:
1) I created duplicate work tables containing data from the two BOS tables.
2) Microsoft SQL Server provides a function called NewID() to generate a GUID.
3) I created columns to record the following IDs:
a) Customer ID
b) Parent Customer ID
c) Primary Contact ID
d) Secondary Contact ID
4) I utilized the NewID() function to assign IDs to each customer and contact, as required, in both work tables, for each record.
5) I also had to perform additional preparation operations such as splitting the Contact name into First and Last names, since that is how CRM requires the data to be imported.
These steps allowed me to have a base set of data that contained everything I needed in order to successfully import our Customer and Contact data.
If I had chosen to allow CRM to create the ID, I would have had to update the Import Database to reflect that value. Since there were so many relationships involved, I found it much easier to provide the ID myself.
Sequencing
Since everything in CRM is related to just about everything else, proper sequencing of import tasks is critical to the success of the operation. When attempting to establish a relationship from one CRM record to another, that record being referenced must already exist within the system. If it doesn't, the operation will fail.
For example: When setting the parentcustomerid for a Contact, you must have already imported the Customer in question before the relationship can be established.
This caused me more than a little mental anguish as I worked through what I thought the "right" or "correct" method was.
Note: I'm not sure there is a "right" or a "wrong" method.
So here was the sequence at which I finally arrived:
1) Import Customers who have Child Customers.
2) Import Customers without Child Customers.
The parentaccountid attribute was supplied from the database to establish the Parent/Child relationship with the Parent Customer.
3) Import Primary Contacts associated with Customers from operation #2.
The parentcustomerid attribute was supplied from the import database to establish the Parent/Child relationship with the Parent Customer.
4) Import Secondary Contacts associated with Customers from operation #2.
The parentcustomerid attribute was supplied from the import database to establish the Parent/Child relationship with the Parent Customer.
5) Import Primary Contacts associated with Customers from operation #1.
The parentcustomerid attribute was supplied from the import database to establish the Parent/Child relationship with the Parent Customer.
6) For those Customers who had valid Primary Contacts, an update operation was performed against all Customer records that would create a link from the Customer to its Primary Contact by updating the value of the primarycontactid attribute to Primary Contact ID found in the import database.
Integration with the Business Operations System
One of the more interesting things we did was to create two SQL Views that would simulate the table and column names that the BOS utilized so that no re-programming was required on the BOS side. These views query the CRM SQL Filtered Views in order to return the requested data. We just modified the location of the linked tables within Microsoft Access and where pretty much done.
The BOS application developer also added a CRM button to the main form that will take the Customer's ID and display the CRM record using the URL Addressable Forms technique outlined in the CRM SDK. This allowed the BOS user to immediate jump to the Customer's CRM record should they need to review additional Customer detail.
In the end, the user now has full access to the features and functionality provided by a true Customer Relationship Management application while still being able to utilize a highly specialized application to run their business operations. The two are linked together so that a minimum of user retraining will be required ( over and above the normal CRM introduction classes ).
Conclusion
What this process allowed me to accomplish was getting all of the data into CRM without worrying about everything absolutely matching up during the first pass – which is how I started off the process and where I wasted much of my time. It also provided a method to verify each step of the operation so that, in some cases, if the import was unsuccessful, I had a checkpoint that I could revert back to without having to delete everything and start over again.
I also fixed a few issues with my Bulk Delete utility that allowed me to delete the CRM records without resorting to using the CRM UI ( and deleting 250 at a time ).
Hopefully, I've provided enough insight into the process to get you to thinking about how you might conduct a similar operation of your own. If not, just ask for clarification.
Have a great weekend.
Development, Dynamics CRM, Installation
(No Ratings Yet)4,404 views
Loading ...
Loading ...