Symphonic Source put together a plan to divide the data according to business divisions, cleanse and dedupe the data, and migrate the Catalogic data to its new salesforce.com CRM.
For security reasons, direct access to the Microsoft Dynamics system was restricted. Further it was not possible to export the underlying globally unique identifier (GUID) for each record, which tied the records and their related data together. So, working from a 90 GB database backup, Symphonic Source’s data experts began to analyze the database schema and how the data had been structured.
Attachments to the various records posed one of the greatest challenges. Saving a file and re-uploading it to a new database is, in itself, an easy process. But with so many file types (spreadsheets, presentations, diagrams, documents, etc.) and so many records, extracting the attachments and tying them to the correct record in the new database was tricky. In the end, Symphonic Source used the mime type of each file to extract and save the file, and then created a CSV file with the name, description, file location, and the parent record to upload into the new salesforce.com database.
Assign and Manage User IDs
The users table in Microsoft Dynamics also posed certain challenges. Syncsort had more than 500 original user IDs in Microsoft Dynamics, but only about 90 would be created in the Catalogic Salesforce CRM. User IDs had different permission settings restricting what could and could not be accessed. Not only did Symphonic Source have to keep all of the relationships between users and their records, those records owned by non-Catalogic users had to be re-assigned according to Catalogic’s requirements. Symphonic Source created a custom field on the user object in the Salesforce CRM to store the Microsoft Dynamics GUID. Then the Salesforce CRM ID was tied back to the user table in Microsoft Dynamics. Records owned by users in Microsoft Dynamics who were not being migrated to salesforce.com had to be assigned new Salesforce GUIDs in order to properly create and populate the account table. In Salesforce, a batch apex job was run to replace the raw GUID data from Microsoft Dynamics with a friendly name.
Preserve Old Fields
Old field values from more than 100 custom fields in Microsoft Dynamics remained a part of the backup file Symphonic Source worked from. Because each division had different needs with respect to the data, it was impossible to determine which fields remained relevant to business operations moving forward. Symphonic Source’s solution was to create a section on the account object in salesforce.com to store all of the data where it could be used and referenced as needed, while also not cluttering the key data fields for each record. Fields were prefixed with a reference to the Microsoft Dynamics database and hidden from standard user and portal user views.
Record relationships were also an issue. In Microsoft Dynamics, every object exists in two tables, a base table and an extension. When moving to Salesforce CRM, Symphonic Source had to create a holding place for the parent ID so that record data could be correctly re-parented once it had made it into the Salesforce database. To get a full picture of a record, the two tables had to be joined against all tables with lookup values relating to the record. The native merge function of Microsoft Dynamics only performs a soft delete of the record so related objects and attachments function in a lookup relationship to the soft deleted record. Symphonic Source had to maintain this relationship so that objects could be properly parented in salesforce.com and then dedupe with a hard delete.
Accounts were loaded into Salesforce CRM first, creating Salesforce ID numbers. Those ID numbers were then linked back to Microsoft Dynamics contacts and opportunity IDs, as well as custom objects, creating join requests in order to create the correct relationships in Salesforce.
Address Time Gaps
Because Symphonic Source was working from a backup file of Microsoft Dynamics data, by the time the migration process was completed and the new database constructed, the data backup was nearly two months old. In the interim period, all divisions of Syncsort and Catalogic continued to use the Microsoft Dynamics database. To compensate for any changes to the data set in that period, and to ensure the most current, complete, and accurate data in the new salesforce.com database, Symphonic Source ran a “Modified Report” from the Microsoft Dynamics front end in order to bring over to salesforce.com any changes to the data in the time since the backup file had been created.
In Microsoft Dynamics, fields don’t always correspond to the tables where the data is stored. The order of steps Symphonic Source followed was to first create Microsoft Dynamics users as Salesforce users, then create account tables in Salesforce, and finally to recreate the fields from Microsoft Dynamics in Salesforce. Because of the data’s importance to forecasting, reporting, and operations, it was critical to understand what a “field” is in Microsoft Dynamics versus Salesforce and how they relate.
“It is critical to ask the right questions in order to make the right choices for how to migrate and structure the data,” said Symphonic Source’s Nielsen. “The discovery phase of the project is arguably more important than the actual technical migration of the data. Had we not spent time talking to Syncsort/Catalogic, their data could have easily and irreparably been damaged.”