5 Transformations
The Retail Analytics Platform 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 Science 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 5-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 5-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 Science, 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 Science 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 |
Orderable Items |
Purchase Order data is limited by the Orderable Flag on the Product
interface. A value of |
Inventory Adjustment Types |
RI accepts 3 types of inventory adjustments using the codes |
Inventory Receipt Types |
RI accepts 3 types of inventory receipts using the codes |
Inventory Transfer Types |
RI accepts 3 types of transfers using the 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.
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 |
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 |
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 Science 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 Science 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.