Reporting out of RMS

While Oracle Retail Insights (RI) is the official data warehouse for the Oracle Retail suite, many retailers still write custom operational reports directly against the RMS database (or a near-real-time replica).

Navigating the 4,000+ tables in RMS can be daunting. As a BI developer, you need to focus on a few core reporting views and tables.

Inventory & Stock Ledger

The most common reports in retail are inventory reports: "What do we have, where is it, and how much is it worth?"

Denormalized Hierarchies

In RMS, the merchandise hierarchy is highly normalized (Dept, Class, Subclass are in separate tables). Writing joins across 5 tables just to get a roll-up report is terrible for performance.

Oracle provides Denormalized tables specifically designed for reporting.

Merchandise Hierarchy Denormalized

Use the DEPS table for departments, but for full roll-ups, look for the SUBCLASS table or specialized reporting views if your DBA has built them. However, RMS natively provides denormalized tables for Locations and Items.

Location Hierarchy Denormalized

Instead of joining STORE -> DISTRICT -> REGION -> AREA -> CHAIN, you can query the STORE_HIERARCHY table.

SELECT 
    store,
    store_name,
    district,
    district_name,
    region,
    region_name,
    chain,
    chain_name
FROM 
    store_hierarchy
WHERE 
    store = 1001;

Using VDATE for Reporting

Always include the current business date on operational reports. Since server dates can shift mid-batch, pulling VDATE from the PERIOD table ensures the report's "As Of" date perfectly matches the financial postings.