1 Introducing Oracle Retail Data Model

This chapter introduces the Oracle Retail Data Model, which is a start-up kit for implementing a retail data warehouse solution:

What is Oracle Retail Data Model?

Oracle Retail Data Model is a startup kit for implementing a retail business intelligence solution. It is a standards-based data model, designed and pre-tuned for Oracle data warehouses, including the HP Oracle Database Machine.

The Oracle Retail Data Model for Retail offers a single-vendor solution package that is tightly integrated with the business intelligence platform. With pre-built data mining, On-line Analytical Processing (OLAP) and dimensional models, Oracle Retail Data Model provides you with industry-specific metrics and insights that you can act on immediately to improve your bottom line. These BI solution offerings take advantage of Oracle's scalability and reliability, using Oracle's familiar optimization, parallelism, and performance engineering within the database.

Oracle Retail Data Model can be used in any application environment and is easily extendable.

By leveraging Oracle's strong retail domain expertise, Oracle Retail Data Model provides an industry standard compliant foundation schema that is modern, relevant, topical, and addresses needs of most retail segments. This normalized foundation schema serves as a detailed and structured representation of the retail business, providing an integrated base for business information with fully defined entities and relationships. Oracle Retail Data Model includes an exhaustive set of embedded advanced analytics, using Oracle's OLAP and data mining technology. You can take advantage of pre-built and pre-tested solution sets designed by industry experts that deliver relevant insights, are actionable, and aimed at improving both top-line and bottom-line results. You can see summarized, aggregated information or quickly navigate to drill-down transaction details to better understand business issues. For example, with Oracle Retail Data Model's out-of-the-box sample reports, merchandisers gain improved insight into product affinities; loss prevention specialists gain improved visibility; and marketing analysts gain improved understanding of promotional effectiveness and customer segmentation. You can add your own reports as well. Oracle Retail Data Model, combined with Oracle technology, provides all of the components required for a complete and extendable Retail Data Warehouse and Business Intelligence framework in order to eliminate complex and costly integration requirements, all designed to reduce your total cost of ownership.

Oracle Retail Data Model is a pre-built, pre-tested solution designed by industry experts to help retailers maximize the value of their Oracle data warehouse. Using sophisticated trending and data mining capabilities based on Oracle's OLAP and data mining technology, retailers - including grocery stores, department stores, specialty store chains, mass merchants, convenience stores, and multi-channel retailers - now have the data analysis capabilities to develop retail-specific insights that are relevant, actionable, and can improve both top-line and bottom-line results.

With Oracle Retail Data Model, you can jump-start the design and implementation of a retail data warehouse to quickly achieve a positive ROI for your data warehousing and business intelligence project with a predictable implementation effort

Scope of Retail Organizations That Oracle Retail Data Model Supports

Oracle Retail Data Model provides a broad base for supporting retail operations. It is geared especially for general merchandise and grocery, but it can also support other types of retail, such as hard lines, soft lines, and multi-channel retailers.

Oracle Retail Data Model provides a data architecture and data model along with reports and key performance indicators to support multiple business areas in retail organizations:

  • Store Operations

  • Point-of-Sale (POS)

  • Loss Prevention

  • Merchandising

  • Inventory

  • Workforce Management

  • Order Management

  • Customer

  • Category Management

  • Promotion

Oracle Products That Make Up Oracle Retail Data Model

Several Oracle technologies are involved in building the infrastructure for retail business intelligence.

Oracle Database with OLAP, Data Mining and Partitioning Option

Oracle Retail Data Model utilizes a complete Oracle technical stack. It leverages the following data warehousing features of the Oracle database: SQL model, compression, partitioning, advanced statistical functions, materialized views, data mining, and online analytical processing (OLAP).


To save some money, you can consider using RAC and commodity hardware.

Oracle Development Tools

The following Oracle tools can be used to customize the predefined logical and physical models provided with ORDM, or to populate the target relational tables,mmaterialized views, or OLAP cubes.

Table 1-1 Oracle Development Tools Used with Oracle Retail Data Model

Name Use


To create the logical model

SQL Developer or SQL*Plus

To create or modify database objects

Oracle Warehouse Builder

For the process control of the intra ETL process

Analytic Workspace Manager

To populate the target OLAP cubes

Oracle BI EE Presentation Tools

Oracle Business Intelligence Suite Enterprise Edition (Oracle BI EE) is a comprehensive suite of enterprise BI products that delivers a full range of analysis and reporting capabilities. You can use Oracle BI EE Answers and Dashboard presentation tools to customize the predefined sample dashboard reports that are provided with Oracle Retail Data Model.

What Are the Components of Oracle Retail Data Model

The Oracle Retail Data Model consists of logical and physical data models, intra-ETL that maps your OLTP tables to the Oracle Retail Data Model tables and views, sample reports, and the Oracle Interactive Dashboard using features of Oracle Business Intelligence Suite Enterprise Edition (OBIEE).

Figure 1-1 Oracle Retail Data Model

An overview of the components of Oracle Retail Data Model

ORDM includes the following components:

Oracle Retail Data Model Logical and Physical Models

The logical and physical models of Oracle Retail Data Model have the following characteristics:

  • Retail industry-specific, 3rd Normal Form logical and physical relational models

  • Physical Data model with 650+ tables and 10500+ attributes

  • Data warehouse models (based on Association for Retails Technology Standards (ARTS)

  • Industry specific Measures

  • Pre-built OLAP cubes

consist of multiple layers, with the details stored in the Base and Reference layers, and summary data stored in the Aggregate Layer and Analytical Workspace. The Derived layer stores information derived from the Base and Reference layers. Lookup tables are also included to store frequently used descriptive data.

Oracle Retail Data Model consists of enterprise data warehouse logical and physical components:

  • Base models (in 3rd Normal Form, transaction detail storage)

  • Reference models (describing the people, places and things within the retail organization)

  • Derived models (variation of the base model; could have minor aggregations, which store state or status information, or a combination of objects from the base model and data mining objects)

  • Aggregate models (aggregations of base and derived data for analytical purposes, including relational materialized views and OLAP). Aggregate data is stored in the form of relational materialized views. These materialized views are aggregates of the Base and Derived layers

Logical Model

Oracle Retail Data Model provides a predefined logical model. The logical data model defines the business entities and their relationships in order provide a clear understanding of the business and data requirements for the data warehouse.

The logical data model is described in detail in Oracle Retail Data Model Reference.

Physical Model

Oracle Retail Data Model provides a predefined physical data model.

The physical data model of the Oracle Retail Data Model is the physical manifestation of the logical data model into database tables and relationships (or foreign key constraints). Partitions, indexes, and materialized views have been added to aid performance.

The physical data model includes the following:

  • Reference tables

  • Lookup tables

  • Database sequences

  • Base tables

  • Derived tables

  • Aggregate tables and relational materialized views

  • (When the optional OLAP component is installed) Multi--dimensional OLAP cubes

The physical data model is described in detail in Oracle Retail Data Model Reference.


When examining the predefined physical model, keep in mind the naming convention using DW (Data Warehouse) prefixes and suffixes to identify the types of tables and views:

DWR_ : Reference data tables

DWL_ : Lookup tables

DWB_ : Base transaction data (3NF) tables

DWD_ : Derived (data mining) tables

DWA_ : Aggregate (ROLAP and MOLAP) tables

_MV : Materialized view

Where Oracle Retail Data Model Fits in a Data Warehousing Project

Oracle Retail Data Model provides much of the data modeling work that you must do for a retail business intelligence solution. The Base Layer provides a solid foundation for a retail data warehouse. The Derived and Aggregate Layers provide the infrastructure for creating business intelligence reports.Each retail operation is unique, and therefore the structure of the data warehouse will need to be different in order to match the needs of that unique retail environments. Oracle Retail Data Model comes with a generic schema that requires modification. These modifications include, adding, deleting, modifying, or renaming tables and columns; or altering foreign keys, constraints, or indexes. These changes must be made to the foundation data warehouse, not in the Oracle Retail Data Model schema itself.

After the data warehouse is populated, populate the derived and aggregate layers to support the reporting requirements. Oracle Retail Data Model includes a solid infrastructure for a range of reports.