Setup and Configuration

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	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. 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. 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.
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.
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 to automatically generate item level reclass records. Can only be used in a non-RMS implementation. Requires that full product files are sent every day, in order to detect when an item moves between hierarchy positions even if no other change occurred.
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). Default = FS
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_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_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.
History Loads (Multi-threading)	Loading history involves extremely large datasets, and it is recommended to increase the number of parallel threads used in RI load programs to improve performance. Some programs can automatically perform parallel execution while others are currently relying on a DB parameter to set a fixed thread count (using a LOC_NUM_OF_THREAD param in C_ODI_PARAM). You may raise an SR to Oracle Support if you need assistance in determining the optimal thread count on programs before you start loading data. In any such SR, ensure you specify which data is to be loaded and the expected data volume (in number of rows).
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_TM E$_W_RTL_PLAN2_PROD2_LC2_T2_TM E$_W_RTL_PLAN3_PROD3_LC3_T3_TM E$_W_RTL_PLAN4_PROD4_LC4_T4_TM 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_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.