Contents

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

1 Introducing Oracle Retail Data Model

• What is Oracle Retail Data Model?
• What Are the Components of Oracle Retail Data Model
• Oracle Products That Make Up Oracle Retail Data Model

2 Logical Data Model of Oracle Retail Data Model

• Reference Entities
  • Certificate Entities
  • Competitor Entities
  • Competitor Retail Item Entities
  • Customer Entities
  • Customer Cluster Item Entities
  • Employee Entities
  • Item Entities
  • Item Cluster Customer Entities
  • Item Market Data Entities
  • Location Geography Entities
  • Media Entities
  • Organization Entities
  • Product Entities
  • Promotion Entities
  • SKU Item Entities
  • SKU Item Business Unit Selling Price Assignment Entities
  • Tendor Repository Entities
  • Time Entities
  • Time of Day Entities
  • Time Transformation Entities
  • Touchpoint Entities
  • Vendor Entities
  • Vendor SKU Business Unit Assignment
• Lookup Entities
  • Account Type
  • Activity Request Type
  • Address Type
  • Analysis Duration
  • Appointment Type
  • Authorization Method
  • Business Unit Types
  • Business Unit Usage Type
  • Card Type
  • Certificate Age Band
  • Certificate Type
  • Channel Type
  • Check In Type
  • Coating
  • Color
  • Cost Per Unit Type
  • Coupon Scan
  • Currency
  • Customer Occasion Type
  • Customer Order And Hold Event
  • Customer Pickup Type
  • Denomination
  • Discount Type
  • Disposition Type
  • Dye
  • Employee Type
  • Entry Method
  • Entry Source
  • Environment Type
  • Fabric
  • Fiber
  • Hazardous Material Type
  • Inventory Accounting Method
  • Inventory Document Type
  • Inventory Location Type
  • Inventory Type
  • Inventory State
  • Inventory Status
  • Item State
  • Issue Type
  • Language Type
  • Local Authority Type
  • Location Type
  • Manufacturer Coupon Family
  • Media Type
  • Membership Type
  • Miscellaneous Line Item Type
  • Multiple Tender Class
  • Order Category Type
  • Order Line Item State
  • Order Type
  • Order Source Type
  • Order State
  • Order Status Type
  • Pay Category
  • Pay Type
  • Personal Id Required Type
  • Preference Type
  • Price List Lookup
  • Profile Source
  • Reason
  • Reason Category
  • Retail Transaction Type Lookup
  • Retail Type
  • Return Status
  • RFMP Method
  • Request Origin Type Lookup
  • Sale Or Return Action Lookup
  • Sale Weight Or Unit Count
  • Security Required Type
  • Selling Location Type
  • Selling Status
  • Shipment Method
  • Shipment Priority
  • Size
  • Size Type
  • SKU Item Style
  • SKU Item Type
  • Status Reason
  • Status Type
  • Stock Item Type
  • Store Financial Ledger Account
  • Tax Authority
  • Tax Exemptions
  • Taxable Group
  • Tender Class
  • Tender Type
  • Tender Repository Class
  • Term Code
  • Theft Type
  • Transaction Type
  • Transfer Type
  • Unit Of Measure
  • UOM Conversion
  • Value Type
  • Variety Type
  • Vendor Class
  • Vendor Rating Type
  • Work Hour Type
• Base Entities
  • Certificate Escheated Day
  • Customer Order
  • Customer Order Line Item
  • Customer Order Line Item State Assignment
  • Customer Order State
  • Customer Service Request
  • Employee Labor
  • Exchange Rate Currency Day
  • Inventory Control Document
  • Inventory Control Document Line Item
  • Inventory Item State
  • Market Sales Item Week
  • Purchase Order
  • Purchase Order Line Item
  • Purchase Order Line Item State
  • Purchase Order State
  • Retail Sale Return Line Item
  • Retail Sale Return Promotion Line Item
  • Retail Tender History
  • Retail Tender Line Item
  • Retail Transaction
  • Retail Transaction Associate Assignment
  • Retail Transaction Miscellaneous Line Item
  • Retail Transaction Line Item
  • Sales Forecast Item Organization Hierarchy Week
  • Sales Plan Item Organization Hierarchy Week
  • Tender Change Line Item
  • Till History
  • Till Tax History
  • Till Tender History
  • Vendor SKU Cost Profit Day
• Derived Entities
  • Certificate Activity Transaction Derived
  • Customer Employee Relationship Day
  • Customer Order Item Day Derived
  • Customer Order Line Item State Derived
  • Customer RFMP Score
  • Customer SKU Sale Return Day Derived
  • Catalog Request by Day Derived
  • Inventory Adjustment by Item Day Derived
  • Inventory Unavailable Item Day
  • Inventory Position by Item Day Derived
  • POS Tender Flow
  • POS Transaction Flow
  • Retail Sale Return Item Day Derived
  • Space Utilization Item Day Derived
  • Till Derived
• Aggregate Entities
  • Carrier Compliance Week Aggr
  • Certificate Activity Day Aggr
  • Certificate Activity Week Aggr
  • Customer Employee Relationship Month Aggr
  • Customer Employee Sale Return Week Aggr
  • Customer Order Department Day Aggr
  • Customer Order Department Month Aggr
  • Customer Order Item Month Aggr
  • Customer Order Item Week Aggr
  • Customer Order Subclass Day Aggr
  • Customer Order Subclass Month Aggr
  • Customer Order Subclass Week Aggr
  • Inventory Budget By Week Aggr
  • Inventory Item State History Week
  • Inventory Position By Department Day Aggr
  • Inventory Position By Department Week Aggr
  • Inventory Position By Item Week Aggr
  • Inventory Position Subclass Day Aggr
  • Inventory Position Subclass Week Aggr
  • Inventory Receipt By Item Day Aggr
  • Inventory Receipt By Item Week Aggr
  • Inventory Receipt By Subclass Day Aggr
  • Inventory Receipt By Subclass Week Aggr
  • Inventory Transfer By Item Day Aggr
  • Inventory Transfer By Item Week Aggr
  • Inventory Transfer By Subclass Day Aggr
  • Inventory Transfer By Subclass Week Aggr
  • Inventory Vendor Compliance Aggr
  • Market Sales Department Week Aggr
  • Promotion Cost Contribution Week Aggr
  • Promotion Sales Margin Week Aggr
  • Retail Markdown Department Day Aggr
  • Retail Markdown Department Week Aggr
  • Retail Markdown Item Day Aggr
  • Retail Markdown Item Week Aggr
  • Retail Sale Return Organization Hierarchy Day Aggr
  • Retail Sale Return Department Day Aggr
  • Retail Sale Return Department Week Aggr
  • Retail Sale Return Department Month Aggr
  • Retail Sale Return Item Week Aggr
  • Retail Sale Return Subclass Day Aggr
  • Retail Sale Return Subclass Month Aggr
  • Retail Sale Return Subclass Week Aggr
  • Retail Transaction Emp Workstation Aggr
  • Space Utilization Department Day Aggr
  • Stock Ledger By Subclass Month Aggr
  • Stock Ledger By Subclass Week Aggr
  • Till History Workstation Aggr
  • Till Tender History Employee Aggr
  • Vendor Availability Item Day Aggr
  • Vendor Compliance Item Week
  • Vendor Compliance Week Aggr
  • Vendor Contract Item Day Aggr

3 Physical Data Model of Oracle Retail Data Model

• Introduction to the Oracle Retail Data Model Physical Model
• Reference Tables
• Lookup Tables
• Database Sequences
• Base Tables
• Derived Tables
• Aggregate Tables and Relational Materialized Views
• Physical Data Model of the Data Mining Component
• Physical Data Model of the OLAP Component
  • Analytic Workspaces Used by the OLAP Component
  • OLAP Data Model in Oracle Retail Data Model
    • OLAP Dimensions in Oracle Retail Data Model
    • OLAP Cubes and Measures in Oracle Retail Data Model
  • Relational Views Used for the OLAP Component
    • Relational Views Used When Loading the Analytic Workspace
    • Relational Views of the OLAP Cubes Used for SQL Query and Reporting

4 Logical to Physical Mappings in the Oracle Retail Data Model

• Entity Mapping Table

5 ETL for the Oracle Retail Data Model

• Introduction to Oracle Retail Data Model ETL
• PKG_INTRA_ETL_PROCESS
• Intra-ETL Packages for Populating Derived Tables
  • PKG_DWD_SPACE_UTLZTN_ITEM_DAY.sql
  • PKG_DWD_CUST_EMP_RLTNSHP_DAY.sql
  • PKG_DWD_CUST_SKU_SL_RETRN_DAY.sql
  • PKG_DWD_INV_UNAVL_BY_ITEM_DAY.sql
  • PKG_DWD_INV_ADJ_BY_ITEM_DAY.sql
  • PKG_DWD_INV_POSN_BY_ITEM_DAY.sql
  • PKG_DWD_CUST_ORDR_LI_STATE.sql
  • PKG_DWD_CERTIFICATE_ACTVTY_TRX.sql
  • PKG_DWD_POS_CNTRL.sql
  • PKG_DWD_CTLG_RQST_BY_DAY.sql
  • PKG_DWD_POS_RTL.sql
  • PKG_DWD_RTV_ITEM_DAY.sql
  • PKG_DWD_CUST_ORDR_ITEM_DAY.sql
  • PKG_DWD_POS_STORE_FINCL.sql
  • PKG_DWD_RTL_SL_RETRN_ITEM_DAY.sql
  • PKG_DWD_POS_TNDR_FLOW.sql
  • PKG_INTRA_ETL_PROCESS.sql
  • PKG_INTRA_ETL_UTIL.sql
• Intra-ETL Scripts for Populating Aggregate Tables and Relational Materialized Views
  • DWA_CUST_EMP_SL_RETRN_WK_MV.sql
  • DWA_CUST_ORDR_ITEM_WK_MV.sql
  • DWA_CUST_ORDR_SBC_DAY_MV.sql
  • DWA_INV_RCPT_BY_ITEM_WK_MV.sql
  • DWA_INV_RCPT_BY_SBC_DAY_MV.sql
  • DWA_INV_TRNSFR_BY_ITEM_WK_MV.sql
  • DWA_INV_TRNSFR_BY_SBC_DAY_MV.sql
  • DWA_RTL_MRKDN_ITEM_DAY_MV.sql
  • DWA_RTL_SL_RETRN_ITEM_WK_MV.sql
  • DWA_RTL_SL_RETRN_SBC_DAY_MV.sql
  • DWA_RTL_TRX_EMP_WRKSTN_MV.sql
  • DWA_SPACE_UTLZTN_DEPT_DAY_MV.sql
  • DWA_TILL_HIST_WRKSTN_MV.sql
  • DWA_TILL_TNDR_HIST_EMP_MV.sql
  • DWA_INV_POSN_BY_ITEM_WK_MV.sql
  • DWA_INV_POSN_BY_SBC_DAY_MV.sql
  • DWA_CERTIFICATE_ACTVTY_DAY_MV.sql
  • DWA_CARRIER_CMPLNC_WK_MV.sql
  • DWA_CUST_EMP_RLTNSHP_MO_MV.sql
  • DWA_INV_ITEM_STATE_HIST_WK_MV.sql
  • DWA_INV_RCPT_BY_ITEM_DAY_MV.sql
  • DWA_INV_TRNSFR_BY_ITEM_DAY_MV.sql
  • DWA_CUST_EMP_SL_RETRN_MO_MV.sql
  • DWA_CUST_ORDR_DEPT_DAY_MV.sql
  • DWA_CUST_ORDR_ITEM_MO_MV.sql
  • DWA_CUST_ORDR_SBC_WK_MV.sql
  • DWA_INV_RCPT_BY_SBC_WK_MV.sql
  • DWA_INV_TRNSFR_BY_SBC_WK_MV.sql
  • DWA_RTL_MRKDN_DEPT_DAY_MV.sql
  • DWA_RTL_MRKDN_ITEM_WK_MV.sql
  • DWA_RTL_SL_RETRN_DEPT_DAY_MV.sql
  • DWA_RTL_SL_RETRN_ITEM_MO_MV.sql
  • DWA_RTL_SL_RETRN_SBC_WK_MV.sql
  • DWA_INV_POSN_BY_SBC_WK_MV.sql
  • DWA_INV_POSN_BY_DEPT_DAY_MV.sql
  • DWA_RTL_SL_RT_ORG_HRCHY_DAY_MV.sql
  • DWA_RTL_MRKDN_DEPT_WK_MV.sql
  • DWA_RTL_SL_RETRN_DEPT_WK_MV.sql
  • DWA_RTL_SL_RETRN_SBC_MO_MV.sql
  • DWA_CUST_ORDR_SBC_MO_MV.sql
  • DWA_INV_POSN_BY_DEPT_WK_MV.sql
  • DWA_CERTIFICATE_ACTVTY_WK_MV.sql
  • DWA_CUST_ORDR_DEPT_MO_MV.sql
  • DWA_MKT_SLS_DEPT_WK_MV.sql
  • DWA_INV_VNDR_CMPLNC_MV.sql
  • DWA_VNDR_CMPLNC_ITEM_WK_MV.sql
  • DWA_VNDR_CMPLNC_WK_MV.sql
• Data Mining Component ETL
  • Data Mining ETL Packages
  • Model Build Procedures
    • Model Build Procedures for Each Type of Model
    • Model Build Procedure Parameters
    • Model Build Procedure Output
• OLAP Component ETL
  • When is the OLAP Component Populated?
  • OLAP Component Installation Scripts
  • OLAP_ETL_AW_LOAD Package
    • Summary of the OLAP_ETL_AW_LOAD Subprograms
    • OLAP_ETL_AW_BUILD
    • OLAP_ETL_AW_CUBES
    • OLAP_ETL_AW_DIMBUILD
    • OLAP_ETL_AW_CUBEBUILD
    • OLAP_ETL_AW_REPL_DEFN
    • OLAP_ETL_AW_RESET_VIEWS

6 Data Mining Models in Oracle Retail Data Model

• About Data Mining in Oracle Retail Data Model
• Associate Basket Analysis Model
  • Examples of Desired Rules for the Associate Basket Analysis Model Report
  • What the Discovered Rules for the Associate Basket Analysis Model Report Explain
  • What the Associate Basket Analysis Model Mines
  • Target Variables for the Associate Basket Analysis Model
  • Source Variables for the Associate Basket Analysis Model
  • Columns Included in the Target Views of the Associate Basket Analysis Model Report
• Associate Loss Analysis Model
  • Examples of Desired Rules for the Associate Loss Analysis Model Report
  • What the Discovered Rules of the Associate Loss Analysis Model Explain
  • What the Associate Loss Analysis Model Mines
  • Target Variables for the Associate Loss Analysis Model
  • Source Variables for the Associate Loss Analysis Model
  • Columns Included in the Target Views of the Associate Loss Analysis Model Report
• Associate Sales Analysis Model
  • Examples of Desired Rules for the Associate Sales Analysis Model
  • What the Discovered Rules of the Associate Sales Analysis Model Explain
  • What the Associate Sales Analysis Model Mines
  • Target Variables for the Associate Sales Analysis Model
  • Source Variables for the Associate Sales Analysis Model
  • Columns Included in the Target Views of the Associate Sales Analysis Model Report
• Customer Category Mix Analysis Model
  • Examples of Desired Rules for the Customer Category Mix Analysis Model
  • What the Discovered Rules for the Customer Category Mix Analysis Model Explain
  • What the Customer Category Mix Analysis Model Mines
  • Target Variables for the Customer Category Mix Analysis Model
  • Source Variables for the Customer Category Mix Analysis Model
  • Columns Included in the Target Views for the Customer Category Mix Analysis Model Report
• Customer Loyalty Analysis Model
  • Examples of Desired Rules for the Customer Loyalty Analysis Model
  • What the Discovered Rules for the Customer Loyalty Analysis Model Explain
  • What the Customer Loyalty Analysis Model Mines
  • Target Variable for the Customer Loyalty Analysis Model
  • Source Variables for the Customer Loyalty Analysis Model
  • Loyalty Categories for the Customer Loyalty Analysis Model
  • Columns Included in the Target Views for the Customer Loyalty Analysis Model Report
• Frequent Shopper Category Mix Analysis Model
  • Examples of Desired Rules for the Frequent Shopper Category Mix Analysis Model
  • What the Discovered Rules for the Frequent Shopper Category Mix Analysis Model Explain
  • What the Frequent Shopper Category Mix Analysis Model Mines
  • Target Variable for the Frequent Shopper Category Mix Analysis Model
  • Source Variables for the Frequent Shopper Category Mix Analysis Model
  • Columns Included in the Target Views of the Frequent Shopper Category Mix Analysis Model Report
• Item Basket Analysis Model
  • Examples of Desired Rules for the Item Basket Analysis Model
  • What the Discovered Rules for the Item Basket Analysis Model Explain
  • What the Item Basket Analysis Model Mines
  • Target Variable for the Item Basket Analysis Model
  • Source Variables for the Item Basket Analysis Model
  • Columns Included in the Target Views for the Item Basket Analysis Model Report
• Item POS Loss Analysis Model
  • Examples of Desired Rules for the Item POS Loss Analysis Model
  • What the Discovered Rules for the Item POS Loss Analysis Model Explain
  • What the Item POS Loss Analysis Model Mines
  • Target Variables for the Item POS Loss Analysis Model
  • Source Variables for the Item POS Loss Analysis Model
  • Columns Included in the Target Views of the Item POS Loss Analysis Model Report
• POS Flow Analysis Model
  • Examples of Desired Rules for the POS Flow Analysis Model
  • What the Discovered Rules for the POS Flow Analysis Model Explain
  • What the POS Flow Analysis Model Mines
  • Target Variables for the POS Flow Analysis Model
  • Source Variables for the POS Flow Analysis Model
  • Columns Included in the Target Views of the POS Flow Analysis Model Report
• Store Loss Analysis Model
  • Examples of Desired Rules for the Store Loss Analysis Model
  • What the Discovered Rules for the Store Loss Analysis ModelExplain
  • What the Store Loss Analysis Model Mines
  • Target Variables for the Store Loss Analysis Model
  • Source Variables for the Store Loss Analysis Model
  • Columns Included in the Target Views for the Store Loss Analysis Model Report

Index