8 Data File Generation
When you are implementing the Retail Analytics and Planning without using an Oracle merchandising system for foundation data, or you are providing history data from non-Oracle sources, you will need to create several data files following the platform specifications. This chapter will provide guidance on the data file formats, structures, business rules, and other considerations that must be accounted for when generating the data.
Important:
Do not begin data file creation for RAP until you have reviewed this chapter and have an understanding of the key data structures used throughout the platform.For complete column-level definitions of the interfaces, including datatype and length requirements, refer to the RI and AI Foundation Interfaces Guide in My Oracle Support.
Files Types and Data Format
The shared platform data files discussed in this chapter may use a standard comma-delimited (CSV) file format, with text
strings optionally enclosed by quotation marks or other characters. The files expect the first line to be column headers,
and lines 2+ should contain the input data. This applies to any filename ending in .csv
. For specific columns
in each of these files, the following standards can be used as a guideline (though they can be changed by configuration options).
Table 8-1 Foundation File Formatting
Datatype | Format | Example | Explanation |
---|---|---|---|
Number |
0.00 |
340911.10 |
Numbers should be unformatted with periods for decimal places. Commas or other symbols should not be used within the numerical values. |
Character |
“abc” |
“Item #4561” |
Any alphanumeric string can be optionally enclosed by quotation marks to encapsulate special characters such as commas in a descriptive value. |
Date |
YYYYMMDD |
20201231 |
Dates should be provided as simple 8-digit values with no formatting in year-month-day sequences. |
Second-generation SaaS Architecture
Note:
If you are an existing RI or AI Foundation customer in our older architecture, or your environments were provisioned initially on version 19 or earlier, then this information does NOT apply. Refer to the section after this for Legacy context file handling.Before creating and processing a data file on the platform, choose the fields that will be populated and instruct the platform
to only look for data in those columns. This configuration is handled through the use of Context (CTX) Files that are uploaded
alongside each base data file. For example, the context file for PRODUCT.csv
will be PRODUCT.csv.ctx
(appending the .ctx
file descriptor to the end of the base filename).
Within each context file you must provide a single column containing:
-
One or more parameters defining the behavior of the file load and the format of the file.
-
The list of fields contained in the source file, in the order in which they appear in the file specification:
-
#TABLE#<Staging Table Name>#
-
#DELIMITER#<Input Value>#
-
#DATEFORMAT#<Input Value>#
-
#REJECTLIMIT#<Input Value>#
-
#RECORDDELIMITER#<Input Value>#
-
#IGNOREBLANKLINES#<Input Value>#
-
#SKIPHEADERS#<Input Value>#
-
#TRIMSPACES#<Input Value>#
-
#TRUNCATECOL#<Input Value>#
-
#COLUMNLIST#<Input Value>#
<COL1>
<COL2>
<COL3>
-
The following is an example context file for the CALENDAR.csv
data file:
File Name: CALENDAR.csv.ctx
#TABLE#W_MCAL_PERIOD_DTS#
#DELIMITER#,#
#DATEFORMAT#YYYY-MM-DD#
#REJECTLIMIT#1#
#RECORDDELIMITER#\n#
#IGNOREBLANKLINES#false#
#SKIPHEADERS#1#
#TRIMSPACES#rtrim#
#TRUNCATECOL#false#
#COLUMNLIST#
MCAL_CAL_ID
MCAL_PERIOD_TYPE
MCAL_PERIOD_NAME
MCAL_PERIOD
MCAL_PERIOD_ST_DT
MCAL_PERIOD_END_DT
MCAL_QTR
MCAL_YEAR
MCAL_QTR_START_DT
MCAL_QTR_END_DT
MCAL_YEAR_START_DT
MCAL_YEAR_END_DT
The file must be UNIX formatted and have an end-of-line character on every line, including the last one. As shown above,
the final EOL may appear as a new line in a text editor. The #TABLE#
field is required: it indicates the
name of the database staging table updated by the file. The COLUMNLIST
tag is also required: it determines
the columns the customer uses in their .dat
or .csv
file. The column list must match the
order of fields in the file from left to right, which must also align with the published file specifications. Include the
list of columns after the #COLUMNLIST#
tag. Most of the other parameters are optional and the rows can be
excluded from the context file. However, this will set values to system defaults that may not align with your format.
Note:
Both RI and AI Foundation can use these context files to determine the format of incoming data.The server maintains a copy of all the context files used, so you do not need to send a context file every time. If no context files are found, the Analytics and Planning uses the last known configuration.
For additional format options, the available values used are from the DBMS_CLOUD package options in ADW.
Legacy Context File Configuration (GBUCS Architecture)
For RI interfaces in environments created from version 19 and earlier (including environments currently on v21 that were
provisioned with v19), a more basic CTX context file is used. These context files only accept a list of columns in the data
file; they cannot control any formatting or other options. They also must have a fixed filename ending in .dat.ctx
regardless of the associated file. Use the name of the target table in RI as the file name. For example, CALENDAR.csv
will have a context file named W_MCAL_PERIOD_DTS.dat.ctx
. The file format itself is also fixed. CSV files
will be comma-delimited, while DAT files will be pipe-delimited. DAT files use the same name for both the data file and context
file, such as W_CODE_DS.dat
and W_CODE_DS.dat.ctx
.
For a complete list of mappings between CSV filenames and associated table names used for CTX file naming, refer to Context File Table Reference .
The following is an example context file for the CALENDAR.csv
data file:
File Name: W_MCAL_PERIOD_DTS.dat.ctx
File Contents:
MCAL_CAL_ID
MCAL_PERIOD_TYPE
MCAL_PERIOD_NAME
MCAL_PERIOD
MCAL_PERIOD_ST_DT
MCAL_PERIOD_END_DT
MCAL_QTR
MCAL_YEAR
MCAL_QTR_START_DT
MCAL_QTR_END_DT
MCAL_YEAR_START_DT
MCAL_YEAR_END_DT
Note:
This legacy context file format applies strictly to Retail Insights. AI Foundation applications do not support the legacy context file format; they expect the full data file contents in their interfaces.Application-Specific Data Formats
Each application within the Retail Analytics and Planning may require data to be provided using specific rules and data formats, which can differ from those used in the common platform files. This section describes the use-cases for alternate data formats and lays out the basic rules that must be followed.
Retail Insights
Retail Insights has a large number of legacy interfaces that do not follow the shared platform data formats. These interfaces
are populated with files named after their target database table with a file extension of .dat
, such as W_PRODUCT_DS.dat
. All files ending with a .dat
extension are pipe-delimited files (using the |
symbol as the column separator). These files also have a Unix line-ending character by default, although the line-ending
character can be configured to be a different value, if needed. These files may be created by a legacy Merchandising (RMS)
extract process or may be produced through existing integrations to an older version of RI or AI Foundation.
Table 8-2 Retail Insights Legacy File Formatting
Datatype | Format | Example | Explanation |
---|---|---|---|
Number |
0.00 |
340911.10 |
Unformatted numbers with periods for decimal places. Commas or other symbols cannot be used within the numerical values. |
Character |
abc |
Item #4561 |
Any alphanumeric string will provided as-is, with the exception that it must NOT contain pipe characters or line-ending characters. |
Date |
YYYY-MM-DD;00:00:00 |
2020-05-09;00:00:00 |
Dates without timestamps must still use a timestamp format,
but they must be hard-coded to have a time of 00:00:00. Date fields (such as |
Timestamp |
YYYY-MM-DD;HH:MM:SS |
2020-05-09;09:35:19 |
Use full date-and-time formatting ONLY when a full timestamp is expected on the column. This is not commonly used and should be noted in the interface specifications, if supported. |
If you are implementing Retail Insights as one of your modules and you are in an environment that was originally installed with version 19 or earlier of RI, you may need to provide some files in this data format, in addition to the foundation files which use the CSV format. This file format is also used when integrating with legacy solutions such as the Retail Merchandising System (RMS) through the Retail Data Extractor (RDE).
Example data from the file W_RTL_PLAN1_PROD1_LC1_T1_FS.dat
:
70|-1|13|-1|2019-05-04;00:00:00|RETAIL|0|1118.82|1|70~13~2019-05-04;00:00:00~0
70|-1|13|-1|2019-05-11;00:00:00|RETAIL|0|476.09|1|70~13~2019-05-11;00:00:00~0
70|-1|13|-1|2019-05-18;00:00:00|RETAIL|0|296.62|1|70~13~2019-05-18;00:00:00~0
Retail AI Foundation Cloud Services
Modules within the AI Foundation Cloud Services leverage the same Context (CTX) file concepts as described in the common foundation file formats. You may control the structure and contents of AI Foundation files using the parameters in the context files. The full list of interfaces used by AI Foundation modules is included in the Interfaces Guide.
Planning Platform
Planning solutions using PDS (Planning Data Schema), such as Merchandise Financial Planning, have two main types of files:
Hierarchy/Dimension Files – Foundation Data for the Hierarchy/Dimensions.
Measure/Fact Files – Factual Data specific to loadable metric/measures.
When loading directly to Planning applications, both types of files should only be in CSV format and they should contain headers. Headers contain the details of the dimension names for Hierarchy/Dimension Files and the fact names for Measure/Fact Files.
Hierarchy/Dimension Files uses the naming convention <Hierarchy Name>.hdr.csv.dat
and Measure Files
can be any meaningful fact-grouping name, but with allowed extensions such as .ovr
, .rpl
, or .inc
.
-
OVR extension is used for override files
-
RPL extension is used to delete and replace position-based data sets
-
INC extension is for incremental files that can increment positional data.
If using the common foundation CSV files, most of the data can be interfaced using those shared integrations. However, certain files (such as the VAT Hierarchy) must be directly loaded to Planning: it does not come from RI at this time. Refer to the application-specific Planning Implementation Guides for more details about the list of files that are not included in foundation integrations.
Dimension Files
A dimension is a collection of descriptive elements, attributes, or hierarchical structures that provide context to your business data. Dimensions tell the platform what your business looks like and how it operates. They describe the factual data (such as sales transactions) and provide means for aggregation and summarization throughout the platform. Dimensions follow a strict set of business rules and formatting requirements that must be followed when generating the files.
There are certain common rules that apply across all of the dimension files and must be followed without exception. Failure to adhere to these rules may result in failed data loads or incorrectly structured datasets in the platform.
-
All dimension files must be provided as full snapshots of the source data at all times, unless you change the configuration of a specific dimension to be
IS_INCREMENTAL=Y
where incremental loads are supported. Incremental dimension loading should only be done once nightly/weekly batch processing has started. Initial/history dimension loads should always be full snapshots. -
Hierarchy levels must follow a strict tree structure, where each parent has a 1-to-N relationship with the children elements below them. You cannot have the same child level identifier repeat across more than one parent level, with the exception of Class/Subclass levels (which may repeat on the ID columns but must be unique on the UID columns). For example, Department 12 can only exist under Division 1, it cannot also exist under Division 2.
-
Hierarchy files (product, organization, calendar) must have a value in all non-null fields for all rows and must fill in all the required hierarchy levels without exception. For example, even if your non-Oracle product data only has 4 hierarchy levels, you must provide the complete 7-level product hierarchy to the platform. Fill in the upper levels of the hierarchy with values to make up for the differences, such as having the division and group levels both be a single, hard-coded value.
-
Any time you are providing a key identifier of an entity (such as a supplier ID, channel ID, brand ID, and so on) you should fill in the values on all rows of the data file, using a dummy value for rows that don’t have that entity. For example, for items that don’t have a brand, you can assign them to a generic “No Brand” value to support filtering and reporting on these records throughout the platform. You may find it easier to identify the “No Brand” group of products when working with CDTs in the AI Foundation Cloud Services or when creating dashboards in RI, compared to leaving the values empty in the file.
-
Any hierarchy-level ID (department ID, region ID, and so on) or dimensional ID value (brand name, supplier ID, channel ID, store format ID, and so on) intended for Planning applications must not have spaces or special characters on any field, or it will be rejected by the PDS load. ID columns to be used in planning should use a combination of numbers, letters, and underscores only.
-
Any change to hierarchy levels after the first dimension is loaded will be treated as a reclassification and will have certain internal processes and data changes triggered as a result. If possible, avoid loading hierarchy changes to levels above Item/Location during the historical load process. If you need to load new hierarchies during the history loads, make sure to advance the business date in RI using the specified jobs and date parameters, do NOT load altered hierarchies on top of the same business date as previous loads.
-
All fields designated as flags (having FLG or FLAG in the field name) must have a
Y
orN
value. Filters and analytics within the system will generally assumeY
/N
is used and not function properly if other values (like0
/1
) are provided. -
Retail Insights requires that all hierarchy identifiers above item/location level MUST be numerical. The reporting layer is designed around having numerical identifiers in hierarchies and no data will show in reports if that is not followed. If you are not implementing Retail Insights, then alphanumeric hierarchy IDs could be used, though it is not preferred.
Product File
The product file is named PRODUCT.csv
, and it contains most of the identifying information about the merchandise
you sell and the services you provide. The file structure follows certain rules based on the Retail Merchandising Foundation
Cloud Services (RMFCS) data model, as that is the paradigm for retail foundation data that we are following across all RAP
foundation files.
The columns below are the minimum required data elements, but the file supports many more optional fields, as listed in the Interfaces Guide. Optional fields tend to be used as reporting attributes in RI and are nullable descriptive fields. Optional fields designated for use in a AI Foundation or Planning module are generally nullable too, but should generally be populated with non-null values to provide more complete data to those modules.
Table 8-3 Product File Required Fields
Column Header | Usage |
---|---|
ITEM |
Product number which uniquely identifies the record. Could be any of these records:
|
ITEM_PARENT |
Parent item associated with this record when the item level is REQUIRED when providing multiple levels of items to establish the parent/child relationships. |
ITEM_GRANDPARENT |
Grandparent item associated with this record when the item level is REQUIRED when providing multiple levels of items to establish the parent/child relationships. |
ITEM_LEVEL |
Item Level ( Typical fashion item levels are:
|
TRAN_LEVEL |
Transaction level ( |
PACK_FLG |
Pack flag (where REQUIRED column if the retailer has packs, Optional otherwise. |
DIFF_AGGREGATE |
Combined differentiator values are used in defining the diff aggregate level (in between Item level |
LVL4_PRODCAT_ID |
Default level for Subclass, which is the first level above item in the hierarchy structure. Sometimes referred to as segment or subsegment. All items of any type are mapped to a subclass as their first level. Parent items are not to be treated as hierarchy levels in the file. |
LVL4_PRODCAT_UID |
Unique identifier of the Subclass. In many merchandising systems the subclass is not unique on its own, so a separate, unique key value must be provided in this case. |
LVL5_PRODCAT_ID |
Default level for Class (sometimes referred to as Subcategory). |
LVL5_PRODCAT_UID |
Unique identifier of the Class. In many merchandising systems the class is not unique on its own, so a separate unique key value must be provided in this case. |
LVL6_PRODCAT_ID |
Default Level for Department (also referred to as Category). |
LVL7_PRODCAT_ID |
Default Level for Group. |
LVL8_PRODCAT_ID |
Default Level for Division. |
TOP_PRODCAT_ID |
Default Level for Company. Only one company is supported at this time, you may not have 2+ companies in the same instance. |
ITEM_DESC |
Product Name or primary item description. |
LVL4_PRODCAT_DESC |
Default Level for Subclass Description. |
LVL5_PRODCAT_DESC |
Default Level for Class Description. |
LVL6_PRODCAT_DESC |
Default Level for Department Description. |
LVL7_PRODCAT_DESC |
Default Level for Group Description. |
LVL8_PRODCAT_DESC |
Default Level for Division Description. |
TOP_PRODCAT_DESC |
Default Level for Company Description. |
The product hierarchy fields use generic level names to support non-traditional hierarchy structures (for example, your first hierarchy level may not be called Subclass, but you are still loading it into the same position in the file). Other levels such as LVL1 to LVL3 have columns in the interface but are not yet used in any module of the platform.
Note:
Multi-level items are not always required and depend on your use-cases. For example, the lowest level (ITEM_LEVEL=3
) for sub-transaction items is only used in Retail Insights for reporting on UPC or barcode level attribute values. If you
are a non-fashion retailer you may only have a single item level (for SKUs) and the other levels could be ignored. The reason
for having different records for each item level is to allow for different attributes at each level, which can be very important
in Retail Insights analytics. You may also need to provide multiple item levels for optimizing or planning data at a Style
or Style/Color level in the non-RI modules. When providing multiple item level records, note that the item IDs must be unique across all levels and records.
Example data for the PRODUCT.csv
file columns above, including all 3 supported item levels:
ITEM,ITEM_PARENT,ITEM_GRANDPARENT,ITEM_LEVEL,TRAN_LEVEL,PACK_FLG,DIFF_AGGREGATE,LVL4_PRODCAT_ID,LVL4_PRODCAT_UID,LVL5_PRODCAT_ID,LVL5_PRODCAT_UID,LVL6_PRODCAT_ID,LVL7_PRODCAT_ID,LVL8_PRODCAT_ID,TOP_LVL_PRODCAT_ID,ITEM_DESC,LVL4_PRODCAT_DESC,LVL5_PRODCAT_DESC,LVL6_PRODCAT_DESC,LVL7_PRODCAT_DESC,LVL8_PRODCAT_DESC,TOP_LVL_PRODCAT_DESC
190085210200,-1,-1,1,2,N,,8,9001,3,910,3,2,1,1,2IN1 SHORTS,Shorts,Active Apparel,Women's Activewear,Activewear,Apparel,Retailer Ltd
190085205725,190085210200,-1,2,2,N,BLK,8,9001,3,910,3,2,1,1,2IN1 SHORTS:BLACK:LARGE,Shorts,Active Apparel,Women's Activewear,Activewear,Apparel,Retailer Ltd
190085205923,190085210200,-1,2,2,N,DG,8,9001,3,910,3,2,1,1,2IN1 SHORTS:DARK GREY:LARGE,Shorts,Active Apparel,Women's Activewear,Activewear,Apparel,Retailer Ltd
1190085205725,190085205725,190085210200,3,2,N,,8,9001,3,910,3,2,1,1,2IN1 SHORTS:BLACK:LARGE:BC,Shorts,Active Apparel,Women's Activewear,Activewear,Apparel,Retailer Ltd
1190085205923,190085205923,190085210200,3,2,N,,8,9001,3,910,3,2,1,1,2IN1 SHORTS:DARK GREY:LARGE:BC,Shorts,Active Apparel,Women's Activewear,Activewear,Apparel,Retailer Ltd
This example and the field descriptions covered in this section all follow the standard Merchandising Foundation (RMS)
structure for product data, and it is strongly recommended that you use this format for RAP. If you are a legacy Planning
customer or have specific needs for extended hierarchies, however, then there is a non-RMS format as well. In the non-RMS
format, the fields for ITEM
, ITEM_PARENT
, and ITEM_GRANDPARENT
are used
to represent the SKU, style/color, and style levels, respectively. While it is not recommended to carry this format forward
into RAP, the interface will accept it and there is a AI Foundation Cloud Services configuration (EXTENDED_HIERARCHY_SRC
) you must change to NON-RMS
to align with this.
Instead, the preferred approach is to convert your non-RMS hierarchy structure to a standard RMS-like foundation format. This conversion involves:
-
Provide only the SKUs and Styles as separate item records (dropping the style/color level from the hierarchy). The Style will be the
ITEM_PARENT
value on the SKU records andITEM_GRANDPARENT
will always be-1
. -
Populate the field
DIFF_AGGREGATE
at the SKU level with the differentiator previously used in the style/color level. For example, a style/color item ID ofS1000358:BLUE
usesS1000358
as theITEM
in the style record andITEM_PARENT
in the SKU record. The valueBLUE
is written in theDIFF_AGGREGATE
field in the SKU-level record. -
When constructing the extended hierarchies in Planning and AI Foundation, the styles and diff aggregate values are concatenated together to dynamically create the style/color level of the hierarchy where needed.
Following this approach for your product hierarchy ensures you are aligned with the majority of Oracle Retail applications and will be able to take up additional retail applications in the future without restructuring your product data again.
For other fields not shown here, they are optional from a data load perspective but may be used by one or more applications on the platform, so it is best to consider all fields on the interface and populate as much data as you can. For example, supplier information is a requirement for Inventory Optimization, and brand information is often used in Clustering or Demand Transference. Also note that some fields come in pairs and must be provided together or not at all. This includes:
-
Brand name and description
-
Supplier ID and description
Description fields could be set to the same value as the identifier if no other value is known or used, but you must include both fields with non-null values when you want to provide the data.
Product Alternates
You may also use the file PRODUCT_ALT.csv
to load additional attributes and hierarchy levels specifically
for use in Planning applications. The file data is always at item level and may have up to 30 flexible fields for data. These
columns exist in the PRODUCT.csv
file if you are a non-RMFCS customer so this separate file would be redundant.
If you are using RMFCS, then this file provides a way to send extra data to Planning that does not exist in RMFCS.
Organization File
The organization file will contain most of the identifying information about the locations where you sell or store merchandise, including physical locations (such as a brick & mortar store) and virtual locations (such as a web store or virtual warehouse entity). The file structure follows certain rules based on the Retail Merchandising Foundation Cloud Services (RMFCS) data model, as that is the paradigm for retail foundation data that we are following across all RAP foundation files. The columns below are the minimum required data elements, but the file supports many more optional fields, as listed in the Interfaces Guide.
Table 8-4 Organization File Required Fields
Column Header | Usage |
---|---|
ORG_NUM |
The external identifier for a location, including stores, warehouses, and partner locations. This value MUST be a number if you will use Retail Insights. RI cannot use non-numeric organization IDs. |
ORG_TYPE_CODE |
The type code of the location. It must be one of |
CURR_CODE |
This is the 3-character base currency code of the organizational entity, such as |
ORG_HIER10_NUM |
Default Level for District, which is the first level above Location in the hierarchy. Hierarchy values MUST be a number if you will use Retail Insights. RI cannot use non-numeric hierarchy IDs. |
ORG_HIER11_NUM |
Default Level for Region. |
ORG_HIER12_NUM |
Default Level for Area. Also referred to as the Channel level in some Planning applications. |
ORG_HIER13_NUM |
Default Level for Chain. |
ORG_TOP_NUM |
Default Level for Company. Only one company is supported at this time. You may not have 2+ companies in the same instance. |
ORG_DESC |
Short name or short description of the location. |
ORG_SECONDARY_DESC |
Full name or long description of the location. |
ORG_HIER10_DESC |
Default Level for District Description. |
ORG_HIER11_DESC |
Default Level for Region Description. |
ORG_HIER12_DESC |
Default Level for Area Description. Also referred to as the Channel level in some Planning applications. |
ORG_HIER13_DESC |
Default Level for Chain Description. |
ORG_TOP_DESC |
Default Level for Company Description. |
The organization hierarchy fields use generic level names to support non-traditional hierarchy levels (for example, your
first hierarchy level may not be called District, but you are still loading it into the same position in the file which is
used for Districts). Other levels, such as 1
to 9
, have columns in the interface but are
not yet used in any module of the platform.
Note:
Warehouses may not have a hierarchy structure in your core organizational hierarchy. If that is the case, you should provide a separate reserved value like1
or 9999
on all hierarchy level numbers between
location and company. This will prevent any potential roll-up issues, such as warehouse inventory being included in AI Foundation
optimization runs at levels above location. Make sure the values used to group warehouses are unique and not used anywhere
in your store hierarchy levels.
Example data for the ORGANIZATION.csv
file columns above as well as some optional fields available on
the interface:
ORG_NUM,ORG_TYPE_CODE,CURR_CODE,STATE_PROV_NAME,COUNTRY_REGION_NAME,ORG_HIER10_NUM,ORG_HIER11_NUM,ORG_HIER12_NUM,ORG_HIER13_NUM,ORG_TOP_NUM,ORG_DESC,ORG_SECONDARY_DESC,ORG_HIER10_DESC,ORG_HIER11_DESC,ORG_HIER12_DESC,ORG_HIER13_DESC,ORG_TOP_DESC,CHANNEL_ID,CHANNEL_NAME,PHYS_WH_ID,STOCKHOLDING_FLG,STORE_FORMAT_DESC,STORE_FORMAT_ID,STORE_TYPE,TRANSFER_ZONE_ID,TRANSFER_ZONE_DESC,VIRTUAL_WH_FLG,STORE_CLASS_TYPE,STORE_CLASS_DESC,WH_DELIVERY_POLICY,WH_REPL_IND,DUNS_NUMBER,STORE_REMODEL_DT,STORE_CLOSE_DT,INBOUND_HANDLING_DAYS,FLEX1_CHAR_VALUE,FLEX2_CHAR_VALUE,FLEX3_CHAR_VALUE,FLEX4_CHAR_VALUE,FLEX5_CHAR_VALUE,FLEX6_CHAR_VALUE,FLEX7_CHAR_VALUE,FLEX8_CHAR_VALUE,FLEX9_CHAR_VALUE,FLEX10_CHAR_VALUE
1000,S,USD,North Carolina,United States,1070,170,1,1,1,Charlotte,Charlotte,North Carolina,Mid-Atlantic,Brick & Mortar,US,Retailer Ltd,1,North America,,Y,Store,1,C,101,Zone 101,N,1,A,,,,,,,WH-1,Warehouse - US,1,Store Pick Up / Take With,3,Comp,6,Mixed Humid,1,Very Large
1001,S,USD,Georgia,United States,1023,400,1,1,1,Atlanta,Atlanta,Georgia,South Atlantic,Brick & Mortar,US,Retailer Ltd,1,North America,,Y,Kiosk,2,C,101,Zone 101,N,6,F,,,,,,,WH-1,Warehouse - US,2,Deliver/Install at Customer ,3,Comp,7,Hot Humid,3,Medium
1002,S,USD,Texas,United States,1104,230,1,1,1,Dallas,Dallas,Texas,Gulf States,Brick & Mortar,US,Retailer Ltd,1,North America,,Y,Store,1,C,101,Zone 101,N,6,F,,,,,,,WH-1,Warehouse - US,3,Home Delivery,3,Comp,4,Hot Dry,3,Medium
It is important that your organization hierarchy follow the standard rules laid out at the beginning of this chapter. All IDs must be unique (within their level) and IDs can never be re-used under multiple parents. All IDs must be numbers if you are using Retail Insights. The entire 6-level structure must be filled out, even if your source system doesn’t have that many levels.
Note:
You may duplicate a higher level down to lower levels if you need to fill it out to meet the data requirements.Also note that some optional fields come in pairs and must be provided together or not at all. This includes:
-
Banner ID and description
-
Channel ID and description
-
Store format ID and description
Description fields can be set to the same value as the identifier if no other value is known or used, but you must include both fields with non-null values when you provide the data.
Organization Alternates
You may also use the file ORGANIZATION_ALT.csv
to load additional attributes and hierarchy levels specifically
for use in Planning applications. The file data is always at location level and may have up to 30 flexible fields for data.
These columns exist on the ORGANIZATION.csv
file if you are a non-RMFCS customer, so this separate file would
be redundant. If you are using RMFCS, then this file provides a way to send extra data to Planning that does not exist in
RMFCS.
Calendar File
The calendar file contains your primary business or fiscal calendar, defined at the fiscal-period level of detail. The most common fiscal calendar used is a 4-5-4 National Retail Federation (NRF) calendar or a variation of it with different year-ending dates. This calendar defines the financial, analytical, or planning periods used by the business. It must contain some form of fiscal calendar, but if you are a business that operates solely on the Gregorian calendar, a default calendar file can be generated by an ad hoc batch program to initialize the system. However, if you are implementing a planning solution, you must use the Fiscal Calendar as your primary calendar, and only this calendar will be integrated from RI to Planning.
Table 8-5 Calendar File Required Fields
Column Header | Usage |
---|---|
MCAL_CAL_ID |
Identifies the accounting calendar. At this time a hard-coded value
of |
MCAL_PERIOD_TYPE |
Identifies the accounting period type ( |
MCAL_PERIOD_NAME |
Name of the fiscal period, such as |
MCAL_PERIOD |
Period number within a year, for a 4-5-4 calendar this should be
between |
MCAL_PERIOD_ST_DT |
Identifies the first date of the period in YYYYMMDD format (default format can be changed in CTX files). |
MCAL_PERIOD_END_DT |
Identifies the last date of the period in YYYYMMDD format (default format can be changed in CTX files). |
MCAL_QTR |
Identifies the quarter of the year to which this period belongs.
Possible values are |
MCAL_YEAR |
Identifies the fiscal year in YYYY format. |
MCAL_QTR_START_DT |
Identifies the start date of the quarter in YYYYMMDD format (default format can be changed in CTX files). |
MCAL_QTR_END_DT |
Identifies the end date of the quarter in YYYYMMDD format (default format can be changed in CTX files). |
MCAL_YEAR_START_DT |
Identifies the start date of the year in YYYYMMDD format (default format can be changed in CTX files). |
MCAL_YEAR_END_DT |
Identifies the end date of the year in YYYYMMDD format (default format can be changed in CTX files). |
The hard-coded calendar ID is used to align with several internal tables that are designed to support multiple calendars
but currently have only one in place, and that calendar uses the provided value of MCAL_CAL_ID
above.
The fiscal calendar should have, at a minimum, a 5-year range (2 years in the past, the current fiscal year, and 2 years forward from that) but is usually much longer so that you do not need to update the file often. Most implementations should start with a 10-15 year fiscal calendar length. The calendar should start at least 1 full year before the planned beginning of your history files and extend at least 1 year beyond your expected business needs in all RAP modules.
Example data for the CALENDAR.csv
file columns above:
MCAL_CAL_ID,MCAL_PERIOD_TYPE,MCAL_PERIOD_NAME,MCAL_PERIOD,MCAL_PERIOD_ST_DT,MCAL_PERIOD_END_DT,MCAL_QTR,MCAL_YEAR,MCAL_QTR_START_DT,MCAL_QTR_END_DT,MCAL_YEAR_START_DT,MCAL_YEAR_END_DT
Retail Calendar~41,4,Period01,1,20070204,20070303,1,2007,20070204,20070505,20070204,20080202
Retail Calendar~41,5,Period02,2,20070304,20070407,1,2007,20070204,20070505,20070204,20080202
Retail Calendar~41,4,Period03,3,20070408,20070505,1,2007,20070204,20070505,20070204,20080202
Retail Calendar~41,4,Period04,4,20070506,20070602,2,2007,20070506,20070804,20070204,20080202
Exchange Rates File
The exchange rates file captures conversion rates between any two currency codes that may appear in your fact data. The standard practice for fact data is to load the source system values in the original currency and allow the platform to convert the amounts to the primary currency. This file facilitates that process and triggers bulk updates in nightly processing any time you wish to change your exchange rates for financial reporting purposes. Adding new rates to the file with an effective date equal to the batch date triggers a mass update to all positional facts, converting all the amounts to the new exchange rate even if the item/location did not otherwise change in the source system.
Note that you do not have to provide exchange rates data for the following scenarios:
-
You are loading your data already converted to primary currency
-
You only use a single currency for your entire business
-
You are only implementing a AI Foundation module and expect to perform those processes in the local currency amounts
Even when exchange rates are not required as a separate file, you should still populate the default currency codes (DOC_CURR_CODE
, LOC_CURR_CODE
, LOC_EXCHANGE_RATE
) in the fact data files with default
values.
Table 8-6 Exchange Rate File Required Fields
Column Header | Usage |
---|---|
START_DT |
Contains the effective start date of the exchange rate. Set to the current business date to trigger new rate conversions. |
END_DT |
Contains the effective end date of the exchange rate. Default to |
EXCHANGE_RATE |
Contains the exchange rate for the specified currency/type/effective date combination. |
FROM_CURRENCY_CODE |
Code of the currency to be exchanged. |
TO_CURRENCY_CODE |
Code of the currency to which a currency is exchanged. |
Sample data for the EXCH_RATE.csv file columns above:
START_DT,END_DT,EXCHANGE_RATE,FROM_CURRENCY_CODE,TO_CURRENCY_CODE
20180514,21000101,0.8640055,CAD,USD
20180514,21000101,0.1233959,CNY,USD
20180514,21000101,1,USD,USD
The exchange rates data must also satisfy the following criteria if you are loading data for use in Retail Insights reporting:
-
Rates must be provided in both directions for every combination of currencies that can occur in your dataset (for example, USD > CAD and CAD > USD).
-
Rows must be included for each currency to itself with a fixed rate of 1 and end date of 2100-01-01 (as shown in the above example). These rows are used when RI converts data to document (source) currencies, to display the correct amounts on rows that have the same local and primary currency.
-
Dates must provide complete coverage of your entire timeframe in the dataset, both for historical and current data. The current effective records for all rates can use 2100-01-01 as the end date. Dates cannot overlap, only a single rate must be effective per day.
-
Rates should not change more often than absolutely necessary based on the business requirements. If you are implementing RI with positional data, a rate change triggers a complete recalculation of the stock on hand cost/retail amounts for the entire business across all pre-calculated aggregate tables. When RI is not used for financial reporting you might only change the rates once each fiscal year, to maintain a single constant currency for analytical purposes.
Attributes Files
Product attributes are provided on two files: one file for the attribute-to-product mappings and another for attribute descriptions and codes. These files should be provided together to fully describe all the attributes being loaded into the system. The attribute descriptors file must be a full snapshot of all attribute types and values at all times. The product attribute mapping file should start as a full snapshot but can move to incremental (delta) load methods once nightly batches begin, if you can extract the information as deltas only.
Product attributes are a major component of the RI and AI Foundation modules and drive many analytical processes but are not required for some planning modules like MFP.
Table 8-7 Attribute File Required Fields
Column Header | Usage |
---|---|
ATTR_VALUE_ID |
Unique identifier for a user-defined attribute or product differentiator.
This interface contains ALL values regardless of whether they are used on items yet or not. These values must also match the |
ATTR_VALUE_DESC |
Descriptive value for a user-defined attribute or product differentiator,
such as |
ATTR_GROUP_ID |
Unique identifier for a group of user-defined attributes, or the
name/code for the differentiator group. Reserved attribute types like |
ATTR_GROUP_DESC |
Descriptive value for a group of user-defined attributes, such as |
ATTR_TYPE_CODE |
Indicates the type of UDA or differentiator. UDA types should be
hard-coded as one of
For non-UDA types, some diffs have special reserved codes for this field as
well, which should be used when applicable, and include |
Sample data for the ATTR.csv
file columns above:
ATTR_VALUE_ID,ATTR_VALUE_DESC,ATTR_GROUP_ID,ATTR_GROUP_DESC,ATTR_TYPE_CODE
13,No_Sugar_IN13,45008,UDA_ING_2018.01.16.01.00,FF
14,Zero_Carbs_IN14,45008,UDA_ING_2018.01.16.01.00,FF
3,Distressed,80008,Wash,LV
STEEL,Steel,METAL,Metals,DIFF
CHOC,Chocolate,FLAVOR,Flavor,FLAVOR
GRAY_43214,Gray,COLOR,Color,COLOR
32X32_9957,32X32,SIZE,Size,SIZE
Table 8-8 Product Attribute File Required Fields
Column Header | Usage |
---|---|
ITEM |
The item number associated with the specified attribute value. |
ATTR_ID |
Identifier for an attribute value, such as the UDA value ID or Diff
ID which is mapped to the item on the record, or the ID for the item list this item belongs to. These values must also match
the |
ATTR_GRP_TYPE |
The attribute group type. This is a set of fixed values which must
be selected from what RAP supports. Supported values are |
ATTR_GRP_ID |
Identifier for the attribute group containing the value on this
record. Must match a
|
DIFF_GRP_ID |
Differentiator group used to assign the diff attribute on this item; for example the Size Group ID used when generating SKUs from a parent Style. Only SKUs will have diff groups associated with them. Only diffs will have groups, not UDAs or other record types. This is not the same as an attribute group, which is the overall grouping of attribute values across all items. This is used mainly for Size Profile Science. Foreign key reference
to |
DIFF_GRP_DESC |
Descriptive value of the diff group mapped to this item/attribute record. |
Sample data for the PROD_ATTR.csv
file columns above:
ITEM,ATTR_ID,ATTR_GRP_TYPE,ATTR_GRP_ID,DIFF_GRP_ID,DIFF_GRP_DESC
91203747,13,ITEMUDA,45008,,
91203747,3,ITEMUDA,80008,,
190496585706,STEEL,ITEMDIFF,METAL,,
86323133004,GRAY_43214,COLOR,COLOR,,
190085302141,CHOC,PRODUCT_ATTRIBUTES,FLAVOR,,
345873291,32X32_9957,PRODUCT_ATTRIBUTES,SIZE,S13,Pant Sizes
Fact Files
Fact Data Key Columns
Fact data files, such as sales and inventory, share many common characteristics and standards that can be followed regardless of the specific file. The table below summarizes those key elements and their minimum requirements. You will generally always want to populate these fields where they exist on an interface file.
Table 8-9 Common Fact Data Fields
Column Header | Usage |
---|---|
ITEM |
Unique identifier of a transaction item. Must align with the product records in the |
ORG_NUM |
Unique identifier of an organizational entity. Must align with the location records in the |
DAY_DT |
Transaction date or business date for the fact data entry, formatted as YYYYMMDD. Must be a valid date within
the range of periods in |
RTL_TYPE_CODE |
Retail types define a general category for the record that varies by interface. For Sales and Markdowns,
it must be one of |
*_QTY |
Fields ending with |
*_AMT_LCL |
Fields containing |
DOC_CURR_CODE |
The original document currency of the record in the source system. For example, a sale made in Canada
may have a value of |
LOC_CURR_CODE |
The local operating currency of your main office or headquarters. A company based in the United
States would use |
Nearly all fact files share a common intersection of an item, location, and date as specified above. Such files are expected to come into the platform on a nightly basis and contain that day’s transactions or business activity.
Most fact data also supports having currency amounts in their source currency, which is then automatically converted to your primary operating currency during the load process. There are several currency code and exchange rate columns on such interfaces, which should be populated if you need this functionality. The most important ones are shown in the list above, and other optional column for global currencies can be found in the Interfaces Guide. When you provide these fields, they must all be provided on every row of data, you cannot leave out any of the values or it will not load properly.
In addition to the commonly used fields, most files have other key columns that can be used to specify important details about the record. Some of these fields are marked as optional in the full interface specifications, meaning that if they are not provided, then some default values will be set for you.
Table 8-10 Additional Fact Data Key Columns
File | Column Header | Usage |
---|---|---|
SALES |
SLS_TRX_ID |
Unique identifier of a sales transaction. By default, it is expected sales data will come at the most granular level (transaction-line). If you are not able to provide transaction-level data, specify another unique value in this field. |
SALES |
MIN_NUM |
Hour and minute of the transaction
in 24-hour, 4-digit format; for example, 11:23 PM would be 2323. Currently only used in Retail Insights reporting; default
to |
SALES |
IT_SEQ_NUM |
Sequence of a line in the transaction. Every line of a transaction must have its own sequence number. This facilitates uniqueness requirements where the same item could have multiple lines. Without a unique sequence number, the line would not be unique on the input file and the system would see them as duplicate records (and they will not load properly as a result). |
SALES |
PROMO_ID PROMO_COMP_ID |
This two-part identifier maps to the Promotion and Offer associated with a transaction line. They are required if you wish to load sales by promotion for Retail Insights or certain AI Foundation modules such as Demand Transference. |
SALES |
CASHIER_ID SALES_PERSON_ID |
Cashier or salesperson associated with the transaction. Currently used only for Retail Insights. |
SALES |
CO_HEAD_ID CO_LINE_ID |
Order ID and line ID for a customer order, such as an online sale. Currently only used by Retail Insights. |
SALES |
CUSTOMER_NUM CUSTOMER_TYPE |
Customer
identifier on a sales transaction, which is a critical piece of information for many AI Foundation modules, such as Customer
Segmentation and Consumer Decisions Trees. |
SALES_PACK |
ITEM PACK_NUM |
|
INVENTORY |
CLEARANCE_FLG |
|
RECEIPT |
INVRC_TYPE_CODE |
Indicates the type of receipt
into a location using merchandising transaction codes |
ADJUSTMENT |
REASON_CODE |
User-defined reason for making the inventory adjustment. Currently used only for Retail Insights. |
ADJUSTMENT |
INVADJ_TYPE_CODE |
Indicates the type of
adjustment to inventory using merchandise transaction codes |
TRANSFER |
FROM_ORG_NUM FROM_CLEARANCE_FLG FROM_* |
Transfers are created for both the source of the transfer (the FROM location) and the destination (the TO location).
Fields not having a |
TRANSFER |
TSF_TYPE_ID |
The type of transfer in the
source system. It must be |
RTV |
INV_STATUS |
Status codes for the type of inventory being returned to a vendor, such as damaged or unsellable. |
Here are sample records for commonly used historical load files having a small set of fields populated. These fields are sufficient to see results in RI reporting and move the data to AI Foundation or MFP but may not satisfy all the functional requirements of those applications. Review the Interfaces Guide for complete details on required/optional columns on these interfaces.
SALES.csv
:
ITEM,ORG_NUM,DAY_DT,MIN_NUM,RTL_TYPE_CODE,SLS_TRX_ID,PROMO_ID,PROMO_COMP_ID,CASHIER_ID,REGISTER_ID,SALES_PERSON_ID,CUSTOMER_NUM,SLS_QTY,SLS_AMT_LCL,SLS_PROFIT_AMT_LCL,RET_QTY,RET_AMT_LCL,RET_PROFIT_AMT_LCL,TRAN_TYPE,LOC_CURR_CODE,DOC_CURR_CODE
1235842,1029,20210228,0,R,202102281029,-1,-1,96,19,65,-1,173,1730,605.5,0,0,0,SALE,USD,USD
1235842,1029,20210307,0,R,202103071029,-1,-1,12,19,55,-1,167,1670,584.5,0,0,0,SALE,USD,USD
1235842,1029,20210314,0,R,202103141029,-1,-1,30,18,20,-1,181,1810,633.5,0,0,0,SALE,USD,USD
INVENTORY.csv
:
ITEM,ORG_NUM,DAY_DT,CLEARANCE_FLG,INV_SOH_QTY,INV_SOH_COST_AMT_LCL,INV_SOH_RTL_AMT_LCL,INV_UNIT_RTL_AMT_LCL,INV_AVG_COST_AMT_LCL,INV_UNIT_COST_AMT_LCL,PURCH_TYPE_CODE,DOC_CURR_CODE,LOC_CURR_CODE
72939751,1001,20200208,N,0,0,0,104.63,0,48.52,0,USD,USD
73137693,1001,20200208,N,0,0,0,104.63,0,48.52,0,USD,USD
75539075,1001,20200208,N,0,0,0,101.73,0,47.44,0,USD,USD
PRICE.csv
:
ITEM,ORG_NUM,DAY_DT,PRICE_CHANGE_TRAN_TYPE,SELLING_UOM,STANDARD_UNIT_RTL_AMT_LCL,SELLING_UNIT_RTL_AMT_LCL,BASE_COST_AMT_LCL,LOC_CURR_CODE,DOC_CURR_CODE
89833651,1004,20200208,0,EA,93.11,93.11,53.56,USD,USD
90710567,1004,20200208,0,EA,90.41,90.41,50.74,USD,USD
90846443,1004,20200208,0,EA,79.87,79.87,44.57,USD,USD
Fact Data Incremental Logic
Daily or weekly fact data files can be provided incrementally instead of as full snapshots, but the specific handling of incremental changes can be different for the various fact types. The table below summarizes the incremental update logic used on the core fact areas.
Facts | Incremental Logic |
---|---|
Transactional (Sales, Receipts, Markdowns, Adjustments, RTVs, and so on) |
Loading transaction data into RAP uses additive merge logic when new data comes into the tables. If the target intersection doesn’t exist, it will insert it. If the target intersection DOES exist, then it will merge the records by adding together the source and target fields. For example, an existing sales transaction that is revised will add together the Quantity and Amount fields from the source and target. Note: When posting a partial revision, send zeros in fields that should not be adjusted. |
Positional (Inventory, Purchase Order, Price, Cost, and so on1) |
Positional data loaded into RAP must always be for the current date — it cannot be back-posted — and will insert into the target tables with the incoming values (because a new day’s position is always being created as new records). Refer to the detailed sections later in this chapter for Inventory Position and Pricing examples. |
Non-Transactional and Non-Positional Facts (Store Traffic, Flex Facts, Planning Facts) |
Some interfaces that are not related to any transactional or positional data elements, like the Store Traffic or Planning interfaces, use non-additive merge logic. When an existing intersection comes into the staging table, it is merged to the target table but overwrites/replaces the target values with the source values. |
Sales Data Requirements
Sales data (SALES.csv
) operates with the assumption that the source system for the data is an auditing
system (like Oracle Sales Audit) or non-Oracle data warehouse system. It applies minimal transformations to the inputs and
assumes all the needed cleansing and preparation of transaction data has happened outside of RI. Whether you are sourcing
history data from one of those systems or directly from a POS or non-Oracle auditing application, there are some business
rules that should be followed.
Requirement | File Type | Explanation |
---|---|---|
Sales Units |
Historical and Ongoing |
The values provided for unit quantities should represent the total transaction-line values for an item, split across the gross sales units and return units. In the case of an exchange, you could have both sales and return units on the same line in RI, but most of the time only SLS or RET fields will have a value. These values will be subtracted from each other to display Net Sales Quantity metrics in RI, so they should almost always be positive. |
Sales Retail Amounts |
Historical and Ongoing |
The retail amounts on a sale or return represent the actual selling/return value, after all discounts are subtracted from the base price of the item. In the case of an exchange, you could have both sales and return units on the same line in RI, but most of the time only SLS or RET fields will have a value. These values will be subtracted from each other to display Net Sales Amount metrics in RI, so they should almost always be positive. |
Sales Profit Amounts |
Historical and Ongoing |
Profit calculations must take into consideration the cost of the item at the time it was sold, and will vary based on the retailer's costing methods. The standard approach is to use the value for Weighted Average Cost (WAC) multiplied by the units sold/returned, and subtract that total cost value from the retail amount. An item that is sold and later returned may not have the same profit amounts, if the cost has changed between the two transactions or the return was not for the full price of the item. Most POS systems do not track item costs, so providing this data requires an external process to do the calculations for you. |
Sales Taxes |
Historical and Ongoing |
Tax amounts generally represent Value Added Tax (VAT); however this column could be used to capture other tax amounts if loading directly from the POS or external audit system. |
Employee Discounts |
Historical and Ongoing |
These columns are specifically for employee discounts when the employee purchases a product at the POS and gets a special discount (or has the discounted amount returned later on). These values are just the discount amount, meaning the reduction in value from the selling price. |
Promotional Discounts |
Historical and Ongoing |
These values represent the total discount taken off the initial selling price for the line-item in the transaction. These values will almost always be populated for promotional sales. However, a regular or clearance sale could have a further discount applied (like a coupon) and that should also be captured here. These values are used to populate the Sales Promotion fact table for retail type “P” transactions. So make sure that any change in price related to a promotion is included in this discount amount, so that it is copied into other tables for Promotion-level reporting. |
Liabilities |
Historical and Ongoing |
Liabilities are sales that have not yet been charged to the customer, either due to layaway practices or customer orders that are posted as a liability transaction before they are fulfilled. Liabilities are posted with a positive value when incurred, and reversed with a negative value when they are converted into a regular sale or cancelled. Liabilities are a separate set of metrics in RI and do not interact with sales values, as it is expected that the liability will always result in a cancellation or a sale being posted at a later date. |
Liability Cancels |
Historical and Ongoing |
Liabilities that are cancelled should first be reversed and then posted to these fields as a positive amount. A cancelled liability will have no impact on sales and has a separate set of metrics in RI. The retailer can use liability cancellation metrics to track the value of customer orders that were cancelled before being charged. |
Retail Type |
Historical and Ongoing |
The retail type represents the category of sale as one of Regular,
Promotion, or Clearance. We use the codes |
Transaction Reversals and Revisions |
Historical and Ongoing |
When using Sales Audit to audit sales, the export process will automatically handle reversing a transaction and posting revisions to a transaction. Without that, you must manually create a process to send reversals and revisions to RI matching the same data format. These two records come at the same time. A reversal is an exact opposite of the original transaction line (usually all negative values, unless the original value was negative). This will be added to RI’s data and zero it out. The revision record should come next and contain the current actual values on the transaction (not the delta or difference in values). Keep in mind that, depending on the method used for calculating sales cost/profit amounts, a reversal and revision may have different values from the original profit amount. This could result in a very small residual profit from the prior revision. |
Original Selling Locations |
Historical and Ongoing |
When including the original selling location of a return transaction, you must also make sure that is a real location included on your Organization input data. Some source systems allow the manual entry of a return’s original selling location, so ensure that all such locations are included, or the records will be rejected by RI. |
Inventory Data Requirements
Inventory data (INVENTORY.csv
) has several special requirements that need to be followed when generating
historical data and ongoing daily feeds, due to the way the data is stored within Retail Insights as well as the different
use-cases in each of the AI Foundation and Planning applications. A retailer may not have the data in the required format
in their source system, and adjustments would have to be made in the data extraction process to ensure these rules are followed.
Requirement | File Type | Explanation |
---|---|---|
Records may be needed before the item/location has any stock on hand |
Historical and Ongoing |
The inventory position contains fields for inventory movement, such as on-order, in-transit, and reserved quantities. As soon as any of those values may contain data for an item/location (and you intend to use those fields in RI), a record should be included for inventory, even if the actual stock on hand is still zero that day or week. |
Zero balances must be sent after an item/location has started carrying a non-zero position |
Historical and Ongoing |
Inventory data is stored positionally, meaning we must maintain the current daily balance of stock on hand for every item/location. If no change comes into the system on a given day, we carry forward that balance. This means that you cannot send only non-zero values in the data files, as it is assumed the last known value is also the current value for the day or week. You must send a zero balance any time the inventory has moved from non-zero to zero. |
Clearance indicator is used to show the end-of-period status of the item/location’s inventory |
Historical (Weekly) |
Inventory data has a required column for a Clearance Flag
( |
Inventory records should continue to be sent based on an item/location’s active ranging status |
Historical (Weekly) |
Once you begin sending inventory records for an item/location, it is ideal that you continue to send records every week even if the balance is zero, until that item/location is no longer active/ranged. This is important for out-of-stock detection and other analytical checks on inventory levels. A null or missing record is not automatically assumed to be zero. |
Any change to values on the inventory position should send an update of the full record from the source system. |
Ongoing (Daily) |
If you are using other inventory values besides stock on hand (such as on-order or in-transit), you must ensure the extracts will send a complete record to the inventory interface when any of those values change. For example, a new item/location may carry an on-order balance or in-transit balance for several weeks before it has any stock on hand, so your extracts must trigger changes to those values, not just changes to stock on hand. Most other values on the interface are only used in RI reporting at this time. |
For historical loads, this results in the following flow of data across all your files:
-
Generate the first month of week-ending inventory balances in
INVENTORY.csv
for all active item/locations in each week of data. Include zero balances if the item/location is actively ranged to a location and is actively selling, so that out-of-stock detection is accurate. Load using the historical inventory load ad hoc process. -
Repeat the monthly file generation process, including sets of week-ending balances in chronological order. Remember that you cannot load inventory data out of order, and once a week is loaded you cannot go backwards to update past periods. All files must continue to include zero balances and other data as defined in the list of requirements.
-
Load every week of inventory snapshots through to the end of your historical period. If there will be a gap of time before starting nightly batches, plan to load an additional history file at a later date to catch up. Make sure you load Receipts data in parallel with inventory data, if you need to capture historical first/last receipt dates against the stock positions.
-
When you are ready to cutover to batches, you must also re-seed the positions of all item/locations that need to have an inventory record on Day 1 of nightly batch execution (same as for all positional facts in RI). This is needed to fill in any gaps where currently active item/locations are not present in the historical files but need to have an inventory record added on day 1. Use the Seeding Adhoc process for Inventory to do this step, not the historical load.
Price Data Requirements
Pricing data (PRICE.csv
) has several special requirements that need to be followed when generating historical
data and ongoing daily feeds, due to the way the data is stored within Retail Insights as well as the different use-cases
in the RI and AI Foundation applications. A retailer may not have the data in the required format in their source system,
and adjustments would have to be made in the data extraction process to ensure these rules are followed.
Requirement | File Type | Explanation |
---|---|---|
The first price file must include records for all active item/locations |
Historical |
The pricing fact is stored positionally, meaning that it first needs a starting position for an entity (for example, an initial price for an item/location) and then it may not be sent again unless there is a change to the value. The very first price file loaded into RI must contain a starting position for all active item/locations. How you determine which item/locations were active on that day in history will depend on your source system (for example, you can base it on items having stock on hand, historical ranging status, or a price history table if you have one). |
The first record sent for an item/location must come as a new price transaction type |
Historical and Ongoing |
Price records have a column for the price type ( |
Price records should follow a specific lifecycle using the type codes |
Historical and Ongoing |
The typical flow of price changes that will occur for an item/location should be as follows:
An item/location may have many changes with types |
Price changes are for the end-of-day value only |
Historical and Ongoing |
An item price may change many times a day, but you must only send the end-of-day final position for the item/location. The file interface assumes only one record will be sent per item/location/effective date, representing the final price on that date. |
For historical loads, this results in the following flow of data across all your files:
-
Generate an initial position
PRICE.csv
that has alltype=0
records for the item/locations you want to specify a starting price for. Load this as the very first file using the historical load ad hoc process. -
Generate your first month of price change records. This will have a mixture of all the price change types. New item/location records may come in with
type=0
and records already established can get updates using any of the other type codes. Only send records when a price or cost value changes; do not send every item/location on every date. You also must not send more than one change per item/location/date. -
Repeat the monthly file generation (or more than one month if your data volume for price changes is low) and load process until all price history has been loaded for the historical timeframe.
-
When you are ready for the cutover to batches, you must also re-seed the positions of all item/locations that need a price record on Day 1 of nightly batch execution (same as for all positional facts in RI). This is needed to fill in any gaps where currently active item/locations are not present in the historical files, but need a price record added on day 1. Use the Seeding Ad Hoc process for Pricing to do this step, not the historical load.
Other Fact File Considerations
The following section describes additional data considerations that may apply to your implementation depending on the types and volumes of data being provided to the platform. Review each topic closely, as it affects the data provided in the foundation files.
Positional Data Handling
The largest sets of fact data in the platform tend to be those that represent every possible item/location combination (such as prices or costs). To efficiently store and process these data volumes, a data warehouse technique known as compression is used to capture only the changed records on a day-to-day basis, effectively maintaining a “current position” for every set of identifiers, which is updated during each batch execution. The output of this compression process is called positional data, and the following functional areas use this method of data load and storage:
-
Inventory (INV and INVU)
-
Prices (PRICE)
-
Costs (BCOST and NCOST)
-
Purchase Orders (PO_ONORD) and Allocations On Order (PO_ONALC)
Positional data loads follow very specific rules and cannot be processed in the same manner as non-positional data such as sales transactions.
Table 8-11 Positional Data Rules
Rule | Explanation |
---|---|
Data Must be Sequential |
Positional data must be loaded in the order of the calendar date on which it occurs and cannot be loaded out-of-order. For example, when loading history data for inventory, you must provide each week of inventory one after the other, starting from Week 1, 2, 3, and so on. |
Data Cannot be Back Posted |
Positional data cannot be posted to any date prior to the current load date or business date of the system. If your current load date is Week 52 2021, you cannot post records back to Week 50: those past positions are unable to be changed. Any corrections that need to be loaded must be effective from the current date forward. |
Data Must be Seeded |
Because positional data must maintain the current position of all data elements in the fact (even those that are inactive or not changing) it is required to initialize or “seed” positional facts with a starting value for every possible combination of identifiers. This happens at two times:
|
Throughout the initial data load process, there will be additional steps called out any time a positional load must be performed, to ensure you accurately capture both historical and initial seed data before starting nightly batch runs.
System Parameters File
The dimension file for RA_SRC_CURR_PARAM_G.dat
is not used as part of your history load process directly,
but instead provides an important piece of information to the platform for operational activities. This file must contain
the current business date associated with the files in the ZIP package. The file should be included with the nightly ZIP upload
that contains your foundation data, such as RI_RMS_DATA.zip
or RAP_DATA.zip
.
The file has only two generic columns, PARAM_NAME
and PARAM_VALUE
. When data is sourced
from RMFCS, it will be automatically generated and sent to RI in the nightly batch. If your data does not come from RMFCS,
then you need to include the file manually. Currently, only two rows will be used, but future releases may look for additional
parameters in the file.
The file should contain the following rows:
VDATE|20220101
PRIME_CURRENCY_CODE|USD
The parameter value with VDATE
is the current business date that all your other files were generated for
in YYYYMMDD format. The date should match the values on your fact data, such as the DAY_DT
columns in sales,
inventory, and so on. This format is not configurable and should be provided as shown. The parameter value with PRIME_CURRENCY_CODE
is used by the system to set default currencies on fact files when you do not provide them yourself or if there are null
currency codes on a row.
Loading Data on Multiple Threads
Large data files, such as sales and inventory, can support multiple threads for parallel execution, which can greatly reduce
the load times for history files. Some tables have an explicit column called ETL_THREAD_VAL
to define your
threads for incoming data. Parameters are available in C_ODI_PARAM_VW
(in the Control Center) to configure
each load program's maximum number of threads, using the parameter name LOC_NUM_OF_THREAD
. For larger retailers,
it is common to define 5 to 8 threads for incoming data feeds. You may choose how you split data across threads when generating
the files, but a common strategy is to assign stores to threads, such that all the data for a given location is processed
on the same thread.
For many interfaces, the ETL_THREAD_VAL
column has been removed and, in those cases, the system will automatically
calculate the threads based on the LOC_NUM_OF_THREAD
parameter values. If ETL_THREAD_VAL
is not provided on a file that has the column, the same automation will be applied to determine multi-threading behavior.
The LOC_NUM_OF_THREAD
values must be updated appropriately by the implementer in C_ODI_PARAM
to leverage multi-threading capabilities.