"Seamless ELT to MongoDB!"

ABAP code, which includes the integration of the Python script for loading and transforming SAP HR data into MongoDB.

REPORT zsap_hr_data_extract.

DATA: lt_hr_data TYPE TABLE OF pa0002,

lv_json_data TYPE string,

lv_filename TYPE string,

lv_python_script TYPE string,

lv_command TYPE string,

lv_result TYPE string.

lv_filename = 'C:\temp\sap_hr_data.json'.

lv_python_script = 'C:\scripts\load_transform_mongodb.py'.

* Step 1: Extract HR data from SAP table PA0002

SELECT * INTO TABLE lt_hr_data FROM pa0002 WHERE pernr = '00000001'. "Condition based on Personnel Number

* Step 2: Convert the extracted HR data to JSON format

CALL FUNCTION 'HR_PAYROLL_JSON_CONVERT'

EXPORTING

iv_table = lt_hr_data

IMPORTING

ev_json = lv_json_data.

* Step 3: Save the JSON data to a file

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = lv_filename

filetype = 'ASC'

TABLES

data_tab = lv_json_data

EXCEPTIONS

others = 1.

IF sy-subrc = 0.

WRITE: / 'HR Data extracted and saved successfully'.

* Step 4: Call the Python script to load and transform data

lv_command = 'python ' && lv_python_script.

CALL FUNCTION 'SXPG_COMMAND_EXECUTE'

EXPORTING

commandname = 'PYTHON_SCRIPT'

additional_parameters = lv_command

IMPORTING

status = lv_result

EXCEPTIONS

no_permission = 1

command_not_found = 2

parameters_too_long = 3

others = 4.

IF lv_result = 0.

WRITE: / 'Python script executed successfully'.

ELSE.

WRITE: / 'Python script execution failed'.

ENDIF.

ELSE.

WRITE: / 'Error during extraction'.

ENDIF.

Explanation:

• lv_filename: Path where the JSON file is saved.

• lv_python_script: Path to the Python script responsible for loading data into MongoDB and transforming it.

• SXPG_COMMAND_EXECUTE: SAP function module used to execute the external Python script.

• lv_command: The command string that runs the Python script using the python interpreter.

Python Script: load_transform_mongodb.py

This Python script, located at C:\scripts\load_transform_mongodb.py, will load the HR data into MongoDB and perform the transformation.

import json

from pymongo import MongoClient

# MongoDB connection setup

client = MongoClient("mongodb://localhost:27017/")

db = client["sap_hr_database"]

collection = db["employee_data"]

# Step 1: Load the JSON data from the file generated by ABAP

with open('C:/temp/sap_hr_data.json', 'r') as file:

hr_data = json.load(file)

# Step 2: Insert the data into MongoDB collection

if isinstance(hr_data, list):

collection.insert_many(hr_data)

else:

collection.insert_one(hr_data)

print("SAP HR data loaded into MongoDB successfully.")

# Step 3: Transformation - Adding a new field "full_name"

def transform_employee_data():

# Add a new field "full_name" by combining "vornam" (first name) and "nachn" (last name)

for employee in collection.find():

full_name = f"{employee.get('vornam', '')} {employee.get('nachn', '')}"

collection.update_one(

{"_id": employee["_id"]},

{"$set": {"full_name": full_name.strip()}}

)

# Step 4: Perform the transformation

transform_employee_data()

print("Data transformation in MongoDB completed.")

Process Summary:

1. Extract: The ABAP program extracts data from SAP's PA0002 table, converts it to JSON format, and saves it locally.

2. Load and Transform: The Python script is called by ABAP to load the JSON data into MongoDB and apply any necessary transformations.

This integration allows SAP to manage the entire process, leveraging Python for tasks that are better suited to a flexible programming language and MongoDB's NoSQL capabilities