Turning billions of rows of transactional data into actionable business intelligence. The enterprise data warehouse that connects every retail system.
What is Retail Insights?
Oracle RMS is an OLTP (Online Transaction Processing) database. It is highly optimized for writing data quickly — processing purchase orders, updating stock-on-hand, and recording transactions. It is terrible at running massive analytical queries spanning five years of history.
Oracle Retail Insights (RI) is the official Enterprise Data Warehouse (EDW) for the Oracle Retail suite. It is an OLAP (Online Analytical Processing) system built specifically to crunch massive amounts of data and serve up dashboards to business users.
Why Not Query RMS Directly?
New developers often ask: "Why build a separate data warehouse? Why not just run reports against RMS?"
The answer lies in fundamental database architecture differences:
| Aspect | RMS (OLTP) | RI (OLAP) |
|---|---|---|
| Optimized for | Individual row inserts/updates | Large aggregated reads |
| Schema | Highly normalized (3NF+) | Denormalized star schema |
| Query Speed | Fast for single-row lookups | Fast for million-row aggregations |
| History | Current state (limited history) | 3-5+ years of historical data |
| Users | Application users, batch jobs | Business analysts, executives |
| Indexes | B-tree (insert-optimized) | Bitmap, aggregate (scan-optimized) |
| Lock Contention | High (transactions are writing) | Low (read-only analytical queries) |
Running a complex analytical query against production RMS would:
- Lock tables that batch jobs need, causing batch failures
- Consume CPU and I/O resources, slowing down PO processing and inventory updates
- Return results slowly because the normalized schema requires 15+ table joins
The ETL Pipeline Architecture
RI does not operate in real-time. It is populated through a nightly ETL (Extract, Transform, Load) pipeline:
┌──────────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ SOURCE │────▶│ EXTRACT │────▶│ TRANSFORM │────▶│ LOAD │
│ SYSTEMS │ │ (BDI/ODI) │ │ (Staging) │ │ (RI Star │
│ │ │ │ │ │ │ Schema) │
│ • RMS │ │ Full Extract │ │ Cleanse │ │ Fact tables │
│ • ReSA │ │ or │ │ Conform │ │ Dimension │
│ • RPAS │ │ Delta Extract│ │ Aggregate │ │ tables │
│ • RPCS │ │ │ │ Derive KPIs │ │ │
│ • Xstore │ │ │ │ │ │ │
└──────────────┘ └──────────────┘ └──────────────┘ └──────────────┘
Extract Methods
BDI (Bulk Data Integration): Oracle's cloud-era ETL framework. BDI extracts data from source systems via REST APIs or database views, supporting both full and incremental (delta) extractions.
ODI (Oracle Data Integrator): The traditional ETL tool used in on-premise deployments. ODI uses "knowledge modules" to generate optimized SQL for extraction, transformation, and loading.
Extraction Types
- Full Extract: Complete dump of a table (e.g., entire ITEM_MASTER). Used for initial load or small reference tables.
- Delta Extract: Only rows changed since the last extraction (based on
LAST_UPDATE_DATETIMEcolumns). Used for large transactional tables to minimize processing time.
Star Schema Design
RI stores data in a star schema — a denormalized design with central fact tables surrounded by dimension tables.
Fact Tables (What Happened)
Fact tables store measurable business events with numeric values:
| Fact Table | Measures | Grain |
|---|---|---|
| W_RTL_SLS_TRX_F | Sales units, sales amount, discount amount, return units | Transaction line item |
| W_RTL_INV_IT_LC_DY_F | SOH, on-order, in-transit, receipts, sales | Item/Location/Day |
| W_RTL_MRGN_IT_LC_WK_F | Cost, retail, margin, markup, markdown | Item/Location/Week |
| W_RTL_ORD_IT_LC_F | PO units, PO cost, received units | Item/Location/PO |
Dimension Tables (Context)
Dimension tables provide the descriptive context for analyzing facts:
| Dimension | Key Attributes | Hierarchy |
|---|---|---|
| W_PRODUCT_D | Item, description, department, class, subclass, brand, UPC | Company → Dept → Class → Subclass → Item |
| W_INT_ORG_D | Store/Warehouse, name, address, district, region | Company → Region → District → Location |
| W_MCAL_PERIOD_D | Date, week, period, quarter, year | Year → Quarter → Period → Week → Day |
| W_PARTY_PER_D | Supplier, name, country, lead time | Supplier Group → Supplier → Site |
Star Schema Diagram
┌───────────────┐
│ W_PRODUCT_D │
│ (Product │
│ Dimension) │
└───────┬───────┘
│
┌───────────────┐ ┌────────▼────────┐ ┌───────────────┐
│ W_MCAL_PERIOD │──────│ W_RTL_SLS_TRX_F │──────│ W_INT_ORG_D │
│ (Calendar │ │ (Sales Fact) │ │ (Location │
│ Dimension) │ │ │ │ Dimension) │
└───────────────┘ │ • Sales Qty │ └───────────────┘
│ • Sales Amt │
│ • Discount Amt │
│ • Return Qty │
└─────────────────┘
Subject Areas & Key Metrics
RI organizes dashboards into subject areas — pre-built analytical domains:
Sales Performance
- Comp store sales (year-over-year same-store sales comparison)
- Sales per square foot
- Average transaction value (ATV)
- Units per transaction (UPT)
Inventory Management
- Weeks of supply (current inventory ÷ weekly sales rate)
- Inventory turn rate
- In-stock percentage
- Aging inventory (inventory older than 90/180/365 days)
Margin & Profitability
- Gross margin percentage by department/class
- Initial markup (IMU) vs. maintained markup (MMU)
- Markdown effectiveness (did the markdown increase unit velocity enough to offset the margin loss?)
Supplier Performance
- Fill rate (% of PO units actually received)
- On-time delivery rate
- Average lead time
- Cost variance (invoice cost vs. PO cost)
OAS / OBIEE Reporting
Oracle Analytics Server (OAS) — formerly OBIEE (Oracle Business Intelligence Enterprise Edition) — is the presentation layer that sits on top of the RI data warehouse.
Architecture Layers
- Physical Layer (RPD): Maps to the actual star schema tables in the RI database
- Business Model Layer (RPD): Defines business logic — joins, calculations, hierarchies, and derived metrics
- Presentation Layer (RPD): Defines subject areas and columns visible to business users
- Dashboard Layer: Pre-built and custom dashboards with interactive charts, tables, and filters
Pre-Built Dashboards
RI ships with dozens of pre-built dashboards:
- Executive Dashboard: High-level KPIs — total sales, gross margin, comp sales growth, inventory turns
- Merchandising Dashboard: Category performance, top/bottom performers, markdown analysis
- Store Operations Dashboard: Store-level sales, staffing efficiency, customer traffic
- Supply Chain Dashboard: Supplier scorecard, PO fill rates, receiving velocity
Retail Science AI/ML Platform
The future of Retail Insights is shifting from descriptive analytics (what happened yesterday) to predictive and prescriptive analytics (what will happen tomorrow, and what should we do about it).
Oracle is heavily integrating the Retail Science Platform into RI:
- Demand Sensing: Near-real-time demand signals that update forecasts intraday based on current sales trends, weather data, and social media sentiment
- Customer Segmentation: AI-driven clustering of customers into behavioral segments (high-value loyalists, deal-seekers, one-time buyers) for targeted marketing
- Markdown Optimization: ML models that recommend optimal markdown percentages and timing to maximize total revenue recovery during clearance
- Size Optimization: Predicting the optimal size curve per item per store based on historical sales patterns and customer demographics
- Assortment Intelligence: Recommending which items to add, drop, or substitute in the assortment based on customer preference and competitive analysis
RI vs. Operational Reporting
| Aspect | RI (Strategic Reporting) | RMS Direct (Operational Reporting) |
|---|---|---|
| Audience | VPs, Directors, Analysts | Buyers, Allocators, Planners |
| Latency | T+1 (yesterday's data) | Near real-time |
| History | 3-5 years | Current + limited history |
| Complexity | Multi-system cross-analysis | Single-system queries |
| Examples | Comp sales trends, 5-year margin analysis | "Is PO 12345 received?" |
Both are necessary. RI answers strategic questions; operational reports answer tactical ones.
Best Practices
Important Gotchas
- !RI data is T+1 (one day old). Never use RI dashboards for real-time operational decisions like "Should I approve this PO right now?" Use RMS operational reports for that.
- !Custom RI reports must use the RPD business model layer, not direct SQL against the star schema. Direct SQL bypasses security filters, aggregation rules, and calculated metrics defined in the RPD.
- !ETL failures are silent killers. If the nightly BDI extraction fails for one source (e.g., ReSA sales), the RI dashboards will show stale data without any visible warning. Monitor ETL job status daily.
- !Star schema changes during RI upgrades can break custom reports. Always regression-test custom dashboards after any RI patch or upgrade.
Key Takeaways
- RI is the enterprise data warehouse that aggregates data from RMS, ReSA, RPAS, RPCS, and Xstore into a single analytical platform.
- Data flows through an ETL pipeline (BDI/ODI) that extracts, transforms, and loads data into a denormalized star schema.
- The star schema uses fact tables (sales, inventory, margin) surrounded by dimension tables (product, location, calendar, supplier).
- OAS/OBIEE provides pre-built dashboards for executive, merchandising, store operations, and supply chain analytics.
- The Retail Science Platform adds AI/ML capabilities for demand sensing, customer segmentation, and markdown optimization.
- RI answers strategic questions (trends, patterns, performance); RMS operational reports answer tactical questions (PO status, current SOH).
