ODP Protocol Deep Dive

This comprehensive guide covers the ODP (Operational Data Provisioning) protocol in ERPL. Learn how to extract and replicate SAP data with delta/full modes, manage subscriptions, and track changes.

What is ODP?

ODP (Operational Data Provisioning) is SAP's standard for data extraction and replication. It provides automatic subscription management, change tracking (Insert/Update/Delete), and supports multiple contexts including BW DataSources, SAPI, ABAP CDS, and SLT extractors.

ODP Architecture

Understanding ODP Contexts

ODP supports multiple contexts for different data sources:

• BW - SAP BW DataSources and InfoProviders
• SAPI - Extraction APIs (SAP Application Interface)
• ABAP_CDS - CDS Views (Core Data Services)
• SLT - SAP Landscape Transformation
• HANA - SAP HANA views and tables

Basic Extraction

List Available Contexts

-- List all available ODP contexts
SELECT * FROM sap_odp_show_contexts();

-- Example output:
-- context_name | description
-- BW          | SAP BW DataSources
-- SAPI        | SAP Application Interface
-- ABAP_CDS    | ABAP CDS Views
-- SLT         | SAP Landscape Transformation

List Data Sources

-- List data sources in BW context
SELECT * FROM sap_odp_show('BW');

-- List data sources in SAPI context
SELECT * FROM sap_odp_show('SAPI');

-- List data sources in ABAP_CDS context
SELECT * FROM sap_odp_show('ABAP_CDS');

Describe Data Source Structure

-- Describe BW DataSource structure
SELECT * FROM sap_odp_describe('BW', 'VBAK$F');

-- Describe SAPI extractor structure
SELECT * FROM sap_odp_describe('SAPI', '2LIS_11_VAHDR');

-- Describe CDS view structure
SELECT * FROM sap_odp_describe('ABAP_CDS', 'I_SALESORDER');

Basic Full Load

-- Full load from BW DataSource
SELECT * FROM sap_odp_read_full('BW', 'VBAK$F');

-- Full load from SAPI extractor
SELECT * FROM sap_odp_read_full('SAPI', '2LIS_11_VAHDR');

-- Full load from CDS view
SELECT * FROM sap_odp_read_full('ABAP_CDS', 'I_SALESORDER');

Delta Replication

Replication Modes

ODP supports three replication modes:

• FULL - Complete data load (creates subscription if needed)
• DELTA - Only changed records since last extraction
• RECOVER - Recovery mode for failed extractions

Initial Full Load

-- First run creates subscription and loads all data
SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F',
  replication_mode => 'FULL'
);

-- With explicit subscriber information
SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F',
  replication_mode => 'FULL',
  subscriber_name => 'ERPL',
  subscriber_type => 'SAP_BW'
);

Subsequent Delta Loads

-- Subsequent runs only get changed records
SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F',
  replication_mode => 'DELTA'
);

-- Check what changed
SELECT 
  RECORD_MODE,
  COUNT(*) AS record_count
FROM sap_odp_read_full(
  'BW', 
  'VBAK$F',
  replication_mode => 'DELTA'
)
GROUP BY RECORD_MODE;

Recovery Mode

-- Use recovery mode if previous extraction failed
SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F',
  replication_mode => 'RECOVER'
);

Advanced Topics

Preview Data Without Subscription

-- Preview data without creating subscription
SELECT * FROM sap_odp_preview('BW', 'VBAK$F');

-- Preview with limit
SELECT * FROM sap_odp_preview('BW', 'VBAK$F', LIMIT => 100);

Selection Parameters and Filtering

-- Apply selection parameters
SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F',
  replication_mode => 'FULL',
  selection => 'ERDAT >= "20240101"'
);

-- Multiple selection criteria
SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F',
  replication_mode => 'FULL',
  selection => 'ERDAT >= "20240101" AND KUNNR = "0000001000"'
);

Advanced: Subscription Management (For SAP Experts)

For SAP Administrators

This section covers advanced subscription management, cursor handling, and recovery strategies.

View Active Subscriptions

-- List all active subscriptions
SELECT * FROM sap_odp_show_subscriptions();

-- Check subscription details
SELECT 
  subscriber_name,
  subscriber_type,
  data_source,
  context,
  status,
  created_date
FROM sap_odp_show_subscriptions();

Check Extraction Cursors

-- View extraction cursors
SELECT * FROM sap_odp_show_cursors();

-- Check cursor status for specific data source
SELECT * FROM sap_odp_show_cursors(
  replication_mode => 'DELTA'
);

Drop Subscriptions

-- Drop specific subscription
PRAGMA sap_odp_drop('BW', 'VBAK$F', 'ERPL');

-- Drop all subscriptions (use with caution)
PRAGMA sap_odp_drop_all();

Subscription Lifecycle Management

-- Complete subscription lifecycle example
-- 1. Check if subscription exists
SELECT * FROM sap_odp_show_subscriptions()
WHERE data_source = 'VBAK$F' AND subscriber_name = 'ERPL';

-- 2. If not exists, create with full load
SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F',
  replication_mode => 'FULL',
  subscriber_name => 'ERPL'
);

-- 3. Subsequent delta loads
SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F',
  replication_mode => 'DELTA'
);

-- 4. Monitor subscription status
SELECT * FROM sap_odp_show_subscriptions()
WHERE subscriber_name = 'ERPL';

Real-World Examples

Daily Delta Replication Pipeline

-- Daily delta replication workflow
WITH delta_data AS (
  SELECT 
    *,
    RECORD_MODE,
    CASE RECORD_MODE
      WHEN 'I' THEN 'INSERT'
      WHEN 'U' THEN 'UPDATE'
      WHEN 'D' THEN 'DELETE'
      ELSE 'UNKNOWN'
    END AS change_type
  FROM sap_odp_read_full(
    'BW', 
    'VBAK$F',
    replication_mode => 'DELTA'
  )
)
SELECT 
  change_type,
  COUNT(*) AS record_count,
  MIN(ERDAT) AS earliest_date,
  MAX(ERDAT) AS latest_date
FROM delta_data
GROUP BY change_type
ORDER BY change_type;

Multi-Context Data Integration

-- Combine data from multiple ODP contexts
WITH bw_sales AS (
  SELECT 
    VBELN AS sales_doc,
    ERDAT AS doc_date,
    KUNNR AS customer,
    NETWR AS net_value,
    'BW' AS source_context
  FROM sap_odp_read_full('BW', 'VBAK$F', replication_mode => 'DELTA')
),
cds_sales AS (
  SELECT 
    SalesOrder AS sales_doc,
    DocumentDate AS doc_date,
    SoldToParty AS customer,
    NetAmount AS net_value,
    'CDS' AS source_context
  FROM sap_odp_read_full('ABAP_CDS', 'I_SALESORDER', replication_mode => 'DELTA')
)
SELECT 
  sales_doc,
  doc_date,
  customer,
  net_value,
  source_context
FROM bw_sales
UNION ALL
SELECT 
  sales_doc,
  doc_date,
  customer,
  net_value,
  source_context
FROM cds_sales
ORDER BY doc_date DESC, net_value DESC;

Change Data Capture Analysis

-- Analyze change patterns
WITH change_analysis AS (
  SELECT 
    RECORD_MODE,
    ERDAT AS change_date,
    COUNT(*) AS change_count,
    SUM(NETWR) AS total_value
  FROM sap_odp_read_full(
    'BW', 
    'VBAK$F',
    replication_mode => 'DELTA'
  )
  GROUP BY RECORD_MODE, ERDAT
)
SELECT 
  RECORD_MODE,
  change_date,
  change_count,
  total_value,
  change_count * 100.0 / SUM(change_count) OVER() AS percentage_of_changes
FROM change_analysis
ORDER BY change_date DESC, change_count DESC;

Error Recovery Pattern

-- Recovery pattern for failed extractions
-- 1. Check subscription status
SELECT * FROM sap_odp_show_subscriptions()
WHERE subscriber_name = 'ERPL' AND status = 'ERROR';

-- 2. Check cursor position
SELECT * FROM sap_odp_show_cursors()
WHERE subscriber_name = 'ERPL';

-- 3. Attempt recovery
SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F',
  replication_mode => 'RECOVER'
);

-- 4. Verify recovery success
SELECT * FROM sap_odp_show_subscriptions()
WHERE subscriber_name = 'ERPL';

Performance Optimization

Batch Processing

-- Process multiple data sources in parallel
SELECT 'VBAK$F' AS data_source, COUNT(*) AS record_count
FROM sap_odp_read_full('BW', 'VBAK$F', replication_mode => 'DELTA')

UNION ALL

SELECT 'VBAP$F' AS data_source, COUNT(*) AS record_count
FROM sap_odp_read_full('BW', 'VBAP$F', replication_mode => 'DELTA')

UNION ALL

SELECT 'KNA1$F' AS data_source, COUNT(*) AS record_count
FROM sap_odp_read_full('BW', 'KNA1$F', replication_mode => 'DELTA');

Selective Field Extraction

-- Extract only specific fields to reduce data volume
SELECT 
  VBELN,
  ERDAT,
  KUNNR,
  NETWR
FROM sap_odp_read_full(
  'BW', 
  'VBAK$F',
  replication_mode => 'DELTA'
);

Connection Pooling

-- Reuse connections for multiple extractions
-- (Connection pooling is handled automatically by ERPL)
SELECT * FROM sap_odp_read_full('BW', 'VBAK$F', replication_mode => 'DELTA');
SELECT * FROM sap_odp_read_full('BW', 'VBAP$F', replication_mode => 'DELTA');
SELECT * FROM sap_odp_read_full('BW', 'KNA1$F', replication_mode => 'DELTA');

Troubleshooting

Common Issues

Data Source Not Found

-- Check available data sources
SELECT * FROM sap_odp_show('BW');

-- Verify data source name (case-sensitive)
SELECT * FROM sap_odp_describe('BW', 'VBAK$F');

Subscription Errors

-- Check subscription status
SELECT * FROM sap_odp_show_subscriptions();

-- Check cursor status
SELECT * FROM sap_odp_show_cursors();

-- Attempt recovery
SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F',
  replication_mode => 'RECOVER'
);

Permission Denied

-- Check ODP access permissions
SELECT * FROM sap_odp_show_contexts();

-- Verify user permissions in SAP
SELECT * FROM sap_odp_show('BW');

No Delta Data

-- Check if subscription exists
SELECT * FROM sap_odp_show_subscriptions()
WHERE data_source = 'VBAK$F';

-- If no subscription, create with full load
SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F',
  replication_mode => 'FULL'
);

Debugging Tips

-- Enable debug mode
SET debug_mode = true;

-- Preview data structure
SELECT * FROM sap_odp_preview('BW', 'VBAK$F', LIMIT => 10);

-- Check subscription details
SELECT * FROM sap_odp_show_subscriptions();

-- Monitor extraction performance
EXPLAIN SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F',
  replication_mode => 'DELTA'
);

For SAP Experts

Advanced ODP Internals

This section covers ODP protocol internals for SAP Basis administrators and developers.

ODP Protocol Details

ODP uses HTTP/HTTPS with XML payloads for communication:

• Port: Usually 8080 or 8443 (HTTPS)
• Protocol: HTTP/HTTPS with XML
• Authentication: Basic Auth or SAML
• Compression: Optional gzip compression
• Unicode: Full Unicode support

Subscription Management

1. Automatic Creation: Subscriptions are created automatically on first FULL load
2. Cursor Management: ODP maintains cursors for delta tracking
3. Recovery: RECOVER mode handles failed extractions
4. Cleanup: Use PRAGMA commands to manage subscriptions

Performance Considerations

1. Delta Optimization: Use DELTA mode for incremental loads
2. Batch Processing: Process multiple data sources in parallel
3. Field Selection: Extract only needed fields
4. Connection Reuse: ERPL handles connection pooling automatically

Security Best Practices

1. Network Security: Use HTTPS for all ODP connections
2. Authentication: Use strong passwords or certificates
3. Authorization: Implement proper ODP user roles
4. Audit Logging: Enable ODP audit logs in SAP

Change Data Capture Patterns

1. Initial Load: Always start with FULL mode
2. Delta Processing: Use DELTA mode for subsequent loads
3. Error Handling: Use RECOVER mode for failed extractions
4. Monitoring: Track subscription status and cursor positions

Next Steps

🚀 Ready for More?

• RFC Protocol Guide - Read SAP tables and call functions
• BICS Protocol Guide - Execute SAP BW queries
• Function Reference - Complete API docs

🔧 Advanced Topics

• ODP Subscription Management - Complete subscription lifecycle guide
• Performance Tuning - Optimize ODP extractions
• Real-World Use Cases - Complete scenarios

💡 Examples

• ERPL Examples - More real-world ODP examples
• Integration with Python - Use ODP data with Pandas
• Real-World Use Cases - Complete scenarios

---

Need help? Check our troubleshooting guide or browse more examples.