Chapter 20: Managing Data Migrations

Since most clients have some kind of system they had been using before NetSuite, successful data migration is almost always required before a client can go live. Migrations don't happen right at the end of the project, though. You and your clients must carefully plan for a successful migration, and that plan needs to include test imports long before the exciting go-live date arrives.

In this chapter, we will cover the following topics:

• Planning for the data migration process
• Performing imports for testing and other reasons
• Planning the cut-over activities
• Performing the final data imports before go-live

After completing this chapter, you should be able to talk to a client about their data migrations and help the client plan them. Most clients can't do this on their own, so experienced guidance is always necessary to get through this milestone.

When planning, testing, and executing a data migration, you need to work with the various owners of each type of data the client will use in the account – items, lists, entities, and transactions (plus any custom records the client will go live with). The clients should be familiar with the NetSuite basics and also using the CSV import feature since that's the most popular way we bring data into an account.

Planning for the data migration process

When is the right time to start planning for the data migration process? The answer is soon after implementation project begins. At this stage, you should know how the client has been running their business before they came to NetSuite, and so what types of data are needed. Ideally, in your company's sales process, you should have already addressed the types of data that need to be migrated, how much historical transaction data will be required, and which open transactions should be included in this process. The most common approach we see with clients coming into NetSuite for the first time is to migrate these data points:

• Lists (chart of accounts, locations, subsidiaries, departments, and so on)
• Items (inventory, non-inventory, assembly, service, and so on)
• Entities (vendors, customers, partners, and so on)
• Historical transactions for a short period (6 or 12 months at most)
• Open transactions (sales orders, purchase orders, and bills)

Anything the client asks for beyond these basics should be carefully discussed and reviewed since, for instance, trying to bring in all of the company's historical transactions in detail can be a huge job, which can massively slow down the project's timeline and involve a lot of extra effort for all the parties involved. When clients have insisted on this or something like 5 years of transactions, in the past, we make sure they understand how difficult this can be and how this will cost them extra since this is not usually required in their initial contract. Most clients will understand limiting their migration to data they can use in NetSuite, and that helps avoid a long, drawn-out migration process.

Important Tip

One exception to this process, which I've only had to do once, happens when a company is moving from one NetSuite account to another. In this case, such as when a subsidiary is split off from their parent company, the demand to bring all of their NetSuite data into the new account has more weight behind it. NetSuite does not have an easy option for doing this, though, so planning for this kind of migration is even more important in this situation.

The plan for data migration usually consists of three phases once the schedule has been planned: data preparation, testing, and the final migration. To make a schedule, we have to start working out the dates for the plan's milestones as early in the project as possible. After that, in each project's migration plan, the next thing that must be clear is who is responsible for the data imports. It should be stated in the client's contract whether they are expecting the consultants to do this work or whether they should do it themselves. This choice will drastically change the work you have to perform. In general, with NetSuite-led implementations, we ask the client to own this task, and we just guide them to a successful outcome. In cases where we are contracted to deliver the migration ourselves, we have teams who specialize in this activity and they work with the client's users to gather the necessary data, clean it up, and run the test and final imports according to a schedule that's been laid out from the beginning.

In all cases, the implementation team should be there to guide the client on this topic. For smaller companies, we can usually just hold a meeting with the client's stakeholders once, explain the schedule of activities, and let whoever is going to run the import begin their work. Most small companies can manage this on their own, with us making sure they stay on schedule and perform the tests they need to. Nobody wants to find out two days before go-live that all of the Customer records include the wrong values or that the phone numbers are all in a format NetSuite won't work with.

For larger companies, or projects where we have a lot of data to migrate, we typically want to write up a strategy document, spelling out all of the data migration details, including the data to be imported, the schedule we'll follow, notes on any special data handling or cleanup we know will be needed, and so on. We don't consider this as a contract, but rather a guide everyone can follow once it's been reviewed as we work through the migration together.

It's at this time that we want to make sure we all understand exactly where all the data to be imported originates. In many cases, it doesn't all come from just one legacy system, but a set of applications. For each of those, we want to be sure we know how the data will be extracted and in what format we'll have it at that stage. For instance, it's not uncommon for a client to say that their legacy ERP system makes it difficult to extract all of the data we need, or to get it into a format more or less like NetSuite expects. In these cases, we need to plan to perform the extraction and clean up the data, making it ready to be imported into NetSuite. Some clients need help with this step, but many have people on staff already who can transform data files from one format into the NetSuite CSV format pretty easily. Some contractors can help with a process like this as well, of course.

Once you have a plan and you know where the data is all coming from, you can start to see whether it's ready to bring into NetSuite. That's why testing is so important, so let's dig into how we can do that next.

Performing imports for testing and other reasons

Once you start to collect a set of files from another system, you want to bring them in, in a limited fashion, to test the waters for the migration. The first step in this process is typically helping the client know what NetSuite is looking for, with each of the lists and record types they want to import. The NetSuite services team typically provides templates, in a set of Excel files, to show the client which columns are to be included with each import, which of those are required or optional, and what the data type for each field should be.

The following table shows what they usually look like, including extra rows explaining how the data should be formatted, and so on:

Figure 20.1 – An example of a CSV template for an entity record

Sending a client a set of templates and guiding them in their use is typically enough to get them started. They will determine which columns in the data files from their legacy system need to be tweaked to match the NetSuite standard, as well as where the lists for things such as customer category or the addresses that have been assigned to each Vendor will need to be adjusted to work in the new system. Be prepared, however, to provide some clients with additional assistance; this can be tricky and most companies don't assign people with a lot of data handling experience to this task.

If you need to help a client create a CSV import template for a record type that is non-standard or one you just don't have a template for yet, here are the steps I follow to make this as painless as possible:

1. Go to the record in NetSuite and create a view showing all of its fields.
2. Create at least one record if you need to.
3. Use the CSV export feature to export that view's data into a CSV file.
4. Open that file in your favorite editor (a spreadsheet program or a text editor with special CSV-handling features).
5. Add notes and additional details to the file to help the client understand what they need to provide for this record type.

Once the client gets used to working with your templates and they have someone convert their files into the NetSuite CSV format, they're ready to start running test imports. The first choice in that process is which account they should import into. If they just have their production account, then that choice is easy; be sure you plan with them to remove any test imports from that account, which we usually do as part of the cut-over activity. If they have one or more sandbox accounts, use the one you think of as the development account for your first import tests.

With this approach, you can work out any issues in the source data or the import mappings. Once you have successfully tested every record type in the development account, for some percentage of the total dataset, you will know that the migration into the next account – typically a user acceptance testing (UAT) sandbox – should go smoothly. In that scenario, we typically perform a full set of migration tests into the UAT account for all of the data we have at that time. This helps us ferret out any potential issues with bad data in the larger set of files, and it also helps the data migration team adjust to any changes the rest of the implementation team might have made. For instance, if the people working on sales orders decided to add a custom field to that record, and they make it mandatory, the data migration team needs to know that and ensure that they're creating sales orders correctly before the go-live.

Next, we'll check out the data testing that occurs in these phases.

Testing the data in phases

We typically do testing for migrations in stages; that is, we recommend starting small, making sure that a few records have been imported correctly. With that, we'll have more confidence that we can import 100 or 1,000 records of the same type. We pause here and validate that data as best we can. It's very important that we take notes of any errors that occur during these test imports; they will tell us what additional steps need to be taken on the source data files as preparation for all future imports.

Data migration performance

One other thing we're testing for at this stage is how long each import takes. If the client is bringing something like 100,000 transactions into the account, for instance, we need to know how much time each import requires so that we know when to start the final migration. In some cases, for older historical transactions, we can import those into the production account in advance. But in most cases, we don't want to capture the source data in a final sense until just before the data arrives. It's in these cases that knowing how much time we need to reserve for the final migration is so important. If the import will take a week to complete, we can't start 2 days before go-live. This is usually not an issue, especially in cases where we've stuck to the records and data volumes I mentioned earlier, but as always, planning and knowing how the imports will perform is the key to success.

It usually doesn't take long at all to get the basic lists – the chart of accounts, subsidiaries, and so on – imported, and then we move on to importing items. This can be more of a challenge, depending on the source data, but there's always a solution to any problems that might arise. We move on from items to the entities, working with each team of data owners, either to assist them in prepping or importing their files, or doing the work for them, if that's what's called for.

Good communication is key here from all parties involved, to make sure we don't let little problems turn into bigger issues by not addressing them quickly. It's also key to make sure everyone is clear on the roles and responsibilities each person on the team has in this overall process. I've seen it happen more than once that a project has been derailed when both the consultants and the client are pointing fingers at each other and saying, I thought you would do that. In other words, it's always OK to be extra explicit and clear in all emails, phone conversations, and meetings about who is doing what and by when they're expected to complete each task. As a consultant, you are expected to avoid embarrassing situations like this.

Once you've performed imports for each record type, including at least something like 50% of the total dataset for each type, you can move on to planning for the go-live.

Planning the cut-over activities

Data migration is typically the most important thing that happens just before going live, but it is in no way the only thing both your team of consultants and the client's team are working on in those days. Depending on the size and complexity of the project, we might start the so-called cut-over activities 2 days before going live, or a week, or sometimes more in some rare cases. The most important factor in this schedule is how long it will take to perform the imports. For most client accounts, there are a limited number of import queues we can perform CSV imports with, so we need to plan (as I mentioned in the previous section).

Aside from the data migration steps, we like to create a list of other steps we'll need to take right before the go-live and make sure everyone knows their assignments as part of that planning. Each part of the system – ERP, CRM, the web store, the warehouse, payroll, HR functions, and so on – needs a checklist of last-minute steps to ensure that every list of values, every set of entities, and every set of forms and roles has been set up correctly in the production account, in time for going live. This isn't the work of the implementation team for the most part; the client's end users and managers need to own these tasks since they should be fully versed in how to use them, and what they'll be doing with them each day following the go-live date.

We typically use a list of standard records that have been imported into the account with NetSuite-led implementations for this. But you can use whatever is easiest for your teams – spreadsheets, post-it notes, whatever; just so long as everyone has their tasks assigned and project managers can monitor their completion. We need a concrete way of knowing whether we're ready to go live, just before that date. Has everything been verified by the subject matter experts who own each function? This is the better safe than sorry approach to going live. I've been a part of a couple of projects where the client just sort of assumed everything would work out, and then they had many unhappy surprises during their first week of using the system to run their business. To avoid that, we have to make sure everyone is on-hand and completes their cut-over activities on time, in the last days before go-live.

Performing the final data imports before going live

When it comes to the final data migration push, we should only have a few things we need to import for most implementations – last-minute list value changes, item updates, and open transactions. Since they're usually ready ahead of time, all of the historical transactions should be in the production account before the final push, so this can be a fairly straightforward operation. We should have practiced importing the open purchase orders, sales orders, and so on in a sandbox before this, so there should not be any surprises. We're always prepared for something to go wrong, of course, but again, proper preparation should preclude any nasty issues with the data or the resulting transactions in the system.

The CSV import feature works on a queue basis, so as we begin these imports, it's important to remember to use whatever settings we found in our testing worked best for each record type. For instance, we may have found that since the account has one SuiteCloud Plus license, there are 10 available queues. And for most of our imports, we can distribute them across the queues, and also use the multi-threading feature, to get the highest throughput for the data coming into the account. But certain record types don't lend themselves to multi-threading, or we might have so many records of one type to be imported that we need to split up the files in advance and then use multiple imports running at the same time, or separately, to maximize the system's CSV import resources efficiently.

It is important to not forget to review CSV Import Preferences in the account before you start performing your imports! You can find them by going to Setup | Imports/Exports | CSV Import Preferences. This is what that screen looks like:

Figure 20.2 – The CSV Import Preferences screen

These Preferences include a setting that allows you to choose (globally) whether server-side scripts and workflows should be run against records being imported, which can have a huge impact on performance. Make sure that this has been set correctly, which will depend on whether or not you require scripts to update the new records as they're created.

The last step in this process, of course, is validating some of the data, to confirm that it looks correct still. This usually includes creating a few test transactions to be sure that, for instance, the customer data, addresses, items, segments, and everything else are all exactly as we expect them to be, and that all of the data works well together. We wouldn't want to find out that we missed a custom category field's source list, for instance, which means that orders can't be processed.

The final data migration process should be completed in production more than a few hours before we flip the switch on any implementation project. Hopefully, this is where all of your solid planning and practice pay off, and the go-live occurs on schedule, to everyone's relief.

Summary

Going live is a tough time for most clients. They have to change how the business runs every day. This is stressful in even the most well-planned cases. You can help the client through this by planning the data migration well ahead of the cut-over activities, as well as assisting them in making sure their historical transactions and other data will be brought into the account without any surprises. We do that by testing everything well in advance of the go-live date and include all of the relevant data types in that testing. We also make sure all the right people are involved in the data migration planning and testing so that nothing is left to chance. And we also make sure we're there to help with any last-minute emergencies that might occur just before the go-live.

At the end of an implementation project, once the data migration is completed and has been validated in the production account, and the business' users have completed all of the last steps in the cut-over checklist, we go live. Users should have stopped entries from going into the legacy system a couple or a few days ago, and now they can start working in the production account. This is both an exciting and nerve-wracking time for these people, so you have to be there to support them however you can. Whether you do this on-site or remotely, regularly scheduled meetings and solid issue tracking will help smooth out the first few days. Assuming everyone did all of the testing they should have in advance, this can be a very nice experience, where a few minor issues come up (no go-live has ever gone 100% smoothly) and you'll deal with them quickly. A lot of last-minute training happens at this time too, of course, and new solutions are found to help solve any problems we didn't anticipate. The client settles in and gets used to NetSuite in a way they couldn't before this. They then start to think about the next phase improvements they want to make – with your help, of course.

I sincerely hope you learned what you hoped to from reading this book. Hopefully, you can go more confidently into your next client meeting, knowing a little (or a lot!) more about working with NetSuite clients than you did previously.

Creating this book was a labor of love for me, writing out all of the things I've learned about implementing NetSuite for you to read. NetSuite is a great system and we always need more help working with clients and expanding the user base worldwide.

Self-assessment

As in the previous chapters, review these self-assessment questions and think about how you would handle each of these situations:

1. 1 week into a new client's implementation, you set up the first data migration call with their project leaders. You explain what your contract says is included in the scope of work for your team, which is just consulting on this topic. You start to explain what the client will need to do to ensure a successful migration, but the client gets mad and suggests they were told you would be handling the data migration process. Their team will not have time for this and they have no experience. How should you handle this?
2. Your team is helping a client with their migration testing in a sandbox account. They try to bring in a set of 100 customers, but a lot of errors are reported by the CSV import job. These errors are mostly about the phone numbers and email addresses being in an incorrect format but many other problems have been reported. Without taking on the task of cleaning up the data yourself, what are some ways you can help the client with that task?
3. 2 months before the go-live date, you and the client agreed on a list of cut-over activities, and each of those has a name associated with it. Your team of consultants has a few assignments, but the client's managers and users are assigned the majority of these tasks. 3 days before the go-live, they let you know that two people are off sick and that they need your team to step in and complete those people's assignments to stay on schedule. Should you help them? If so, what other steps do you need to take on to make sure everyone understands the risks involved in you doing that?
4. Your software business client goes live on a Monday. At first, everything seems to have gone very well. 2 days later, though, they notice that their GL reports are showing strange numbers. You realize that a customization is not performing as expected and now there are over 1,000 journals in the live production account with the wrong debit and credit amounts. You need to help them fix the bad data while the technical team updates the customization. What's the fastest option you have for managing these updates? How can you help the client ensure that they've caught all of the bad journals until that correction is in place?