Purpose
The white paper lists and describes steps needed to perform data conversion for SAP from the staging point of view.
Goals and Objectives
The goal is to enable the staging developers during Data conversion process and help them verify the data converted before it goes into SAP.
Pre-requisites for Developers
The goal is to enable the staging developers during Data conversion process and help them verify the data converted before it goes into SAP.
Developers working on the Staging part should have the following pre requisites.
- Knowledge of Oracle
- SQL
- PL/SQL
- SQL Loader Utility
- Knowledge of MS Excel
- Familiarity with TOAD
Overview
Data conversion process involves converting/processing the data received from the legacy applications/systems and transforming it into desired output structure of SAP as per the specifications provided.
Functional specification documents provide details about the business rules that are to be applied on the data before it goes into SAP. Staging team does the work of applying those business rules by writing stored procedures in ORACLE.
Pictorial representation of Data conversion process
Data conversion process
Steps to be followed
- Understanding and analyzing the requirements of conversion
- Development of procedures for conversion
- Import process
- Processing data
- Versioning
- Exception Reports
Understanding and analyzing the requirements of conversion
- Understanding business rules (from specs) to be applied and analyzing data received from legacy systems
- Getting clarifications from the client on any of the doubts that may arise from step 1
- Preparing a mapping document (using MS excel); listing all the input fields from the data received and output fields as per the structure required for a particular conversion
- Ensuring that the mapping done is correct by getting it verified from a business user
Development of procedures for conversion
Once a mapping document is in place and has been verified by business users, the actual development of code and import process of data is initiated for a particular conversion.
Import process
SQL Loader utility is used to import data into ORACLE tables. Data received from a legacy system could be in various forms. For e.g. it could either be in a tab delimited, CSV or can even be in an excel spreadsheet format. It is important to review the data received is in correct format and ensure that no field / information is missing. Process to import data may differ depending upon the format in which data has been received.
Steps in general that are needed to be performed for the import process:
In case the data received is in tab delimited format, MS Excel can be used to prepare the file before it is used with the SQL Loader tool. Although data can be imported to ORACLE as is, but sometimes the data received is not directly importable into ORACLE tables and has to be converted into a format which is acceptable to the ORACLE.
Select Import Data option available under Data->Import External Data menu option.
- Step 1 will open up an Open file dialog window
- Select a file (Excel, tab delimited or CSV format) containing data using an appropriate path and follow the steps of the Import wizard
- Note: An important thing that has to be kept in mind before importing the data is to set the format of all the cells to text. Otherwise, a value larger in size or one with leading zeros might create a problem. Leading zeros get trimmed and a larger value does not get displayed properly in a cell or doesn’t get exported properly.
- After the import process completes successfully, a file can be saved as a tab delimited file through MS Excel only. The tab delimited file can then be used by SQL Loader utility to load the data into ORACLE tables.
Processing data
Processing the data imported is one of the major steps in data conversion process. It’s quite possible that in a real life scenario, changes to the code might be required during the course of data conversion for a particular module in the procedures created for processing the data.
Creating separate versions of code
One way to protect and maintain the code written for a particular type of conversion is to write a new stored procedure. That’s the way it’s been mostly done while there have been changes that we were to make in our code during the process of conversion in Wave I. Maintaining separate versions of code like this is a tedious process especially without an integration of a version management tool with the development environment.
Example:
Here is an example of a data conversion process for a module (Sales Order), where there was a requirement to create a separate set of data for all the orders belonging to plant in Canada. In this kind of a scenario, we had created a separate procedure to process the records for Canadian plant. During this conversion process, there have been number of changes, which were done quite frequently to each of these two separate procedures and the nature of changes that were to be made were also different for each of the plants. In a real life scenario, it becomes very difficult to maintain separate versions and also keep track of the changes being made on continuous basis to these different sets of code.
Processed Data
Once the processing is complete, the data has to be delivered to the SAP.
Data Version
Versions of the data are maintained as the data goes into different environments in SAP. It’s required because of the following reasons:
- Delta loads – When a delta load has to be sent for a particular version, it becomes important to know what all data has already been sent so that no duplicate records go into SAP
- Identifying any incorrect data that might have been sent to a particular environment of SAP because of any reason. This helps in keeping track whether incorrect data was sent by Legacy, Staging or it something went wrong at SAP’s end only
- Generating Exception records reports
Generating an exception report is one of the most crucial steps in the process of data conversion. An exception report is a log of those records which could not be processed due to the business rules applied as per the functional specs.
An exception report helps the staging team in reporting the users/developers of the legacy system to identify the problems at their end and resolve them and re-send the data to the staging team to process it, so that the same could be uploaded in SAP too. A typical format of an exception report is a collection of raw data fields along with a reason as why those could not get processed. This is sent usually in an MS excel format.
Also, count of records processed and the records that came as part of the raw data is maintained and communicated to the business users/legacy system team to find out how much data got loaded and how much of it failed to load.
