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.
Within DSR, an organization generally equates to a company. Organization types supported by DSR include:
The manufacturer (which is company that owns DSR)
Customers (also called retailers or distributors)
Competitors (which are used to analyze all sales within a category including competitor products)
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:
Stores
Customer distribution centers
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.
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.
Illustrated here is the method by which reference data is loaded into DSR:
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:
Basic formatting - Validating completeness, to ensure required fields have been loaded and that dates are valid.
Code validation - Unit of Measure, Currency Codes, Types of Business Units (which are user defined).
Duplicate records - The system can be configured to error if duplicate records exist in the interface table. For example, if the same manufacturer item exists twice (or more) in the interface table. If the system is configured to ignore duplicate records, then the last record processed overlays any previous records.
Simulation or Partial Load - A system parameter that controls how reference data is loaded. In simulation mode, the data in the interface tables are validated and any errors encountered are written to the error table (and CSV file); however, the valid records are not written to the target table (because this is just a simulation). This feature is useful when first implementing DSR. In partial mode, the system works like we described earlier. Valid records are written to the target table, while records in error are written to the error table.
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:
BUNIT_HDAY
CAUSAL_CNDTN
CRNCY_EXC_RATE
GEOGRPC_DEMOGRPY
GEOGRPY_RGN_HCHY
GOALS_THD
ITM_BSNS_UNT_ASG
ITM_CLUSTER
ITM_UOM
LOOKUP_DATA
MARKET_AREA
MARKET_AREA_BSNS_UNIT
MFG_ITM_HCHY
ORG_HCHY
RTL_CLUSTER
RTL_ITM_HCHY
SKU_BSNS_INV_RULE
STR_CLSTR
SYND_GEO
SYND_PROD
TDLINX_ORG_HCHY
Illustrated here is the method by which fact data is loaded into DSR:
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:
Basic Formatting - Validating completeness, to ensure required fields have been loaded and that dates are valid.
Code Validation - Unit of Measure, Currency Codes, Types of Business Units (which are user defined).
Unrecognized Items or Locations - This includes validating retailer item number and the existence of a global item number that maps to an item in the manufacturer’s item hierarchy, and validating locations (for example, stores, distribution center, or other level in the organization hierarchy).
Ambiguous Items - This occurs when the retailer provided data solely in terms of its local item number, and there is more than one Global Item Number (GTIN, or other item ID type) that maps to that local item.
Unauthorized or New Items - This occurs when the item/business unit combination is not set up in DSR, and item discovery is turned off.
Missing Allocation Pattern - This occurs when data was loaded at an aggregate level, but no allocation pattern was defined for the associated organization and/or week.
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:
FORECAST_EF_TO_INF_STG
FORECAST_ERR_TO_INF_STG
FORECAST_INF_STG_TO_TGT
INVNTORY_EF_TO_INF_STG
INVNTORY_ERR_TO_INF_STG
INVNTORY_INF_STG_TO_TGT
ORDERS_EF_TO_INF_STG
ORDERS_ERR_TO_INF_STG
ORDERS_INF_STG_TO_TGT
PROMOPLN_EF_TO_INF_STG
PROMOPLN_ERR_TO_INF_STG
PROMOPLN_INF_STG_TO_TGT
SHIPMENT_EF_TO_INF_STG
SHIPMENT_ERR_TO_INF_STG
SHIPMENT_INF_STG_TO_TGT
SLS_RTRN_EF_TO_INF_STG
SLS_RTRN_ERR_TO_INF_STG
SLS_RTRN_INF_STG_TO_TGT