6 Transformations

The Retail Analytics and Planning is a data-driven set of applications performing many transformations and calculations as part of normal operations. Review this chapter to learn about the most common types of data transformation activities occurring within the platform that may impact the data you send into the platform and the results you see as an end user.

Aggregate Tables in RI

Retail Insights accepts most data at a common base intersection of item, location, and date. However, downstream applications such as Planning or AI Foundation may need this data at higher levels of aggregation than that. To support those data needs, RI pre-aggregates the incoming fact data to certain higher levels, depending on the requirements of the consuming application. For Planning purposes, all fact data is aggregated to a common level of item, location, and fiscal week before it is exported for downstream consumption. Review the sections below to learn more about how data moves through Retail Insights for other applications to use.

Table Structures

If you are currently loading data into RI and need to access database tables for debugging or validation purposes, there are naming and format conventions used on each aggregate table. A base intersection table is abbreviated using the following notations:

Table 6-1 RI Base Fact Structure

Table Name Component	Explanation
W_	Most RI tables start with a “W” to denote a core data warehouse table.
IT	Abbreviation for Item
LC	Abbreviation for Location
DY	Abbreviation for Day
WK	Abbreviation for Week
_D	Dimensional tables end with “D”
_F	Base intersection fact tables end with “F”
_A	Aggregate tables end with “A”

Using the above notation, you may interpret the table W_RTL_SLS_IT_LC_WK_A as “Sales aggregate table at the item/location/week intersection”.

Key Columns

Most fact tables in RI use the same key column structure, which consists of two types of internal identifiers. The first identifier is referred to as a WID value. The WID on a fact table is a foreign key reference to a dimension table’s ROW_WID column. For example, a PROD_WID column in a sales table is referring to the ROW_WID on W_PRODUCT_D (the product dimension table). Joining the WIDs on a fact and a dimension will allow you to look up user-facing descriptors for the dimensions, such as the product number.

The second identifier is known as SCD1_WID and refers to slowly changing dimensions, which is a common data warehousing concept. The IDs on the SCD1_WID columns are carried forward through reclassifications and other dimensional changes, allowing you to locate a single product throughout history, even if it has numerous records in the parent dimension table. For example, joining PROD_SCD1_WID from a sales table with SCD1_WID on W_PRODUCT_D will receive all instances of that product’s data throughout history, even if the product has several different ROW_WID entries due to reclassifications, which insert new records to the dimension for the same item.

The other core structure to understand is Date WIDs (key column DT_WID). These also join with the ROW_WID of the parent dimension (W_MCAL_DAY_D usually), but the format of the WID allows you to extract the date value directly if needed, without table joins. The standard DT_WID value used is a combination of 1 + date in YYYYMMDD + 000. For example, 120210815000 is the DT_WID value for “August 15, 2021”.

Transformations from RI to Planning

Data Filtering and Conversions

In addition to simply aggregating the incoming fact data from item/location/date to item/location/week level, it is also important to understand what RI is doing with the data as it moves from the input files to the outbound interfaces. The table below summarizes the transformations and business logic applied to shared facts used by RAP applications.

Table 6-2 RI Shared Data Transformations

Transformation	Explanation
Currency Conversion	As part of the nightly batch, RI will use exchange rate information to convert all incoming data from the source currency to the primary business currency. All data sent to downstream applications is in the primary currency. The RI data model maintains separate columns for both local and primary currency amounts for BI usage.
Tax Handling	RI includes non-US taxes, such as VAT, in the sales retail amounts based on the indicators set up in the source system (such as Sales Audit). When sending the sales data to Planning and AI Foundation, the default sales values may include VAT and only specific VAT-exclusive fields will remove it.
Transaction Date Usage	All fact data coming into the system includes a transaction date on the record. RI aggregates from day to week level using transaction dates and does not alter or re-assign any records to different dates from what is provided. Transaction data in the past will be added to their historical week in the aggregates, no matter how far back it is dated.
Pack Item Handling	Downstream applications are currently only interested in the component item level, so RI will not send any data for pack items to other applications. Pack item sales must be spread to the component item level and loaded into the Sales Pack interface if this data is required for AI Foundation or Planning. All inventory, purchase order, and transaction data must be loaded at the component item level only.
Stockholding Locations	Inventory data for Planning is only exported for stockholding locations. A store indicated as a non-stockholding location on the location dimension will not be included in outbound data. Physical warehouses which are not stockholding (because you use virtual warehouses) will also not be included.
Future On Order	Planning applications require a forward-looking view of purchase orders based on the OTB EOW Date. RI accepts the actual purchase order details on the interfaces but will then transform the on-order amounts to be future-dated using the provided OTB EOW Dates. Orders which are past the OTB date will be included in the first EOW date, they will never be in the past.
Include On Order	Purchase Order data is limited by the Include On Order Flag on the Order Head interface. A value of N will not be included in the calculations for Planning.
Orderable Items	Purchase Order data is limited by the Orderable Flag on the Product interface. A value of N will not be included in the calculations for Planning.
Inventory Adjustment Types	RI accepts 3 types of inventory adjustments using the codes 22, 23, and 41. For Planning, only the first two codes are exported. Code 22 relates to Shrink and code 23 relates to Non-Shrink.
Inventory Receipt Types	RI accepts 3 types of inventory receipts using the codes 20, 44~T, and 44~A. For Planning, all codes are sent but the 44s are summed together. Code 20 relates to purchase order receipts. Code 44 relates to Transfer receipts and Allocation receipts.
Inventory Transfer Types	RI accepts 3 types of transfers using the codes N, B, and I (normal, book, and intercompany). All three types are sent to planning along with the type codes.

Data Mappings

When you are generating input files to RAP, you may also want to know which columns are being moved to the output and how that data translates from what you see in the file to what you see in Planning applications. The list of mappings below describes how the data in the Retail Insights data model is exported to PDS.

Note:

Conversions and filters listed in the prior section of this chapter apply to all of this data (for example, data may be stored in local currency in RI but is always converted to the primary currency for export).

Sales Mapping

Data for sales is loaded from the SALES.csv file or from RMFCS (Sales Audit). The primary RI table is the week-level aggregate generated by the historical load process. All data mappings in this area are split out by retail type. Any measure having reg/pro/clr in the name are being filtered on that retail type code as part of the export. When you provide input data to RAP, you specify the retail type code as R, P, or C, and those values are used here to determine the output. A custom 4^th option (using type code O for Other) is also allowed, as long as you extend the W_XACT_TYPE_D dimension in RI to have the extra type code. Other sales are only included in the Total Sales measures in the PDS export.

Measure	Target Table	Target Column	RI Data Source
Gross Reg Sales Units	W_PDS_SLS_IT_LC_WK_A	SALES_REG_UNITS	W_RTL_SLS_IT_LC_WK_A.SLS_QTY + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY
Gross Pro Sales Units	W_PDS_SLS_IT_LC_WK_A	SALES_PRO_UNITS	W_RTL_SLS_IT_LC_WK_A.SLS_QTY + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY
Gross Clr Sales Units	W_PDS_SLS_IT_LC_WK_A	SALES_CLR_UNITS	W_RTL_SLS_IT_LC_WK_A.SLS_QTY + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY
Gross Reg Sales Cost	W_PDS_SLS_IT_LC_WK_A	SALES_REG_COST	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-SLS_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-SLSPK_PROF_AMT)
Gross Pro Sales Cost	W_PDS_SLS_IT_LC_WK_A	SALES_PRO_COST	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-SLS_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-SLSPK_PROF_AMT)
Gross Clr Sales Cost	W_PDS_SLS_IT_LC_WK_A	SALES_CLR_COST	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-SLS_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-SLSPK_PROF_AMT)
Gross Reg Sales Retail	W_PDS_SLS_IT_LC_WK_A	SALES_REG_RETAIL	W_RTL_SLS_IT_LC_WK_A.SLS_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT
Gross Pro Sales Retail	W_PDS_SLS_IT_LC_WK_A	SALES_PRO_RETAIL	W_RTL_SLS_IT_LC_WK_A.SLS_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT
Gross Clr Sales Retail	W_PDS_SLS_IT_LC_WK_A	SALES_CLR_RETAIL	W_RTL_SLS_IT_LC_WK_A.SLS_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT
Gross Reg Sales Tax	W_PDS_SLS_IT_LC_WK_A	SALES_REG_TAX	W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT
Gross Pro Sales Tax	W_PDS_SLS_IT_LC_WK_A	SALES_PRO_TAX	W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT
Gross Clr Sales Tax	W_PDS_SLS_IT_LC_WK_A	SALES_CLR_TAX	W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT
Net Reg Sales Units	W_PDS_SLS_IT_LC_WK_A	NET_SALES_REG_UNITS	(W_RTL_SLS_IT_LC_WK_A.SLS_QTY-RET_QTY) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY-RETPK_QTY)
Net Pro Sales Units	W_PDS_SLS_IT_LC_WK_A	NET_SALES_PRO_UNITS	(W_RTL_SLS_IT_LC_WK_A.SLS_QTY-RET_QTY) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY-RETPK_QTY)
Net Clr Sales Units	W_PDS_SLS_IT_LC_WK_A	NET_SALES_CLR_UNITS	(W_RTL_SLS_IT_LC_WK_A.SLS_QTY-RET_QTY) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY-RETPK_QTY)
Net Reg Sales Cost	W_PDS_SLS_IT_LC_WK_A	NET_SALES_REG_COST	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) - (W_RTL_SLS_IT_LC_WK_A.SLS_PROFIT_AMT-RET_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) - (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_PROF_AMT-RETPK_PROF_AMT)
Net Pro Sales Cost	W_PDS_SLS_IT_LC_WK_A	NET_SALES_PRO_COST	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) - (W_RTL_SLS_IT_LC_WK_A.SLS_PROFIT_AMT-RET_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) - (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_PROF_AMT-RETPK_PROF_AMT)
Net Clr Sales Cost	W_PDS_SLS_IT_LC_WK_A	NET_SALES_CLR_COST	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) - (W_RTL_SLS_IT_LC_WK_A.SLS_PROFIT_AMT-RET_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) - (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_PROF_AMT-RETPK_PROF_AMT)
Net Reg Sales Retail	W_PDS_SLS_IT_LC_WK_A	NET_SALES_REG_RETAIL	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT)
Net Pro Sales Retail	W_PDS_SLS_IT_LC_WK_A	NET_SALES_PRO_RETAIL	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT)
Net Clr Sales Retail	W_PDS_SLS_IT_LC_WK_A	NET_SALES_CLR_RETAIL	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT)
Net Reg Sales Retail Excluding VAT	W_PDS_SLS_IT_LC_WK_A	NET_SALES_REG_RETAIL_VAT_EXCL	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) - (W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT-RET_TAX_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) - (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT-RETPK_TAX_AMT)
Net Pro Sales Retail Excluding VAT	W_PDS_SLS_IT_LC_WK_A	NET_SALES_PRO_RETAIL_VAT_EXCL	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) - (W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT-RET_TAX_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) - (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT-RETPK_TAX_AMT)
Net Clr Sales Retail Excluding VAT	W_PDS_SLS_IT_LC_WK_A	NET_SALES_CLR_RETAIL_VAT_EXCL	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) - (W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT-RET_TAX_AMT) +(W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) - (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT-RETPK_TAX_AMT)
Net Reg Sales Tax	W_PDS_SLS_IT_LC_WK_A	NET_SALES_REG_TAX	(W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT-RET_TAX_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT-RETPK_TAX_AMT)
Net Pro Sales Tax	W_PDS_SLS_IT_LC_WK_A	NET_SALES_PRO_TAX	(W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT-RET_TAX_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT-RETPK_TAX_AMT)
Net Clr Sales Tax	W_PDS_SLS_IT_LC_WK_A	NET_SALES_CLR_TAX	(W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT-RET_TAX_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT-RETPK_TAX_AMT)
Returns Reg Units	W_PDS_SLS_IT_LC_WK_A	RETURNS_REG_UNITS	W_RTL_SLS_IT_LC_WK_A.RET_QTY + W_RTL_SLSPK_IT_LC_WK_A.RETPK_QTY
Returns Pro Units	W_PDS_SLS_IT_LC_WK_A	RETURNS_PRO_UNITS	W_RTL_SLS_IT_LC_WK_A.RET_QTY + W_RTL_SLSPK_IT_LC_WK_A.RETPK_QTY
Returns Clr Units	W_PDS_SLS_IT_LC_WK_A	RETURNS_CLR_UNITS	W_RTL_SLS_IT_LC_WK_A.RET_QTY + W_RTL_SLSPK_IT_LC_WK_A.RETPK_QTY
Returns Reg Cost	W_PDS_SLS_IT_LC_WK_A	RETURNS_REG_COST	(W_RTL_SLS_IT_LC_WK_A.RET_AMT-RET_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.RETPK_AMT-RETPK_PROF_AMT)
Returns Pro Cost	W_PDS_SLS_IT_LC_WK_A	RETURNS_PRO_COST	(W_RTL_SLS_IT_LC_WK_A.RET_AMT-RET_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.RETPK_AMT-RETPK_PROF_AMT)
Returns Clr Cost	W_PDS_SLS_IT_LC_WK_A	RETURNS_CLR_COST	(W_RTL_SLS_IT_LC_WK_A.RET_AMT-RET_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.RETPK_AMT-RETPK_PROF_AMT)
Returns Reg Retail	W_PDS_SLS_IT_LC_WK_A	RETURNS_REG_RETAIL	W_RTL_SLS_IT_LC_WK_A.RET_AMT + W_RTL_SLSPK_IT_LC_WK_A.RETPK_AMT
Returns Pro Retail	W_PDS_SLS_IT_LC_WK_A	RETURNS_PRO_RETAIL	W_RTL_SLS_IT_LC_WK_A.RET_AMT + W_RTL_SLSPK_IT_LC_WK_A.RETPK_AMT
Returns Clr Retail	W_PDS_SLS_IT_LC_WK_A	RETURNS_CLR_RETAIL	W_RTL_SLS_IT_LC_WK_A.RET_AMT + W_RTL_SLSPK_IT_LC_WK_A.RETPK_AMT
Returns Reg Tax	W_PDS_SLS_IT_LC_WK_A	RETURNS_REG_TAX	W_RTL_SLS_IT_LC_WK_A.RET_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.RETPK_TAX_AMT
Returns Pro Tax	W_PDS_SLS_IT_LC_WK_A	RETURNS_PRO_TAX	W_RTL_SLS_IT_LC_WK_A.RET_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.RETPK_TAX_AMT
Returns Clr Tax	W_PDS_SLS_IT_LC_WK_A	RETURNS_CLR_TAX	W_RTL_SLS_IT_LC_WK_A.RET_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.RETPK_TAX_AMT
Total Gross Sales Units	W_PDS_SLS_IT_LC_WK_A	SALES_TOTAL_UNITS	W_RTL_SLS_IT_LC_WK_A.SLS_QTY + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY
Total Gross Sales Cost	W_PDS_SLS_IT_LC_WK_A	SALES_TOTAL_COST	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-SLS_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-SLSPK_PROF_AMT)
Total Gross Sales Retail	W_PDS_SLS_IT_LC_WK_A	SALES_TOTAL_RETAIL	W_RTL_SLS_IT_LC_WK_A.SLS_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT
Total Gross Sales Tax	W_PDS_SLS_IT_LC_WK_A	SALES_TOTAL_TAX	W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT
Total Net Sales Units	W_PDS_SLS_IT_LC_WK_A	NET_SALES_TOTAL_UNITS	(W_RTL_SLS_IT_LC_WK_A.SLS_QTY-RET_QTY) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY-RETPK_QTY)
Total Net Sales Cost	W_PDS_SLS_IT_LC_WK_A	NET_SALES_TOTAL_COST	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) - (W_RTL_SLS_IT_LC_WK_A.SLS_PROFIT_AMT-RET_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) - (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_PROF_AMT-RETPK_PROF_AMT)
Total Net Sales Retail	W_PDS_SLS_IT_LC_WK_A	NET_SALES_TOTAL_RETAIL	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT)
Total Net Sales Tax	W_PDS_SLS_IT_LC_WK_A	NET_SALES_TOTAL_TAX	(W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT-RET_TAX_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT-RETPK_TAX_AMT)

Gross Sales Mapping

A separate sales aggregate is also provided for Demand Forecasting (RDF), which filters and aggregates the sales differently from the base extract for the other Planning applications. This export includes only gross sales and has a single set of measure columns with a separate field for the retail type (reg/pro/clr). The data is filtered to include only sales for forecastable items and non-warehouse locations.

Measure	Target Table	Target Column	RI Data Source
Gross Sales Units	W_PDS_GRS_SLS_IT_LC_WK_A	SALES_UNITS	W_RTL_SLS_IT_LC_WK_A.SLS_QTY + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY
Gross Sales Cost	W_PDS_GRS_SLS_IT_LC_WK_A	SALES_COST	(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-SLS_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-SLSPK_PROF_AMT)
Gross Sales Retail	W_PDS_GRS_SLS_IT_LC_WK_A	SALES_RETAIL	W_RTL_SLS_IT_LC_WK_A.SLS_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT
Gross Sales Tax	W_PDS_GRS_SLS_IT_LC_WK_A	SALES_TAX	W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT

Inventory Position Mapping

Data is loaded from the INVENTORY.csv file or from RMFCS. The primary RI table is the week-level aggregate generated by the historical load process.

Measure	Target Table	Target Column	RI Data Source
Inventory Units	W_PDS_INV_IT_LC_WK_A	REGULAR_INVENTORY_UNITS	W_RTL_INV_IT_LC_WK_A.INV_SOH_QTY + INV_IN_TRAN_QTY
Inventory Cost	W_PDS_INV_IT_LC_WK_A	REGULAR_INVENTORY_COST	W_RTL_INV_IT_LC_WK_A.INV_SOH_COST_AMT + INV_IN_TRAN_COST_AMT
Inventory Retail	W_PDS_INV_IT_LC_WK_A	REGULAR_INVENTORY_RETAIL	W_RTL_INV_IT_LC_WK_A.INV_SOH_RTL_AMT + INV_IN_TRAN_RTL_AMT
Unit Cost	W_PDS_INV_IT_LC_WK_A	UNIT_COST	W_RTL_INV_IT_LC_WK_A.INV_UNIT_COST_AMT
Average Cost	W_PDS_INV_IT_LC_WK_A	AV_COST	W_RTL_INV_IT_LC_WK_A.INV_AVG_COST_AMT
Unit Retail	W_PDS_INV_IT_LC_WK_A	UNIT_RETAIL	W_RTL_INV_IT_LC_WK_A.INV_UNIT_RTL_AMT

On Order Mapping

Data is loaded from the ORDER_HEAD.csv and ORDER_DETAIL.csv files or from RMFCS. Purchase order data is transformed from the raw order line details into a forward-looking total on-order amount based on the OTB end-of-week date on the order. Data is also filtered to remove orders not flagged as Include On Order.

Measure	Target Table	Target Column	RI Data Source
On Order Units	W_PDS_PO_ONORD_IT_LC_WK_A	ON_ORDER_UNITS	W_RTL_PO_ONORD_IT_LC_DY_F.PO_ONORD_QTY
On Order Cost	W_PDS_PO_ONORD_IT_LC_WK_A	ON_ORDER_COST	W_RTL_PO_ONORD_IT_LC_DY_F.PO_ONORD_COST_AMT_LCL
On Order Retail	W_PDS_PO_ONORD_IT_LC_WK_A	ON_ORDER_RETAIL	W_RTL_PO_ONORD_IT_LC_DY_F.PO_ONORD_RTL_AMT_LCL

Markdown Mapping

Data is loaded from the MARKDOWN.csv file or from RMFCS. The primary RI table is the week-level aggregate generated by the historical load process.

Measure	Target Table	Target Column	RI Data Source
Regular Markdown	W_PDS_MKDN_IT_LC_WK_A	REG_MARKDOWN_RETAIL	W_RTL_MKDN_IT_LC_WK_A.MKDN_AMT where retail type = R
Promotion Markdown (Regular)	W_PDS_MKDN_IT_LC_WK_A	PROMO_MARKDOWN_RETAIL_REG	W_RTL_MKDN_IT_LC_WK_A.MKDN_AMT where retail type = P and CLEARANCE_FLG = N
Promotion Markdown (Clearance)	W_PDS_MKDN_IT_LC_WK_A	PROMO_MARKDOWN_RETAIL_CLEAR	W_RTL_MKDN_IT_LC_WK_A.MKDN_AMT where retail type = P and CLEARANCE_FLG = Y
Clearance Markdown	W_PDS_MKDN_IT_LC_WK_A	CLEAR_MARKDOWN_RETAIL	W_RTL_MKDN_IT_LC_WK_A.MKDN_AMT where retail type = C
Markup	W_PDS_MKDN_IT_LC_WK_A	MARKUP	W_RTL_MKDN_IT_LC_WK_A.MKUP_AMT where retail type = R
Markup Cancel	W_PDS_MKDN_IT_LC_WK_A	MARKUP_CANCEL	W_RTL_MKDN_IT_LC_WK_A.MKUP_CAN_AMT
Markdown Cancel	W_PDS_MKDN_IT_LC_WK_A	MARKDOWN_CANCEL	W_RTL_MKDN_IT_LC_WK_A.MKDN_CAN_AMT
Intercompany Markup	W_PDS_MKDN_IT_LC_WK_A	INTERCOMPANY_MARKUP	W_RTL_MKDN_IT_LC_WK_A.MKUP_AMT where retail type = I
Intercompany Markdown	W_PDS_MKDN_IT_LC_WK_A	INTERCOMPANY_MARKDOWN	W_RTL_MKDN_IT_LC_WK_A.MKDN_AMT where retail type = I

Wholesale/Franchise Mapping

Data is loaded from the SALES_WF.csv file or from RMFCS. The primary RI table is the week-level aggregate generated by the historical load process.

Measure	Target Table	Target Column	RI Data Source
Franchise Sales Units	W_PDS_SLSWF_IT_LC_WK_A	FRANCHISE_SALES_UNITS	W_RTL_SLSWF_IT_LC_WK_A.SLSWF_QTY
Franchise Sales Cost	W_PDS_SLSWF_IT_LC_WK_A	FRANCHISE_SALES_COST	W_RTL_SLSWF_IT_LC_WK_A.SLSWF_ACQ_COST_AMT
Franchise Sales Retail	W_PDS_SLSWF_IT_LC_WK_A	FRANCHISE_SALES_RETAIL	W_RTL_SLSWF_IT_LC_WK_A.SLSWF_AMT
Franchise Sales Tax	W_PDS_SLSWF_IT_LC_WK_A	FRANCHISE_SALES_TAX	W_RTL_SLSWF_IT_LC_WK_A.SLSWF_TAX_AMT
Franchise Returns Units	W_PDS_SLSWF_IT_LC_WK_A	FRANCHISE_RETURNS_UNITS	W_RTL_SLSWF_IT_LC_WK_A.RETWF_QTY
Franchise Returns Cost	W_PDS_SLSWF_IT_LC_WK_A	FRANCHISE_RETURNS_COST	W_RTL_SLSWF_IT_LC_WK_A.RETWF_ACQ_COST_AMT
Franchise Returns Retail	W_PDS_SLSWF_IT_LC_WK_A	FRANCHISE_RETURNS_RETAIL	W_RTL_SLSWF_IT_LC_WK_A.RETWF_AMT
Franchise Returns Tax	W_PDS_SLSWF_IT_LC_WK_A	FRANCHISE_RETURNS_TAX	W_RTL_SLSWF_IT_LC_WK_A.RETWF_TAX_AMT
Franchise Restocking Fee	W_PDS_SLSWF_IT_LC_WK_A	FRANCHISE_RESTOCK_FEE	W_RTL_SLSWF_IT_LC_WK_A.RETWF_RSTK_FEE_AMT
Franchise Markdown	W_PDS_SLSWF_IT_LC_WK_A	WF_MARKDOWN_RETAIL	W_RTL_SLSWF_IT_LC_WK_A.SLSWF_MKDN_AMT - RETWF_MKDN_AMT
Franchise Markup	W_PDS_SLSWF_IT_LC_WK_A	WF_MARKUP_RETAIL	W_RTL_SLSWF_IT_LC_WK_A.SLSWF_MKUP_AMT - RETWF_MKUP_AMT

Inventory Adjustments Mapping

Data is loaded from the ADJUSTMENT.csv file or from RMFCS. The primary RI table is the week-level aggregate generated by the historical load process.

Measure	Target Table	Target Column	RI Data Source
Shrink Units	W_PDS_INVADJ_IT_LC_WK_A	SHRINK_UNITS	W_RTL_INVADJ_IT_LC_WK_A.INVADJ_QTY where adj type = 22
Shrink Cost	W_PDS_INVADJ_IT_LC_WK_A	SHRINK_COST	W_RTL_INVADJ_IT_LC_WK_A.INVADJ_COST_AMT where adj type = 22
Shrink Retail	W_PDS_INVADJ_IT_LC_WK_A	SHRINK_RETAIL	W_RTL_INVADJ_IT_LC_WK_A.INVADJ_RTL_AMT where adj type = 22
Non-Shrink Adjustments Units	W_PDS_INVADJ_IT_LC_WK_A	NON_SHRINK_ADJ_UNITS	W_RTL_INVADJ_IT_LC_WK_A.INVADJ_QTY where adj type = 23
Non-Shrink Adjustments Cost	W_PDS_INVADJ_IT_LC_WK_A	NON_SHRINK_ADJ_COST	W_RTL_INVADJ_IT_LC_WK_A.INVADJ_COST_AMT where adj type = 23
Non-Shrink Adjustments Retail	W_PDS_INVADJ_IT_LC_WK_A	NON_SHRINK_ADJ_RETAIL	W_RTL_INVADJ_IT_LC_WK_A.INVADJ_RTL_AMT where adj type = 23

Inventory Receipts Mapping

Data is loaded from the RECEIPT.csv file or from RMFCS. The primary RI table is the week-level aggregate generated by the historical load process.

Measure	Target Table	Target Column	RI Data Source
PO Receipt Units	W_PDS_INVRC_IT_LC_WK_A	PO_RECEIPT_UNITS	W_RTL_INVRC_IT_LC_WK_A.INVRC_QTY where rcpt type code = 20
PO Receipt Cost	W_PDS_INVRC_IT_LC_WK_A	PO_RECEIPT_COST	W_RTL_INVRC_IT_LC_WK_A.INVRC_COST_AMT where rcpt type code = 20
PO Receipt Retail	W_PDS_INVRC_IT_LC_WK_A	PO_RECEIPT_RETAIL	W_RTL_INVRC_IT_LC_WK_A.INVRC_RTL_AMT where rcpt type code = 20
Transfer/Allocation Receipt Units	W_PDS_INVRC_IT_LC_WK_A	TSF_RECEIPT_UNITS	W_RTL_INVRC_IT_LC_WK_A.INVRC_QTY where rcpt type code = 44~A or 44~T
Transfer/Allocation Receipt Cost	W_PDS_INVRC_IT_LC_WK_A	TSF_RECEIPT_COST	W_RTL_INVRC_IT_LC_WK_A.INVRC_COST_AMT where rcpt type code = 44~A or 44~T
Transfer/Allocation Receipt Retail	W_PDS_INVRC_IT_LC_WK_A	TSF_RECEIPT_RETAIL	W_RTL_INVRC_IT_LC_WK_A.INVRC_RTL_AMT where rcpt type code = 44~A or 44~T
PO Receipt Custom Measures 1 - 20	W_PDS_INVRC_IT_LC_WK_A	PO_FLEX1_NUM_VALUE through PO_FLEX20_NUM_VALUE	W_RTL_INVRC_IT_LC_WK_A.FLEX1_NUM_VALUE through W_RTL_INVRC_IT_LC_WK_A.FLEX20_NUM_VALUE where rcpt type code = 20
Transfer Receipt Custom Measures 1 - 20	W_PDS_INVRC_IT_LC_WK_A	TSF_FLEX1_NUM_VALUE through TSF_FLEX20_NUM_VALUE	W_RTL_INVRC_IT_LC_WK_A.FLEX1_NUM_VALUE through W_RTL_INVRC_IT_LC_WK_A.FLEX20_NUM_VALUE where rcpt type code = 44~A or 44~T

Inventory Transfers Mapping

Data is loaded from the TRANSFER.csv file or from RMFCS. The primary RI table is the week-level aggregate generated by the historical load process.

Measure	Target Table	Target Column	RI Data Source
Transfer Type	W_PDS_INVTSF_IT_LC_WK_A	TSF_TYPE	W_XACT_TYPE_D.W_XACT_TYPE_CODE in (N,B,I) (for normal/book/intercompany tsfs)
Transfer Inbound Units	W_PDS_INVTSF_IT_LC_WK_A	TSF_IN_UNITS	W_RTL_INVTSF_IT_LC_WK_A.TSF_TO_LOC_QTY
Transfer Inbound Cost	W_PDS_INVTSF_IT_LC_WK_A	TSF_IN_COST	W_RTL_INVTSF_IT_LC_WK_A.TSF_TO_LOC_COST_AMT
Transfer Inbound Retail	W_PDS_INVTSF_IT_LC_WK_A	TSF_IN_RETAIL	W_RTL_INVTSF_IT_LC_WK_A.TSF_TO_LOC_RTL_AMT
Transfer Outbound Units	W_PDS_INVTSF_IT_LC_WK_A	TSF_OUT_UNITS	W_RTL_INVTSF_IT_LC_WK_A.TSF_FROM_LOC_QTY
Transfer Outbound Cost	W_PDS_INVTSF_IT_LC_WK_A	TSF_OUT_COST	W_RTL_INVTSF_IT_LC_WK_A.TSF_FROM_LOC_COST_AMT
Transfer Outbound Retail	W_PDS_INVTSF_IT_LC_WK_A	TSF_OUT_RETAIL	W_RTL_INVTSF_IT_LC_WK_A.TSF_FROM_LOC_RTL_AMT

Inventory RTVs Mapping

Data is loaded from the RTV.csv file or from RMFCS. The primary RI table is the week-level aggregate generated by the historical load process.

Measure	Target Table	Target Column	RI Data Source
RTV Units	W_PDS_INVRTV_IT_LC_WK_A	RTV_UNITS	W_RTL_INVRTV_IT_LC_WK_A.RTV_QTY
RTV Cost	W_PDS_INVRTV_IT_LC_WK_A	RTV_COST	W_RTL_INVRTV_IT_LC_WK_A.RTV_COST_AMT
RTV Retail	W_PDS_INVRTV_IT_LC_WK_A	RTV_RETAIL	W_RTL_INVRTV_IT_LC_WK_A.RTV_RTL_AMT
RTV Restocking Fee	W_PDS_INVRTV_IT_LC_WK_A	RTV_RESTOCK_FEE	W_RTL_INVRTV_IT_LC_WK_A.RTV_RSTCK_COST_AMT

Inventory Reclass Mapping

Data is loaded from the INV_RECLASS.csv file or from RMFCS. The primary RI table is the week-level aggregate generated by the historical load process.

Measure	Target Table	Target Column	RI Data Source
Reclass In Units	W_PDS_INVRECLASS_IT_LC_WK_A	RECLASS_IN_UNITS	W_RTL_INVRCL_IT_LC_WK_A.RCL_TO_LOC_QTY
Reclass In Cost	W_PDS_INVRECLASS_IT_LC_WK_A	RECLASS_IN_COST	W_RTL_INVRCL_IT_LC_WK_A.RCL_TO_LOC_COST_AMT
Reclass In Retail	W_PDS_INVRECLASS_IT_LC_WK_A	RECLASS_IN_RETAIL	W_RTL_INVRCL_IT_LC_WK_A.RCL_TO_LOC_RTL_AMT
Reclass Out Units	W_PDS_INVRECLASS_IT_LC_WK_A	RECLASS_OUT_UNITS	W_RTL_INVRCL_IT_LC_WK_A.RCL_FROM_LOC_QTY
Reclass Out Cost	W_PDS_INVRECLASS_IT_LC_WK_A	RECLASS_OUT_COST	W_RTL_INVRCL_IT_LC_WK_A.RCL_FROM_LOC_COST_AMT
Reclass Out Retail	W_PDS_INVRECLASS_IT_LC_WK_A	RECLASS_OUT_RETAIL	W_RTL_INVRCL_IT_LC_WK_A.RCL_FROM_LOC_RTL_AMT

Deal Income Mapping

Data is loaded from the DEAL_INCOME.csv file or from RMFCS. The primary RI table is the week-level aggregate generated by the historical load process.

Measure	Target Table	Target Column	RI Data Source
Deal Income Sales Based	W_PDS_DEALINC_IT_LC_WK_A	DEAL_INCOME_SALES	W_RTL_DEALINC_IT_LC_WK_A.DEAL_SLS_COST_AMT
Deal Income Purchases Based	W_PDS_DEALINC_IT_LC_WK_A	DEAL_INCOME_PURCHASES	W_RTL_DEALINC_IT_LC_WK_A.DEAL_PRCH_COST_AMT

Intercompany Margin Mapping

Data is loaded from the IC_MARGIN.csv file or from RMFCS. The primary RI table is the week-level aggregate generated by the historical load process.

Measure	Target Table	Target Column	RI Data Source
Intercompany Margin	W_PDS_ICM_IT_LC_WK_A	INTERCOMPANY_MARGIN	W_RTL_ICM_IT_LC_WK_A.IC_MARGIN_AMT

Transformations in Planning

Planning applications allow the loading of fact data at the load intersection level (such as Item and Location) but uses the data within the application at an aggregated level (called the base intersection). In MFP, though all facts are loaded at the item level, it only needs data to plan at the Subclass level. The data will be aggregated from item level to subclass level for all the configured metrics to be directly used by the application. During re-classifications (such as when one item is moved from one subclass to another subclass), after the new hierarchy details are imported into MFP it also triggers re-classification of all fact data. Re-aggregation of fact data then happens only for shared facts having different load and base intersections.

In Planning applications, fact data is grouped as dynamic fact groups based mainly on the base intersection and interface details, as defined in the Data Interface of the Application Configuration. RI and AI Foundation use a relational data model, whereas Planning applications internally use a hierarchical data model. Data from RAP, stored using the relational data model, needs to be transformed to be loaded into Planning applications. A similar approach is necessary for data coming out of planning applications to AI Foundation or external sources. These data transformations happen as part of the interfaces defined in interface.cfg (Interfaces Configuration File), which is a mapping of dimensions and measures from Planning applications to external system table columns. Refer to the application-specific Implementation Guides for more information about Planning Data Interfaces.