2 Setup and Configuration
The Retail Insights application is a part of the Retail Analytics and Planning solutions and much of the implementation steps have been combined into a single platform-level document referred to as the Retail Analytics and Planning Implementation Guide. For general guidance on implementing RI or AI Foundation applications, first refer to that document. The chapters of this guide supplement the platform documents with RI-specific details as needed.
Initial Configurations
After performing all instructions for initial environment configuration in the RAP Implementation Guide, you may also want to configure Retail Insights-specific parameters that will impact your data loading and conversion processes. Review the table below for a complete list of these parameters.
Additionally, the Retail Data Extractor (RDE) tool has some configurations specifically for controlling the ETL logic between RMFCS and RI. These settings do not apply if you are implementing the Retail Insights without RMFCS. The RDE settings are available from the Control Center in the same C_ODI_PARAM_VW table used for RI.
Table 2-1 C_ODI_PARAM Initial Setup
Scenario | Parameter | Usage |
---|---|---|
GLOBAL |
RI_UA_ROW_DELIMITER |
Change the row ending characters for all data files sent into RI, both from RDE and external sources. If the default value of "\n" may occur in text strings, a custom delimiter MUST be set before loading data. |
GLOBAL |
NON_NUMERIC_HIERARCHY |
Configures certain RI attributes in reporting to allow non-numeric values in hierarchy IDs. Default = N, which means only numeric values are allowed. Change this value to “Y” if you will be having non-numeric hierarchy IDs and then raise a Service Request to Oracle informing them that you have altered the value of NON_NUMERIC_HIERARCHY in C_ODI_PARAM and you will need your Retail Insights configuration to be reloaded for it to take effect. |
GLOBAL |
LANGUAGE_CODE |
Default language code used by the system to load data. Do not change unless your source systems are also using a non-English default language. |
GLOBAL |
RI_INV_HIST_DAYS |
The number of days to retain a zero-balance record on inventory positions. After the set number of days, we will stop carrying forward a balance on W_RTL_INV_IT_LC_DY_F table for the zeros, and it will also stop appearing on the aggregates from that day on. Excessive retention of zero balances can cause batch performance issues due to high data volumes. Default=91 days. |
GLOBAL |
RI_CLOSED_PO_HIST_DAYS |
The number of days to retain closed purchase orders on the daily positional snapshots. After the set number of days, we will stop carrying forward a balance on W_RTL_PO_ONORD_IT_LC_DY_F table for the closed order. Default=30 days. |
GLOBAL |
RI_PART_DDL_CNT_LIMIT |
Maximum number of partitions to create during the initial setup run, recommended value is 100000. The average initial setup of the calendar may need 50-60,000 partitions. |
GLOBAL |
RA_CLR_LEVEL |
Disables the mapping of clearance event IDs to clearance inventory updates, set to N to disable. Disabling may improve batch performance. |
GLOBAL |
ANCHOR_TO_YEARS |
Number of years of data to consider for Same Stores method of Comparable Store reporting. Set to 3 if LLY is required. |
GLOBAL |
SAME_STORES |
Enable or disable Same Stores method of Comparable Store reporting. |
GLOBAL |
GIFT_CARD_TENDER_TYPE_ID |
The tender type ID associated with gift cards in RMS, for gift card fact usage. |
GLOBAL |
CLSTR_GRP_TYPE |
Controls the type of data loaded to the Cluster interfaces, either CLSTR for store clusters or PRICE_ZONE for price zones. |
GLOBAL |
ITEM_CFA_VISIBILTY LOC_CFA_VISIBILTY SUPS_CFA_VISIBILTY ITEM_LOC_CFA_VISIBILTY MCAL_DAY_CFA_VISIBILTY |
Controls access to CFAS attributes in OAS reporting, set to 0 to enable, 1 to disable. |
GLOBAL |
LY_SHIFT_TYPE |
Controls the default LY calendar mapping used, accepts values in UNSHIFT, SHIFT, or GUNSHIFT. The first two are fiscal calendar variations, while the third is Gregorian calendar. The value in the param chooses which LY calendar to use out of the three stored in the database. SHIFT calendar maps LY shifted by 1 week, so week 53 maps to week 1 of the same FY. UNSHIFT keeps the days/weeks mapped 1-to-1 (FY week 52 maps to week 52 LY, week 53 has no LY). GUNSHIFT uses 1-for-1 mapping but on Gregorian calendar (Day 1 of the year maps to day 1 LY). |
GLOBAL |
ITEM_GRP1_IS_INCREMENTAL |
Enable incremental processing of the W_RTL_ITEM_GRP1_D table, which is used for product attributes, UDAs, and item lists. Should be set to ‘Y’ in production environments once initial loading is done. When set to ‘N’, records not part of the daily load are set to CURRENT_FLG=N and cannot be used or reactivated. |
GLOBAL |
RTVR_REASON_CAT_CODE |
RMS code type for RTV reason codes. |
GLOBAL |
RI_TRX_COUNT_RECLASS_IND |
Set to ‘Y’ to enable reclassification processing of transaction count aggregates. This may greatly impact batch runtimes so it should not be enabled unless required. |
GLOBAL |
RI_GEN_PROD_RECLASS_IND |
Set to ‘Y’ to enable RI product data loads to automatically generate item-level reclass records from your hierarchy data. From version 24 onwards, this is the default method to handle reclasses. Requires that full product files are sent every day, or at least full enough to detect when an item moves between hierarchy positions even if no other change occurred. Default = Y |
GLOBAL |
RI_INT_ORG_DS_MANDATORY_IND |
Set to ‘Y’ to require input data on the Organization hierarchy interface in order for the batch to run. This will prevent the batch from executing if the data files were not uploaded properly for a given day or the file was missing from the upload. |
GLOBAL |
RI_PROD_DS_MANDATORY_IND |
Set to ‘Y’ to require input data on the Product hierarchy interface in order for the batch to run. This will prevent the batch from executing if the data files were not uploaded properly for a given day or the file was missing from the upload. |
GLOBAL |
CURRENCY_CODE |
Set the default currency code to use when loading CSV-based fact data files if none are provided on the files themselves. Defaults to ‘USD’. |
GLOBAL |
RI_EXCLUDE_VAT_IND |
Determines if special Sales metrics are displayed in OAS for “Sales excluding VAT”. These metrics subtract the tax amounts from the sales values under the assumption tax is always included in the inputs, but should not be shown in reporting. Set to ‘0’ to show the metrics, or ‘1’ to hide them. |
GLOBAL |
START_OF_YEAR_MONTH |
The name of the Gregorian month associated with the first fiscal period in your business calendar. For example, if your fiscal year starts 06-FEB-22 then set this to FEBRUARY. This will be used to display month names in RI reporting on the fiscal calendar. Default = JANUARY |
GLOBAL |
HIST_ZIP_FILE |
Change the default name for the ZIP file package used by the history file load process. Default=RAP_DATA_HIST.zip |
GLOBAL |
RI_AGG_FULL_LOAD_TYPE |
Controls date extending behavior for aggregation utility. Should be one of (F, FS, FE, NA). Refer to the AIF Operations Guide for details on the utility usage. Default = FS |
GLOBAL |
RI_ITEM_REUSE_IND |
Enable or disable the ability to re-use item numbers over time to represent entirely new items. Also enables retention of existing items for a number of days, so that if an item drops and reappears quickly, it is not considered a new item and will continue to use the existing records. Set to ‘Y’ to enable. If this is not enabled, items that are dropped from the product interface are immediately closed and deactivated and cannot be re-opened. Default = N |
GLOBAL |
RI_ITEM_REUSE_AFTER_DAYS |
The number of days between when an item is deleted and when it’s allowed to appear as a new item having the same ID. This will trigger the old version of the item to be archived in the data warehouse using an alternate key, so the new version of the item is treated as completely new. For example, setting this to 5 days means that an item can be dropped/deleted and after 5 days, when the same items come again it will be treated as a brand-new item. If the item re-appears in the data before 5 days has passed, it will be treated as the same item as before and the existing item data remains active. Default = 0 |
GLOBAL |
RI_LAST_MKDN_HIST_IND |
Set to Y to enable the Price fact columns for LPO (LST_MKDN_RTL_AMT_LCL, LST_MKDN_DT, LST_PROMO_RTL_AMT_LCL, LST_PROMO_DT) to be populated during history loads. This will impact performance of the loads and is disabled by default. |
GLOBAL |
PDS_PROD_INCLUDE_ITEM_ID |
Control if item identifiers are included in the PDS product labels or not. When set to a value of "N", only the product descriptions are included in the labels. When changed to a value of "Y", the item IDs are concatenated in front of the descriptions on W_PDS_PRODUCT_D.Default = N |
GLOBAL |
RI_LAST_MKDN_HIST_IND |
Set to Y to enable the Price fact columns for LPO (LST_MKDN_RTL_AMT_LCL, LST_MKDN_DT, LST_PROMO_RTL_AMT_LCL, LST_PROMO_DT) to be populated during history loads. This will impact performance of the loads and is disabled by default. |
GLOBAL |
PDS_PROD_INCLUDE_ITEM_ID |
Control if item identifiers are included in the PDS product labels or not. When set to a value of "N", only the product descriptions are included in the labels. When changed to a value of "Y", the item IDs are concatenated in front of the descriptions on W_PDS_PRODUCT_D.Default = N |
GLOBAL |
PDS_EXPORT_DAILY_ONORD |
Determines if the EOW_DATE used in Purchase Order export data is allowed to contain non-end-of-week (EOW) dates or the system must convert it to a week-ending date in all cases. When set to a value of ‘Y’, it means daily dates are allowed in the EOW_DATE field on the export (if there is a daily date in the OTB_EOW_DATE column of ORDER_HEAD.csv). When set to a value of ‘N’, it means the system will automatically convert the input dates from ORDER_HEAD.csv to be week-ending dates only. |
PLP_RETAILINVPOSITIONITLCWKAGGREGATE |
INV_CLR_REQ_IND |
Controls the behavior of inventory loads with regards to the Clearances dimension. When set to “Y”, the inventory load ETL will join with the clearance dimension to capture the clearance ID and markdown number associated with the inventory updates. When set to “N”, the join is disabled and inventory will not be tracked by clearance ID or markdown ID.Default = N |
SIL_DAYDIMENSION |
END_DT |
End date for generating the system calendar (this is different from the fiscal calendar). Set at least 6 months beyond the end of the fiscal calendar. |
SIL_DAYDIMENSION |
START_DT |
Start date for generating the system calendar (this is different from the fiscal calendar). Set at least 6 months before the start of the fiscal calendar. |
SIL_DAYDIMENSION |
WEEK_START_DT_VAL |
Starting day of the week for the system calendar (1 = Sunday). |
SIL_RETAILINVPOSITIONFACT |
RI_INVAGE_REQ_IND |
Disables calculation of first/last receipt dates and inventory age measures. Disabling may improve batch performance. |
SIL_RETAILINVPOSITIONFACT |
RI_PRES_STOCK_IND |
Disables usage of replenishment data for presentation stock to calculate inventory availability measures Disabling may improve batch performance. |
SIL_RETAILINVPOSITIONFACT |
RI_BOH_SEEDING_IND |
Disables the creation of initial beginning-on-hand records so analytics have a non-null starting value in the first week. Disabling may improve batch performance. |
SIL_RETAILINVPOSITIONFACT |
RI_MOVE_TO_CLR_IND |
Disables calculation of move-to-clearance inventory measures when an item/location goes into or out of clearance status. Disabling may improve batch performance. |
SIL_RETAILINVPOSITIONFACT |
RI_MULTI_CURRENCY_IND |
Disables recalculation of primary currency amounts if you are only using a single currency. Disabling may improve batch performance. |
SIL_RETAILINVPOSITIONFACT |
INV_FULL_LOAD_IND |
Control the Inventory Position fact load behavior. When set to N it uses the incremental update behavior where it requires just the changes to inventory to be posted, including zeros. When set to Y, it assumes full nightly snapshots are being loaded and automatically zeroes out all inventory positions not sent for that load. Default = N. |
SIL_ITEMDIMENSION |
IS_INCREMENTAL |
Controls if the product dimension is full snapshot or incremental changes only for the daily load. |
SIL_RETAILITEMCFADIMENSION |
IS_INCREMENTAL |
Controls if the product CFAS dimension is full snapshot or incremental changes only for the daily load. |
SIL_RETAILITEMLOCCFADIMENSION |
IS_INCREMENTAL |
Controls if the product loc CFAS dimension is full snapshot or incremental changes only for the daily load. |
SIL_RETAILLOCATIONCFADIMENSION |
IS_INCREMENTAL |
Controls if the location CFAS dimension is full snapshot or incremental changes only for the daily load. |
SIL_RETAILSUPPCFADIMENSION |
IS_INCREMENTAL |
Controls if the supplier CFAS dimension is full snapshot or incremental changes only for the daily load. |
SIL_RETAILSUBSTITUTEITEMDIMENSION |
IS_INCREMENTAL |
Controls if the substitute item dimension is full snapshot or incremental changes only for the daily load. |
SIL_RETAILPROMOTIONDIMENSION |
RI_INCREMENTAL_IND |
Controls if the promotion dimension is full snapshot or incremental changes only for the daily load. |
SIL_RETAILPOONORDERFACT |
PO_FULL_LOAD_IND |
Control the Purchase Order fact load behavior. When set to “N” it will use the incremental update behavior where it requires just the changes to POs the be posted, including zeros. When set to “Y”, it will assume full nightly snapshots are being loaded and will automatically zero out all POs not sent for that load. Default = N |
SIL_SEEDEMPLOYEEDIMENSION |
RI_MIS_CASHIER_REQ_IND |
Seed missing Cashier IDs from sales fact to Employee dimension. |
SIL_SEEDCOHEADDIMENSION |
RI_MISSLS_COHEAD_REQ_IND |
Seed missing customer order (CO) head IDs from sales fact to CO Dimension. |
SIL_SEEDCOLINEDIMENSION |
RI_MIS_COLINE_REQ_IND |
Seed missing customer order (CO) line IDs from sales fact to CO Dimension. |
SIL_SEEDCOUPONDIMENSION |
RI_MIS_COUPON_REQ_IND |
Seed missing coupon IDs from sales discount fact to Coupon dimension. |
SIL_SEEDCUSTOMERDIMENSION |
RI_MIS_CUSTOMER_REQ_IND |
Seed missing customer IDs from sales fact to Customer dimension. |
SIL_SEEDCUSTOMERLOYALTYAWARDACCOUNTDIMENSION |
RI_SEED_AWARD_ACCOUNT_IND |
Seed missing award account IDs from loyalty fact to Award Account dimension. |
SIL_SEEDDISCOUNTTYPEDIMENSION |
RI_MIS_DISC_TYPE_REQ_IND |
Seed missing discount type codes from sales discount fact to Discount Type dimension. |
SIL_SEEDPROMOTIONDIMENSION |
RI_MISSLS_PROMO_REQ_IND |
Seed missing promotions from the sales promo fact to the Promotion dimension. |
SIL_SEEDSTOCKCOUNTDIMENSION |
RI_MIS_STOCK_CNT_REQ_IND |
Seeds missing stock counts from the systemic (RMS) stock count fact to the stock count dimension. |
SIL_RETAILSALESPROMOTIONTRANSACTIONFACT |
RI_EXT_PROMO_COL |
Select a reference column on the sales fact which represents an External Promotion. This will be joined with W_RTL_PROMO_EXT_DS interface to load externally sourced promotion sales into RI that don't exist in Pricing CS, and treat them as valid promo sales. |
SIL_RETAIL_SALESPOS_DATA_HANDLING |
PURGE_POSXML_DAYS |
Number of days to keep POS sales logs in raw XML format. This data is only intended for internal debugging/error resolution, as POS sales are only shown in reporting for the current date. |
Table 2-2 C_ODI_PARAM RDE Configurations
Scenario | Parameter | Usage |
---|---|---|
GLOBAL |
RDE_UA_ROW_DELIMITER |
Default row delimiter on incoming data files (both RMS and external). |
GLOBAL |
RI_UA_ROW_DELIMITER |
Default row delimiter on data files going out to RI, must match with RI incoming UA delimiter value. |
GLOBAL |
RPM_PROMO_EVENT_LEVEL |
Enable (set to ‘Y’) if using legacy RPM on-premise functionality. |
GLOBAL |
RETURN_REASON_CAT_CODE |
RMS code type for customer return reason codes. |
GLOBAL |
RTVR_REASON_CAT_CODE |
RMS code type for RTV reason codes. |
GLOBAL |
WHOLESALE_CHANNEL |
Identify if there is a wholesale channel setup in RMS. |
GLOBAL |
ITEM_GRP1_IS_INCREMENTAL |
Controls if item attributes are incremental or full snapshot on the daily load. Must be in sync with the same-named RI parameter. |
GLOBAL |
RMS_VERS_CHECK |
Controls the behavior of code that is linked to a specific RMS version. |
GLOBAL |
RA_INV_WAC_IND |
Controls the inventory cost calculation in RDE. When set to ‘Y’ it will use Weighted Avg Cost (WAC) as the item cost for all items. When set to ‘N’ it will dynamically load RMFCS valuation methods set per department or item and apply them, choosing from avg cost, unit cost, and retail-based cost. |
GLOBAL |
RA_INV_TAX_IND |
Controls the calculation and removal of tax amounts from retail valuation of stock on hand and on-order amounts. When set to ‘N’, only simple VAT (SVAT) calculations are supported and non-VAT items are left as-is. When set to ‘Y’, the system dynamically loads RMFCS global tax and VAT information and applies it by item/loc. |
GLOBAL | RA_SLS_TAX_IND | Controls the calculation and removal of tax amounts from retail valuation of sales. When set to ‘N’, VAT rates are included in the sales retail amounts (this is how it comes from Sales Audit). When set to ‘Y’ the VAT is removed from the retail amounts. Profit is always VAT-exclusive in either case, this applies to columns based on the total selling retail like SLS_AMT and RET_AMT. |
SDE_RETAILINVRECEIPTSFACT |
VWH_NO_ALC_RCPTS |
Specify a type of warehouse that cannot receive allocations in the feed to RI. Converts the allocs to normal transfer receipts. Uses codes from VWH_TYPE column in RMFCS. |
SDE_RETAILINVRECEIPTSFACT |
STORE_NO_ALC_RCPTS |
Specify a type of store that cannot receive allocations in the feed to RI. Converts the allocs to normal transfer receipts. |
SDE_RETAILITEMDIMENSION |
IS_INCREMENTAL |
Controls if the product dimension is full snapshot or incremental changes only for the daily load. |
SDE_RETAILITEMLOCATIONRANGEDIMENSION |
IS_INCREMENTAL |
Controls if the product location range dimension is full snapshot or incremental changes only for the daily load. |
SDE_RETAILITEMSUPPLIERDIMENSION |
IS_INCREMENTAL |
Controls if the supplier-item dimension is full snapshot or incremental changes only for the daily load. |
SDE_RETAILSUBSTITUTEITEMDIMENSION |
IS_INCREMENTAL |
Controls if the substitute-item dimension is full snapshot or incremental changes only for the daily load. |
SDE_RETAILITEMLOCATIONDIMENSION |
IS_INCREMENTAL |
Controls if the product location attr dimension is full snapshot or incremental changes only for the daily load. |
SDE_RETAILITEMLOCCFADIMENSION |
IS_INCREMENTAL |
Controls if the product loc CFAS dimension is full snapshot or incremental changes only for the daily load. |
SDE_RETAILSUPPCFADIMENSION |
IS_INCREMENTAL |
Controls if the supplier CFAS dimension is full snapshot or incremental changes only for the daily load. |
SDE_RETAILLOCATIONCFADIMENSION |
IS_INCREMENTAL |
Controls if the location CFAS dimension is full snapshot or incremental changes only for the daily load. |
SDE_RETAILITEMCFADIMENSION |
IS_INCREMENTAL |
Controls if the product CFAS dimension is full snapshot or incremental changes only for the daily load. |
Configuration Recommendations
Table 2-3 Configuration Recommendations
Scenario | Details |
---|---|
First Time Dimension Loads (Full vs. Incremental) |
When first loading data into RI, you will need to ensure all IS_INCREMENTAL flags are set to a value of N, meaning the programs will expect full snapshots of your data and not deltas only. |
First Time Dimension Loads (Seeding) |
When loading fact data for the first time, you will want to verify the seeding indicators are mostly set to a value of Y, meaning that if any fact record has an unknown identifier, we will try to create the record for it to avoid rejected data or failures. For example, RI_MIS_COHEAD_REQ_IND must be set to Y to auto-seed customer order IDs that may appear on your sales transaction history. |
Inventory History Loads |
When loading inventory history, you should disable (set to N) all configuration options that you do not need in the final dataset, in order to improve the load performance. For example, RI_BOH_SEEDING_IND should be disabled if you don’t need RI to create zero-balance BOH records, as these records are purely for reporting purposes to show a zero instead of null values in initial BOH positions. |
Daily/Weekly Data Loads (Full vs. Incremental) |
Once you are done with initial data loads, you may want to switch interfaces from full snapshot to incremental. Update the IS_INCREMENTAL parameters to Y at this time to start accepting regular delta files. If you are loading data from RMFCS you must synchronize this between programs to avoid failures in the batch (for example, if you change RDE to send incremental attributes data, but RI expects full snapshot, then the RI batch will deactivate all records which don’t come on the file). |
Report Behaviors |
Several parameters in C_ODI_PARAM actually affect reporting behaviors and need to be configured properly before allowing end-users into the system. This includes: Comp Store handling, CFAS attribute visibility, and LY calendar shift type. |
Row Delimiter Usage
The configuration setting in C_ODI_PARAM for RI_UA_ROW_DELIMITER can be very helpful when integrating data from RMFCS that may contain traditional line-ending characters like \n or \r\n (the UNIX and Windows default line endings). When line-endings are encountered within a record that match the actual line-ending character on the data file rows, RI will fail to process those records because of the additional row breaks. For example, users may accidently copy and paste a string of text into RMFCS that includes these line-ending characters but have no knowledge of it, because the characters will be invisible when displayed back in the RMFCS UI.
With this RI_UA_ROW_DELIMITER parameter, you have the option to add additional characters to the RDE/RI flat file row delimiter, effectively allowing the system to process mid-line breaks without failing. A common sequence used is ~~\n instead of the default \n. This will cause RDE to append the ~~ symbols to every line of output data, and likewise RI will only accept line breaks if the same sequence is found. Once the parameter is set on both RDE and RI, the effect on the data is entirely transparent to the end user, as the extra characters will be ignored after the data is pulled into the RI database.
Also note that this parameter can apply to both DAT and CSV files. This means that by default, CSV files which don’t come from RMFCS should still use the line-ending character set on this parameter (i.e. UNIX line endings). If your files will have Windows line-endings, then you may want to change RI to use \r\n for this parameter. If you are using a version of RI that allows line-endings to be set on the Context Files, then this does not apply as the context files will override the system setting.
Error Tables
The following table describes database tables you may need to query during the initial data load process to identify and resolve rejected records in RI (using Data Visualizer to access the database, as described in the RAP Implementation Guide). Note that error tables do not exist unless created as part of the rejection process. The list below describes the most commonly used rejection tables, but all fact interfaces to RI have the ability to generate one using a similar naming scheme.
Table 2-4 Core Data Load Error Tables
Table Name | Usage |
---|---|
E$_W_RTL_BCOST_IT_LC_DY_TMP E$_W_RTL_NCOST_IT_LC_DY_TMP E$_W_RTL_PRICE_IT_LC_DY_TMP |
Rejection of base cost, net cost, and price records. The most common rejection reason is due to inactive items, locations, or suppliers in the dimension data. Closed items or locations should stop getting data on these interfaces but it can happen that the source system tries to send an update for them. |
E$_W_RTL_CUST_LYL_AWD_TRX_DY_T E$_W_RTL_CUST_LYL_TRX_LC_DY_TM |
Rejection of customer loyalty transaction records. The most common rejection reason is due to inactive or missing customers or loyalty account records. |
E$_W_RTL_FLEXFACT1_TMP E$_W_RTL_FLEXFACT2_TMP E$_W_RTL_FLEXFACT3_TMP E$_W_RTL_FLEXFACT4_TMP |
Rejection of flexible fact records. The most common reason for rejections is due to dimension identifiers on the incoming file not aligning with the configured data levels of the flex fact (such as department identifiers in a file intended for class level data). |
E$_W_RTL_INVADJ_IT_LC_DY_TMP E$_W_RTL_INVRC_IT_LC_DY_TMP E$_W_RTL_INVRECLASS_IT_LC_DY_T E$_W_RTL_INVRTV_IT_LC_DY_TMP E$_W_RTL_INVTSF_IT_LC_DY_TMP |
Rejection if various inventory transaction records. The most common reason for rejections is due to invalid or missing status codes, reason codes, or product/location records in the dimension data. |
E$_W_RTL_INV_IT_LC_DY_TMP E$_W_RTL_INV_IT_LC_DY_TMP1 |
Rejection of inventory position records. Positional data may be rejected if an item or location record is not active on the effective date of the fact record. The second table is only used for history load rejections. |
E$_W_RTL_PLAN1_PROD1_LC1_T1_TMP E$_W_RTL_PLAN2_PROD2_LC2_T2_TMP E$_W_RTL_PLAN3_PROD3_LC3_T3_TMP E$_W_RTL_PLAN4_PROD4_LC4_T4_TMP E$_W_RTL_PLAN5_PROD5_LC5_T5_TMP E$_W_RTL_PLANFC_PROD1_LC1_T1_T E$_W_RTL_PLANFC_PROD2_LC2_T2_T |
Rejection of planning and forecast data. The most common reason for rejections is due to dimension identifiers on the incoming file not aligning with the configured data levels of the fact (such as department identifiers in a file intended for class level data). |
E$_W_RTL_FACT1_PROD1_LC1_T1_TMP E$_W_RTL_FACT2_PROD2_LC2_T2_TMP E$_W_RTL_FACT3_PROD3_LC3_T3_TMP E$_W_RTL_FACT4_PROD4_LC4_T4_TMP |
Rejection of fact aggregate interface data. The most common reason for rejections is due to dimension identifiers on the incoming file not aligning with the configured data levels of the fact (such as department identifiers in a file intended for class level data). |
E$_W_RTL_SLSDSC_TRX_IT_LC_DY_T |
Rejection of sales discount data. The most common reason for rejections is due to invalid/missing discount type codes, promotion codes, or inactive item/locations on the date the transaction occurred. |
E$_W_RTL_SLS_TRX_IT_LC_DY_TMP |
Rejection of sales transaction data. Sales transactions have a large number of primary keys and could be rejected due to invalid/missing dimensions on any of them (employee, customer order number, item, location, date, time-of-day, etc.) |
E$_W_RTL_TRX_TNDR_LC_DY_TMP |
Rejection of sales tender data. The most common reason for rejections is due to invalid/missing tender type codes or inactive locations on the date the transaction occurred. |
Attribute Metadata Configuration
If you plan to use User-Defined Attributes (UDAs) in RI reports, then you will also want to provide a UDA configuration file to setup your most important reporting attributes. When UDA data first comes into RI, it is held in a raw row-based format where each row is an item/attribute value pair. From that data, RI can take up to 50 attribute groups and pivot them into a column-based format that is better suited to most reporting needs. Only attributes which are pivoted in this manner can be displayed side-by-side in reports as named columns. Unpivoted attributes have to pull from the larger row-based dataset which can take a significant amount of time to return results and cannot be displayed with more than one attribute group at a time.
The interface file for this configuration is W_RTL_UDA_METADATA_G.dat. It is a full load interface, meaning it should be sent nightly in the batch or the data will be dropped. If you do not have the ability to send it nightly, you can instead disable the job in POM after the file is loaded once. The file uses the legacy RI data format with pipe delimiters and Unix line endings. All columns on the interface should be provided in the file. The table below describes the fields in the interface.
Table 2-5 W_RTL_UDA_METADATA_G Interface Columns
Field Name | Usage |
---|---|
ATTR_NAME |
The attribute group ID for the UDA you want to place in a pivoted column. |
SOURCE |
Reference code for the source system providing the attributes data. Is not used at this time and could be hard-coded as “RMS”. |
PHYSICAL_COL_NAME |
The target column in RI where this UDA will be stored. Column names start from UDA_ATTR01_NAME and go through UDA_ATTR50_NAME. |
TABLE_NAME |
Reference code for the RI data table. Currently should be hard-coded as “W_PRODUCT_ATTR_D” |
DESCRIPTION |
Descriptive value for the attribute group specified on ATTR_NAME. |
DATA_TYPE |
Not used at this type – leave blank in the file |
DATASOURCE_NUM_ID |
Hard-code as “1” |
INTEGRATION_ID |
Set equal to the ATTR_NAME |
TENANT_ID |
Not used at this type – leave blank in the file |
X_CUSTOM |
Not used at this type – leave blank in the file |
The data below shows example records for five UDA groups which will be pivoted into the first five columns in the attributes table.
-
45|RMS|UDA_ATTR01_NAME|W_PRODUCT_ATTR_D|Color Family||1|45||
-
61|RMS|UDA_ATTR02_NAME|W_PRODUCT_ATTR_D|Silhouette||1|61||
-
7|RMS|UDA_ATTR03_NAME|W_PRODUCT_ATTR_D|Material||1|7||
-
18|RMS|UDA_ATTR04_NAME|W_PRODUCT_ATTR_D|Print||1|18||
-
29|RMS|UDA_ATTR05_NAME|W_PRODUCT_ATTR_D|Design||1|29||
Once this file is loaded into RI, the job which loads the UDA data in POM is W_RTL_PRODUCT_ATTR_UDA_D_JOB. This job will populate the target table W_RTL_PRODUCT_ATTR_UDA_D. If you are not sending the metadata file every night in batch, then after the load is successful you should disable the job W_RTL_UDA_METADATA_G_JOB and restart your POM schedule so it will take effect for the next batch.
The pivoted UDA data is accessible using Item dimension attributes. By default, these are named like “Item UDA ID 1” and “Item UDA Desc 1”. You may rename the attributes to better match their contents using Resource Bundle Customization screens in Retail Home. You may add one or more of these attributes into your Item level reporting, or use them to aggregate item level data to the UDA level of summarization.