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?
• New Features in Oracle Retail Data Model
• What Are the Components of Oracle Retail Data Model
• Oracle Products That Make Up Oracle Retail Data Model

Part I Logical and Physical Data Model

2 Logical Data Model of Oracle Retail Data Model

• Overview of Retail Data Model Entity Types
  • Reference Entities
  • Lookup Entities
  • Base Entities
  • Derived Entities
  • Aggregate Entities
• Logical Data Model Business Areas and Subject Areas
• Logical Data Model Subject Area Entity Lists
  • Account
  • Address Location
  • Business Unit
  • Calendar
  • Call Center
  • Call Center Agent
  • Catalog Menu
  • Certificate Voucher
  • Channels
  • Clickstream
  • Competitor
  • Contract
  • Control Transaction
  • Cost
  • Customer
  • Customer Contract
  • Customer Order
  • Customer Service Request
  • Deal
  • Event
  • Food Service
  • Fueling Business Service
  • Financial Ledger
  • Geography
  • Inventory
  • Inventory Control
  • Invoice
  • Item
  • Loyalty
  • Manufacturer Organization
  • Manufacturer SKU Item
  • Order Fulfillment
  • Organization Business Unit
  • Party
  • Party Interaction
  • Planogram
  • Point of Service (POS)
  • Promotion / Campaign
  • SKU Item Recipe
  • Retail Sales
  • Retail Transaction
  • Revenue Center
  • Sales Audit
  • SKU Item
  • Subscription
  • Supply Chain
  • Tender Control
  • Tender Repository
  • Time
  • Touchpoint
  • Vendor
  • Vendor Item
  • Workforce Management
  • Workstation and Till
• Logical Data Model Entity Dictionary

3 Physical Data Model of Oracle Retail Data Model

• Introduction to the Oracle Retail Data Model Physical Model
• Reference Tables
• Lookup Tables
• Base Tables
• Derived Tables
• Aggregate Tables
• Control and Configuration Tables
• Metadata Tables and Views
• Database Sequences
• Oracle Retail Data Model OLAP Cube MVs, OLAP Views
• Oracle Retail Data Model Relational MVs
• Oracle Retail Data Model Relational Views
• Oracle Retail Data Model Data Mining MVs

4 Logical to Physical Mappings in the Oracle Retail Data Model

• Entity Mapping Table

5 Oracle Retail Data Model Partitioning

• Partitioning Strategy for Oracle Retail Data Model

Part II Intra-ETL, OLAP, Data Mining, and Utility Scripts

6 ETL for the Oracle Retail Data Model

• Introduction to Oracle Retail Data Model ETL
• Intra-ETL Packages for Populating Derived Tables
  • PKG_DWD_ACTVTY_RQST_DAY
  • PKG_DWD_CARRIER_CMPLNC_DAY
  • PKG_DWD_COST_DAY
  • PKG_DWD_CRTFCT_ACTVTY_TRX
  • PKG_DWD_CUST_EMP_RLTNSHP_DAY
  • PKG_DWD_CUST_ORDR_LI_STATE
  • PKG_DWD_CUST_RFMP_SCR
  • PKG_DWD_CUST_SKU_SL_RETRN_DAY
  • PKG_DWD_CUST_TYP_ORDR_ITEM_DAY
  • PKG_DWD_EMP_LBR
  • PKG_DWD_EMP_WG_PYMNT_DAY
  • PKG_DWD_INV_ADJ_ITEM_DAY
  • PKG_DWD_INV_POSN_ITEM_DAY
  • PKG_DWD_INV_RCPT_ITEM_DAY
  • PKG_DWD_INV_UNAVL_ITEM_DAY
  • PKG_DWD_INV_XFER_ITEM_DAY
  • PKG_DWD_ORG_BSNS_UNT_TRFC_DAY
  • PKG_DWD_POS_CNTRL
  • PKG_DWD_POS_RTL
  • PKG_DWD_POS_STORE_FINCL
  • PKG_DWD_POS_TNDR_FLOW
  • PKG_DWD_RTL_SL_RETRN_ITEM_DAY
  • PKG_DWD_RTL_TRX_EMP_WRKSTN_DAY
  • PKG_DWD_RTV_ITEM_DAY
  • PKG_DWD_SPACE_UTILIZATION_ITEM_DAY
  • PKG_DWD_VENDOR_CMPLNC_ITEM_DAY
  • PKG_INTRA_ETL_PROCESS
  • PKG_INTRA_ETL_UTIL
• Intra-ETL for Populating Aggregate Tables and Relational Materialized Views
  • DWA_ACCT_PAYBL_MO
  • DWA_ACCT_RCVBL_MO
  • DWA_ACTVTY_RQST_MO
  • DWA_ASSTS_MO
  • DWA_CARRIER_CMPLNC_WK
  • DWA_COST_MO
  • DWA_CRTFCT_ACTVTY_DAY
  • DWA_CUST_EMP_RLTNSHP_MO
  • DWA_CUST_EMP_SL_RETRN_MO
  • DWA_CUST_TYP_ORDR_DEPT_MO
  • DWA_CUST_TYP_ORDR_SBC_WK
  • DWA_INV_POSN_DEPT_DAY
  • DWA_INV_POSN_SBC_MO
  • DWA_INV_RCPT_SBC_WK
  • DWA_LIAB_MO
  • DWA_PCHSE_ORDR_DEPT_MO
  • DWA_PCHSE_ORDR_LI_DAY
  • DWA_PCHSE_ORDR_LI_MO
  • DWA_PCHSE_ORDR_SBC_DAY
  • DWA_POS_RTL_EMP_MO
  • DWA_RTL_SL_RETRN_DEPT_DAY
  • DWA_RTL_SL_RETRN_SBC_MO
  • DWA_RTV_DEPT_DAY
  • DWA_RTV_SBC_MO
  • DWA_SPACE_UTLZTN_DEPT_DAY
• Intra-ETL to Load OLAP Analytical Workspace
• Intra-ETL to Load/Rebuild Mining Models
• Intra-ETL Process Flows
  • Details of the ORDM_DERIVED_FLW
  • Details of the ORDM_AGG_N_DEP_FLW
  • Details of the ORDM_AGG_DEP_FLW
  • Details of the OLAP_MAP Mapping
  • Details of the ORDM_MNNG_FLW
• Executing the Intra-ETL

7 Oracle Retail Data Model OLAP ETL

• Oracle Retail Data Model OLAP Source Objects
  • General Process to Populate the OLAP Analytical Workspace in Oracle Retail Data Model
  • SQL Access to Analytical Workspace including Query Rewrite to Cube Organized Materialized Views
• OLAP Component ETL
  • OLAP Component Installation Process
  • OLAP Component Load Scripts
    • OLAP Component Initial Load Script
    • OLAP Component Incremental Load Script
  • When is the OLAP Analytical Workspace Populated?
    • Populating During the Initial Load of the OLAP Cube Data
    • Populating on a Continuous or Scheduled Basis to Update the OLAP Cube Data
  • OLAP_ETL Package: PKG_ORDM_OLAP_ETL_AW_LOAD
    • Summary of the PKG_OLAP_ETL_AW_LOAD Subprograms
    • OLAP_ETL_AW_BUILD
    • OLAP_ETL_NF_CUBE_BUILD
    • OLAP_ETL_FCST_BUILD

8 Oracle Retail Data Model OLAP Dimensions

• OLAP Dimensions Overview
• Activity Request Type: ACTRQSTTYP
• Assets Type: ASSTTYPE
• Business Unit Shift: BUSHIFT
• Carrier: CARRIER
• Campaign Media: CMPGNMEDIA
• Customer: CUSTOMER
• Employee: EMPLOYEE
• Environment Type: ENVTYPE
• Interaction Reason: INTRACNRSN
• Interaction Status: INTRACNSTAT
• Interaction Type: INTRACNTYP
• Inventory Location: INVLOC
• Liability Type: LIABTYP
• Order Type: ORDRTYP
• Organization: ORGANIZATION
• Organization QR Dimension: ORGQR
• Pay Type: PAYTYPE
• Post Code: POSTCD
• Product QR Dimension: PRODQR
• Product: PRODUCT
• Reason: REASON
• RFMP: RFMP
• Request Origin: RQSTORIGIN
• Time: TIME
• Time QR Dimension: TIMEQR
• Touchpoint: TOUCHPOINT
• UOM: UOM
• Vendor: VENDOR
• Vendor Item: VNDRITEM
• Vendor Site: VNDRSITE

9 Oracle Retail Data Model OLAP Cubes

• Oracle Retail Data Model OLAP Cubes Summary
• Oracle Retail Data Model OLAP Cubes
  • Activity Request Cube: AR
  • Asset Cube: ASSET
  • Carrier Compliance Cube: CC
  • Customer Order Cube: CO
  • Customer RFMP Cube: CRFMP
  • Customer RFMP DC Cube: CRFMPDC
  • Customer SKU Sale Return Cube: CSSR
  • Employee Labor Cube: EL
  • Employee Wage Payment Cube: EWGP
  • Inventory Adjustment Cube: IA
  • Inventory Cube: INV
  • Inventory Forecast Cube: INV_FCST
  • Inventory Forecast Statistic Cube: INV_FCST_STTSTC
  • Inventory Receipt Cube: IR
  • Inventory Unavailable Cube: IU
  • Liability Cube: LIABILITY
  • Store Hours Cube: OBUH
  • Store Traffic Cube: OBUT
  • Purchase Order Line Item State Cube: POLIS
  • Purchase Order State Cube: POS
  • Retail Transaction Employee Workstation Cube: RTEW
  • Sales Plan Item Organization Hierarchy Cube: SLPLN
  • Sales Cube: SLS
  • Sales Cube - Cube based QR enabled: SLSQR
  • Sales Cube Forecast: SLS_FCST
  • Sales Cube Forecast Statistic: SLS_FCST_STTSTC
  • Space Utilization Cube: SU
  • Vendor Compliance Cube: VC

10 Data Mining Models in Oracle Retail Data Model

• About Data Mining in Oracle Retail Data Model
  • Mining Model Overview
  • Steps to Build Mining Models
  • Using the Mining Model Refresh Procedure
  • Data Mining Target Tables
• Oracle Retail Data Model Data Mining Models
  • Model 1: Employee Basket Analysis
    • Employee Basket Analysis Target Variables
    • Employee Basket Analysis Source Table
    • Employee Basket Analysis Target Tables
    • Employee Basket Analysis Example of Desired DT Rules
    • Employee Basket Analysis Attribute Ranking with SVM Factors
    • Employee Basket Analysis Employee KPIs Prediction using DT
    • Employee Basket Analysis Employee KPIs Prediction using SVM
  • Model 2: Employee Sales Analysis
    • Employee Sales Analysis Target Variables
    • Employee Sales Analysis Source Table
    • Employee Sales Analysis Target Tables
    • Employee Sales Analysis Example of Desired DT Rules
    • Employee Sales Analysis Attribute Ranking with SVM Factors
    • Employee Sales Analysis Employee KPIs Prediction using DT
    • Employee Sales Analysis Employee KPIs Prediction using SVM
  • Model 3: Customer Loyalty Analysis
    • Customer Loyalty Analysis Target Variable
    • Customer Loyalty Analysis Source Table
    • Customer Loyalty Analysis Target Tables
    • Customer Loyalty Analysis Example of Desired DT Rules
    • Customer Loyalty Analysis Attribute Ranking with SVM Factors
    • Customer Loyalty Analysis Customer Loyalty Prediction (By SVM & DT)
  • Model 4: Store Loss Analysis
    • Store Loss Analysis Source Table
    • Store Loss Analysis Target Tables
    • Store Loss Analysis Examples of Desired Rules
    • Store Loss Analysis Attribute Ranking with SVM Factor
    • Store Loss Analysis Store KPIs Prediction using DT
    • Store Loss Analysis Store KPIs Prediction Using SVM
  • Model 5: Item POS Loss Analysis
    • Item POS Loss Analysis Target Variables
    • Item POS Loss Analysis Source Table
    • Item POS Loss Analysis Target Tables
    • Item POS Loss Analysis Examples of Desired Rules
    • Item POS Loss Analysis Attribute Ranking with SVM Factor
    • Item POS Loss Analysis Item KPIs Prediction using DT
    • Item POS Loss Analysis Item KPIs Prediction using SVM
  • Model 6: Product Category Mix Analysis
    • Product Category Mix Analysis Source Table
    • Product Category Mix Analysis Target Table
    • Product Category Mix Analysis Examples of Desired Rules
  • Model 7: Product Price Elasticity Analysis
    • Product Price Elasticity Analysis Source Table
    • Product Price Elasticity Analysis Target Table
    • Product Price Elasticity Analysis Attribute Ranking with SVM Factor
  • Model 8: Employee Combination Analysis
    • Employee Combination Analysis Target Variables
    • Employee Combination Analysis Source Table
    • Employee Combination Analysis Target Tables
    • Employee Combination Analysis Examples of Desired DT Rules
    • Employee Combination Analysis Attribute Ranking with SVM Factor
    • Employee Combination Analysis Employee Combination Performance Prediction (By DT & SVM)
  • Model 9: Customer Segmentation Analysis
    • Customer Segmentation Analysis Source Table
    • Customer Segmentation Analysis Target Table
    • Customer Segmentation Analysis Examples of desired Segment profile
  • Model 10: Customer Life Time Value Analysis
    • Customer Life Time Value Analysis Target Variables
    • Customer Life Time Value Analysis Source Table
    • Customer Life Time Value Analysis Target Tables
    • Customer Life Time Value Analysis Examples of Desired DT Rules
    • Customer Life Time Value Analysis Attribute Ranking with SVM Factor
    • Customer LTV Code Prediction (Using DT) - Classification
    • Customer LTV Prediction (Using SVM)- Regression
    • Customer LT Survival Value Code Prediction (Using) - Classification
    • Customer LT Survival Value Prediction (Using SVM)- Regression
  • Model 11: Customer Churn Analysis
    • Customer Churn Analysis Source Table
    • Customer Churn Analysis Target Tables
    • Customer Churn Analysis Examples of Desired DT Rules
    • Customer Churn Analysis Attribute Ranking with SVM Factor
    • Customer Churn Prediction (By DT & SVM)
  • Model 12: Customer Sentiment Analysis
    • Customer Sentiment Analysis Target Variable
    • Customer Sentiment Analysis Source Table
    • Customer Sentiment Analysis Target Table

11 Oracle Retail Data Model Utility Scripts

• Calendar Population
  • Calendar Population Scripts
  • How to Populate Calendar Data

12 Oracle Retail Data Model Sample Reports

• Customer Reports
  • Demography
  • Frequent Shopper
    • Frequent Shopper Penetration
    • Frequent Shopper Sales Analysis
    • Frequent Shopper Transactions
  • RFMP and Cluster
    • RFM Scoring
    • RFMP Trending
    • Cluster Analysis
    • Customer Frequency Defection Analysis
• Promotion Reports
  • Actual and Plan
    • Promotion Planning
    • Promotion Comparison
    • Promotional Forecast Planning
  • Performance
• Category Reports
  • Contribution
    • Contribution Scorecard
    • Item Profit on Cost
    • Profit on Net Cost
    • Cost Trend
    • Class Item Level Profit
  • Performance
    • Bottom Performance by Sales
    • Bottom Performance % Profit
    • Top Performance
    • Comp Sales & Inv
  • Pricing
    • Organization Pricing
    • Price Analysis
  • Product Analysis
    • Sales by Banner
    • Listing (Bottom)
  • Sales Analysis
    • Cross Sell Analysis
    • Vendor Sales by Channel
  • Sales and Profit
    • Gross Profit
    • Sales Profit Return by Channel (with store for local values)
    • Sales Profit & Return by Channel
    • Sales Profit by Product Cluster
    • Product Cluster Sales & Profit
    • Sales & Profit by Customer Cluster
    • Sales Profit & Return by Location Trait
  • Product Price Elasticity
    • Sales Prediction - Batch Mode
    • Dynamic Sales Prediction
• Merchandise Reports
  • Flow Analysis
    • Fast Moving Item
  • Margin Contribution
    • Sales Analysis
    • Contribution to Chain
    • Organization Sales and Profit
    • Sales and Stock
    • Sales, Profit & Return Store
    • Sales, Return and Profit Channel
  • Performance
    • Contract Detail by Item
    • Vendor
    • Top Performance
    • Vendor Product Summary
    • Commitment Summary
    • Monthly Performance
    • Pack Compared to Standalone Sales
  • Sales Analysis
    • Comp Sales
    • Product Sales & Return
    • Item Profit
    • Net Cost, Net Profit
    • Department Sales
    • Vendor Sales
  • Scorecard
    • Sales Scorecard
    • Store Sales Scorecard
    • Vendor Scorecard
  • Stock Movement
    • Receipts
    • Stock Ledger

13 Oracle Retail Data Model Sample Reports Continued

• Workforce Reports
  • Performance
    • Salesperson Performance
    • Salesperson Ranking
    • Salesperson Profit
    • Cashier
  • Ranking
    • Cashier Ranking
    • Salesperson Ranking
  • Transactions
    • Employees Sales and Returns
    • Cashier Transaction Summary
• Point of Service Reports
  • Conversation
    • Sales Conversion
    • Sales Comparison
    • Store Traffic Day
    • Store Traffic Week
  • Flow Analysis
    • Store Traffic
    • Transaction Profile
  • Scorecard
    • Sales Productivity
    • Location Sales Productivity
    • Selling Location Productivity
  • Till Analysis
  • Transactions
    • Transaction by Associates
    • Transaction Profile
• Loss Prevention Reports
  • Defection
    • Monetary
    • Profitability
  • Shrink and Theft
    • Department Trending
    • Transaction by Tender
  • Statistical Outliers
    • Statistical Emp Outlier
    • Statistical Store Outlier
  • Store Credits
    • Store Credits
• Inventory Reports
  • Accounting
    • Stock Ledger Gross Margin
    • Adjustment Summary
    • Inventory Adjustment
    • Availability Analysis
  • Compliance
  • Merchandise
  • Quality Assurance
  • Stock Movement
    • Inventory & Receipts
    • Ending On Hand (EOH) Value
• Order Management Reports
• Store Operation Reports
  • Comp Store
    • Sales Analysis
    • Sales Comparison
  • Store Performance
    • Contribution
    • Store Performance
    • Ranking
    • Scorecard
  • Over/Short
    • Employee Over/Short
    • Store Over/Short
  • Traffic
    • Store Traffic Day
    • Store Traffic Week
  • Transaction
    • Transaction

Part III Appendices

A Control Tables

• Intra-ETL Load Parameters Control Table
• Intra-ETL OLAP Mapping Control Table
• Intra-ETL Monitoring Process Control Tables

Index