CRM On Premise to CRM Online Data Migration Strategy

Problem Statement

More than a problem in itself, the article describes the strategy I used for data migration from CRM 2011 on premise to CRM 2016 online. With the blog, I intend to present a data migration strategy which may serve as a guide for migrating data.

In my last project engagement we had to migrate data from CRM 2011 on premise to CRM 2016 Online. The total size of the data which had to be migrated was close to 50 GB. Most of the entities that were to be migrated had 1: N, N: 1 and N: N relations which had to be migrated as well.

Using the already available tools like Kingsway it’s easy to migrate the data across to the destination organization. However we still need to think of the mentioned below points while designing our packages

  • Maintainability of packages – Designing the packages in a way that
    • Avoids creating duplicate records in the CRM organization.
    • If a package is stopped and rerun, it should only retrieve the records which are yet to be created in Dynamics.
    • Provide error logging for the records which are not inserted in the organization.
  • Increases the overall speed of packages. That is number of operations that are being done in the package each second.
  • Maintaining the sanity of Data, i.e. relations between the migrated records should be maintained.
  • In some cases it’s also important to map the created records to the same owner as in the source organization.

Designing the Packages

a) Setting up Master Data – Most CRM implementations have certain entities which have very few records (no 1: N, N: 1 or N: N). the record count is also like less than 1000. We can easily utilize CRM out of box import excel or csv for this.

b) Outlining the hierarchy of records – While migrating data, we need to ensure that we must migrate the parent records first. Otherwise the reference field in child records will not be set. For example before migrating activities we must migrate the parent entities like contact account, opportunities etc.

c) Creating a Staging DB having temporary success and error tables – To ensure that we do not end up creating duplicate records in the target organization, we can create two tables’ success and error. Only required columns like record unique GUID, name identifier or error message can be added to the tables.

The idea is to do a join between the source CRM table and this success table. This will help us in identifying the source records which have not been inserted in target CRM organization. Mentioned below Visio diagram explains the flow in general.

datamigration

 

 

A Note on Creating Retrieve Queries on Source CRM Database

In some cases, the user under whose context the packages are executing, might not be a user in the source CRM organization. Due to this any query on Filtered Views, will return zero records. Due to this, the Query needs to be run under the context of a different user. Mentioned below is the way this could be accomplished

DECLARE  @uid uniqueidentifier

SET @uid = convert(uniqueidentifier, ‘64061C21-DE18-E611-93E1-0050569A0D99’)

SET CONTEXT_INFO @uid

Select * from FilteredCompetitor

64061C21-DE18-E611-93E1-0050569A0D99 is the unique ID of the admin user in the Source DB. We can get it by just executing a Select query on table “SystemUser”

A Note on Increasing the efficiency of packages

When the project is being executed, there are several counters available wherein you can check and monitor the bottleneck.

Just from the perspective of SSIS, there is a component Balanced Data Distributor. The idea of this component is to distribute the source into equal sub streams. Each sub stream can then be added to the custom code component or Kingsway software do perform the CRUD operations in CRM.

A Note on Online Storage Limit

Till Dynamics CRM 2016, by default all instances are allotted a memory space of 5 GB. Therefore it is recommended to consult and filter out unnecessary records. For example we may not need closed tasks, phone calls, appointments along with their attachments.

A Note on Mapping the Owner Field for records

By default all the records created in the target organization will be assigned to the owner under whose context we are executing the organization request.

To assign the records to their respective users, we need to firstly create the users in target organization and then do a retrieve the user id using a condition on their name. After getting the ownerid we can then set the ownerid field

A Note on maintaining N: N relations

As we need record ids for each of the associated records, we must do this operation in a separate package. We can do this by doing a lookup operation to success tables of each of the migrated entities and then executing the associate request.

 

 

 

 

 

 

 

 

Advertisements

Programmatically creating a custom view and adding Not In condition on Child entity records

Problem Statement –

There are several views which are not possible through the CRM out of box view filter editor

For example – Viewing all Contacts which do not have any appointment scheduled last 2 months

Out of box we cannot create a fetchxml to filter these contacts. However mentioned below are the two solutions we can use

Possible Solutions –

  1. Using a Rollup Field – On the contact we can create a custom field with data type as integer. The value of the field can be configured based upon the related appointments.

img1

img2

 

As you can see by the highlighted text, the solution will only work in case of static values. That is if I want to put a filter for appointments where date not in last month I cannot do that.

 

  1. Programmatically creating a Not In View between Contact and Appointment – In CRM we do have a request wherein we can create custom views programmatically through SDK.

While creating the view, we can add whatever fetchxml we want in the query. For our case (Contacts not having any appointments) we can use the mentioned below fetchxml

<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’true’>

<entity name=’contact’>

<attribute name=’fullname’ />

<link-entity name=’appointment’ from=’regardingobjectid’ to=’contactid’ alias=’ab’ link-type=’outer’>

<attribute name=’regardingobjectid’ />

</link-entity>

<filter type=’and’>

<condition entityname=’ab’ attribute=’regardingobjectid’ operator=’null’ />

</filter>

</entity>

</fetch>”

 

In the filter expression we can always add additional conditions as per our requirement. Then we can use the mentioned below pseudo code for creating the view.

 

System.String layoutXml =

@”<grid name=’resultset’ object=’2′ jump=’fullname’ select=’1′

preview=’1′ icon=’1′>

<row name=’result’ id=’contactid’>

<cell name=’fullname’ width=’150′ />

</row>

</grid>”;

System.String fetchXml =

@”<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’true’>

<entity name=’contact’>

<attribute name=’fullname’ />

<link-entity name=’appointment’ from=’regardingobjectid’ to=’contactid’ alias=’ab’ link-type=’outer’>

<attribute name=’regardingobjectid’ />

</link-entity>

<filter type=’and’>

<condition entityname=’ab’ attribute=’regardingobjectid’ operator=’null’ />

</filter>

</entity>

</fetch>”;

 

Entity savedQuery = new Entity();

savedQuery.LogicalName = “savedquery”;

savedQuery[“name”] = “A New Custom Public View”;

savedQuery[“description”] = “A Saved Query created in code”;

savedQuery[“returnedtypecode”] = “contact”;

savedQuery[“fetchxml”] = fetchXml;

savedQuery[“layoutxml”] = layoutXml;

savedQuery[“querytype”] = 0;

 

Guid viewId = serviceProxy.Create(savedQuery);

 

After the code is executed, we can see the view in CRM.

img3

Dynamics CRM Integration With Marketo

What is Marketo?

Like Mircosoft Dynamics Marketing, Marketo is a Marketing automation software which provides solutions related to

  1. Lead Management
  2. Sales Insight
  3. Revenue Cycle Analytics
  4. Social Marketing

It provides connectors which integrate the Lead, Contact between Dynamics CRM and Marketo.

Problem Statement

I recently did a project engagement in which the Sales Process was maintained in Dynamics CRM however the Marketing activities were to be handled in Marketo.

After the lead was captured and created in CRM, using out of box connector the lead was synced to Marketo. Marketo then handled the marketing activities for the lead. However there was no sync of marketing activities between Marketo and Dynamics CRM.

Therefore a sales person in Dynamics CRM was not aware of the marketing activities that were carried out of the lead in Marketo.

Solution

Marketo exposes Rest API’s which expose various information on the lead. We can use this API to retrieve lead activities. When a lead is synced between Dynamics CRM and Marketo, a lead id field is populated in Dynamics CRM. This is the unique reference field for the corresponding lead in Marketo.

Each call made to the Marketo API needs to be authenticated. Mentioned below are the fields that need to be maintained in Dynamics CRM what are required for authentication

  1. UserID – The userid configured should have API access privilege in Marketo.
  2. Password – The password of the corresponding UrserID.
  3. Marketo URL – Like Dynamics CRM exposes a discovery service for connecting to the organization, Marketo also exposes this url for account identification.
  4. Marketo Client Secret Key – A key that needs to be passed for authentication purpose.
  5. Marketo Client ID – A client id that needs to be passed for authentication purpose.
  6. Marketo Identity URL – Like Dynamics CRM exposes an Organization API, Marketo also exposes the API for various requests.

Pseudo code

  1. Using the authentication settings, we firstly need to find the access token

For doing so, make a REST Call to the mentioned below URL

Marketo Identity URL  + “/identity/oauth/token?grant_type=client_credentials&client_id=” + this.clientID + “&client_secret=” + this.client_secretID;

The result will be returned in JSON format. Deserialize the returned object and retrieve the value of access token.

  1. Now using the access token returned in the previous step, make a call to retrieve lead activities using the mentioned below REST Call

Marketo Identity URL  + “/rest/v1/leads.json?access_token=” + this.accessToken + “&filterType=leadid&filterValues=” + leadID(Synced field between Dynamics CRM and Marketo) + “&fields=id”;

The result will be returned in JSON format. The captured activities can then be shown in Dynamics CRM using a web resource or server side plugin.