What is the Foundation Data Layer?

The Foundation Data Layer (FDL) is RMS's systematic mechanism for extracting master data and making it available to downstream systems. While the RIB handles real-time message-based integration, the FDL handles bulk batch-based data feeds.

Think of the FDL as the "data export engine" of RMS. Every night, FDL programs extract items, locations, suppliers, hierarchies, and inventory data from RMS tables, flatten them into standardized formats, and deposit them for downstream consumption.

Extract Types: Full vs. Delta

FDL supports two extraction strategies:

Full Extract

A full extract dumps the complete contents of the source entity. Every row in the source table is included regardless of when it was last modified.

When to use:

• Initial data load for a new downstream system
• Data warehouse rebuild after a failed ETL
• Periodic data reconciliation (monthly full refresh)

Trade-off: Full extracts on large tables (ITEM_MASTER: 5M rows, ITEM_LOC_SOH: 500M rows) are extremely resource-intensive and time-consuming.

Delta Extract

A delta extract includes only rows that have been created, modified, or deleted since the last extraction run. RMS tracks changes using LAST_UPDATE_DATETIME columns on most tables.

When to use:

• Nightly incremental feeds to the data warehouse
• Daily updates to planning systems (RPAS)
• Regular synchronization with third-party systems

Trade-off: Delta extracts are fast (typically 1-5% of full volume) but require accurate LAST_UPDATE_DATETIME values. If a batch job updates rows without setting this column, those changes are invisible to delta extracts.

Extraction Logic

-- FDL delta extraction pattern
-- Extract items modified since last run
SELECT 
    im.item,
    im.item_desc,
    im.dept,
    im.class,
    im.subclass,
    im.status,
    im.item_level,
    im.tran_level,
    im.create_datetime,
    im.last_update_datetime
FROM 
    item_master im
WHERE 
    im.last_update_datetime > (
        SELECT last_extract_datetime 
        FROM   fdl_control 
        WHERE  entity_name = 'ITEM'
    );
    
-- After successful extraction, update the control table
UPDATE fdl_control 
SET    last_extract_datetime = SYSDATE,
       last_extract_count = :rows_extracted
WHERE  entity_name = 'ITEM';

COMMIT;

FDL Entity Catalog

FDL extracts cover the major RMS data entities:

FDL Staging Tables

Extracted data is loaded into dedicated FDL staging tables that flatten the normalized RMS structure:

Downstream Consumer Mapping

ODI Integration Patterns

In on-premise deployments, Oracle Data Integrator (ODI) is the primary tool for orchestrating FDL data flows:

1. ODI Interface: Defines the source (FDL staging tables), transformation logic, and target (data warehouse tables)
2. Knowledge Modules: ODI's pluggable components that generate optimized SQL for extract, load, and transformation
3. Load Plans: Orchestrate the execution sequence (extract items before item-locations, because of dependencies)
4. Error Handling: ODI captures rejected rows in error tables for review and reprocessing

ODI Integration Pattern

RMS Database                 ODI Server                Target System
┌──────────────┐     ┌──────────────────────┐    ┌──────────────┐
│ FDL Staging  │────▶│ ODI Interface:       │───▶│ RI Data      │
│ Tables       │     │                      │    │ Warehouse    │
│              │     │ 1. Extract from FDL  │    │              │
│ FDL_ITEM     │     │ 2. Transform         │    │ W_PRODUCT_D  │
│ FDL_ITEM_LOC │     │    (cleanse, conform)│    │ W_RTL_INV_F  │
│ FDL_SUPPLIER │     │ 3. Load to target    │    │              │
└──────────────┘     └──────────────────────┘    └──────────────┘

BDI in Cloud Deployments

In Oracle Retail Cloud (v19+), the FDL concept evolves into BDI (Bulk Data Integration):

• REST-Based Extraction: Instead of direct database queries against FDL tables, BDI exposes REST API endpoints for data extraction
• Cloud Object Storage: Extracted data is deposited in Oracle Cloud Object Storage buckets rather than on-premise file systems
• Managed Scheduling: BDI extraction schedules are managed through POM, not external schedulers
• Pre-Built Connectors: BDI ships with pre-built connectors for all major downstream systems

Best Practices