Implementing Demand Signal Repository

Implementation Overview

The DSR implementation can be grouped into these high-level functional areas:

Initial setup of codes and calendar data.
Loading of manufacturer and retailer (customer) organizations, hierarchies and related data.
Loading of manufacturer and retailer (customer) items, hierarchies and related data.
Importing of fact data, such as sales, inventory, orders, shipments, and so on.

The following diagram illustrates the DSR integration setup steps:

the picture is described in the document text

Setting Up Codes and Calendars

The first step required in the DSR implementation is to configure your codes and calendars. The following diagram illustrates the required and optional calendar setup steps:

the picture is described in the document text

Step 1: Populate Pre-Seed Data

Run the DDR_SETUP_UTIL.SETUP procedure to populate predefined code types and values used by DSR. These types and values include system parameter default values, error messages, hierarchy level names, and external measure names.

The setup procedure has only one parameter, the Manufacturer’s Organization Code, which is used to create a manufacturer’s organization record in the organization (DDR_R_ORG) table.

Procedure Name	DDR_SETUP_UTIL.SETUP
Parameters	Mfg Org Code
Source Tables	None
Target Tables	DDR_R_LKUP_TYPE DDR_R_LKUP_MST DDR_R_ORG
Dependent Tables	None
Example	APPS. DDR_SETUP_UTIL.SETUP(‘VCG’);

Step 2: Load Gregorian Calendar

Run the DDR_POP_CALENDAR_PKG.Populate_STND_Calendar procedure to populate the Gregorian (or standard) calendar. The Gregorian calendar is independent of any organization, so it has no dependency to other tables. You must populate the Gregorian calendar before you populate any of the other calendar types (for example, business, fiscal, advertising, and planning) for both manufacturer and retailer.

To populate Gregorian calendar, execute the DDR_POP_CALENDAR_PKG.Populate_STND_Calendar procedure. The first parameter is the number of years you want to create. The second parameter is the starting year.

This procedure only inserts years. It does not update existing data. The procedure checks for the existence of data with respect to the calendar type and year. If the calendar type and year combination already exists, then the procedure reports an error and stops processing.

To add years to the Gregorian calendar, supply only the Number of Years parameter. The second parameter must be null.

Procedure Name	DDR_POP_CALENDAR_PKG.Populate_STND_Calendar
Parameters	Number of Years, Starting Year
Source Tables	None
Target Tables	DDR_R_CLNDR_YR DDR_R_CLNDR_QTR DDR_R_CLNDR_MNTH DDR_R_CLNDR_WK DDR_R_DAYDDR_R_WKDAY
Dependent Tables	None
Example	DDR_POP_CALENDAR_PKG.Populate_STND_Calendar(‘5,2006’);

Step 3: Load Business Calendar

Run the DDR_POP_CALENDAR_PKG.Populate_BSNS_Calendar procedure to populate the manufacturer’s or customer’s business calendar. Before populating any organization-specific calendars, like business, fiscal, advertising, or planning calendars, the corresponding organization record must be available in the organization table (DDR_R_ORG).

You must load the manufacturer’s business calendar. Loading the customer’s (or retailer’s) business calendar is optional and is dependent on loading the customer organization first. Once the populate business calendar procedure completes, the tables are populated based on the parameter values that are supplied.

To execute the populate business calendar procedure, you must supply these parameter values:

The organization code to which the business calendar applies. Initially, this will be the manufacturer’s organization code used when loading pre-seeded data (Step 1). After loading customer organizations, you may want to load the customer’s business calendar as well.
Number of years to create.
Beginning date. This is the first day of the first business year to be created.
Months that will contain 5 weeks.
Years that will contain 53 weeks.
Month that will contain the 53rd week.

This procedure, like the procedure to load the Gregorian calendar, only inserts (or adds) years. If the calendar type and year combination already exists, then the procedure reports an error and stops processing. To add years to the business calendar, supply all but the third parameter, Beginning Date, which must be null.

All of the calendar types (excluding the Gregorian calendar, which we just learned is not associated to any organization) can be associated to the manufacturer; however, only the business calendar can be associated to the customer or retailer.

Procedure Name	DDR_POP_CALENDAR_PKG.Populate_BSNS_Calendar
Parameters	Org Code, Number of Years, Beginning Date, Months with 5 wks, Years with 53 wks, Month with 53rd Week
Source Tables	None
Target Tables	DDR_R_CLNDR DDR_R_BSNS_YR DDR_R_BSNS_QTR DDR_R_BSNS_MNTH DDR_R_BSNS_WK DDR_R_BASE_DAY
Dependent Tables	DDR_R_ORG
Example	DDR_POP_CALENDAR_PKG.Populate_BSNS_Calendar (‘VCC’, ‘5’, ’01-JAN-06’, ‘M3,M5,M8,M11’, ‘2008’, ‘M12’);

Step 4: Run the Time Transformation Procedure

Run the DDR_TIME_TRANSFORM_PKG.Populate_BSNS_Transformation procedure to group days into business weeks, quarters, and years. This procedure must be run for the date filters in the DSR reports and scorecards to work. This procedure only applies to the business calendar, and it must be re-executed when years are added.

To run the time transformation procedure, you must supply these parameter values:

Organization code for the business calendar you are transforming
Beginning year to transform
Ending year

You can run this procedure multiple times. It will recreate any transformations that were previously created. After adding new years in a business calendar, you should run the transformation for the years that were added.

Procedure Name	DDR_TIME_TRANSFORM_PKG.Populate_BSNS_Transformation
Parameters	Org Code, Beginning Year, Ending Year
Source Tables	DDR_R_BSNS_YR DDR_R_BSNS_QTR DDR_R_BSNS_MNTH DDR_R_BSNS_WK DDR_R_BASE_DAY DDR_R_DAY
Target Tables	DDR_R_DAY_TRANS DDR_R_WK_TRANS DDR_R_MNTH_TRANS DDR_R_QTR_TRANS DDR_R_YR_TRANS DDR_R_DAY_TODATE_TRANS DDR_R_WK_TODATE_TRANS DDR_R_MNTH_TODATE_TRANS DDR_R_QTR_TODATE_TRANS
Dependent Tables	DDR_R_BSNS_YR DDR_R_BSNS_QTR DDR_R_BSNS_MNTH DDR_R_BSNS_WK DDR_R_BASE_DAY DDR_R_DAY
Example	DDR_TIME_TRANSFORM_PKG.Populate_BSNS_Transformation (‘VCG’, ‘2006’, ‘2010’);

Step 5: (Optional) Load Fiscal Calendar

Run the DDR_POP_CALENDAR_PKG.Populate_FSCL_Calendar procedure to populate the manufacturer’s fiscal calendar. This step is optional.

To populate fiscal calendar, you must supply these parameter values:

Manufacturer’s organization code
Number of years you want to create
First day of the starting fiscal year

Like the other calendar procedures, this procedure only inserts (or adds) years. If the calendar type and year combination already exists, then the procedure reports an error and stops processing.

To add years to the fiscal calendar, supply only the first two parameters; the Start of Year parameter must be null.

Procedure Name	DDR_POP_CALENDAR_PKG.Populate_FSCL_Calendar
Parameters	Mfg Org Code, Number of Years, Start of Year
Source Tables	None
Target Tables	DDR_R_CLNDR DDR_R_FSCL_YR DDR_R_FSCL_QTR DDR_R_FSCL_MNTH DDR_R_BASE_DAY
Dependent Tables	DDR_R_ORG
Example	DDR_POP_CALENDAR_PKG.Populate_FSCL_Calendar (‘VCG’, ‘5’, ‘200601’);

Step 6: (Optional) Load Planning Calendar

Run the DDR_POP_CALENDAR_PKG.Populate_PLNG_Calendar procedure to populate the planning calendar. This step is optional.

To execute the populate planning procedure, you must supply these parameter values:

Organization code to which the planning calendar applies
Number of years to create
Beginning date (this is the first day of the first planning year to be created)
Months that will contain 5 weeks
Years that will contain 53 weeks
Month that will contain the 53rd week

This procedure, like others that load calendar information, only inserts (or adds) years. If the calendar type or year combination already exists, then the procedure reports an error and stops processing. To add years to the planning calendar, supply all but the third parameter, Beginning Date, which must be null.

Procedure Name	DDR_POP_CALENDAR_PKG.Populate_PLNG_Calendar
Parameters	Org Code, Number of Years, Beginning Date, Months with 5 wks, Years with 53 wks, Month with 53rd Week
Source Tables	None
Target Tables	DDR_R_CLNDR DDR_R_PLNG_YR DDR_R_PLNG_QTR DDR_R_PLNG_PRD DDR_R_PLNG_WK, DDR_R_BASE_DAY
Dependent Tables	DDR_R_ORG
Example	DDR_POP_CALENDAR_PKG.Populate_PLNG_Calendar (‘VCG’, ‘5’, ’01-JAN-06’, ‘Q1=3, Q2=3, Q3=4, Q4=3’, P1=4, P2=4, P3=4, P4=4, P5=4, P6=4, P7=4, P8=4, P9=4’, ‘2008’, ‘P12’);

Step 7: (Optional) Load Advertising Calendar

Run the DDR_POP_CALENDAR_PKG.Populate_ADVR_Calendar procedure to populate the Manufacturer’s Advertising calendar. This step is optional.

To populate the advertising procedure, supply these parameter values (similar to those for the loading planning calendar):

Organization code to which the advertising calendar applies
Number of years to create
Beginning date (the first day of the first advertising year to be created)
Months that will contain 5 weeks
Years that will contain 53 weeks
Month that will contain the 53rd week

This procedure, like others that load calendar information, only inserts (or adds) years. If the calendar type and year combination already exists, then the procedure reports an error and stops processing. To add years to the advertising calendar, supply all but the third parameter, Beginning Date, which must be null.

Procedure Name	DDR_POP_CALENDAR_PKG.Populate_ADVR_Calendar
Parameter	Mfg Org Code, Number of Years, Start of Year, Qtrs with 5 wks, Periods and Weeks, Starting Year, Period with extra week
Source Tables	None
Target Tables	DDR_R_CLNDR DDR_R_ADVR_YR DDR_R_ADVR_QTR DDR_R_ADVR_PRD DDR_R_ADVR_WK DDR_R_BASE_DAY
Dependent Tables	DDR_R_ORG
Example	DDR_POP_CALENDAR_PKG.Populate_ADVR_Calendar (‘VCG’, ‘5’, ‘Q1=3, Q2=3, Q3=4, Q4=3’, P1=4, P2=4, P3=4, P4=4, P5=4, P6=4, P7=4, P8=4, P9=4’);

Step 8: Load Lookup Masters

Run the DDR_LKUP_MAIN_PF procedure to load any required lookup values. You must load additional lookup values before you can load organizations or items.Lookup values include Global Location ID Types (for example, GLID), Global Item ID Types (for example, GTIN, EAN, UPC), and Address Types (for example, Store Add and Mailing Add).

Loading lookup information is accomplished by executing an Oracle Data Integrator (ODI) scenario. The appropriate lookup values must first be loaded into the source table, in this case, the Look Up Interface table (DDR_I_LKUP). When you run (or start) the ODI Look Up scenario, the system reads the source table and populates the lookup master table.

Before running the Look Up scenario, you must load the manufacturer’s organization code and Look Up Types (using the setup utility discussed in Step 1).

The following required fields must be populated in the interface table:

Manufacturer’s Organization Code (MFG_ORG_CD)
Look Up Type Code (LKUP_TYP_CD)
Look Up Code (LKUP_CD)

If any of these fields is not populated, an error is written to the error table.

The following key validations are required:

Manufacturer’s Organization must be valid.
Look Up Type Code must be valid.

Procedure Name	DDR_LKUP_MAIN_PF
Parameter	Manufacturer’s Organization, Look Up Type Code, Look Up Code
Source Tables	DDR_I_LKUP
Target Tables	DDR_R_LKUP_MST
Dependent Tables	DDR_R_ORG DDR_R_LKUP_TYP

Step 9: Load Currency Codes

Run the DDR_ETL_UTIL_PKG.Import Data procedure to load currency codes and exchange rates. Currency code is a required column in some of the subsequent scenarios, so even if all data is in a single currency, you must set up at least one currency code. Additional currency codes are required if customer data is received in other currencies.

This procedure has no scenario. Currencies are loaded directly into the Demand Signal Repository target table (DDR_R_CRNCY). DSR provides a generic import data procedure that can be used to load currency codes, as well as other data, into DSR. After the procedure completes, data is loaded from the specified tab-delimited text file into the currency code table (DDR_R_CRNCY).

To populate currency codes using the generic import data procedure, you must specify two parameter values. The first parameter is the target table to be populated. The second parameter is the source table, which must be a tab-delimited text file.

To successfully process the tab-delimited text file, you must ensure that the first row contains column headings that match the database column names of the target table. Also, date fields must be in year, month, day format (yyyy-mm-dd).

This procedure only inserts data; it does not update existing data. You will receive a unique constraint error if you attempt to load a record that already exists.

Note: You also can use this import data procedure to load data into any DSR interface table before running the associated ODI scenario.

Procedure Name	DDR_ETL_UTIL_PKG.Import Data
Parameter	Target Table, Source Table
Source Tables	Text (tab delimited) .txt file
Target Tables	DDR_R_CRNCY
Dependent Tables	DDR_R_ORG
Example	DDR_ETL_UTIL_PKG.Import Data (‘DDR_R_CRNCY’, ‘DDR_R_CRNCY.txt’);

Step 10: Load Currency Exchange Rate

The DDR_CRNCY_EXC__RATE_MAIN_PF Currency Exchange Rate scenario populates exchange rates between two currencies for a given day so that retailer monetary amounts can be converted to the manufacturer’s currency.

These required fields must be populated in the interface table:

From currency (FROM_CRNCY_CD)
To currency (TO_CRNCY_CD)
Calendar Date (CLNDR_DT)
Exchange Rate (EXCHNG_RATE)

If any of these fields is not populated, an error is written to the error table.

The following key validations are required:

From and To Currency Codes must be valid (in table DDR_R_CRNCY)
Date must be valid (based on Gregorian Calendar)

Procedure Name	DDR_CRNCY_EXC__RATE_MAIN_PF
Source Tables	DDR_I_CRNCY_EXCHNG_RATE
Target Tables	DDR_B_CRNCY_EXCHNG_RATE
Dependent Tables	DDR_R_DAY DDR_R_CRNCY

Step 11: Load Country Codes

Load country codes using the DDR_ETL_UTIL_PKG.Import Data procedure. This procedure loads data from a tab-delimited file into the specified target table.

The same rules for formatting the tab-delimited text apply, which means the first row must contain the appropriate column headings and any date fields must be in yyyy-mm-dd format. This procedure only inserts data; it does not update existing data. You will receive a unique constraint error if you attempt to load a record that already exists.

Procedure Name	DDR_ETL_UTIL_PKG.Import Data
Source Tables	Text (tab-delimited) .txt file
Target Tables	DDR_R_CNTRY
Dependent Tables	DDR_R_ORG

Step 12: Load State Codes

Load state codes using the DDR_ETL_UTIL_PKG.Import Data procedure. This procedure loads data from a tab-delimited file into the specified target table.

Procedure Name	DDR_ETL_UTIL_PKG.Import Data
Source Tables	Text (tab-delimited) .txt file
Target Tables	DDR_R_STATE
Dependent Tables	DDR_R_ORG DDR_R_CNTRY

Step 13: Load City Codes

Load city codes using the DDR_ETL_UTIL_PKG.Import Data procedure. This procedure loads data from a tab-delimited file into the specified target table.

Procedure Name	DDR_ETL_UTIL_PKG.Import Data
Source Tables	Text (tab-delimited) .txt file
Target Tables	DDR_R_CITY
Dependent Tables	DDR_R_ORG

Step 14: Load Organization Demographic Groups

Organization demographic groups (and their associated attribute values) can be associated to customer business units. Load demographic groups using the DDR_ETL_UTIL_PKG.Import Data procedure, which loads data from a tab-delimited file into the specified target table.

Organization demographic groups (and their associated attribute values) are assigned to business units. For example, organization demographic groups can be used to create a group called Retail Floor Space. Attributes, such as Large, Medium, and Small can then be assigned to the Retail Floor Space demographic group. When loading a retail organization, you can then specify that a store have a value of Medium for the Retail Floor Space. In addition, you can capture an associated attribute value, such as 18,000 SQFT (square feet).

Procedure Name	DDR_ETL_UTIL_PKG.Import Data
Source Tables	Text (tab-delimited) .txt file
Target Tables	DRR_R_ORG_DEMOG_GRP
Dependent Tables	DDR_R_ORG

Step 15: Load Demographic Attributes

Organization demographic attributes are associated demographic groups and can be assigned to customer business units. Load demographic attributes using the DDR_ETL_UTIL_PKG.Import Data procedure, which loads data from a tab-delimited file into the specified target table.

Procedure Name	DDR_ETL_UTIL_PKG.Import Data
Source Tables	Text (tab-delimited) .txt file
Target Tables	DRR_R_ORG_DEMOG_ATTR
Dependent Tables	DDR_R_ORG

Loading Organizations and Hierarchies

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

The manufacturer (company that owns the DSR)
Customers (also called retailers or distributors)
Competitors (companies 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 its organization. Customers with common characteristics can be grouped into Retail Clusters for further analysis.

The following diagram illustrates the required and optional organization and hierarchy setup steps:

the picture is described in the document text

Step 1: (Optional) Load Geographic Regions

The Geographic Region Hierarchy scenario captures the manufacturer’s hierarchical view of geographic region to sub-regions. It is used to aggregate data by geographical area. This step is optional.

When you execute the Geographic Region Hierarchy scenario, the system reads the source table and populates the geographic region and subregion target tables listed. Before running the Geography Region scenario, you must load the Manufacturer’s Organization Code into the organization table (via the setup utility).

The following fields are required and must be populated in the interface table:

Manufacturer’s Organization Code (MFG_ORG_CD)
Region Code (RGN_CD)
Sub-region Code (SUB_RGN_CD)

If any of these fields is not populated, an error is written to the error table.

You must populate both levels of the Geographic Region hierarchy. For example, if a manufacturer uses only one level for Geographic Region (for example, sub-region), then the manufacturer must populate the data for the sub-region into the region level of the Interface table.

The following key validation is required:

Manufacturer’s Organization must be valid.

Scenario Name	DDR_GEO_HCHY
Source Tables	DDR_I_GEO_RGN_HCHY
Target Tables	DDR_R_GEO_RGN DDR_R_GEO_SUB_RGN
Dependent Tables	DDR_R_ORG

Step 2: (Optional) Load Geography Regions

The Geography Demographics scenario captures demographic information like population, household, education, and so on and associates it to a geographic code (which is distinct from region or sub-region). When loading retail business units (stores, distribution centers, and so on.) you can associate a geography code (GEO_CD) with the address. This code links the geography demographics to a location. Loading of geography demographic data is optional.

When you execute the Geography Demographics scenario, the system reads the source table and populates the target table listed. Before running the Geography Demographics scenario, you must load the Manufacturer’s Organization Code into the organization table.

The following fields are required in the interface table (DDR_I_GEO_DEMOG):

Manufacturer’s Organization Code (MFG_ORG_CD)
Geography code (GEO_CD)
Geography Value (GEO_VAL) cannot be null

The following key validation is required:

Manufacturer’s Organization must be set up in the organization table (DDR_R_ORG)

Scenario Name	DDR_GEO_DEMOG
Source Tables	DDR_I_GEO_DEMOG
Target Tables	DDR_R_GEO_DEMOG
Dependent Tables	DDR_R_ORG

Step 3: Load Manufacturer Hierarchy

The Organization Hierarchy (DDR_ORG_HCHY) scenario captures Manufacturer and Retailer Organization information, including valid locations (business units), location types (store, distribution center, and so on), and organization hierarchy information. Competitor organizations are also captured, but not competitor business unit and store information.

Competitor information is loaded only to the organization table. The system does not load competitor hierarchy or competitor business unit information (such as stores and distribution centers).

Step 4: Load Customer Organization and Hierarchy

The Organization Hierarchy scenario (DDR_ORG_HCHY) captures Manufacturer and Retailer Organization information, including valid locations (or business units), location types (store, distribution center, and so on), and organization hierarchy information. Competitor organizations are also captured, but not competitor business unit and store information.

Competitor information is loaded only in the organization table. The system does not load competitor hierarchy or competitor business unit information (such as stores and distribution centers).

Three source tables are loaded with organizational data. The main organization source table (DDR_I_ORG) identifies each organization and whether the organization is a manufacturer, retailer, or competitor organization. Based on organization type, the scenario writes a retail or manufacturer item company record.

The organization hierarchy source table (DDR_I_ORG_HCHY) associates business units to districts, regions, areas, and chains. Retail organization hierarchy values can be different from the manufacturer’s organization hierarchy.

The organization business unit source table (DDR_I_ORG_BSNS_UNIT) describes the business units (stores or distribution centers) or other user-defined business unit types and their related attributes. This information includes the address of the business unit, valid inventory locations, and attributes such as number of external signs, number of checkout counters, and so on.

These fields are required for the Organization table (DDR_I_ORG):

Manufacturer’s Organization Code (MFG_ORG_CD)
Organization Code (ORG_CD)
Organization Type (ORG_TYP)
Global Location ID (GLBL_LOC_ID)
Global Location Type (GLBL_LOC_ID_TYP)

If any of these fields is not populated, the system writes an error to the error table.

These fields are required for the Organization Hierarchy table (DDR_I_ORG_HCHY):

Chain Code, Chain Global Location ID, and Chain Global Location ID Type (CHAIN_CD, CHAIN_GLBL_LOC_ID, CHAIN_GLBL_LOC_ID_TYP)
Area Code, Area Global Location ID, and Area Global Location ID type (AREA_CD, AREA_GLBL_LOC_ID, AREA_GLBL_LOC_ID_TYP)
Region Code, Region Global Location ID, and Region Global Location ID type (RGN_CD, RGN_GLBL_LOC_ID, RGN_GLBL_LOC_ID_TYP)
District Code, District Global Location ID, and District Global Location ID type (DSTRCT_CD, DSTRCT_GLBL_LOC_ID, DSTRCT_GLBL_LOC_ID_TYP)

These fields are required for the Organization Business Unit table (DDR_I_BSNS_UNIT):

Business Unit Code, Business Unit Global Location ID, and Business Unit Global Location ID type (BSNS_UNIT_CD, BSNS_UNIT_GLBL_LOC_ID and BSNS_UNIT_GLBL_LOC_ID_TYP)

The following key validations are required:

Manufacturer’s Organization must be set up in the organization table (DDR_R_ORG).
Organization type must be RTL (Retailer), CMP (Competitor), or MFG (Manufacturer).
All levels of the hierarchy must be populated.
SCD type 2 loading is used for all the organization hierarchy tables except the upper-level organization table.

Scenario Name	DDR_ORG_HCHY
Source Tables	DDR_I_ORG DDR_I_ORG_HCHY DDR_I_ORG_BSNS_UNIT
Target Tables	DDR_R_ORG DDR_R_MFG_ITEM_CMPNY DDR_R_RTL_ITEM_CMPNY DDR_R_ORG_CHAIN DDR_R_ORG_AREA DDR_R_ORG_RGN DDR_R_ORG_DSTRCT DDR_R_ORG_BSNS_UNIT DDR_R_ORG_BSNS_UNIT DDR_R_ADDR_LOC DDR_R_ORG_DEMOG_VAL DDR_R_INV_LOC
Dependent Tables	DDR_R_GEO_SUB_RGN DDR_R_GEO_RGN DDR_R_GEO_DEMOG DDR_R_ORG_DEMOG_GRP DDR_R_ORG_DEMOG_ATTR DDR_R_GEO_SUB_RGN DDR_R_GEO_RGN DDR_R_GEO_DEMOG DDR_R_ORG_DEMOG_GRP DDR_R_ORG_DEMOG_ATTR

Step 5: Load BU Holidays

The Business Unit Holidays ETL captures the days in which a business unit (for example, store, distribution center) is not open for business. When you execute the business unit holidays scenario (DDR_BSNS_UNIT_HDAY), the system reads the source table and populates the target table listed.

The following fields are required in the interface table:

Organization Code (MFG_ORG_CD)
Business Unit Code (BSNS_UNIT_CD)
Calendar Date (CLNDR_DT)

If any of these fields is not populated, the system writes an error to the error table.

The following key validations are required:

Organization must be set up in the organization table (DDR_R_ORG).
Date must be valid (DDR_R_DAY).

Note: The Business Unit Holidays scenario does not support removing a holiday date from the Business Unit Holiday table.

Scenario Name	DDR_BSNS_UNIT_HDAY
Source Tables	DDR_I_ORG_BSNS_UNIT_HDAY
Target Tables	DDR_R_ORG_BSNS_UNIT_HDAY
Dependent Tables	DDR_R_ORG DDR_R_ORG_BSNS_UNIT DDR_R_DAY

Step 6: Load Market Areas (optional)

The Market Area scenario captures the definition of a market area and its related attributes. This includes the Market Area Type, which identifies the source of the market area definition. Examples include syndicated data sources such as IRI and Nielson (which currently aligns their market area definitions), other third party sources, or your own internal definitions. In addition, this scenario captures the Market Area Code, and demographic data such as the city and state population, and the state’s retail sales.

When you execute the market area scenario, the system reads the source table and populates the target table listed here. Before running the Market Area scenario, the manufacturer’s organization must be loaded into the organization table. In addition, valid market area types must be defined in the look up master table.

Scenario Name	DDR_MKT_AREA
Source Tables	DDR_I_MKT_AREA
Target Tables	DDR_R_MKT_AREA
Dependent Tables	DDR_R_ORG DDR_R_LKUP_MST

The following fields are required and must be populated in the interface table:

Organization Code (MFG_ORG_CD)
Market Area Type (MKT_AREA_TYP)
Market Area Code (MKT_AREA_CD)
Market Area Name (MKT_AREA_NAME)

The following key validations are required:

Manufacturer’s Organization must be valid
Market Area Type must be valid
City, State, and Country must be valid

Market Area Code must be unique within a Market Area Type. Ten user-defined attributes are available to capture additional information.

Step 7: Load Market Areas Associations

The Market Area / Business Unit Association scenario captures the association between a store and market areas. Up to three market areas (one for each Market Area Type) can be associated to a store.

When you execute the Market Area / Business Unit Association scenario, the system reads the source table and populates the target table listed here. Before running the Market Area / Business Unit Association scenario, the retailer organization and their related business units must be loaded into the organization table. In addition, the day associated market area must be loaded.

Scenario Name	DDR_MKT_AREA_BSNS_UNIT
Source Tables	DDR_I_MKT_AREA
Target Tables	DDR_R_ORG_BSNS_UNIT
Dependent Tables	DDR_R_ORG DDR_R_ORG_BSNS_UNIT DDR_R_MKT_AREA

The following fields must be populated in the interface table. If any of these fields are not populated, an error will be written to the error table:

Organization Code (MFG_ORG_CD)
Business Unit Code (BSNS_UNIT_CD)
Market Area Type 1 (MKT_AREA_TYP_1) and Market Area Code 1 (MKT_AREA_CD) are required. The other two pairs of market area type / market area code fields are optional.

The following key validations are required:

Manufacturer’s Organization must be valid
Customer Organization / Business Unit combination must be set up in the organization BU table (DDR_R_ORG_BSNS_UNIT )
If populated, the Market Area Type / Market Area Code combination must be set up in Market Area table (DDR_R_MKT_AREA)

Step 8: Load Retail Cluster Codes (optional)

Retail clusters are used to group customer organizations (for example, grocery chains versus convenience stores). Load Retail Cluster Codes using the DDR_ETL_UTIL_PKG.Import Data procedure, which loads data from a tab-delimited file into the specified target table.

Source Tables	Text (tab-delimited) .txt file
Target Tables	DRR_R_RTL_CLSTR
Dependent Tables	DDR_R_ORG

Step 9: Load Retail Cluster Associations

Use the Retail Cluster scenario (RTL_CLSTR) to associate customer (retail) organizations to predefined cluster codes. Once you define cluster codes, you can run the Retail Cluster scenario to associate customer organizations with retail cluster codes. In addition to the retail cluster codes, you must also load the retail organization code into the organization table.

If any of the following fields is not populated, an error is written to the error table:

Retail Organization Code (RTL_ORG_CD)
Retail Cluster Code (RTL_CLSTR_CD)

The following key validations are required:

Retail Organization Code must be valid.
Retail Cluster must be valid.

Scenario Name	RTL_CLSTR
Source Tables	DDR_I_RTL_CLSTR
Target Tables	DDR_R_RTL_CLSTR_RTL_ASSC
Dependent Tables	DDR_R_ORG (Retailer) DDR_R_RTL_CLSTR

Step 8: Load Causal Conditions

The Causal Condition scenario (DDR_CAUSAL_CNDTN) captures the internal, external, and weather condition data from retailers for different business units on a given day. The Causal Condition scenario captures the internal, external, and weather condition data from retailers for different business units on a given day.

Before running the Causal Conditions scenario, you must load the associated retailer’s business unit as well as geography demographics and Gregorian calendar.

If any of the following fields is not populated, an error is written to the error table:

Retail Organization Code (ORG_CD)
Business Unit Code (BSNS_UNIT_CD)
Geography Code (GEO_CD)
Calendar Date (CLNDR_DT)

The following key validations are required:

Retail Organization must be valid.
Retail Business Unit must be valid for the Retailer.
Geography Code must be valid (in table DDR_R_GEO_DEMOG).
Calendar date must be valid (in table DDR_R_DAY).

Scenario Name	DDR_CAUSAL_CNDTN
Source Tables	DDR_I_DDR_CAUSAL_CNDTN
Target Tables	DDR_R_CAUSAL_CNDTN
Dependent Tables	DDR_R_ORG_BSNS_UNIT DDR_R_GEO_DEMOG DDR_R_DAY

Step 9: Load Organization Allocation Patterns

If sales (or sales forecast) fact data is received at a higher level than business unit, an organization allocation pattern must be defined to allocate the fact data down to the business units (based on percentage). Load Organization Allocation Patterns using the DDR_ETL_UTIL_PKG.Import Data procedure.

Source Tables	DDR_I_CAUSAL_CNDTN
Target Tables	DDR_R_CAUSAL_CNDTN
Dependent Tables	DDR_R_ORG_BSNS_UNIT DDR_R_GEO_DEMOG DDR_R_DAY

Step 10: Load Time Allocation Patterns

If sales or sales forecast fact data is received on a weekly (versus daily) basis, a time allocation pattern must be defined to allocate the fact data to the days of the week (based on percentage). Load Time Allocation Patterns using the DDR_ETL_UTIL_PKG.Import Data procedure.

Loading Items and Hierarchies

The following diagram illustrates the required and optional item and hierarchy setup steps:

the picture is described in the document text

Step 1: Load Units of Measure

The Unit of Measure scenario (DDR_UOM) defines the valid units of measure (UOM) used by an organization. When you execute the Unit of Measure scenario, the system reads the source table and populates the target table. Before running the Unit of Measure scenario, the Manufacturer’s Organization Code must be loaded into the organization table.

If any of the following fields is not populated, an error is written to the error table:

Manufacturer’s Organization Code (MFG_ORG_CD)
Unit of Measure Code (UOM_CD)

The following key validation is required:

Manufacturer’s Organization must be set up in the organization table (DDR_R_ORG).

Scenario Name	DDR_UOM
Source Tables	DDR_I_UOM
Target Tables	DDR_R_UOM
Dependent Tables	DDR_R_ORG

Step 2: Load Manufacturer Varieties

Load the manufacturer varieties using the DDR_ETL_UTIL_PKG.Import Data procedure, which loads data from a tab delimited file into the specified target table.

Scenario Name	DDR_ETL_UTIL_PKG.Import Data
Source Tables	Text (tab delimited) .txt file
Target Tables	DDR_R_MFG_VRTY
Dependent Tables	DDR_R_ORG

Step 3: Load Manufacturer Items and Hierarchy

The Manufacturer Item Hierarchy scenario (DDR_MFG_ITEM_HCHY) populates all levels of the manufacturer’s item hierarchy as well as the manufacturer SKU item information. Some of the SKU item attributes that can be loaded are:

Price
Item type
Color
Coating
Fiber
Weave

Two source tables are loaded with manufacturer item data. The Manufacturer Item Hierarchy source table (DDR_I_MFG_ITEM_HCHY) associates individual SKU items to the various levels of the item hierarchy, including item, subclass, class, group, and division. The Manufacturer SKU Item source table (DDR_I_MFG_SKU_ITEM) identifies the unique SKUs that the manufacturer produces or the unique competitor SKUs for which they are the category captain for a retailer.

Before running the Manufacturer Item Hierarchy scenario, you must load the manufacturer into the organization table. In addition, Manufacturer Variety table (DDR_R_MFG_VRTY) and UOM codes referenced in the source table must be set up in the UOM tables. Any brand names referenced in the source tables must be set up in the Look Up Master table.

If any of the following fields is not populated, an error is written to the error table. Required fields for the Manufacturer Item Hierarchy table (DDR_I_MFG_ITEM_HCHY) are:

Manufacturer’s Organization Code (MFG_ORG_CD)
Company Code (MFG_CMPNY_CD)
Division Code (MFG_DIV_CD)
Group Code (MFG_GRP_CD)
Class Code (MFG_CLASS_CD)
Sub-Class Code (MFG_SBC_CD)
Item Number (MFG_ITEM_NBR)
SKU Item Number (MFG_SKU_ITEM_NBR)
Global Item ID (GLBL_ITEM_ID)
Global Item ID Type (GLBL_ITEM_ID_TYP)

Required fields for Manufacturer SKU Item table (DDR_I_MFG_SKU_ITEM) are:

SKU Item Number (MFG_SKU_ITEM_NBR)
Global Item ID (GLBL_ITEM_ID)
Global Item ID Type (GLBL_ITEM_ID_TYP)
UOM Code (UOM_CD)
Alternate UOM Code (ALT_UOM_CD)

The following key validations are required:

Manufacturer’s Organization must be set up in the organization table (DDR_R_ORG).
Brand, UOM, Alternate UOM codes must be valid.
All Item Hierarchical Levels must populated.
SCD type 2 loading is used for all the item hierarchy tables.

SKU Item Type, Color, Coating, Size, Fiber, Fabric, Weave, Style, Stock Item Type and Dye values are automatically loaded into the Look Up Master table.
UOM Conversion Factors (from Transaction to Alternate UOM) are automatically loaded into the UOM Conversion table.

Scenario Name	DDR_MFG_ITEM_HCHY
Source Tables	DDR_I_MFG_ITEM_HCHY DDR_I_MFG_SKU_ITEM
Target Tables	DDR_R_MFG_ITEM_DIV DDR_R_MFG_ITEM_GRP DDR_R_MFG_ITEM_CLASS DDR_R_MFG_ITEM_SBC DDR_R_MFG_ITEM DDR_R_MFG_SKU_ITEM DDR_R_MFG_SKU_ITEM DDR_R_MFG_SKU_ITEM_VRTY DDR_R_UOM_CNVRSN
Dependent Tables	DDR_R_ORG DDR_R_MFG_ITEM_CMPNY DDR_R_MFG_VRTY DDR_R_UOMDDR_R_LKUP_MST

Step 4: Load Item Cluster Groups

Item clusters are used to group items together. Load Item Cluster Codes using the DDR_ETL_UTIL_PKG.Import Data procedure, which loads data from a tab-delimited file into the specified target table.

Scenario Name	DDR_ETL_UTIL_PKG.Import Data
Source Tables	Text (tab delimited) .txt file
Target Tables	DRR_R_ITEM_CLSTR
Dependent Tables	DDR_R_ORG

Step 5: Load Item Clusters

The Item Cluster scenario populates data which assigns items to an item cluster. Clusters can reference any level of the item hierarchy. For example, all items in a sub-class can be included in a cluster along with other items that were associated to the cluster based on SKU item number. Before running the Item Cluster scenario, the Manufacturer’s Item Hierarchy must be loaded, as well as the item cluster codes that are valid for each retailer.

If any of the following fields is not populated, an error is written to the error table:

Manufacturer’s Organization Code (MFG_ORG_CD)
Item Cluster Code (ITEM_CLSTR_CD)
Item Cluster Type (ITEM_CLSTR_TYP)

The following key validations are required:

Manufacturer’s Organization must be set up in the organization table (DDR_R_ORG).
Cluster Code must be valid (based on DDR_R_ITEM_CLSTR).

Scenario Name	DDR_ITEM_CLSTR
Source Tables	DDR_I_ITEM_CLSTR
Target Tables	DDR_R_ITEM_CLSTR_ASSC
Dependent Tables	DDR_R_ORG DDR_R_ MFG_ITEM_CMPNY DDR_R_ MFG_ITEM_DIV DDR_R_ MFG_ITEM_GRP DDR_R_ MFG_ITEM_CLASS DDR_R_ MFG_ITEM_SBC DDR_R_LKUP_MST DDR_R_ITEM_CLSTR

Step 6: Load Retailer Item Varieties

Load retailer varieties using the DDR_ETL_UTIL_PKG.Import Data procedure, which loads data from a tab-delimited file into the specified target table.

Scenario Name	DDR_ETL_UTIL_PKG.Import Data
Source Tables	Text (tab delimited) .txt file
Target Tables	DDR_R_RTL_VRTY
Dependent Tables	DDR_R_ORG (Manufacturer & Retailer)

Step 7: Load Retailer Items and Hierarchy

The Retailer Item Hierarchy scenario (DDR_RTL_ITEM_HCHY) populates all levels of the Retailer’s Item Hierarchy as well as the Retailer’s SKU item information. The available Retailer SKU Item attributes are similar to the Manufacturer's SKU Item attributes (price, item type, color, coating, fiber, weave, dye, fabric, item style, stock item type, size, and so on).

Two source tables are loaded with retailer item data: Retailer Item Hierarchy source table (DDR_I_RTL_ITEM_HCHY) and Retailer SKU item source table (DDR_I_RTL_SKU_ITEM). The Retailer Item Hierarchy source table associates individual SKU items to the various levels of the retailer’s item hierarchy, including item, subclass, class, group, and division.

The Retailer SKU item source table identifies the unique SKUs that the retailer sells, including any competitor SKUs that the manufacturer manages as the category captain. Before running the Retailer Item Hierarchy Scenario, you must load the Retailer’s Organization into the organization table. In addition, varieties must be set up in the Retailer’s Variety table (DDR_R_RTL_VRTY) and UOM codes must be set up. Brand names referenced in the source tables must be set up in the Look Up Master table.

If any of the following fields is not populated, the system writes an error to the error table. Required fields for Retailer Item Hierarchy table (DDR_I_RTL_ITEM_HCHY) are:

Manufacturer’s Organization Code (MFG_ORG_CD)
Company Code (RTL_CMPNY_CD)
Division Code (RTL_DIV_CD)
Group Code (RTL_GRP_CD)
Class Code (RTL_CLASS_CD)
Sub-Class Code (RTL_SBC_CD)
Item Number (RTL_ITEM_NBR)
SKU Item Number (RTL_SKU_ITEM_NBR)
Global Item ID (GLBL_ITEM_ID)
Global Item ID Type (GLBL_ITEM_ID_TYP)

Required fields for Retailer SKU Item table (DDR_I_RTL_SKU_ITEM) are:

Retail Organization (RTL_ORG_CD)
SKU Item Number (RTL_SKU_ITEM_NBR)
Global Item ID (GLBL_ITEM_ID)
Global Item ID Type (GLBL_ITEM_ID_TYP)
UOM Code (UOM_CD)

The following key validations are required:

Retailer Organization must be set up in the organization table (DDR_R_ORG).
Brand and UOM codes must be valid.
All Item Hierarchical Levels must be populated.
SCD type 2 loading is used for all the item hierarchy tables.

Note: SKU Item Type, Color, Coating, Size, Fiber, Fabric, Weave, Style, Stock Item Type and Dye values are automatically loaded into the Look Up Master table.

Scenario Name	DDR_RTL_ITEM_HCHY
Source Tables	DDR_I_RTL_ITEM_HCHY DDR_I_RTL_SKU_ITEM
Target Tables	DDR_R_RTL_ITEM_DIV DDR_R_RTL_ITEM_GRP DDR_R_RTL_ITEM_CLASS DDR_R_RTL_ITEM_SBC DDR_R_RTL_ITEM DDR_R_RTL_SKU_ITEM DDR_R_RTL_SKU_ITEM DDR_R_RTL_SKU_ITEM_VRTY DDR_R_UOM_CNVRSN
Dependent Tables	DDR_R_ORG DDR_R_RTL_ITEM_CMPNY DDR_R_RTL_VRTY DDR_R_UOMDDR_R_LKUP_MST

Step 8: Load Item/Business Unit Association

The Item Business Unit Association scenario (ITM_BSNS_UNT_ASC) loads data that identifies the SKUs each business unit is authorized to sell. When the Discovery mode system parameter is cleared, this table is used when validating sales and returns, and sales forecast fact data. When Discovery mode is selected, the fact data scenario inserts a record into this table automatically.

When you execute the item/BU association scenario, the system reads the source table and populates the target table. Before running the Item/Business Unit Association scenario, the Retailer’s organization and their related business units, as well as the manufacturer’s items must be loaded.

If any of the following fields are not populated, an error is written to the error table:

Retail Organization Code (RTL_ ORG_CD)
Retail Business Unit Code (RTL_BSNS_UNIT_CD)
Manufacturer SKU Item Number (MFG_SKU_ITEM_NBR)

The following key validations are required:

Retail Organization must be set up in the organization table (DDR_R_ORG).
Retail Business Unit must be valid.
Manufacturer SKU Item Number must be valid.

Scenario Name	DDR_ITM_BSNS_UNT_ASC
Source Tables	DDR_I_ITEM_BSNS_UNT_ASSC
Target Tables	DDR_R_ITEM_BSNS_UNT_ASSC
Dependent Tables	DDR_R_ORG DDR_R_ORG_BSNS_UNIT DDR_R_MFG_SKU_ITEM

Importing Fact Data

The Demand Signal Repository (DSR) data model is based on the Oracle retail data model, but it is designed to meet manufacturer’s requirements. The user can view data from either a manufacturer perspective 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/day. This low-level granularity gives the user the maximum flexibility to organize information by time or organizational dimensions. For cases in which a retailer submits data at an aggregate level, for example, by week or by a group of stores, DSR allocates data based on a predefined organization allocation or time-allocation pattern.

The DSR data model retains historical changes to dimensional (hierarchical) data, such as retailer organization hierarchy or item classification hierarchy. This feature, called Slowly Changing Dimensions (SCD) enables users to analyze data according to the hierarchy in effect at a specific time.

System Parameters

When processing fact data, the system uses the following system parameters to determine how the data should be processed.

Run Type for Fact Data

The Run Type for Fact Data parameter uses values similar to the Run Type for Reference Data parameter. However, in addition to Simulation and Partial modes, the Run Type for Fact Data parameter also supports an All or None mode. If Run Type for Fact Data is A (All or Nothing) and one or more errors is encountered with the set of records being loaded, then the system writes no records to the target table.

Discovery Mode

If the Discovery Mode system parameter is set to Yes, then the system automatically writes a record to the Business Unit / Item Association table (DDR_R_ITEM_BSNS_UNT_ASSC). If the Discovery Mode system parameter is set to No, then the system validates that the business unit is authorized to sell this item. If the business unit and item combination is not defined, the system writes a record to the error table.

Perform Duplicate Check

The Perform Duplicate Check system parameter determines what to do if duplicate data is received in a single run. For example, if two records exist in the Sales and Returns interface table for the same Business Unit/Item/Day, then:

If the Perform Duplicate Check system parameter is set to No, the last record processed overwrites any previous records. No errors are reported.
If the Perform Duplicate Check system parameter is set to Yes, the system reports any duplicates as errors and writes records to the error table.

Common Validations

The following common validations are used when processing fact data.

Unit of Measure

Data that reports quantity information must have an associated transaction unit of measure. If unit of measure is left blank, it is assumed to be Each (EA).
DSR provides you with the option to have an alternate unit of measure that you want to see quantities expressed.

For example, the same product is often sold in different container sizes. Soda may be sold by the carton, which contains twelve 12-ounce can, as well as individual two-liter bottles. In addition to knowing the units sold, you may also wish to know total volume (in liters) that was sold.
When an alternate unit of measure is used, a conversion factor must be set up between the transaction unit of measure and the alternate unit of measure; otherwise, the system will treat this as an error and load the corresponding error table.

Currency

Every manufacturer and retailer organization has an associated currency.
Fact data that reports monetary information is assumed to be in the retailer’s currency and must be converted to the manufacturer’s currency.
An exchange rate must be set up between the retailer’s currency and the manufacturer’s currency for the transaction date, even if the currency codes are the same; otherwise, the system treats this as an error and loads the corresponding error table.

For example, you would need to load conversion factor of 1 to go from the Retailer’s currency code of USD to the Manufacturer’s currency code of USD.
If you are loading sales forecast, you should load the future date currency exchange rates for your forecast transaction dates.

Item Number

Retailer SKU Number (RTL_SKU_ITEM_NBR) is a required field for all fact data scenarios.
Global Item ID is not required if a one-to-one relationship exists between retailer SKU number and Global Item ID.
If a one-to-many relationship exists between Retailer SKU Number and Global Item ID, then the Global Item ID is required.
The Global Item ID must be valid in the Manufacturer’s Item table (DDR_R_MFG_SKU_ITEM table).
Business Unit and Item must be active for the given transaction date.
Global Item ID/Type must be valid (appear in the DDR_R_MFG_SKU_ITEM table). If receiving competitor item sales, ensure that these items are set up in the Manufacturer’s item table.
The item must be in effect (active) for the transaction date being processed.

Business Unit / Item

Business Unit must be active (that is, effective) for the given transaction date.
Retailer SKU Item number must be valid (it must exist in the DDR_R_MFG_SKU_ITEM table).

Location

Inventory Location must be valid (it must exist in the DDR_R_INV_LOC table).
If the retailer provides no inventory location, then the business unit code should be populated as the Inventory Location code to make the load possible. A null Inventory Location code is reported as an error.

Fact Data Scenarios

The following fact data scenarios are used in the Demand Signal Repository implementation process.

Sales & Returns Scenario

The Retail Sales & Returns scenario (SLS_RTRN_INT_STG_TO_TGT) loads units sold, units returned, and their related monetary value and cost. Data can be reported at the business unit level (for example, store) or at a higher level in the organization for a given day or week. Sales reported at an aggregate level are allocated to the lowest level (by business unit per day).

Scenario Name	SLS_RTRN_INT_STG_TO_TGT
Source Tables	DDR_I_RTL_SL_RTN_ITEM
Target Tables	DDR_I_RTL_SL_RTN_ITEM

When you execute the sales and returns scenario, the system reads the source table and populates the target table listed here.

If any of the following fields are not populated, an error is written to the error table:

Record ID
Location Identifier
Location Identifier Flag
Retail SKU Item Number
Unit of Measure
Transaction Date
Global ID and Global ID Type are required if Retail SKU Item Number is associated to more than one Global ID / Global ID Type.

In addition to the validations common to all fact data, the following is a list of key validations:

Authorized Business Unit check
Location ID Flag

‘B’ (for Business Unit) or ‘O’ (for Organization)
Period Type

‘D’ (for day) or ‘W’ (for week)

If the Discovery Mode system parameter is set to No, the system validates that the Business Unit is authorized to sell this item. If the business unit/item combination is not defined, a record is written to the error table. If discovery mode is set to Yes, the system automatically writes a record to the Business Unit/Item Association table.

The location ID flag indicates whether the retailer is reporting data at the business unit level (that is, the lowest level in their organization hierarchy) or a higher level within their organization. Data reported at a higher level than business unit will be allocated down to the business unit level based on the pattern established in the Organization Allocation table. If location ID flag is not populated, it is assumed to be a ‘B’ (Business Unit) and no allocation is performed.

The period type indicates whether the retailer is reporting data on a weekly or a daily basis. Data reported on a weekly basis will be allocated down to day based on the pattern established in the Time Allocation table. If Period Type is not populated, it is assumed to be a ‘D’ (day) and no allocation is performed.

Shipments Scenario

The Shipments scenario (DDR_RTL_SHIP) loads shipped units and monetary amounts for a given retail business unit per item per day.

The following fields are required:

Record ID, Business Unit, Retail SKU Item Number, Unit of Measure, Transaction Date
Global ID and Global ID Type if Retail SKU Item Number is associated to more than one Global ID/Global ID Type

The common validations described earlier are performed for shipment data. No additional shipment specific validations are performed.

Scenario Name	DDR_RTL_SHIP
Source Tables	DDR_I_RTL_SHIP_ITEM
Target Tables	DDR_B_RTL_SHIP_ITEM_DAY

If any of the following fields are not populated, an error is written to the error table:

Record ID
Business Unit
Retail SKU Item Number
Unit of Measure
Transaction Date
Global ID and Global ID Type are required if Retail SKU Item Number is associated to more than one Global ID / Global ID Type.

The common validations described earlier are performed for shipment data. No additional shipment specific validations are performed.

Orders Scenario

The Orders scenario (DDR_RTL_ORDR) loads order quantities and monetary amounts for a given business unit per item per day. Orders (like Shipments) are reported against a specific store or distribution center. No allocation logic is supported for order data.

The following fields are required:

Record ID, Business Unit, Retail SKU Item Number, Unit of Measure, Transaction Date
Global ID and Global ID Type if Retail SKU Item Number is associated to more than one Global ID/Global ID Type

The common validations described previously are performed for shipment data. No additional shipment-specific validations are performed.

Scenario Name	DDR_RTL_ORDR
Source Tables	DDR_I_RTL_ORDR_ITEM
Target Tables	DDR_B_RTL_ORDR_ITEM_DAY

Item Inventory Scenario

The Item Inventory scenario (DDR_RTL_INV) loads on-hand, received, in-transit, back-ordered, and quality hold quantities, net costs, and retail amounts for a given business unit/item/location/day. Item Inventory, such as Shipments and Orders, is reported against a specific store or distribution center; therefore, no allocation logic is supported for order data.

The following fields are required:

Record ID, Retail Business Unit, Retail SKU Item Number, Inventory Location, Transaction Date
Global ID and Global ID Type if Retail SKU Item Number is associated to more than one Global ID / Global ID Type1

The following key validations are required:

Inventory location must be valid for the business unit.
The common validations described previously are performed for Item Inventory.

Scenario Name	DDR_RTL_INV
Source Tables	DDR_I_RTL_INV_ITEM
Target Tables	DDR_B_RTL_INV_ITEM_DAY

Promotion Plans Scenario

The Promotions Plans scenario (DDR_RTL_PRMTN_PLN) loads information about the promotions that are in effect at a store for an item during a given time period, as well as whether or not the promotion includes an associated price discount. No allocation logic is supported for promotions plans.

The following fields are required:

Record ID, Retail Business Unit, Retail SKU Item Number, Promotion Type, Promotion From Date, Promotion To Date, Promotion Price Amount
Global ID and Global ID Type if Retail SKU Item Number is associated to more than one Global ID/Global ID Type

The common validations described previously are performed for shipment data. No additional shipment-specific validations are performed.

Scenario Name	DDR_RTL_PRMTN_PLN
Source Tables	DDR_I_PRMTN_PLN
Target Tables	DDR_B_PRMTN_PLN

Sales Forecast Scenario

The Sales Forecast scenario (FORECAST_INT_STG_TO_TGT) loads forecasted sales units and their related monetary value. Data can be reported at the business unit level (for example, store) or at a higher level in the organization for a given day or week. Sales reported at an aggregate level are allocated to the lowest level (business unit/day).

The following fields are required:

Record ID, Forecast Number, Forecast Type, Location Identifier, Location Identifier Flag, Retail SKU Item Number, Unit of Measure, Transaction Date
Global ID and Global ID Type if Retail SKU Item Number is associated to more than one Global ID/Global ID Type

The following key validations are required:

Authorized Business Unit check
Location ID Flag: B (business unit) or O (organization)
Period Type: D (day) or W (week)

If the Discovery Mode system parameter is set to No, the system validates that the business unit is authorized to sell (and, therefore, report sales forecast) for this item. If the business unit and item combination is not defined, the system writes a record to the error table. If the Discovery Mode system parameter is set to Yes, the system automatically writes a record to the Business Unit / Item Association table.

The Location ID flag indicates whether the retailer is reporting data at the business unit level (that is, the lowest level in its organization hierarchy) or at a higher level within the organization. Data reported at a higher level than business unit will be allocated down to the business unit level based on the pattern established in the Organization Allocation table. If the Location ID flag is not populated, the system assumes it is a B (business unit) and performs no allocation.

The Period Type flag indicates whether the retailer is reporting data weekly or daily. Data reported weekly will be allocated down to day, based on the pattern established in the Time Allocation table. If the Period Type flag is not populated, the system assumes it is a D (day) and performs no allocation.

Scenario Name	FORECAST_INT_STG_TO_TGT
Source Tables	DDR_I_SLS_FRCST_ITEM
Target Tables	DDR_B_SLS_FRCST_ITEM_DAY

Goals and Targets Scenario

The Goals and Targets scenario (DDR_GOAL_TARGET) loads goals, thresholds, and external measures. Goals, thresholds, and external measures can be loaded for any level of the retail organization hierarchy, manufacturer item hierarchy, or retailer item hierarchy. These measures can also be loaded for any time period (year, month, quarter, week, or day).

The following fields are required:

Record ID
Manufacturer’s Org
Retailer Org Code and Level
Mfg Item Hierarchy Code and Level
Time Hierarchy Code and Level
Retailer Item Hierarchy Code and Level
Measure Name, Type and Value

If any of these fields is not populated, the system writes it to the error table.

Note: When setting goals or thresholds, or defining external measures based on item hierarchy, you must choose either the manufacturer’s or the retailer’s item hierarchy, but not both.

The following key validations are required:

Measure Type Name:

EXTERNAL, GOAL, THRESHOLD
Measure Name:

GROSS MARGIN, % RETURNS, CATEGORY MONETARY SALES, DEDUCTION AMT, DEDUCTION BALANCE, IN STOCK %, INVENTORY COVER MAX, INVENTORY COVER MIN, INVENTORY TURNS, INVOICE ACCURACY, ITEM DATA ACCURACY, ITEM DATA SYNCHRONIZATION, LATE PAYMENT, MONETARY SALES, NO OF DEDUCTIONS, ON TIME DELIVERY, ON-HAND INVENTORY, ORDER CHANGE %, ORDER CYCLE TIME, PAYMENT DAYS, PERFECT ORDER %, RETAIL GROSS MARGIN (%), SALES FORECAST ACCURACY (%), SERVICE LEVEL, UNIT SALE
Organization level:

ORGANIZATION, CHAIN, AREA, REGION, DISTRICT, BU (or NA)
Manufacturer Item Hierarchy Level:

COMPANY, GROUP, DIVISION, CLASS, SUBCLASS, ITEM, SKU (or NA)
Retailer Item Hierarchy Level:

COMPANY, GROUP, DIVISION, DEPARTMENT, CLASS, SUBCLASS, ITEM, SKU (or NA)
Time Hierarchy Level:

YEAR, QUARTER, MONTH, WEEK, DAY

Note: When loading goals or thresholds, or when importing external measures, you must use one or more of the appropriate organization, item, or time hierarchy level codes listed. NA indicates that the organization, manufacturer item, or retailer item hierarchy is not applicable for the goal, threshold, or external measure.

Scenario Name	DDR_GOAL_TARGET
Source Tables	DDR_I_GOAL_TARGET
Target Tables	DDR_B_GOAL_TARGET

Loading Syndicated Consumption Data

The following diagram illustrates the required setup steps for loading syndicated consumption data:

the picture is described in the document text

Setup the TDLinx File in ODI

Before loading syndicated store data, you must provide a .csv file using the filename created under External File location (such as DDR_TDLINX_STR_DATA.csv) in strict TDLinx format, as specified in Nielsen’s TD Data Dictionary. The file must be a location named DDR_TDLINX_FILE_LOCATION, and the input file is expected to be a csv file with placeholders for all values.

Load Org Hierarchy and Store Names

From Oracle Data Integrator, load the Org reference data into the DDR_R_ORG table using the DDR_ORG_HCHY scenario. The following tables are loaded with the ORG hierarchy reference data and TDLinx store names using the DDR_ORG_HCHY scenario:

DDR_R_ORG
DDR_R_ORG_CHAIN
DDR_R_ORG_AREA
DDR_R_ORG_REGION
DDR_R_ORG_DISTRICT
DDR_R_ORG_BSNS_UNIT

Load TDLinx Data using the TDLINX_ORG_HCHY Scenario

From Oracle Data Integrator, use the OWB_TDLINX_ORG_HCHY scenario to load TDLinx store data into the DDR_R_ORG_BSNS_UNIT table.

The data can be loaded in discrete steps by first running the Map to populate the interface table and then running Map to load the reference table. The table DDR_I_ORG_BSNS_UNIT in DDR_SYND_STR_DATA_FI_MAP is populated with the data in the TDLinx file. Validations like duplicate records check, referential integrity check, NOT Null check and others will be done by map DDR_ORG_HCHY_IF_MAP.

Implementing User-Defined Fields

The process to load Item and Organization Hierarchies is:

Create the source files, or modify existing files, in the required format and include 20 User-Defined Attribute Fields for:
- Manufacturer Item Hierarchy
- Manufacturer SKU Item
- Retailer Item Hierarchy
- Retailer SKU Item
- Organization Hierarchy
- Organization Business Unit
Load data to appropriate interface files.
Run MAP or scenario to move data to target tables.
- Correct any errors.
- Rerun the MAP or scenario.

Oracle Data Integrator (ODI) contains the MAPs and scenarios to load data from the interface tables into DSR. After loading the data into the interface tables, run the MAPs or scenario to move the data from the interface tables into the target or error tables. The new MAP DDR_MFG_RTL_ORG_HCHY_ATTR_MAP loads the Manufacturer Item, Retailer Item and Organization Attributes to their target or error tables.

The new scenario DDR_USER_ATTR loads the Manufacturer Item, Retailer Item and Organization Attributes to their target or error tables. Scenarios can be used, instead of MAPs, to load the data in stages to facilitate troubleshooting.

FILE_INF scenarios move data from the source to the interface or error table.
ERR_TO_INF scenarios move corrected data from the error table to the interface table.
MAIN_PF scenarios move corrected data from the interface table to the target table.

Setup Flow – Item Clusters, Retailer Clusters and Customer Promotion Plan

The process to load Item Clusters, Retailer Clusters and Customer/Retailer Promotion Plans is:

Create the source files, or modify existing files, in the required format and include 20 User-Defined Attribute Fields for:
- Item Clusters
- Retailer Clusters
- Customer/Retailer Promotion Plans
Load data to the appropriate interface files.
Run MAP or scenario to move data to Target Tables.
- Correct any errors.
- Rerun the MAP or scenario.

Note: The existing MAPs for Item Clusters, Retailer Clusters and Customer Promotions have been modified to include the attributes loaded from user-defined fields. The existing scenarios for Item Clusters, Retailer Clusters and Customer Promotions have also been modified to include the attributes.

Scenarios and MAPs

The scenarios and MAPs for loading Retailer Item, Manufacturer Item, Organization, Item Clusters, Retail Clusters and Customer Promotions are below. Items in bold are used for loading the Attributes from the User-Defined Fields.

Scenario	Map	Entity Loaded
DDR_MFG_RTL_ORG_HCHY	DDR_MFG_RTL_ORG_HCHY_ATTR_MAP	Attributes for Manufacturer & Retailer Organization Hierarchies
DDR_ORG_HCHY	DDR_ORG_HCHY_IF_MAP	Organization Hierarchy
DDR_MFG_ITEM_HCHY	DDR_MFG_ITEM_HCHY_IF_MAP	Manufacturer Item Hierarchy
DDR_RTL_ITEM_HCHY	DDR_RTL_ITEM_HCHY_IF_MAP	Retailer Item Hierarchy
DDR_ORG_HCHY	DDR_ORG_HCHY_IF_MAP	Organization Hierarchy
DDR_ITEM_CLSTR	DDR_ITEM_CLSTR_IF_MAP	Item Clusters
DDR_RTL_CLSTR	DDR_RTL_CLSTR_IF_MAP	Retail Clusters
DDR_MFG_PRMTN	DDR_MFG_PRMTN_IF_MAP	Manufacturer Promotion

Manufacturer Promotions

Manufacturers are responsible for loading promotion data into the interface table. Interface tables are provided along with DSR. Users are free to use these tables as templates for designing their own interface tables.

The Organizations and SKUs included in the Manufacturer Promotion detail file must be loaded into the Reference tables prior to loading the promotion detail. Duplicate records are moved to an error table with only ‘Duplicate’ error message. That is, no further checking is done on these records.

When associating promotions to sales data:

The Promotion Status Field is informational only and is not used in the mapping process.
Only promotions that the Manufacturer wants considered in the mapping process should be loaded into DSR. DSR attempts to map POS data to any relevant Manufacturer Promotion regardless of the Promotion Status field.
When the POS data for a SKU is aggregated to provide a daily total, if one unit of an SKU is sold on promotion and 99 units are not sold on promotion, the POS data aggregated by SKU, Store and Day will be flagged as promoted for that SKU.

Manufacturer Promotion Tables and Scenarios

Manufacturer Promotion dimension data is loaded into interface table DDR_I_MFG_PRMTN, and the source file must contain:

Promotion Code
Promotion Name
Promotion Type
Dates must be in yyyymmdd format

Data is then loaded into the target table, DDR_R_MFG_PRMTN, by running one of the following:

DDR_MFG_PRMTN_IF_MAP
DDR_MFG_PRMTN Scenario

Manufacturer Promotion detail data is loaded into interface table DDR_I_MFG_PRMTN_DTL. The Manufacturer Promotion detail source file must contain:

Promotion Code
Retail Organization Code
Manufacturer SKU Item Number
Promotion From (Start) Date
Promotion End Date
Dates should be in yyyymmdd format

The promotion details are then loaded into the target table, DDR_R_MFG_PRMTN_DTL , by running one of the following:

DDR_MFG_PRMTN_IF_MAP
DDR_MFG_PRMTN Scenario

Run the scenario PRM_ASSC>DDR_MFG_PRMTN_ASSC after data successfully loads to target tables. Enter the parameter for number of days prior to the current date that sales should be considered for association to the promotions.

Once data is loaded and the Manufacturer Promotions are associated with the Customer’s sales, run the DSR_OTHER_PROCESS_FLOWS scenario to refresh the Materialized Views.

Third Party Distributors

the picture is described in the document text

In order to implement an Third Party Distributors, you need to complete the following steps:

Run the set up utility to load any new pre-seeded values.
Define any geography regions or sub-regions in which the distribution centers reside.
Load any new units of measures.

Step 1: Run Setup Utility

Use the DDR_SETUP_UTIL.SETUP setup utility to load pre-seeded data into DSR. This includes:

System parameters
Error messages
Lookup values

The set up utility will load the organization type of “DST” into the look up master which is required for the third party distributor feature to be used.

Step 2: Load Geographic Regions

Use the GEOGRPHY_RGN_HCHY scenario to load the appropriate geographic regions. If you want to analyze distributor data by geographic region or sub-region, you’ll need to make sure they are set up prior to loading the distribution organization. If you are already using geographical regions and sub-regions to analyze customer data, you may already have the necessary regions and sub-regions set up.

Step 3: Load Units of Measure

Use the DDR_UOM scenario to set up the required UOM codes. Note that if you have already loaded unit of measure codes for manufacturer and retailer items, chances are that you already have the necessary UOM codes set up. If, however, a distributor item uses a unit of measure that’s not in use by either the manufacturer or retailer, then you’ll need to set that unit of measure up as well.

Manufacturer Shipments

In order to implement Manufacturer Shipments, you need to complete the following steps:

Run the set up utility to load any new pre-seeded values.
Define any geography regions or sub-regions in which the distribution centers reside.
Load any new units of measure.

Step 1: Run Setup Utility

Use the Manufacturer Shipments utility to load pre-seeded data into DSR. This includes:

System parameters
Error messages
Look up values

The set up utility will load the organization type of “MFG” into the look up master which is required for the Manufacturer Organization to be loaded. The DSR set up utility is a PL/SQL Procedure (DDR_SETUP_UTIL.SETUP).

Step 2: Load Geographic Regions

Distribution Centers may be associated to a geographic sub-region which aggregates to a geographic region, and are used to analyze business units (stores or distribution center) by geography. Use the scenario DDR_GEO_HCHY to load Geographic regions.

Note: If you want to analyze manufacturer data by geographic region or sub-region, you’ll need to make sure they are set up prior to loading the manufacturer’s organization. If you are already using geographical regions and sub-regions to analyze customer data, you may already have the necessary regions and sub-regions set up.

Step 3: Load Units of Measure

The last step is to load your units of measure using the scenario DDR_UOM.

Alternate Organization Hierarchy

the picture is described in the document text

In order to implement an Alternate Organization Hierarchy, you need to complete the following steps:

Determine how many different Alternate Organization Hierarchies you need and load an AOH code for each one used. It’s likely that you will only have one or two sources of Alternate Organization Hierarchy Data.
Define how many levels each Alternate Organization Hierarchy has and the name of each level.
Modify the OBIEE repository to expose each Alternate Organization Hierarchy and their appropriate levels.
Modify existing dashboards to use an Alternate Organization Hierarchy or create new dashboards that use the Alternate Organization Hierarchy.

Step 1: Load Alternate Organization Hierarchy Code

The first step is to load an Alternate Organization Hierarchy Code. An AOH Code uniquely identifies each Alternate Organization Hierarchy in use. For example, the value of ‘SDP’ can be assigned to an alternate organization hierarchy received from a syndicated data provider, or the value of ‘CBO’ can be assigned to an AOH that represents the Customer’s Buying Organization. When you execute the AOH Attributes scenario, the system reads the source table and populates the target table listed here.

There is no prerequisite data which needs to be loaded prior to loading the Alternate Organization Hierarchy Code.

Scenario Name:	DDR_AOH
Source Table(s):	DDR_I_DDR_AOH
Target Table(s):	DDR_R_ORG_HCHY
Dependent Table(s):	None
Required Fields:	AOH Code AOH Name
Key Validations:	There are no additional validations beyond checking for the required fields and reporting any duplicate records. Duplicate record checking is standard within DSR. A duplicate record error will occur when the same AOH Code appears more than once in the interface table.

Step 2: Define Alternate Organization Hierarchy Levels

This is a planning step, and the outcome determines what changes (if any) are needed to the DSR repository. For each source of alternate organization hierarchy information, you need to determine the maximum number of levels to be exposed. The sample Alternate Organization Hierarchy contains four levels plus the business unit. The existing Alternate Organization Hierarchy structure supports up to 10 levels, plus business unit. If you have an Alternate Organization Hierarchy over 10 levels (not including business unit), then additional data model changes will be required.

Next you will need to assign names to each level in the Alternate Organization Hierarchy. In a ragged hierarchy, level names may be generic. For example, in the sample Alternate Organization Hierarchy level names are simply called, level 1, 2, 3, and so on. In a more fixed taxonomy, you may want to assign more specific names to each level, like “National Account”, “Regional Account”, or “Planning Account”.

Then you’ll want to determine what account attributes should be exposed at each level. In this example, we have exposed the same set of attributes at each level, but you may choose to expose different attributes at each level. In addition to the attributes shown here, there are a set of 20 user-defined attributes (10 character and 10 numeric), which can be exposed and named according to their use.

Step 3: Modify OBIEE Repository

The third step is to modify the OBIEE repository based on the requirements defined in step two. DSR exposed four levels of an Alternate Organization Hierarchy. If additional levels are needed, or if attributes need to be renamed, someone with OBIEE administration experience will perform the following changes:

Create a materialized view (MV) containing level five alternate organization hierarchy information. Existing alternate organization hierarchy materialized views can be used as a model.
Add the newly created MV to the connection pool in the physical layer under the Alternate Organization Hierarchy dimension.
Add a new child object between Level four and the Business Unit in the business model and mapping layer. The business unit should always be the last level in the Alternate Organization dimension.
Drag and drop the needed columns over from the physical layer to the Business Model and Mapping layer. For example, Level five Account ID and Level five Name.
Drag and drop the desired columns into the presentation layer and rename accordingly.
Once you have completed changes to the repository they must be deployed to the BI server.
Finally, the existing Reference Data Complete Refresh scenario need to be modified to include the refresh of the new MV created in the first step.

For more information on updating , see Configuring the Alternative Organization Hierarchy in OBIEE

Step 4: Modify or Create Dashboards

The final set up step is to modify existing dashboards or create new dashboards which use the Alternate Organization Hierarchy. Any of the existing out-of-the-box DSR queries which use the customer organization hierarchy can be modified to use a desired level within the Alternate Organization Hierarchy.