CRM Data Migration Part 3: Framework

CRM Data Migration Part 3: Framework

In 2016 most new CRM installations are Cloud based, including Salesforce.com, MS Dynamics Online and Oracle Sales Cloud.

There are many options for tools that will move data into both the Staging area and the CRM. For the purpose of this article I will use Informatica Cloud.

Performance

Performance depends on a lot of variables such as the speed of your internet pipes, servers, types of cloud systems used, etc.

It is worth noting that considerable data migration may cause a significant impact to your framework. For example you may need to move a copy of your data to an area that can reach through your DMZ. How long will it take to copy 10k records over to your staging environment? How long will it take to copy those fixed records into your new Cloud platform? How long can you take your CRM system offline during migration?

On-Premise Approach

You can setup your staging environment and ETL tools on-premise. This has the advantage that you have some control over environmental variables. Remember that you may not have total control, which could prove signifiant. What if they are other ETL processes running overnight that use up all the local bandwidth you were looking for in your migration?

Cloud Approach

Although you lack some of the direct control that on-premise offers, you do nevertheless have a more reliable environment that is dedicated to you and segregated from other services running on the cloud platform.

To keep with the Cloud theme I will also use a Microsoft Windows Server with SQL Server as a Microsoft Azure VM for the Staging database. Any other cloud (or on-premise) database solution such as AWS would work just as well, as well as a solution leveraging the Azure SQL Database Service.

cloud

Given that you can get free trials of most Cloud software, you can actually setup a ‘proof of concept’ and seeing how it works for you quite quickly. In my example this applies to Microsoft Azure, Salesforce and Informatica Cloud.

One benefit of the Cloud based approach is that you can provision your framework just for the duration of the migration. When the migration has been completed and signed-off then you can just turn it off and stop paying for it.

 

 

CRM Data Migration Part 2: Scenario

CRM Data Migration Part 2: Scenario

In order to illustrate how a migration will work, I have decided to migrate my (hypothetical) sports business from a custom solution based on Microsoft SQL Server to Salesforce.com.

Luckily Microsoft provide a good example of a legacy system : Adventureworks! You can download the database, attach it to whatever SQL Server instance you have (even the free Express edition) and treat that as your legacy source.

The great thing about the AdventureWorks is that it illustrates very well how differently two CRM systems can treat a customer. This is the data model of ‘Person’ in AdventureWorks, which we will have to migrate to Salesforce.com’s ‘Contact’ object:

Person2.png

We can already see that a good amount of the data structure is not required. We don’t have to migrate the data in ‘StateProvince’ and ‘CountryRegion’ because it already exists within Salesforce.com (although the values will need to be matched still). The ‘Password’ table should almost certainly not be migrated, although it leaves a tricky question about how to bring that (presumably) web site security login functionality to the new system. Many aspects are highly denormalized (limitless phone numbers, email addresses, etc).

Basically a simple mapping will not be possible. The flexibility of the relational database will make your migration task difficult. It is important to determine what data is truly important to you.

CRM Data Migration Part 1: Conceptual

CRM Data Migration Part 1: Conceptual

Introduction

So you’ve decided to invest in a brand new CRM system. Given that most CRM systems are customized to some degree, moving data from any source to target system has many issues.

Please note that these articles are only about data migration. CRM system design is a different subject altogether.

This is also not a step by step guide. It is really just to describe some (and not all) of the challenges that you may face, as well as some examples to show how a migration framework could work.

Migration frameworks cannot be purchased off the shelf or downloaded, but rather a combination of working out an approach that works for your business scenario and matching that with the appropriate technologies that address that approach. You need to consider a good number of aspects before you even begin to think technically about it. The conceptual areas below may help with this.

 

1. High Level Steps

Conceptually you can have three steps:

Concept

Step 1: Source can be any data repository you have. An Excel spreadsheet, an existing CRM system, a data warehouse, etc. Additionally you can have multiple sources required for a migration.

Step 2: Stage is where you consolidate, massage, enhance and prepare your data sets for loading.

Step 3: Target is where the data ends up

It is worth noting the Step 2 can be optional. If your source data is of acceptable quality, your transformation requirements are simple and your ETL tool has enough functionality then you could just go from Source to Target.

2. Master Data

Does your source CRM contain all of your master data? What if it needs to be combined with other data such as a separate system that manages products? What if other departments in your company want to maintain their own master lists of customer information?

 

3. Multiple Source Systems

It is common to merge multiple systems together into the new CRM. For example your company has acquired another, and you want to use this opportunity to consolidate your customer systems. You will need to think of merging rules, such as what to do when the same distinct customer exists in both systems and which data come first? Do you accept that the ‘losing’ data source will not be used?

 

4. Change Deltas / Cutover Plan

Data may not always migrate cleanly in a CRM system. For example, an Opportunity may be in an open state and be waiting for further workflow actions. Is it a good idea to move it now or wait for it to close? Moving it now is the quickest way forward, but may lose your business valuable opportunities if now migrated correctly. Waiting for it to close means maintaining your old CRM system and then migrating the delta, which may itself be complicated to work out (i.e. a Contact details could be updated in both old and new systems whilst the related Opportunity remains open. Which has primacy?).

 

5. Difference in Data Structures

Many consultancies will direct you to not consider the design of your old CRM system when building your new CRM system, but rather ‘focus on the business outcome’ desired for the upgrade. This is all fine in theory, but legacy data structures were created for a reason (even bad ones) and you will probably find that you will either have compromise the amount of data each structure brings across, or invest heavily in ETL techniques to achieve full data.

6. Data Completeness and Business Buy In

Realistically you are not going to migrate 100% of the data to be found in your legacy CRM system. Do try and do so would incur a considerable cost, and probably impact the effectiveness of your new CRM by filling it with low value data. It is better to identify the key data sets early and get agreement with what you are leaving out. For example, does the new CRM require customer records that have been disabled? By reducing the scope of the data to be migrated you are increasing the chances of a successful migration.

 

Informatica Cloud and Windows Server 2012 R2 – CreateProcess error=14001

I just setup a new Windows 2012 R2 server with SQL Server 2012, and hooked it up to Salesforce with Informatica Cloud.

The configuration of the connections was very easy, as was the mapping of source to target fields.

However when I tried to run the mapping I got the following error:

“Internal error. The DTM process failed to start due to the following error: [CreateProcess error=14001, The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log or use the command-line sxstrace.exe tool for more detail”

Apparently this is just down to the Informatica Secure Cloud Agent not having VC++ binaries installed. This is rather confusing given there isn’t very much on this error message. Anyway I found and installed the the VC++ and all worked well.

 

Informatica KB on the issue:

https://kb.informatica.com/solution/23/Pages/4/157468.aspx

 

Link to the VC++ binaries that everyone seems to insist that you don’t need:

https://www.microsoft.com/en-in/download/details.aspx?id=15336

 

Recover lost Powerpoint files in Office for Mac 2016

I’m finding the new Microsoft Office for Mac 2016 rather buggy, and this evening PowerPoint crashed losing all my changes throughout the day!

Powerpoint has a ‘Restore’ command under its ‘File’ menu, which was… empty.

There are lots of answers on Google to restore under this scenario for PowerPoint 2011, but nothing for the new version

So after working backwards from a solution for Excel 2016, I discovered that you just have to look in:

/Users/”User Name”/Library/Containers/com.microsoft.Powerpoint/Data/Library/Preferences/AutoRecovery

(Obviously change “User Name” with that of the user you lost the file under)

You should hopefully see some files with the word ‘_autorecover’ appended at the end. Identify one with a timestamp close to where you want to recover.

Copy that file somewhere safe (such as the desktop) and give it a .pptx suffix at the end.

Finally you can try opening with PowerPoint but it may tell you that it is corrupted. If this happens try one of the following strategies:

  • Change the suffix to .ppt and try open again with PowerPoint 2016
  • Open with Apple KeyNote instead
  • Change the suffix to .rtf and try open again in PowerPoint (this was suggested in the comments, I haven’t tried myself)

Salesforce.com and the Site.com Content Management System – Part 3

Part 1 and Part 2 of this series was about how to create a simple site in the Salesforce.com Site.com Content Management System.

I left the Publish part for Part 3, since this can be a discrete action that a business may want to execute in isolation from the content generation.

You probably want to have a look at the simple ‘Site Configuration’ panel, which will give you some options around ‘click jacking’ (preventing the rendering of your site in an iFrame from another site), as well as allowing you to set the HTML files that will be used for the default ‘Home’ page as well as the ‘404 Not Found’ page.

‘Enable Anonymous Preview’ is useful if you want to test from a variety of devices.

Screen Shot 2015-07-21 at 11.34.15 AM

There are a few more panels which provide the ability to:

  • Custom properties : Constant values that could be reused throughout the site, such as a phone number
  • Branding properties : Expressions for use in Stylesheets
  • IP Restrictions : Restrict access to specific assets in your site based on an IP range
  • URL Redirects : Single place to manage redirects
  • User Roles : All users with access to Site.com will be listed here already
  • Languages : Add other languages variants to your site

Unfortunately my Developer edition does not allow publishing as part of the licensing, however if I had another edition then I would see a ‘Publish Changes…‘ button on the toolbar. (Note to Salesforce: Why not just have the button disabled? Hiding it makes no sense at all).

You should then be able to see your site published. The following screenshots are cheating, since I am just using Preview, but it should look exactly the same.

previewdesktop

iphone

It is also important to note that you can duplicate and export entire sites, so it is possible to experiment as much as you like in your developer instance and later export to production when you are ready.

Site.com manages basic web hosting quite effectively. It is not versatile enough for complex web hosting, and in a way it is probably a good thing because at this level of functionality it is possible to give business users a direct method to publish content.

I would still probably advise a technical administrator actually oversees the process however, since you are restricted to 25 sites, which means inevitably you will get reuse, deletions and overwriting of existing content.

Salesforce.com and the Site.com Content Management System – Part 2

My previous post gave a quick look at creating a new Site.com site.

I thought this post would be about the difficulties around getting a ‘real world’ site to work, however I was surprised how easy this actually was.

I identified some HTML5 sites that can be freely downloaded as ZIP files. I chose the Parallelism example, which has the folder structure on the left.

As you can see it is fairly comprehensive, with one html main page along with lots of CSS stylesheets, images, fonts and javascript.

It is also a good example because it is ‘responsive’, and should render appropriately on different browsers and devices.

You can run this example just by opening the index.html file in your local browser.

Screen Shot 2015-07-21 at 10.52.06 AM

Download the ZIP file containing the site you want, and do not extract.

Screen Shot 2015-07-21 at 11.07.54 AM On your empty site, just click the ‘Import‘ button
Then select the ZIP file and import it with all the options ticked.  Screen Shot 2015-07-21 at 11.15.06 AM

And that is … it. Site.com automatically places all the files into its own folder structure:

Screen Shot 2015-07-21 at 11.24.16 AM

You can then click the ‘Preview’ button to see how it looks hosted on Site.com.

The great thing about this is that for relatively simple web content that you may receive from a Creative Agency, then the technical barrier to directly import, preview and then publish that content is very low indeed.

My next post will look at the Publishing action.