Data Model

Data Model Overview

The Demand Signal Repository data model is based on the Oracle retail data model but is designed to meet manufacturer’s requirements. Users can view data from either a manufacturer or retailer perspective. For example, sales can be viewed using the manufacturer’s item hierarchy, item numbers and descriptions, or from the retailer’s item hierarchy, using the retailer’s item classification method and the retailer’s item numbers and descriptions. Slowly changing data, like organizations and items, is called reference data. Data that contains quantities, amounts, or other types of “facts” is called fact data.

Fact Data is stored by store, SKU and Day. This low level of granularity allows users to “slice and dice” information by time or organizational dimensions. In cases where a retailer submits data at an aggregate level, for example, by week or by a group of stores, DSR will allocate data based on a pre-defined organization allocation or time-allocation pattern.

The DSR Data Model retains historical changes to dimensional (aka hierarchical) data, such as retailer organization hierarchy, or item classification hierarchy. This feature, called Slowly Changing Dimensions (SCDs), allows users to analyze data according to the hierarchy in effect at the time.

Organization and Business Units

Within DSR, an organization generally equates to a company. Organization types supported by DSR include:

DSR maintains a customer organizational hierarchy to allow customer data to be summarized based on the customer’s view of their organization. Customers with common characteristics can be grouped into Retail Clusters for further analysis.

A Business Unit is a single customer location. Each business unit has one physical address. This enables the Business Unit to be analyzed by a geographic dimension, as well as the organization dimension. While business unit types are user-defined, the two most common types of business units are:

Each business unit in DSR must have a unique ID. Typically this is a GLN (Global Location Number) or a D&B Duns Plus 4 number.

Customer Organization Hierarchy

Retailer stores and distribution centers are organized into a customer organization hierarchy that includes districts, regions and areas. A retail group is an organization that owns one or more banners, or chain stores. For example, Wal-Mart’s “Neighborhood Market” chain versus its “Supercenter” chain.

In addition to organization hierarchy, DSR also allows customers to organize each retailer’s business units by channel, as well as by demographic features. Geography can be used to classify retail locations across multiple retailers. In addition to the geography of city, state and region, manufacturers can organize locations by their own sales regions and sub-regions, or by the regional scheme provided by a third party.

Reference Data Load Process

Illustrated here is the method by which reference data is loaded into DSR:

the picture is described in the document text

The source file can either come from the customer (for example, retailer items), or generated by the manufacturer (for example, manufacturer items). The source file is loaded into a standard interface table, where an Oracle Warehouse Builder process flow validates the data in the interface table.

If the data passes validation, it is moved from the interface table to the associated target table (or tables) in DSR. If the data does not pass validation, the associated record (or records) in the interface table(s) are moved to an error table. The error table contains the original source data, plus a column that lists the error (or errors) encountered. In addition, a related CSV file is created so that the errors can be imported into a spreadsheet (or other) application and corrected, if desired.

Some of the common validations that are commonly performed against reference data include:

Reference Data Process Flows

For each data set there is a main process flow which processes data from the interface table to the target table, as well as two additional process flows to move data from the error table (or .CSV error file) back to the interface table once errors have been corrected. The main process flows can be scheduled to run automatically using Oracle Warehouse Builder’s scheduling feature, or a script can be created which executes the process flow as part of multi-step process.

The available DSR reference data process flows are:

Fact Data Load Process

Illustrated here is the method by which fact data is loaded into DSR:

the picture is described in the document text

The process works similar to loading reference data, but once the fact data passes validation it is moved to a staging table. Only after all fact records associated with a process flow are loaded into the staging tables are they copied to the target tables.

If Fact Load Method (a system parameter) is set to Partial, then all records that passed validation are moved from the staging table to the target table. If Fact Load Method is set to All or None, then all records must pass validation before a single record is written to the target tables.

Some of the common validations that are commonly performed against fact data include:

Fact Data Process Flows

For each data set (for example, sales and returns data) there is a main process flow which processes data from the interface table to the target table, as well as two additional process flows to move data from the error table (or .CSV error file) back to the interface table once errors have been corrected.

Like reference data process flows, the main process flows can be scheduled to run automatically using Oracle Warehouse Builder’s scheduling feature, or a script can be created which executes the process flow as part of multi-step process.

The available DSR fact data process flows are: