Data Caching Implementation Example: Transaction Data from S/4HANA

---

Scenario

A Supplier Portal needs access to transaction data, such as purchase orders (POs), invoices, and delivery schedules, from SAP S/4HANA. This data is dynamic and updated frequently, requiring a caching strategy that balances real-time accuracy with system efficiency.

---

Objective

• Cache frequently accessed transaction data (e.g., purchase orders) locally in the portal.
• Use incremental sync (differential caching) to fetch only new or updated records.
• Ensure cached data is consistent with the backend while minimizing API calls.

---

Steps for Implementation

Step 1: Identify Transaction Data Entities

Identify transaction data relevant for caching:

• Purchase Orders:
  • Header and line item details.
• Invoices:
  • Payment status and outstanding amounts.
• Deliveries:
  • Delivery status and quantities.

---

Step 2: Design the Cache Database

Define database tables to store transaction data. These tables will be updated incrementally.

Purchase Order Header Table:

CREATE TABLE PurchaseOrderHeader (
    PurchaseOrderID VARCHAR(20) PRIMARY KEY,
    SupplierID VARCHAR(20),
    PODate DATE,
    Status VARCHAR(20),
    TotalAmount DECIMAL(15, 2),
    LastUpdated TIMESTAMP
);

Purchase Order Item Table:

CREATE TABLE PurchaseOrderItem (
    ItemID SERIAL PRIMARY KEY,
    PurchaseOrderID VARCHAR(20),
    MaterialID VARCHAR(20),
    Quantity DECIMAL(10, 2),
    UnitPrice DECIMAL(10, 2),
    TotalPrice DECIMAL(15, 2),
    LastUpdated TIMESTAMP,
    FOREIGN KEY (PurchaseOrderID) REFERENCES PurchaseOrderHeader(PurchaseOrderID)
);

Invoice Table:

CREATE TABLE Invoice (
    InvoiceID VARCHAR(20) PRIMARY KEY,
    PurchaseOrderID VARCHAR(20),
    InvoiceDate DATE,
    Amount DECIMAL(15, 2),
    Status VARCHAR(20),
    LastUpdated TIMESTAMP
);

Delivery Table:

CREATE TABLE Delivery (
    DeliveryID VARCHAR(20) PRIMARY KEY,
    PurchaseOrderID VARCHAR(20),
    DeliveryDate DATE,
    DeliveredQuantity DECIMAL(10, 2),
    Status VARCHAR(20),
    LastUpdated TIMESTAMP
);

---

Step 3: Fetch Data from S/4HANA

Use APIs to fetch transaction data. Include filters to fetch only changed or new records.

Purchase Order API Call:

GET /sap/opu/odata/sap/API_PURCHASEORDER_PROCESS_SRV/A_PurchaseOrder?$filter=LastChangeDate gt '2025-01-01T00:00:00'

Invoice API Call:

GET /sap/opu/odata/sap/API_BILLING_DOCUMENT_SRV/A_BillingDocument?$filter=LastChangeDate gt '2025-01-01T00:00:00'

Delivery API Call:

GET /sap/opu/odata/sap/API_OUTBOUND_DELIVERY_SRV/A_OutboundDelivery?$filter=LastChangeDate gt '2025-01-01T00:00:00'

---

Step 4: Load Data into the Cache

Transform the API response into the database schema and load it into the cache.

Example Node.js Code for Incremental Sync:

const axios = require('axios');
const { Pool } = require('pg');

// Database connection pool
const pool = new Pool({
  user: 'username',
  host: 'localhost',
  database: 'supplier_portal',
  password: 'password',
  port: 5432,
});

// API endpoint and headers
const apiUrl = 'https://s4hana.example.com/sap/opu/odata/sap/API_PURCHASEORDER_PROCESS_SRV/A_PurchaseOrder';
const headers = { Authorization: 'Bearer YOUR_ACCESS_TOKEN' };

// Fetch and update purchase orders
async function syncPurchaseOrders(lastSync) {
  try {
    const response = await axios.get(`${apiUrl}?$filter=LastChangeDate gt '${lastSync}'`, { headers });
    const purchaseOrders = response.data.value;

    const client = await pool.connect();

    for (const po of purchaseOrders) {
      await client.query(`
        INSERT INTO PurchaseOrderHeader (PurchaseOrderID, SupplierID, PODate, Status, TotalAmount, LastUpdated)
        VALUES ($1, $2, $3, $4, $5, NOW())
        ON CONFLICT (PurchaseOrderID) DO UPDATE
        SET SupplierID = EXCLUDED.SupplierID,
            PODate = EXCLUDED.PODate,
            Status = EXCLUDED.Status,
            TotalAmount = EXCLUDED.TotalAmount,
            LastUpdated = NOW();
      `, [
        po.PurchaseOrder,
        po.SupplierID,
        po.PODate,
        po.Status,
        po.TotalAmount,
      ]);
    }

    client.release();
  } catch (error) {
    console.error('Error syncing purchase orders:', error.message);
  }
}

// Example usage
syncPurchaseOrders('2025-01-01T00:00:00');

---

Step 5: Schedule Incremental Sync Jobs

Set up periodic jobs to fetch only new or updated records using filters like LastChangeDate.

Example Cron Job:

0 */3 * * * node sync_transaction_data.js

This job runs every 3 hours to fetch incremental updates.

---

Step 6: Monitor and Validate Cache

1. Monitoring:
   • Log API responses and sync status.
   • Track errors and retry failed updates.
2. Validation:
   • Periodically reconcile the cache with the source system.
   • Compare key transaction counts (e.g., number of open POs).

---

Best Practices for Caching Transaction Data

1. Optimize API Calls:
   • Use delta tokens or timestamps to fetch only necessary data.
   • Implement pagination for large datasets.
2. Handle Conflicts:
   • Use the LastChangeDate field to resolve data discrepancies.
   • Prioritize the latest update when merging records.
3. Use Appropriate Expiry Policies:
   • Set TTL for cached records based on transaction frequency.
   • Example: Retain POs in the cache for 30 days after closure.
4. Implement Fallback Mechanisms:
   • Fetch data directly from S/4HANA if a cache miss occurs.
5. Secure Data Storage:
   • Encrypt sensitive transactional data.
   • Use role-based access controls for the cache database.

---

Example Use Case: Supplier Portal

Scenario:

Suppliers view purchase orders and delivery schedules in real time while interacting with the portal.

Workflow:

1. Supplier logs in and navigates to the “Purchase Orders” page.
2. Portal queries the local cache for POs belonging to the supplier.
3. If a requested PO is not in the cache:
   • Fetch the PO details from S/4HANA using an API call.
   • Update the cache with the new data.
4. Display the PO and related delivery/invoice details.

Benefits:

• Reduces backend dependency for frequently accessed POs.
• Ensures up-to-date data through incremental updates.
• Improves portal performance with faster query responses.

---

Conclusion

Caching transaction data from S/4HANA using incremental sync ensures efficient access to frequently changing records while minimizing backend load. By implementing dynamic on-demand caching, scheduled updates, and secure storage, businesses can enhance performance and reliability in integration scenarios. Proper monitoring and validation mechanisms ensure the cache remains consistent with the source system.