Data migration to an SAP system can occur if an SAP-run company acquires a non-SAP shop, a business running SAP performs an upgrade to a latest SAP version, or an entirely new implementation occurs. Irrespective of whether it is a fresh implementation project, rollout, or merger, the desired results are the same: efficient and smooth transformation of master and transactional data to or within the SAP system.

With proper integration and coordination with business stakeholders, a well-managed data migration process can produce significant business gains across the enterprise. However, poorly-managed migration processes will likely result in significant time and cost overruns and lack of integrity in the converted data. Therefore, it is important to understand the complete end-to-end migration process and make sure that there is a data migration plan in place that integrates smoothly with the overall project plan.

Let me take you through all the steps involved in a typical data migration process with examples of data migration objects for SAP ERP procurement and inventory. This article is useful for anyone working on an SAP project involving data migration, be it a conversion lead, project manager, or functional consultant if your company hires one. I have used the methodology explained in this article in four successful data migration projects.

Figure 1 explains the complete data migration process in simple terms. It shows the four major steps in a typical data migration process:

1. Extraction of data from the source system, including data cleansing
2. Transformation and subsequent validation of data
3. Loading data into the SAP system
4. Reconciliation of data

Every step helps achieve efficient results. Let’s go through the details of each step below.

Figure 1

The major steps behind data migration

Extract Your Data

The first thing you need to do in a data migration project is understand the legacy system and its landscape (i.e., whether it has separate development, quality, and production environments). Then it’s important to study the source data thoroughly to understand its content, structure, quality, and integrity. Decide what data to move and how much data to move. Prepare a list of master and transactional data that needs to migrate — consider data for material master/bill of materials, customer master, vendor master, inventory, G/L accounts, cost center, fixed assets, and purchasing. Identify stakeholders who provide data for testing of migration objects and for final loading into your SAP system.

Once you define what data needs to migrate, the next step is to extract that data from the source system. You can accomplish this in many ways, but choosing which method to use largely depends on the source system’s technical complexity, budget, resources, and volume of data.

Below are examples of how one can initiate the extraction process:

• Business provides the data in the form of files such as Microsoft Excel or tab-delimited text. This is the best method if the volume is not large and if lots of technical work is necessary to connect to the source legacy system.
• Develop a program that automatically fetches data from the source system’s database tables and stores it in Excel files. This method is good if the technical team has access to the source system and data is available in the form of relational database tables (e.g., Oracle SQL table).
• Use standard tools, such SAP BusinessObjects Data Integrator, to connect to a legacy system to extract legacy data.

It is the responsibility of a conversion analyst to ensure that each and every SAP field involved with the project is explained to business users so that they can relate source data fields with SAP table fields. This will improve the quality of data coming from the source system. For example, a typical initial inventory upload in an SAP system requires an input file with the following fields:

• Material (MATNR) — This field is meant for all the active materials with stock quantities greater than zero.
• Plant (WERKS) and storage location (SLOC) — Business needs to provide these values if stock is stored at a plant/SLOC level in the legacy system.
• Batch (CHARG) — If materials are managed at batch level, business needs to provide batch numbers associated with each material.
• Quantity (ERFMG) — This field is meant for the quantity of material to be migrated.
• Vendor (LIFNRS) — If materials are managed at a subcontractor level — such as material provided to vendor (MPV) — or as consignment stock, then business needs to provide vendor numbers at which stock is available. Here, it is important for the conversion project team member to explain that if stock is MPV, then they might not need to provide SLOC above because stock will be captured in the system as subcontracting stock.
• Special stock indicator (SOBKZ) and movement type (BWART) — A conversion team member can populate these fields after getting information from the business depending on what type of stock data is provided. For example, if it is MPV subcontracting stock, then the standard movement type in SAP is 561 for taking initial stock into the SAP system. The special stock indicator is the letter O for subcontracting stock. For plant stock at storage location level, it is going to be 561 and the special stock indicator is left blank because it needs to be loaded under a plant and storage location.

Cleanse the Source Data

This step ensures that the data you upload into your SAP system is of the utmost quality. Here, the responsibility of a conversion team member is to define rules after discussion with business about what data needs consideration. For example, with purchase order data, a rule could be only to consider open purchase orders created in the last year. Cleansing data rules result from a mutual agreement between business and the conversion project team. Table 1 shows an example of a cleansing tracking sheet.

Table 1

Data clean-up tracking sheet

Transform Data Among Sources

In this step, you perform mapping between source data fields and SAP fields by either using automated tools — such as the Legacy System Migration Workbench (LSMW) — for high volume and complex data, or manually for low volume and simple data. The following are options to perform this step:

• Manually using formulas in Excel. This is fastest way to handle transformation if data volumes are low and there are few fields that require transformation. For example, in the initial stock upload file in Table 2, assume only the plant number is being changed and other values will be created/configured as they are in the SAP system (e.g., legacy plant FRTT has been renamed as CP01 in the SAP system, but other fields will not undergo any changes). Then you only need to transform the plant data, and that can be done easily in Excel using the VLOOKUP function.

Table 2

Initial stock upload file

• If technical team support is available, use SQL tables to perform the transformation. In this method, a technical team builds a SQL table with all the required fields, creates mapping tables with old and new values, and writes a stored procedure to transform the old values (source fields) to new values (SAP fields). This method is suitable if you have a large number of fields that require transformation. However, if technical team support is not available, then it is better to stick with SAP standard LSMW.
• Use SAP BusinessObjects Data Integrator, a standard tool that has reusable and sharable transforms and functions (drag and drop) that minimize hard coding.

Validate Transformed Data

In this step, business confirms that data has been transformed correctly, which is an important step for a data conversion project. Here, you provide a comparison between a source data file provided by business and the transformed data. This preload validation step can be done using Excel sheet macros or through a SQL table. The final report should have all the required fields with old and new value columns showing compared results. For example, Table 3 shows Plant and SLOC comparisons (just two columns of the complete file are shown). Here, I have used EXACT function in Excel.

Table 3

Comparison columns for data validation

In a compare column you want to see a FALSE value if a field required a transformation because FALSE means the old value has changed to a new value. If you see TRUE in a comparison column, that means an old value has not been transformed and you need to investigate why it did not convert. In Table 3, Row 5, the values for Old plant and Old SLOC did not convert to New plant and New SLOC values. In such a case, business should go through the preload validation report thoroughly and provide confirmation to the project team that all the data has been transformed correctly. Only at that point should the project team move ahead with the next step of loading data into the SAP system.

Load Data Into the SAP System

Once business signs off on the preload validation report, you are ready to load your data into the SAP system. It is important to identify the data loading technique at the initial stage of the project because it might take time to develop loading programs if you are not using standard loading techniques. SAP provides LSMW for migration of data, and it comes as an add-on with your SAP installation. If used properly, LSMW can save time and money by avoiding development of complex loading programs, which in turn reduces your dependency on an ABAP or technical team. This is a win-win situation for the functional and technical teams, as the technical team can focus on critical objects in the project and the functional team can perform the migration steps independently from beginning to end without any technical help.

There are four ways to upload data into the SAP system using LSMW:

• Standard batch/direct input
• Batch input recording
• Business object method (i.e., BAPI)
• Intermediate Document (IDoc)

It is advisable to first check if loading can be done using a BAPI or IDoc because they are the fastest and most efficient methods, so verify that you have a standard BAPI defined in the system for your object. Examples of LSMW BAPIs and IDocs for purchasing documents are given in Figures 2, 3, and 4. You can see these screens by using transaction LSMW.

Figure 2

BAPI and IDoc for purchase order upload

Figure 3

BAPI and IDoc for scheduling agreement upload

Figure 4

BAPI and IDoc for contract upload

Once you complete all the steps of LSMW, you can check the status of loading by choosing Create IDOC Overview in LSMW via transaction LSMW (Figure 5).

Figure 5

Select Create IDoc Overview

If loading was successful, you see the Current status of IDoc designated as 53; however, if loading failed, you see the Current status as 51. Figure 6 shows a successful loading with the Current status as 53.

Figure 6

Successful loading report

Create a Reconciliation Report 

Once your data has been loaded successfully into the SAP system, the final step is to generate a reconciliation report, which compares transformed data and loaded data in the SAP system. This report ensures that the loading program has worked properly and all the transformed data has been uploaded into SAP correctly. Like the previous pre-load validation report, here, you can also use SQL queries or Excel to compare transformed and uploaded data.

For example, in the case of inventory data migration, you already have the transformed data comparisons (Table 3). To get the uploaded data file you have to go to an SAP table for storage location data for material (MARD). To access this table, use transaction SE16, enter the table name as MARD, enter your plant number, and press F8. This gives you the entire inventory data loaded into SAP at plant and storage location level. Similarly for subcontracting stock you have to use a table for special stocks for vendors (MSLB).  

Once you get the data, you have to save it into an Excel file. Do this via menu option System > List > Save > Local File, give a name to the file using an extension of .xls, and save it on your desktop (Figure 7).

Figure 7

Save your data to a local file

Now you have two Excel files: one having transformed data and another having uploaded data from the SAP system. You need to combine both files into a single version so that you can use Excel’s EXACT function to compare transformed and loaded data. Here, for all the fields you should get a TRUE value, which means transformed data is loaded as it is in the SAP system (Table 3). If you get a FALSE value in any cell, it implies data has not been loaded correctly and you need to investigate why the loading program did not load the value correctly. Note the difference in whether a TRUE is tye desirable value between Tables 3 and 4.

Table 4

Transformed data has loaded correctly into the SAP system

Once the project team ensures that the reconciliation report is giving the desired results, the team seeks sign-off from the business that all the data has been loaded correctly.

I have followed the above migration strategy during four projects and all went go-live successfully without a single issue. However, apart from following the strategy correctly, it is important that the project manager make a proper project plan to accommodate the data migration strategy as one of the major milestones. Another important aspect I always follow is to start working on the data migration strategy at the beginning of the project instead of waiting for the design to freeze. This gives you the opportunity to understand the data in detail, which in turn helps you create a foolproof data migration strategy.