ODP via OData (Delta Replication)

Extract SAP data with automatic delta replication using ODP (Operational Data Provisioning) via the OData protocol. Keep your data warehouse synchronized with minimal network overhead.

Overview

ODP (Operational Data Provisioning) is SAP's framework for extracting data with delta replication support. Since SAP OSS Note 3255746 banned RFC use for external data extraction, ODP via OData is now the recommended approach.

What you get:

• ✅ Automatic delta replication (only changed records)
• ✅ Subscription management (persistent state tracking)
• ✅ Change type detection (Insert/Update/Delete)
• ✅ Audit logging for monitoring
• ✅ Initial full load + incremental updates
• ✅ Real-time data synchronization

---

Why Use ODP OData?

Traditional RFC Limitations

• ❌ Banned by SAP - RFC extraction prohibited per OSS Note 3255746
• ❌ No delta support - Must extract full datasets
• ❌ Firewall issues - Requires NetWeaver connectivity

ODP OData Benefits

• ✅ SAP Approved - Official method for data extraction
• ✅ Delta replication - Only changed records transferred
• ✅ Web-based - Works through HTTPS
• ✅ Real-time - Incremental updates as data changes
• ✅ Scalable - Handles large datasets efficiently

---

Key Concepts

Term	Description	Example
Entity Set	A collection of data (like a table)	FactsOf0D_NW_C01 (sales data)
Delta Token	A bookmark for incremental updates	'D20250914154609_000019000'
Subscription	A persistent connection for delta updates	Automatically managed by ERPL-Web
RECORD_MODE	Change type indicator	'' (update), 'N' (insert), 'D' (delete)
OData Service	The API endpoint providing the data	/sap/opu/odata/sap/Z_ODP_BW_1_SRV/

---

Prerequisites

Before starting, ensure you have:

1. SAP System with ODP enabled - S/4HANA, BW, ECC with ODP support
2. ICF Services activated - OData services must be active in SAP
3. User Permissions - Access to ODP providers and data sources
4. Network Access - HTTPS connectivity to SAP system

tip

Check if ICF services are activated using the activate_icf_services.abap script in the erpl-web repository.

---

Quick Start

Step 1: Create Authentication Secret

-- Load the extension
LOAD erpl_web;

-- Create HTTP Basic authentication secret
CREATE SECRET sap_system (
  TYPE http_basic,
  username 'YOUR_SAP_USERNAME',
  password 'YOUR_SAP_PASSWORD'
);

Step 2: Discover Available Data

-- Find ODP OData services
SELECT 
  service_name,
  entity_set_name,
  full_entity_set_url,
  description
FROM odp_odata_show('https://your-sap-server:port', secret='sap_system')
WHERE entity_set_name LIKE '%SALES%'
LIMIT 10;

Example Output:

┌─────────────────┬──────────────────┬────────────────────────────────┬──────────────────┐
│  service_name   │ entity_set_name  │     full_entity_set_url        │   description    │
├─────────────────┼──────────────────┼────────────────────────────────┼──────────────────┤
│ Z_ODP_BW_1_SRV  │ FactsOf0D_NW_C01 │ https://server/sap/opu/odata...│ Sales Facts Data │
└─────────────────┴──────────────────┴────────────────────────────────┴──────────────────┘

Step 3: Initial Load (Creates Subscription)

The first call to odp_odata_read() automatically:

• Creates a subscription
• Performs a full initial load
• Stores a delta token for future updates

-- Create subscription and load all data
SELECT COUNT(*) as total_records
FROM odp_odata_read(
  'https://your-sap-server:port/sap/opu/odata/sap/Z_ODP_BW_1_SRV/FactsOf0D_NW_C01',
  secret='sap_system'
);

Example Output:

┌───────────────┐
│ total_records │
├───────────────┤
│        15420  │
└───────────────┘

Step 4: Delta Load (Only Changes)

Subsequent calls fetch only changes since last extraction:

-- Fetch delta updates
SELECT RECORD_MODE, COUNT(*) as count
FROM odp_odata_read(
  'https://your-sap-server:port/sap/opu/odata/sap/Z_ODP_BW_1_SRV/FactsOf0D_NW_C01',
  secret='sap_system'
)
GROUP BY RECORD_MODE;

Example Output:

┌─────────────┬───────┐
│ RECORD_MODE │ count │
├─────────────┼───────┤
│             │   342 │  -- Updated records
│ N           │    87 │  -- New records
│ D           │    23 │  -- Deleted records
└─────────────┴───────┘

---

Understanding RECORD_MODE

The RECORD_MODE column tells you what action to take for each record:

RECORD_MODE	Meaning	SQL Action
'' (empty string)	Updated Record	UPDATE in your target
'N'	New Record	INSERT in your target
'D'	Deleted Record	DELETE from your target

Example: Processing Changes

-- Get all changes
WITH delta_data AS (
  SELECT * FROM odp_odata_read(
    'https://sap-server/odata/SALES_DATA',
    secret='sap_system'
  )
)
-- Process inserts
INSERT INTO target_table
SELECT * FROM delta_data WHERE RECORD_MODE = 'N';

-- Process updates
UPDATE target_table t
SET 
  field1 = d.field1,
  field2 = d.field2
FROM delta_data d
WHERE t.key = d.key AND d.RECORD_MODE = '';

-- Process deletes
DELETE FROM target_table
WHERE key IN (
  SELECT key FROM delta_data WHERE RECORD_MODE = 'D'
);

---

Subscription Management

ERPL-Web automatically manages subscriptions, but you can monitor and control them.

List Active Subscriptions

SELECT 
  subscription_id,
  entity_set_name,
  entity_set_url,
  subscription_status,
  last_delta_token,
  created_at
FROM odp_odata_list_subscriptions();

Example Output:

┌─────────────────┬──────────────────┬─────────────────────┬────────────────────┬──────────────────────────┬─────────────────────┐
│ subscription_id │ entity_set_name  │  entity_set_url     │ subscription_status│    last_delta_token      │     created_at      │
├─────────────────┼──────────────────┼─────────────────────┼────────────────────┼──────────────────────────┼─────────────────────┤
│ sub_001         │ FactsOf0D_NW_C01 │ https://sap/odata...│ active             │ D20250914154609_000019000│ 2024-01-15 10:30:00 │
└─────────────────┴──────────────────┴─────────────────────┴────────────────────┴──────────────────────────┴─────────────────────┘

Remove a Subscription

-- Remove subscription from local tracking
PRAGMA odp_odata_remove_subscription('sub_001', false);

-- Remove from local AND delete on SAP server
PRAGMA odp_odata_remove_subscription('sub_001', true);

warning

Removing a subscription resets delta tracking. The next extraction will be a full load.

Subscription Lifecycle

1. Creation - First call to odp_odata_read() creates subscription
2. Active - Subscription tracks delta token after each extraction
3. Delta Updates - Subsequent calls use stored delta token
4. Removal - Manually remove when no longer needed

---

Monitoring & Auditing

ERPL-Web logs all ODP operations to an audit table for monitoring and troubleshooting.

View Audit Logs

SELECT 
  subscription_id,
  request_timestamp,
  request_type,
  package_count,
  records_received,
  has_more_data,
  new_delta_token,
  execution_time_ms
FROM erpl_web.odp_subscription_audit
ORDER BY request_timestamp DESC
LIMIT 20;

Columns Explained:

• subscription_id: Which subscription made the request
• request_timestamp: When the extraction occurred
• request_type: 'INITIAL' or 'DELTA'
• package_count: Number of packages received
• records_received: Total records extracted
• has_more_data: Whether more data is available
• new_delta_token: Delta token for next extraction
• execution_time_ms: Query duration

Performance Analysis

-- Average extraction time by subscription
SELECT 
  subscription_id,
  entity_set_name,
  AVG(execution_time_ms) as avg_time_ms,
  AVG(records_received) as avg_records,
  COUNT(*) as extraction_count
FROM erpl_web.odp_subscription_audit
GROUP BY subscription_id, entity_set_name
ORDER BY avg_time_ms DESC;

Monitor Data Freshness

-- Check when data was last updated
SELECT 
  subscription_id,
  entity_set_name,
  MAX(request_timestamp) as last_extraction,
  DATEDIFF('hour', MAX(request_timestamp), CURRENT_TIMESTAMP) as hours_since_update
FROM erpl_web.odp_subscription_audit
GROUP BY subscription_id, entity_set_name;

---

Delta Replication Workflow

Complete ETL Pattern

-- 1. Create target table (first time only)
CREATE TABLE sales_facts (
  sales_order VARCHAR,
  customer_id VARCHAR,
  revenue DECIMAL(15,2),
  order_date DATE,
  RECORD_MODE VARCHAR  -- Track change type
);

-- 2. Initial Load
INSERT INTO sales_facts
SELECT * FROM odp_odata_read(
  'https://sap/odata/Z_ODP_BW_1_SRV/FactsOf0D_NW_C01',
  secret='sap_system'
);

-- 3. Incremental Updates (run periodically)
WITH delta AS (
  SELECT * FROM odp_odata_read(
    'https://sap/odata/Z_ODP_BW_1_SRV/FactsOf0D_NW_C01',
    secret='sap_system'
  )
)
-- Handle inserts
INSERT INTO sales_facts
SELECT * FROM delta WHERE RECORD_MODE = 'N';

-- Handle updates
UPDATE sales_facts t
SET 
  revenue = d.revenue,
  order_date = d.order_date
FROM delta d
WHERE t.sales_order = d.sales_order 
  AND d.RECORD_MODE = '';

-- Handle deletes
DELETE FROM sales_facts
WHERE sales_order IN (
  SELECT sales_order FROM delta WHERE RECORD_MODE = 'D'
);

Export to Parquet Data Lake

-- Extract to Parquet with partitioning
COPY (
  SELECT * FROM odp_odata_read(
    'https://sap/odata/SALES_DATA',
    secret='sap_system'
  )
) TO 'sales_data.parquet' (
  FORMAT PARQUET,
  PARTITION_BY (order_date)
);

Incremental File Updates

-- Daily delta extraction to dated files
COPY (
  SELECT * FROM odp_odata_read(
    'https://sap/odata/SALES_DATA',
    secret='sap_system'
  )
) TO 'sales_delta_2024-01-15.parquet' (FORMAT PARQUET);

---

Advanced Usage

Batch Processing Multiple Entities

-- Create a list of entity sets to extract
CREATE TABLE entity_sets (entity_url VARCHAR);
INSERT INTO entity_sets VALUES 
  ('https://sap/odata/Z_ODP_BW_1_SRV/FactsOf0D_NW_C01'),
  ('https://sap/odata/Z_ODP_BW_1_SRV/CustomerData'),
  ('https://sap/odata/Z_ODP_BW_1_SRV/ProductData');

-- Extract all entities
SELECT 
  e.entity_url,
  COUNT(*) as record_count
FROM entity_sets e,
LATERAL (
  SELECT * FROM odp_odata_read(e.entity_url, secret := 'sap_system')
) data
GROUP BY e.entity_url;

Custom Subscription IDs

By default, subscriptions are auto-generated. For persistent tracking:

-- The subscription_id is derived from the entity_set_url
-- and stored automatically in the audit table

Error Handling and Retries

-- Wrap extraction in error handling
CREATE OR REPLACE MACRO extract_with_retry(url, max_retries := 3) AS (
  -- Implementation would require scripting/procedural logic
  -- For now, check audit logs for failures and re-run
);

---

Troubleshooting

Authentication Failures

Issue: 401 Unauthorized

Solutions:

-- 1. Verify credentials
CREATE SECRET sap_system (
  TYPE http_basic,
  username 'correct_username',
  password 'correct_password'
);

-- 2. Test with a simple HTTP request first
SELECT status FROM http_get(
  'https://sap-server:port',
  auth := 'user:pass',
  auth_type := 'BASIC'
);

Subscription State Issues

Issue: Subscription not found or corrupt

Solution:

-- Remove and recreate subscription
PRAGMA odp_odata_remove_subscription('subscription_id', false);

-- Next extraction will create new subscription
SELECT * FROM odp_odata_read(
  'https://sap/odata/ENTITY_SET',
  secret='sap_system'
);

Network Timeouts

Issue: Request timeout

Solution:

-- ODP extractions can be large. Check audit logs for progress.
-- If timeout occurs, subscription state is preserved and can resume.

-- Check last successful extraction
SELECT * FROM erpl_web.odp_subscription_audit
WHERE subscription_id = 'your_sub_id'
ORDER BY request_timestamp DESC LIMIT 1;

No Delta Data Returned

Issue: Delta extraction returns 0 records

Explanation: This is normal! It means no data changed since the last extraction.

-- This is expected if no changes occurred
SELECT COUNT(*) FROM odp_odata_read(...);
-- Returns: 0

Memory Issues

Issue: Out of memory during large extractions

Solutions:

1. Check package sizes in audit logs
2. Increase DuckDB memory settings
3. Extract to files instead of memory:

-- Stream directly to Parquet
COPY (
  SELECT * FROM odp_odata_read(...)
) TO 'output.parquet' (FORMAT PARQUET);

Enable Tracing

For detailed debugging:

SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';

-- Run extraction
SELECT * FROM odp_odata_read(...);

-- Check trace logs

---

Performance Optimization

1. Monitor Extraction Times

SELECT 
  entity_set_name,
  AVG(execution_time_ms) / 1000.0 as avg_time_seconds,
  AVG(records_received) as avg_records
FROM erpl_web.odp_subscription_audit
WHERE request_type = 'DELTA'
GROUP BY entity_set_name;

2. Reduce Extraction Frequency

If extractions are slow, consider less frequent updates:

• Real-time: Every 5 minutes
• Near real-time: Every hour
• Batch: Daily/weekly

3. Use Parquet for Large Datasets

-- Don't load large datasets into memory
-- Stream directly to Parquet files
COPY (SELECT * FROM odp_odata_read(...))
TO 'data.parquet' (FORMAT PARQUET, COMPRESSION 'ZSTD');

4. Parallel Extraction

Extract multiple entity sets in parallel (using multiple DuckDB sessions):

# Session 1
duckdb -c "SELECT * FROM odp_odata_read('url1', ...)"

# Session 2 (separate process)
duckdb -c "SELECT * FROM odp_odata_read('url2', ...)"

---

Integration Patterns

Scheduled Delta Sync

#!/bin/bash
# daily_sync.sh

duckdb analytics.db <<EOF
LOAD erpl_web;

-- Extract delta changes
INSERT INTO sales_facts
SELECT * FROM odp_odata_read(
  'https://sap/odata/SALES_DATA',
  secret='sap_system'
)
WHERE RECORD_MODE = 'N';  -- Only inserts for simplicity

-- Log completion
INSERT INTO sync_log VALUES (CURRENT_TIMESTAMP, 'SALES_FACTS', 'SUCCESS');
EOF

Data Lake Pattern

-- Daily partitioned extractions
COPY (
  SELECT *, CURRENT_DATE as extraction_date
  FROM odp_odata_read(
    'https://sap/odata/SALES_DATA',
    secret='sap_system'
  )
) TO 'datalake/sales/year=2024/month=01/day=15/data.parquet'
(FORMAT PARQUET);

Change Data Capture (CDC)

-- Track all changes with timestamps
CREATE TABLE cdc_log (
  change_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  source_table VARCHAR,
  change_type VARCHAR,
  key_value VARCHAR,
  old_value JSON,
  new_value JSON
);

-- Log changes from ODP
WITH changes AS (
  SELECT * FROM odp_odata_read(...)
)
INSERT INTO cdc_log
SELECT 
  CURRENT_TIMESTAMP,
  'SALES_FACTS',
  RECORD_MODE,
  sales_order,
  NULL,  -- Would need to query old value
  to_json(changes)
FROM changes;

---

Best Practices

1. Always Use Secrets

-- Good
CREATE SECRET sap_prod (TYPE http_basic, ...);

-- Bad (hardcoded credentials)
-- SELECT * FROM odp_odata_read(..., auth := 'user:pass');

2. Monitor Audit Logs Regularly

-- Weekly audit review
SELECT 
  DATE_TRUNC('day', request_timestamp) as day,
  COUNT(*) as extraction_count,
  SUM(records_received) as total_records
FROM erpl_web.odp_subscription_audit
WHERE request_timestamp >= CURRENT_DATE - INTERVAL 7 DAYS
GROUP BY day;

3. Handle All RECORD_MODE Types

Always process inserts, updates, AND deletes:

-- Don't forget deletes!
DELETE FROM target WHERE key IN (
  SELECT key FROM delta WHERE RECORD_MODE = 'D'
);

4. Archive Audit Logs

-- Prevent audit table from growing too large
DELETE FROM erpl_web.odp_subscription_audit
WHERE request_timestamp < CURRENT_DATE - INTERVAL 90 DAYS;

5. Test with Small Datasets First

-- Start with a small entity set
SELECT * FROM odp_odata_show(...)
WHERE entity_set_name LIKE '%TEST%';

---

Complete Reference Example

Here's a production-ready delta replication workflow:

-- ============================================
-- Complete ODP Delta Replication Example
-- ============================================

-- 1. Setup (once)
LOAD erpl_web;

CREATE SECRET sap_prod (
  TYPE http_basic,
  username 'SAP_USER',
  password 'SAP_PASS'
);

-- 2. Discover data sources
CREATE TABLE available_sources AS
SELECT * FROM odp_odata_show(
  'https://sap-prod:8001',
  secret='sap_prod'
);

-- 3. Create target table
CREATE TABLE sales_data (
  sales_order VARCHAR PRIMARY KEY,
  customer_id VARCHAR,
  product_id VARCHAR,
  quantity INTEGER,
  revenue DECIMAL(15,2),
  order_date DATE,
  last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. Initial load
INSERT INTO sales_data
SELECT 
  sales_order,
  customer_id,
  product_id,
  quantity,
  revenue,
  order_date
FROM odp_odata_read(
  'https://sap-prod:8001/sap/opu/odata/sap/Z_SALES_SRV/SalesOrders',
  secret='sap_prod'
);

-- 5. Delta sync function (run periodically)
CREATE OR REPLACE MACRO sync_sales_data() AS TABLE (
  WITH delta AS (
    SELECT * FROM odp_odata_read(
      'https://sap-prod:8001/sap/opu/odata/sap/Z_SALES_SRV/SalesOrders',
      secret='sap_prod'
    )
  )
  -- Return summary
  SELECT 
    COUNT(CASE WHEN RECORD_MODE = 'N' THEN 1 END) as inserts,
    COUNT(CASE WHEN RECORD_MODE = '' THEN 1 END) as updates,
    COUNT(CASE WHEN RECORD_MODE = 'D' THEN 1 END) as deletes
  FROM delta
);

-- 6. Monitor performance
SELECT * FROM erpl_web.odp_subscription_audit
WHERE entity_set_name LIKE '%SalesOrders%'
ORDER BY request_timestamp DESC LIMIT 10;

---

Next Steps

• Learn about OData for general OData services
• Explore Secrets Management for credential security
• See Tracing & Diagnostics for debugging
• Check Examples for more patterns

---

Summary

ODP via OData in ERPL-Web provides:

✅ SAP-Approved - Official method replacing RFC extraction
✅ Delta Replication - Only transfer changed data
✅ Automatic - Subscription and token management built-in
✅ Auditable - Complete logging for monitoring
✅ Scalable - Handle large SAP datasets efficiently

Start synchronizing your SAP data with delta replication today!