+ Start a Discussion
justdevjustdev 

Data migration - what is best approach?

Hi,

For data migration (Accounts with contacts, Lead, Opportunities etc), we are trying to determine what is the best approach. Would somebody be able to provide answers to following questions:-
1. For a large size of data (say 25000 customers with multiple contacts per customer, about 25000 opportunities etc), will excel upload be adviceable?
2. I read in documentation about LexiLoader. Is it viable option for large data migration?
3. If we design a migration accessing WebService API, is there any available statistics about average time for creation of records?
4. Is it possible to do bulk loading or multithreading if we use WebService API to cut down on conversion timing?

Thanks,
Ayyappa
ScotScot

If you are on the Enterprise Edition, you'll want to include the Sforce Data Loader in your alternatives. It will probably be the fastest option.

There have been comments about the timing of loads in these forums, though it may be tricky to find them ...

EnderEnder
1. For loads of that size, Excel may not be the best solution -- it really depends on the nature of the data and data load, and the Excel code that is executing the update. I have seen in the past memory issues when loading very large amounts of data with Excel, but have not tested it recently (within the last 6 months).

2. LexiLoader aka the Sforce Data Loader was designed solely to perform large Data Migrations. So, yes it is a viable option for large data loads. (LexiLoader was the internal name, it was rebranded Sforce Data Loader when released, but you can call it LexiLoader if you want ;-)

3. The average load will vary, it will depend on a couple of factors. First, which program you are using, and does that program use compression, persistant connections, and other optimizations (the Data Loader and new Excel plugin both do I believe), and the speed of your internet connection. Second, what is the width of your data. Small records can go in faster than 100,000+ an hour, large ones around 15 - 20 K an hour. I haven't done a statistical analysis, so I'll let one of the Product Managers list official numbers, but I usually see above 40 K an hour. The type of entity makes a difference, and if you have complex sharing rules or workflow rules, the speed will be affected as well.

4. Many of the dataloading tools do batching of records, which is what I assume you mean by "bulk loading" The data loader definitely does.

I'd also like to take a moment to clear up a common misconception. Many people ask is the Excel plugin faster than using the Web Services API, or is the Data Loader faster than the Web Sevices API. Well, they are one and the same. All sforce applications use the Web Services API -- the data loader makes the same xml requests that the quickstart does. The difference are the optimizations, UI, and other client functionality, but in essence, they are all doing the same thing.

And as a side note, if you decide to use the loader, save your success.csv files to help you map your child entities to their parents. I.e after your account load, use that success.csv to map the sfdc id of the accounts to the corresponding contacts. The loader should be the fastest way to load data.

Cheers.
RRESRRES

This really depends on what platform your moving data from (ie. act, goldmine, outlook)

Best Option:

1. Move all your data into an access database

2. Purchase demand tools.

3. Import data from access tables using mass effect (part of the demand tools suite)

If you need help along the way feel free to email me.

Rich

justdevjustdev
Thanks Ender, RRES and Scot. The comments were really helpful. My data will be hosted on Oracle 9i database.

From my understanding so far, one disadvantage I see with data loader is, it will be difficult to build relationships between data elements. If we have to load customer accounts, contacts linked to them, build relationships between customer accounts (build hierarchy), we will have to manually do the linkage using IDs posted back into success.csv.
I understood that if we develop a custom migration for each object (Customer, Leads, Opportunity etc) we need to account for persistent connection, inserting a batch of 200 records, compression etc.

So i think for large data loads, it will be a trade-off between dataloader and custom migration interface. If there are not many relationships to be build or rules to be processed, dataloader may be a good option.

Let me know if you anybody has any comments.

Thanks.
RRESRRES
The best way to build relationships is with access. Create a query to define the relationship you are looking for. Then either use demand tools or data loader to move the data in. Once the links are setup from the make table query you defined, the data will be linked in salesforce.
EnderEnder
Yes you are correct, you need to manipulate the csv files to create the relationships with the dataloader.

If you are going the custom development route, and choose java, the data loader is open source. You could use it as a reference application or even enhance it to connect to oracle. It shouldn't be too hard to swap out the csvwriters and include a jdbc connector instead. Then creating the relationships should be very easy. Hopefully it saves you time.

And personally, I would recommend against using Access for any large dataloads. Access does not scale very well, and is flaky at best when it comes to connections and performance. You already have your data in Oracle -- no need to move it out to another non-enterprise database.
darozdaroz
I gotta 2nd Ender on this one. Modifying (extending) the Data Loader is probibly your best bet. I'd also stay away from access in this situation. Chances are if you're working with Oracle 9i now you have the skills to manage your relationships with it. JDBC direct to the oracle DB does sound like the best way to do this.

And a word of advice from experiece -- if you have unique IDs in the oracle DB import them into SFDC in a hidden custom field anyplace you put the data. If you find later your relations weren't 'quite' right or you have an anomolous record you'll be able to back track it to the source even if substancial portions of the record had changed.
sanjaykumarsanjaykumar

Hi All,

I needed to ,migrate the data from JD Edwards one to Salesforce.com, I want to do this through Java , can u help me a way out or guide me the way,Please let me know the different ways Data could be migrated from one application to another using different file  formats.

Thanks in advance,

Sanjay

FutureFuture
Were you able to load data directly into any of the History related lists?

Thanks...