Data Migration

Data Migration from SAP ECC to HANA for SempCorp

Objective: Migrate critical data from legacy SAP ECC systems to SAP HANA across 10 countries while ensuring data accuracy, consistency, and completeness. The migration was essential as the legacy ECC system was to be decommissioned, with SAP HANA becoming the sole operational system.

Project Scope

1. Countries Involved: India (IN), Oman (OM), Myanmar (MM), Bangladesh (BD), UAE (AE), Vietnam (VN), China (CN), UK, and Singapore.

2. Migration Cycles:

   • Cycle 1 (C1): System Integration Testing (SIT) with pilot country as Singapore

   • Cycle 2 (C2): User Acceptance Testing (UAT) - Part 1 for all the countries

   • Cycle 3 (C3): User Acceptance Testing (UAT) - Part 2

   • Cycle 4 (C4): Pre-Migration

   • Cycle 5 (C5): Final Migration (to be completed in 2 days over a weekend due to system downtime).

Technology Stack

• ETL Tool: Azure Synapse Analytics (used for data extraction and loading).

• Programming Languages: Python (for data transformation and validation scripts), Pyspark, SQL for querying data, VBA (for Excel automation).

• File Formats: Excel templates (XML-based) provided by Deloitte for HANA uploads.

• Data Sources: SAP ECC (legacy system).

Key Activities

1. Data Migration Workflow

• Data Extraction:

  • Scheduled nightly jobs in Azure Synapse Analytics to extract data from SAP ECC.

  • Applied filters for transactional data to handle large datasets and extracted delta data for master data.

  • Monthly refresh of master data to ensure updates were reflected accurately.

• Data Transformation:

  • Created Python transformation scripts for 9 objects, including master and transactional data.

  • Transformed data into a predefined template format required for HANA uploads.

• Data Upload:

  • Mapped transformed data to the HANA Excel templates using VBA automation.

2. Data Transformation Details

• Objects Migrated:

  1. Master Data: BP Customer, BP Supplier.

  2. Transactional Data: AP Balance, AR Balance, GL Open Items, GL Opening Balance, AUC Balance, Fixed Asset, and Trial Balance.

• Steps Involved:

  1. Data Cleaning:

     • Removed duplicates, fixed null values, standardized formats, and filtered unnecessary data.

  2. Business Logic:

     • Applied business-specific rules for accurate transformation.

  3. Data Formatting:

     • Ensured data adhered to template formats, preserving leading/trailing zeros, special characters, regional language characters and mixed alphanumeric fields.

3. Automation for Excel Template Population

• Challenge:
  The HANA upload template provided by Deloitte was a complex Excel file with multiple sheets, requiring data from various ECC tables. Manual population of these templates was time-intensive and prone to errors.

• Solution:

  • Automated the data mapping and population using VBA scripts:

    • Handled multiple data formats, including regional characters (Chinese, Vietnamese).

    • Preserved leading/trailing zeros and special formatting.

  • Created two separate scripts for handling files with regional characters differently from English format files.

• Impact:

  • Reduced manual effort by 200 man-hours per week (as experienced in C1).

  • Improved accuracy and consistency in template population.

4. Data Quality and Validation

• Importance:
  Ensuring data quality was critical due to the high stakes of data migration, where errors could lead to severe monetary and operational repercussions.

• Approach:

  1. Validated data at every step using automated Python scripts.

  2. Conducted sanity checks and generated reports to identify inconsistencies and inaccuracies.

• Validation Steps:

  1. Verified record counts before and after each process.

  2. Ensured mandatory columns were populated.

  3. Ensured the format and structure of data fields.

  4. Ensured unique keys maintained uniqueness.

  5. Identified and removed duplicate records.

• Outcome:
  Maintained a 98.59% data quality standard, reviewed and approved by the business team.

Challenges and Resolutions

1. Challenge: Handling complex data formats in Excel templates.

   • Resolution: Developed robust VBA scripts to automate data mapping and formatting.

2. Challenge: Ensuring regional data integrity for files with Chinese and Vietnamese characters.

   • Resolution: Created specialized scripts to handle regional encoding and character sets.

3. Challenge: Managing large transactional datasets during extraction.

   • Resolution: Applied filters for transactional data and extracted only delta changes for master data.

Key Achievements

• Successfully migrated data across 10 countries within tight timelines.

• Automated Excel template population, saving significant manual effort and enhancing accuracy.

• Delivered 98.59% data quality, mitigating risks and ensuring business continuity post-migration.