E Retail Analytics Interfaces Files

Note that ORASE consumes this data directly from RADM and is not needed if RADM is properly configured and populated. It is included purely for reference.

Interfaces

Product Hierarchy

The hierarchy that groups products into related groups.

Table E-1 Product Hierarchy File

Field	Type	Description
Merchandise Hierarchy External ID	Text	Customer-visible Merchandise ID, used to denote any non-leaf node of the product hierarchy. (Source: lvl#anc_prodcat_id or top_lvl_prodcat_id from w_prod_cat_dh)
Merchandise Hierarchy PK	Text	Primary key used to identify a level of the merchandise hierarchy. (Source: ROW_WID from w_prod_cat_dh)
Merchandise Hierarchy External Key	Text	Externally suitable integration ID for the merchandise hierarchy. (Source integration_id from w_prod_cat_dh)
Effective From Date	Text	Starting date the source record is effective from. (Source: effective_from_dt in w_prod_cat_dh)
Effective To Date	Text	Ending date the source record is effective from. (Source: effective_to_dt in w_prod_cat_dh)
Current Flag	Text	Y/N flag to indicate if the record is considered a currently active hierarchy record. (Source: current_flg in w_prod_cat_dh)
Delete Flag	Text	Y/N flag to indicate if the record is considered deleted. (Source: delete_flg in w_prod_cat_dh)
Merchandise Hierarchy Name	Text	Name of the merchandise hierarchy. (Source: domain_member_name from w_domain_member_lkp_tl)
Merchandise Hierarchy Description	Text	Description of the merchandise hierarchy. (Source: domain_member_descr from w_domain_member_lkp_tl)

Product

Product is the terminal node of the product/merchandise hierarchy.

Table E-2 Product File

Field	Type	Description
Merchandise External ID	Text	External Merchandise ID, used to denote the leaf node of the product hierarchy. (Source:prod_num from w_product_d)
Merchandise PK	Text	Primary key used to identify a SKU of the merchandise hierarchy. (Source: SCD1_WID from w_product_d)
Merchandise External Key	Text	Externally suitable integration ID for the merchandise hierarchy. (Source integration_id from w_product_d)
Parent Product Hierarchy ID	Text	FK to the parent hierarchy this product belongs to. (Source prod_cat5_wid from w_product_d)
Effective From Date	Date	Starting date the source record was effective from. (Source: effective_from_dt in w_product_d)
Effective To Date	Date	Ending date the source record was effective from. (Source: effective_to_dt in w_product_d)
Current Flag	Text	Y/N flag to indicate if the record is considered a currently active hierarchy record. (Source: current_flg in w_product_d)
Delete Flag	Text	Y/N flag to indicate if the record is considered deleted. (Source: delete_flg in w_product_d)
Merchandise Name	Text	Name of the SKU. (Source: product_name from w_product_d_tl)
Merchandise Description	Text	Description of the SKU. (Source: product_descr from w_product_d_tl)

Product Attributes

Generic attributes for products.

Table E-3 Product Attributes File

Field	Type	Description
Merchandise PK	Text	Primary key used to identify a SKU of the merchandise hierarchy.
Attribute Number Value	Number	Numeric value for a generic product attribute. (Source: product_attr#_num_value from w_product_attr_d)
Attribute Text Value	Text	Text value for a generic product attribute. (Source: product_attr#_name from w_product_attr_d)

User Defined Product Attributes

User defined attributes for products.

Table E-4 User Defined Product Attributes File

Field	Type	Description
Merchandise PK	Text	Primary key used to identify a SKU of the merchandise hierarchy.(Source: prod_scd1_wid from w_rtl_item_grp1_d)
Attribute ID	Text	External ID for the UDA. (Source: flex_attrib_1_char from w_rtl_item_grp1_d)
Attribute Value ID	Text	External ID for the UDA value. (Source: flex_attrib_4_char from w_rtl_item_grp1_d)
Attribute Value Descr	Text	Descriptive value of the attribute value. (Source: Domain_member_name from w_rtl_domain_member_lkp_tl where domain_code = ITEM_UDA)

Organization Hierarchy

A hierarchy under which the organization can be organized.

Table E-5 Organization Hierarchy File

Field	Type	Description
Organization Hierarchy External ID	Text	Customer visible ID used to identify an organization hierarchy level member. (Source: org_top_num/org_hier#_num columns from w_int_org_dh)
Organization Hierarchy PK	Text	Primary key used to identify a level of the organization hierarchy. (Source: scd1_wid from w_int_org_dh)
Organization Hierarchy External Key	Text	Externally suitable integration ID for the location hierarchy. (Source integration_id from w_int_org_dh)
Effective From Date	Date	Starting date the source record was effective from. (Source: effective_from_dt in w_int_org_dh)
Effective To Date	Date	Ending date the source record was effective from. (Source: effective_to_dt in w_int_org_dh)
Current Flag	Text	Y/N flag to indicate if the record is considered a currently active hierarchy record. (Source: current_flg in w_int_org_dh)
Delete Flag	Text	Y/N flag to indicate if the record is considered deleted. (Source: delete_flg in w_int_org_dh)
Organization Hierarchy Name	Text	Name of the organization hierarchy. (Source: domain_member_name from w_domain_memeber_lkp_tl)
Organization Hierarchy Description	Text	Description of the organization hierarchy. (Source: domain_member_descr from w_domain_member_lkp_tl)

Store Locations

Store locations is the terminal node of the organization hierarchy

Table E-6 Store Locations File

Field	Type	Description
Location External ID	Text	Customer visible ID used to identify an organization location. (Source: Appropriate org_hier#_num column from w_int_org_dh)
Location PK	Text	Primary key used to identify a store of the location hierarchy. (Source: SCD1_WID from w_int_org_dh)
Location External Key	Text	Externally suitable integration ID for the location hierarchy. (Source integration_id from w_int_org_dh)
Parent Organization Hierarchy ID	Text	FK to the organization hierarchy this location belongs to. (Source: appropriate org_hier#_num column from w_int_org_dh)
Effective From Date	Date	Starting date the source record was effective from. (Source: effective_from_dt in w_int_org_dh)
Effective To Date	Date	Ending date the source record was effective from. (Source: effective_to_dt in w_int_org_dh)
Current Flag	Text	Y/N flag to indicate if the record is considered a currently active hierarchy record. (Source: current_flg in w_int_org_dh)
Delete Flag	Text	Y/N flag to indicate if the record is considered deleted. (Source: delete_flg in w_int_org_dh)
Location Name	Text	Name of the Location. (Source: org_name from w_int_org_d_tl)
Location Description	Text	Description of the Location. (Source: org_descr from w_int_org_d_tl)

Location Attributes

Generic attributes for locations.

Table E-7 Location Attributes File

Field	Type	Description
Location PK	Text	Primary key used to identify a store of the location hierarchy.
Attribute Number Value	Number	Numeric value for a generic location attribute. (Source: org_attr#_num_value from w_int_org_attr_d)
Attribute Text Value	Text	Text value for a generic location attribute. (Source: org_attr#_name from w_int_org_attr_d)

Customer Segments

The list of customer segments that enable aggregate processing of customer data.

Table E-8 Customer Segments File

Field	Type	Description
Customer Segment External ID	Text	Customer visible ID used to identify an customer segment. (Source: custseg_id from w_rtl_custseg_d)
Customer Segment PK	Text	Primary key used to identify a customer segment. (Source: row_wid from w_rtl_custseg_d)
Customer Segment External Key	Text	Externally suitable integration ID for the customer segment. (Source integration_id from w_rtl_custseg_d)
Effective From Date	Date	Starting date the source record was effective from. (Source: effective_from_dt in w_rtl_custseg_d)
Effective To Date	Date	Ending date the source record was effective from. (Source: effective_to_dt in w_rtl_custseg_d)
Current Flag	Text	Y/N flag to indicate if the record is considered a currently active hierarchy record. (Source: current_flg in w_rtl_custseg_d)
Delete Flag	Text	Y/N flag to indicate if the record is considered deleted. (Source: delete_flg in w_rtl_custseg_d)
Customer Segment Name	Text	Name of the customer segment. (Source: custseg_name from w_rtl_custseg_d)

Customer Segment Customer Members

Associates customer IDs to customer segment IDs, so that a customer's identity can be classified under a customer segment.

Table E-9 Customer Segment Customer Members File

Field	Type	Description
Customer Segment ID	Text	ID of the customer segment (Source: custseg_hier_id from w_rtl_cust_custseg_d)
Customer ID	Text	ID of the customer (Source: customer_id from w_rtl_cust_custseg_d)

Customer Segment

This interface provides the hierarchy members for the Consumer Segment dimension.

Table E-10 Customer Segment File

Field	Type	Description
Consumer Segment External ID	Text	Customer visible ID used to identify a Consumer Segment.
Consumer Segment PK	Text	Primary key used to identify a Consumer Segment.
Consumer Segment External Key	Text	Externally suitable integration ID for the Consumer Segment.
Effective From Date	Date	Starting date the source record was effective from.
Effective To Date	Date	Ending date the source record was effective from.
Current Flag	Text	Y/N flag to indicate if the record is considered a currently active record.
Delete Flag	Text	Y/N flag to indicate if the record is considered deleted.
Consumer Segment Name	Text	Name of the Consumer Segment.

Consumer Segment/Location/Product Mapping

An allocation of store locations to any number of product hierarchy members per consumer segment.

Table E-11 Consumer Segment/Location/Product Mapping File

Field	Type	Description
Consumer Segment PK	Text	Primary key of a consumer segment.
Location PK	Text	Primary key used to identify a store location.
Product Hierarchy PK	Text	Primary key used to identify a member of the product hierarchy.
Allocation Percent	Number	Percent of this location and product intersection that is allocated to this consumer segment.

Fiscal Year

Fiscal calendar year level data.

Table E-12 Fiscal Year File

Field	Type	Description
Fiscal Year PK	Text	Primary Key suitable for identifying the fiscal year. (Source: row_wid from w_mcal_year_d)
Fiscal Year	Number	Externally known identifier for the fiscal year. (Source: mcal_year from w_mcal_year_d)
Fiscal Year Name	Text	The name associated with the fiscal year. (Source: mcal_per_name_year from w_mcal_year_d)
Start Date	Date	The date the fiscal year starts. (Source: mcal_year_start_dt from w_mcal_year_d)
End Date	Date	The date the fiscal year ends. (Source: mcal_year_end_dt from w_mcal_year_d)

Fiscal Quarter

Fiscal calendar's quarter level data.

Table E-13 Fiscal Quarter File

Field	Type	Description
Fiscal Quarter PK	Text	Primary Key suitable for identifying the fiscal quarter. (Source: row_wid from w_mcal_qtr_d)
Fiscal Quarter	Number	Externally known identifier for the fiscal quarter. (Source: mcal_qtr from w_mcal_qtr_d)
Fiscal Quarter Name	Text	The name associated with the fiscal quarter. (Source: mcal_per_name_qtr from w_mcal_qtr_d)
Start Date	Date	The date the fiscal quarter starts. (Source: mcal_qtr_start_dt from w_mcal_qtr_d)
End Date	Date	The date the fiscal quarter ends. (Source: mcal_qtr_end_dt from w_mcal_qtr_d)

Fiscal Period

Fiscal calendar's period level data.

Table E-14 Fiscal Period File

Field	Type	Description
Fiscal Period PK	Text	Primary Key suitable for identifying the fiscal period. (Source: row_wid from w_mcal_period_d)
Fiscal Period	Number	Externally known identifier for the fiscal period. (Source: mcal_period from w_mcal_period_d)
Fiscal Period Name	Text	The name associated with the fiscal period. (Source: mcal_per_name_period from w_mcal_period_d)
Start Date	Date	The date the fiscal period starts. (Source: mcal_period_start_dt from w_mcal_period_d
End Date	Date	The date the fiscal period ends. (Source: mcal_period_end_dt from w_mcal_period_d)

Fiscal Day

Fiscal Calendar day information.

Table E-15 Fiscal Day File

Field	Type	Description
Fiscal Day PK	Text	Primary Key suitable for identifying the fiscal day. (Source: row_wid from w_mcal_day_d)
Fiscal Day	Number	Externally known identifier for the fiscal day. (Source: mcal_day_of_year from w_mcal_day_d)
Date	Date	The date of the fiscal day. (Source: mcal_day_dt from w_mcal_day_d)

Fiscal Week

Fiscal calendar week level data.

Table E-16 Fiscal Week File

Field	Type	Description
Fiscal Week PK	Text	Primary Key suitable for identifying the fiscal week. (Source: row_wid from w_mcal_week_d)
Fiscal Week	Number	Externally known identifier for the fiscal week. (Source: mcal_week from w_mcal_week_d)
Fiscal Week Name	Text	The name associated with the fiscal week. (Source: mcal_per_name_week from w_mcal_week_d)
Start Date	Date	The date the fiscal week starts. (Source: mcal_week_start_dt from w_mcal_week_d)
End Date	Date	The date the fiscal week ends. (Source: mcal_week_end_dt from w_mcal_week_d)

Trade Area Hierarchy Levels

Provides the level descriptions for the trade area alternate location hierarchy.

Table E-17 Trade Area Hierarchy Levels File

Field	Type	Description
Level Number	Number	A number that represents the level of the hierarchy.
Level Description	Text	Descriptive text for the hierarchy level.
Source Level Description	Text	The level description for the level of the location hierarchy this hierarchy converges with.

Trade Area Hierarchy

An alternate hierarchy for the organization hierarchy, which will group locations together by trading area.

Table E-18 Trade Area Hierarchy File

Field	Type	Description
Trade Area Hierarchy External ID	Text	Customer visible ID used to identify a trade area hierarchy member.
Trade Area Hierarchy PK	Text	Primary key used to identify a level of the trade area hierarchy.
Trade Area Hierarchy External Key	Text	Externally suitable integration ID for the trade area hierarchy.
Effective From Date	Date	Starting date the source record was effective from.
Effective To Date	Date	Ending date the source record was effective from.
Current Flag	Text	Y/N flag to indicate if the record is considered a currently active hierarchy record.
Delete Flag	Text	Y/N flag to indicate if the record is considered deleted.
Trade Area Hierarchy Name	Text	Name of the trade area hierarchy.
Organization Hierarchy FK	Text	At the point this hierarchy converges with the organization hierarchy, a link to the organization hierarchy should be provided here.

Category Management Group Hierarchy

This is an alternate hierarchy for the product hierarchy, which can be used to group products together into groups that a customer would perceive as the same type of product.

Table E-19 Category Management Group Hierarchy File

Field	Type	Description
Category Management Group Hierarchy External ID	Text	Customer visible Category Management Group ID, used to denote any non-leaf node of the Category Management Group hierarchy. (Source: lvl#anc_prodcat_id or top_lvl_prodcat_id from w_prod_cat_dh)
Category Management Group Hierarchy PK	Text	Primary key used to identify a level of the Category Management Group hierarchy. (Source: ROW_WID from w_prod_cat_dh)
Category Management Group Hierarchy External Key	Text	Externally suitable integration ID for the Category Management Group hierarchy. (Source integration_id from w_prod_cat_dh)
Effective From Date	Date	Starting date the source record was effective from. (Source: effective_from_dt in w_prod_cat_dh)
Effective To Date	Date	Ending date the source record was effective from. (Source: effective_to_dt in w_prod_cat_dh)
Current Flag	Text	Y/N flag to indicate if the record is considered a currently active hierarchy record. (Source: current_flg in w_prod_cat_dh)
Delete Flag	Text	Y/N flag to indicate if the record is considered deleted. (Source: delete_flg in w_prod_cat_dh)
Category Management Group Hierarchy Name	Text	Name of the Category Management Group hierarchy. (Source: domain_member_name from w_domain_memeber_lkp_tl)
Category Management Group Hierarchy Description	Text	Description of the Category Management Group hierarchy. (Source: domain_member_descr from w_domain_member_lkp_tl)
Merchandise Hierarchy FK	Text	At the point this hierarchy converges with the merchandise hierarchy, a link to the merchandise hierarchy should be provided here.

Category Management Group Hierarchy Levels

This interface defines the levels that are part of the alternate category management group hierarchy.

Table E-20 Category Management Group Hierarchy Levels File

Field	Type	Description
Level Number	Number	A number that represents the level of the hierarchy.
Level Description	Text	Descriptive text for the hierarchy level.
Source Level Description	Text	The level description that this alternate hierarchy converges with the primary product hierarchy.

Location/Product Price and Cost

Current (at a given date) price and cost information provided at the product store level.

Table E-21 Location/Product Price and Cost File

Field	Type	Description
Product PK	Text	Primary key used to identify a product.
Location PK	Text	Primary key used to identify a store location.
Price	Number	Available price for the given date for the product/store combination.
Cost	Number	Available cost for the given date for the product/store combination.

Sales Transaction Data

Sales data at the product, location, day, and transaction level.

Table E-22 Sales Transaction Data File

Field	Type	Description
Product PK	Text	Primary key used to identify a product.(Source: prod_scd1_wid in w_rtl_sls_trx_it_lc_dy_f)
Location PK	Text	Primary key used to identify a store location. (Source: org_scd1_wid in w_rtl_sls_trx_it_lc_dy_f)
Day PK	Number	Primary key used to identify the date of the transaction. (Source: dt_wid in w_rtl_sls_trx_it_lc_dy_f)
Transaction ID	Text	Unique transaction identifier for a collection of sales for a given sales transaction. (Source: sls_trx_id in w_rtl_sls_trx_it_lc_dy_f)
Sales Amount		Amount of sales expressed in global currency (Source: Sls_amt_lcl / loc_exchange_rate in w_sls_trx_it_lc_dy_f)
Sales Units		Units sold. (Source: sls_qty in w_rtl_sls_trx_it_lc_dy_f)
Sales Profit		Profit amount expressed in global currency (Source: sls_profit_amt_lcl / loc_exchange_rate in w_rtl_sls_trx_it_lc_dy_f)
Customer ID	Text	PK for the customer who completed the transaction. (Source: customer_wid in w_rtl_sls_trx_it_lc_dy_f)
Promotion ID	Text	Primary Key for the promotion component for this sales record. (Source: promo_comp_wid in w_rtl_slspr_trx_it_lc_dy_f)

Item Ranging

Source: W_RTL_IT_LC_D table.

Table E-23 Item Ranging File

Field	Type	Description
Product ID	VARCHAR2(30)	This is a foreign key to the W_INT_ORG_D table using the SCD Type 1 WID.(Source: PRODUCT_SCD1_WID)
Location ID	VARCHAR2(30)	This is a foreign key to the W_INT_ORG_D table using the SCD Type 1 WID. (Source: ORG_SCD1_WID
Ranged indicator	VARCHAR2(30)	This is the indicator from the source table that states that even though the item is not present in a particular location it exists in inventory. This assists in acceptance of item not ranged in a location to be accepted in case of customer or cross channel returns. (Source: RANGED)
Status of the item	VARCHAR2(1)	Current status of item at the store. Valid values are: A = Active (item is valid and can be ordered) and sold, I = Inactive (item is valid but cannot be ordered) or sold, C = Discontinued (item is valid and sellable but no longer order-able, and D = Delete (item is invalid and cannot be ordered or sold). (Source: ITEM_STATUS)
Location type	VARCHAR2(1)	Type of location in the location field. Valid values are S (store), W (warehouse), and E (external finisher). (Source: LOC_TYPE)
Start effective date	Date	(Source: EFFECTIVE_FROM_DT)
End effective date	Date	(Source: EFFECTIVE_TO_DT)