Implementing Demand Signal Repository

Implementation Overview

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

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:

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:

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:

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:

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):

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:

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

The following key validations are required:

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:

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

The following key validations are required:

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.

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_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.

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_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:

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:

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:

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):

The following key validation is required:

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):

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):

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

The following key validations are required:

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:

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

The following key validations are required:

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:

The following key validations are required:

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:

The following key validations are required:

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:

The following key validations are required:

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:

The following key validations are required:

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:

The following key validation is required:

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:

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:

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

The following key validations are required:

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:

The following key validations are required:

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:

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

The following key validations are required:

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:

The following key validations are required:

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:

Common Validations

The following common validations are used when processing fact data.

Unit of Measure

Currency

Item Number

Business Unit / Item

Location

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:

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

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:

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:

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:

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:

The following key validations are required:

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:

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:

The following key validations are required:

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:

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:

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:

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:

  1. 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

  2. Load data to appropriate interface files.

  3. 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.

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

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

  1. 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

  2. Load data to the appropriate interface files.

  3. 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:

Manufacturer Promotion Tables and Scenarios

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

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

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

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

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:

  1. Run the set up utility to load any new pre-seeded values.

  2. Define any geography regions or sub-regions in which the distribution centers reside.

  3. 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:

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:

  1. Run the set up utility to load any new pre-seeded values.

  2. Define any geography regions or sub-regions in which the distribution centers reside.

  3. 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:

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:

  1. 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.

  2. Define how many levels each Alternate Organization Hierarchy has and the name of each level.

  3. Modify the OBIEE repository to expose each Alternate Organization Hierarchy and their appropriate levels.

  4. 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:

  1. Create a materialized view (MV) containing level five alternate organization hierarchy information. Existing alternate organization hierarchy materialized views can be used as a model.

  2. Add the newly created MV to the connection pool in the physical layer under the Alternate Organization Hierarchy dimension.

  3. 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.

  4. 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.

  5. Drag and drop the desired columns into the presentation layer and rename accordingly.

  6. Once you have completed changes to the repository they must be deployed to the BI server.

  7. 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.