SAP ERP Connection

Extension Credit

This guide uses the erpl extension by the DuckDB community. Thanks to the contributors who made SAP ERP integration possible through RFC connections!

Connect flAPI to SAP ERP systems (ECC, S/4HANA) using DuckDB's ERPL extension. Extract data from tables, run function modules, and execute ABAP queries directly.

Prerequisites

SAP NetWeaver RFC SDK installed
SAP user with appropriate authorizations
Network access to SAP system

Quick Start

# flapi.yaml
connections:
  sap-erp:
    init: |
      INSTALL erpl;
      LOAD erpl;
    properties:
      ashost: sap.company.com    # Application server
      sysnr: '00'                # System number
      client: '100'              # Client number
      user: ${SAP_USER}          # Username
      passwd: ${SAP_PASSWORD}    # Password
      lang: EN                   # Language

Connection Parameters

Parameter	Description	Required	Example
`ashost`	SAP application server hostname	Yes	`sap.company.com`
`sysnr`	System number	Yes	`'00'`
`client`	Client number	Yes	`'100'`
`user`	SAP username	Yes	`${SAP_USER}`
`passwd`	SAP password	Yes	`${SAP_PASSWORD}`
`lang`	Language (EN, DE, etc.)	No	`EN`

Reading SAP Tables

Basic Table Read

# sqls/materials.yaml
url-path: /materials/

request:
  - field-name: plant
    field-in: query
    description: Plant code
    required: false

template-source: materials.sql
connection:
  - sap-erp

-- sqls/materials.sql
SELECT 
  MATNR as material_number,
  MAKTX as material_description,
  WERKS as plant,
  LGORT as storage_location,
  LABST as stock_quantity
FROM {{conn.sap-erp.ashost}}.MARD  -- Material master data
WHERE 1=1
{{#params.plant}}
  AND WERKS = '{{params.plant}}'
{{/params.plant}}

Joining SAP Tables

-- sqls/sales-orders.sql
SELECT 
  h.VBELN as sales_order,
  h.ERDAT as creation_date,
  h.KUNNR as customer_number,
  c.NAME1 as customer_name,
  i.MATNR as material,
  i.KWMENG as quantity,
  i.NETWR as net_value
FROM {{conn.sap-erp.ashost}}.VBAK h     -- Sales order header
LEFT JOIN {{conn.sap-erp.ashost}}.KNA1 c -- Customer master
  ON h.KUNNR = c.KUNNR
LEFT JOIN {{conn.sap-erp.ashost}}.VBAP i -- Sales order items
  ON h.VBELN = i.VBELN
WHERE h.ERDAT >= '20240101'
ORDER BY h.ERDAT DESC

Common SAP Tables

Materials Management (MM)

MARA - Material master (general data)
MAKT - Material descriptions
MARD - Storage location data
MARC - Plant data for materials

Sales & Distribution (SD)

VBAK - Sales document header
VBAP - Sales document items
KNA1 - Customer master (general)
KNVV - Customer master (sales)

Finance (FI)

BKPF - Accounting document header
BSEG - Accounting document items
SKA1 - G/L account master

Purchasing (MM-PUR)

EKKO - Purchase order header
EKPO - Purchase order items
LFA1 - Vendor master

Caching SAP Data

SAP tables are large and slow. Always use caching:

# sqls/materials.yaml
url-path: /materials/

cache:
  enabled: true
  table: sap_materials_cache
  schedule: 6h           # Refresh every 6 hours
  template_file: materials_cache.sql

template-source: materials.sql
connection:
  - sap-erp

-- sqls/materials_cache.sql
-- Load all active materials
SELECT 
  MATNR as material_number,
  MAKTX as material_description,
  MTART as material_type,
  MATKL as material_group,
  MEINS as base_unit,
  WERKS as plant,
  LABST as stock_quantity
FROM {{conn.sap-erp.ashost}}.MARA m
JOIN {{conn.sap-erp.ashost}}.MAKT t
  ON m.MATNR = t.MATNR
  AND t.SPRAS = 'E'  -- English descriptions
WHERE m.LVORM IS NULL  -- Not marked for deletion

Performance comparison:

Method	Latency	Cost
Direct SAP query	5-30 seconds	High RFC load
flAPI cache	1-5ms	Minimal (scheduled refresh)

Security Best Practices

✅ DO

# Use environment variables
connections:
  sap-erp:
    properties:
      user: ${SAP_USER}
      passwd: ${SAP_PASSWORD}

# Restrict with authentication
authentication:
  required: true
  methods: [jwt]
  roles: [sap-user]

❌ DON'T

# Never hardcode credentials
connections:
  sap-erp:
    properties:
      user: JOHNDOE          # ❌ BAD
      passwd: Password123    # ❌ BAD

Environment Setup

# .env (never commit)
export SAP_USER="RFC_API_USER"
export SAP_PASSWORD="secure-password"
export SAP_ASHOST="sap-prod.company.com"
export SAP_SYSNR="00"
export SAP_CLIENT="100"

Complete Example

# ═══════════════════════════════════════════════════════════════
# flapi.yaml
# ═══════════════════════════════════════════════════════════════
connections:
  sap-prod:
    init: |
      INSTALL erpl;
      LOAD erpl;
    properties:
      ashost: ${SAP_ASHOST}
      sysnr: ${SAP_SYSNR}
      client: ${SAP_CLIENT}
      user: ${SAP_USER}
      passwd: ${SAP_PASSWORD}
      lang: EN

# ═══════════════════════════════════════════════════════════════
# sqls/inventory.yaml
# ═══════════════════════════════════════════════════════════════
url-path: /inventory/

authentication:
  required: true
  methods: [jwt]
  roles: [inventory-user]

cache:
  enabled: true
  table: sap_inventory
  schedule: 1h
  template_file: inventory_cache.sql

request:
  - field-name: plant
    field-in: query
    description: Plant code (e.g., 1000)
    required: false
  - field-name: material_type
    field-in: query
    description: Material type (e.g., FERT, HALB)
    required: false

template-source: inventory.sql
connection:
  - sap-prod

-- sqls/inventory_cache.sql
-- Materialize inventory data
SELECT 
  m.MATNR as material,
  t.MAKTX as description,
  m.MTART as material_type,
  s.WERKS as plant,
  s.LGORT as storage_location,
  s.LABST as unrestricted_stock,
  s.INSME as quality_inspection_stock,
  s.SPEME as blocked_stock,
  p.STPRS as standard_price,
  p.PEINH as price_unit
FROM {{conn.sap-prod.ashost}}.MARA m
JOIN {{conn.sap-prod.ashost}}.MAKT t
  ON m.MATNR = t.MATNR AND t.SPRAS = 'E'
JOIN {{conn.sap-prod.ashost}}.MARD s
  ON m.MATNR = s.MATNR
JOIN {{conn.sap-prod.ashost}}.MBEW p
  ON m.MATNR = p.MATNR AND s.WERKS = p.BWKEY
WHERE m.LVORM IS NULL

-- sqls/inventory.sql
-- Fast queries against cache
SELECT * FROM sap_inventory
WHERE 1=1
{{#params.plant}}
  AND plant = '{{params.plant}}'
{{/params.plant}}
{{#params.material_type}}
  AND material_type = '{{params.material_type}}'
{{/params.material_type}}
ORDER BY unrestricted_stock DESC

Troubleshooting

Connection Failed

Error: RFC_ERROR_COMMUNICATION - Connection to SAP system failed

Solutions:

Verify ashost and sysnr are correct
Check network connectivity: ping sap.company.com
Ensure SAP NetWeaver RFC SDK is installed
Verify firewall allows RFC connections (port 33XX)

Authorization Errors

Error: RFC_ERROR_AUTHORITY - User lacks authorization

Solutions:

Ensure user has RFC authorizations
Check transaction authorizations (S_RFC, S_TABU_NAM)
Contact SAP Basis team for access

Slow Queries

SAP queries taking > 10 seconds:

Solutions:

✅ Enable caching (6h-24h refresh)
✅ Use indexes on SAP tables
✅ Limit date ranges
✅ Avoid SELECT *
❌ Don't query SAP directly for high-frequency APIs

Next Steps

SAP BW Connection: Connect to SAP BW/4HANA
Caching Setup: Optimize performance
Authentication: Secure your API

Prerequisites​

Quick Start​

Connection Parameters​

Reading SAP Tables​

Basic Table Read​

Joining SAP Tables​

Common SAP Tables​

Materials Management (MM)​

Sales & Distribution (SD)​

Finance (FI)​

Purchasing (MM-PUR)​

Caching SAP Data​

Security Best Practices​

✅ DO​

❌ DON'T​

Environment Setup​

Complete Example​

Troubleshooting​

Connection Failed​

Authorization Errors​

Slow Queries​

Next Steps​

Prerequisites

Quick Start

Connection Parameters

Reading SAP Tables

Basic Table Read

Joining SAP Tables

Common SAP Tables

Materials Management (MM)

Sales & Distribution (SD)

Finance (FI)

Purchasing (MM-PUR)

Caching SAP Data

Security Best Practices

✅ DO

❌ DON'T

Environment Setup

Complete Example

Troubleshooting

Connection Failed

Authorization Errors

Slow Queries

Next Steps