SAP Relationship Mapping

How the Synthesized Platform maintains SAP table relationships through module knowledge and configurable virtual foreign keys.

The SAP Relationship Challenge

SAP systems present unique challenges for data management:

No Explicit Foreign Keys

Unlike typical relational databases, SAP tables rarely define foreign key constraints at the database level:

-- SAP HANA shows no FK constraints
SELECT * FROM SYS.REFERENTIAL_CONSTRAINTS
WHERE SCHEMA_NAME = 'SAP_SCHEMA';
-- Result: Empty or minimal results

Why?

  • Performance optimization in high-volume OLTP systems

  • Flexibility for customizations (Z-tables)

  • Historical design decisions from R/3 era

Implicit Relationships

Relationships exist through naming conventions and field semantics:

MARA.MATNR ←→ MAKT.MATNR  (Material master to descriptions)
MARA.MATNR ←→ MARC.MATNR  (Material master to plant data)
MARC.WERKS ←→ T001W.WERKS (Plant to plant master)

These are "virtual" foreign keys - understood by SAP applications but not enforced by the database.

How the Platform Handles This

Module-Specific Knowledge

The platform includes pre-configured knowledge of SAP table structures:

MM Module Relationships

MARA (Material Master - Root)
│
├── MANDT + MATNR ─────────────┬── MAKT (Descriptions)
│                              ├── MARC (Plant Data)
│                              ├── MARD (Storage Location)
│                              ├── MBEW (Valuation)
│                              ├── MARM (Units of Measure)
│                              └── MVKE (Sales Data)
│
└── MANDT + MATNR ─────────────── MKPF/MSEG (Documents)

Automatic Table Ordering

The platform automatically orders tables for correct data flow:

# Tables are processed in dependency order:
tables:
  # 1. Root table first (MARA)
  - table_name_with_schema: "SCHEMA.MARA"

  # 2. Dependent tables follow
  - table_name_with_schema: "SCHEMA.MAKT"  # depends on MARA
  - table_name_with_schema: "SCHEMA.MARC"  # depends on MARA
  - table_name_with_schema: "SCHEMA.MARD"  # depends on MARC

Referential Integrity Preservation

When transforming data, the platform ensures:

  1. Key Fields Match - MATNR values are consistent across tables

  2. Master Data Exists - Parent records present before child records

  3. Cascading Updates - Changes propagate to related tables

Virtual Foreign Key Definitions

MM Module Virtual FKs

Child Table Child Fields Parent Table Parent Fields

MAKT

MANDT, MATNR

MARA

MANDT, MATNR

MARC

MANDT, MATNR

MARA

MANDT, MATNR

MARD

MANDT, MATNR, WERKS

MARC

MANDT, MATNR, WERKS

MBEW

MANDT, MATNR

MARA

MANDT, MATNR

MARM

MANDT, MATNR

MARA

MANDT, MATNR

MVKE

MANDT, MATNR

MARA

MANDT, MATNR

MSEG

MANDT, MBLNR, MJAHR

MKPF

MANDT, MBLNR, MJAHR

Key Field Patterns

SAP uses consistent patterns for relationships:

Pattern Description Example

MANDT

Client/mandant - always first in composite keys

MANDT = '100'

Primary Business Key

Main object identifier

MATNR, LIFNR, KUNNR

Organizational Keys

Org structure identifiers

WERKS, BUKRS, VKORG

Document Keys

Document identification

MBLNR + MJAHR, BELNR + GJAHR

Configuration Options

Using Predefined Relationships

The SAP Workflow Wizard automatically applies known relationships:

# Automatically generated - no manual configuration needed
tables:
  - table_name_with_schema: "SCHEMA.MARA"
    transformations:
      - columns: ["MANDT", "MATNR"]
        params:
          type: passthrough  # Preserve keys for relationships

  - table_name_with_schema: "SCHEMA.MAKT"
    transformations:
      - columns: ["MANDT", "MATNR"]
        params:
          type: passthrough  # Match parent keys

Custom Virtual Foreign Keys

For custom tables (Z-tables) or non-standard relationships, define virtual FKs:

virtual_foreign_keys:
  - table_schema: "SCHEMA"
    table_name: "ZCUSTOM_TABLE"
    columns:
      - "MANDT"
      - "MATNR"
    referenced_table_schema: "SCHEMA"
    referenced_table_name: "MARA"
    referenced_columns:
      - "MANDT"
      - "MATNR"

Foreign Key Generator

For explicit foreign key handling:

tables:
  - table_name_with_schema: "SCHEMA.ZCUSTOM_CHILD"
    transformations:
      - columns: ["MANDT", "MATNR"]
        params:
          type: foreign_key_generator
          referred_schema: "SCHEMA"
          referred_table: "MARA"
          referred_fields: ["MANDT", "MATNR"]

Module Filtering

How It Works

The platform filters tables based on selected modules:

Selected Module: MM

Included Tables:
  ✓ MARA (Material Master)
  ✓ MAKT (Material Descriptions)
  ✓ MARC (Plant Data)
  ✗ KNA1 (Customer Master - not MM)
  ✗ LFA1 (Vendor Master - not MM)

Cross-Module References

Some tables are shared across modules:

Table Used By Type

T001

All modules

Company Code Master

T001W

MM, SD, PP

Plant Master

T024

MM, FI

Purchasing Group

The platform includes shared tables automatically when needed.

Handling Complex Relationships

Many-to-Many Relationships

SAP uses intermediate tables for M:N relationships:

MARA ←→ KNMT ←→ KNA1
(Material)  (Cross-Ref)  (Customer)

The platform handles these through proper table ordering and key preservation.

Self-Referencing Tables

Some SAP tables reference themselves:

MARA.SATNR → MARA.MATNR  (Configurable material reference)

These are handled with careful insert ordering and nullable field handling.

Circular Dependencies

The platform detects and resolves circular references:

cycle_resolution_strategy: FAIL  # or BREAK_CYCLE

Validation

Pre-Workflow Validation

Before running, the platform validates:

  1. All referenced tables exist

  2. Key fields have compatible types

  3. Relationship chains are complete

Post-Workflow Validation

After running, verify relationships:

-- Verify MAKT records match MARA
SELECT COUNT(*) AS orphan_descriptions
FROM TARGET.MAKT t
WHERE NOT EXISTS (
    SELECT 1 FROM TARGET.MARA m
    WHERE m.MANDT = t.MANDT AND m.MATNR = t.MATNR
);

-- Should return 0

Troubleshooting

Missing Parent Records

Symptom: Child records reference non-existent parents

Cause: Time slicing excluded older master data

Solution: * Ensure referential integrity is enabled * Extend time slice period * Verify master data filter includes referenced records

Duplicate Key Errors

Symptom: Unique constraint violation on insert

Cause: Table already contains data

Solution: * Use table_truncation_mode: TRUNCATE * Or use schema_creation_mode: DROP_AND_CREATE

Relationship Not Recognized

Symptom: Related data not properly linked

Cause: Custom table not in module definition

Solution: * Add virtual foreign key definition * Use foreign_key_generator transformation

Best Practices

Start with Standard Tables

  1. Use wizard-generated configurations first

  2. Validate standard table relationships work

  3. Add custom tables incrementally

Document Custom Relationships

For Z-tables and custom configurations:

  • Document the business relationship

  • Add virtual FK definitions

  • Test with sample data

Test Referential Integrity

Always validate after workflows:

  • Run integrity check queries

  • Test SAP transactions

  • Verify report data consistency

See Also