Oracle Retail Knowledge Base

2 Setup and Configuration

25 min read

Mirror of Oracle documentation

Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.

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

ScenarioParameterUsage
GLOBALRI_UA_ROW_DELIMITE
R		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.
GLOBALNON_NUMERIC_HIERA
RCHY		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.

Table 2-1 (Cont.) C_ODI_PARAM Initial Setup

ScenarioParameterUsage
GLOBALLANGUAGE_CODEDefault language code used by
the system to load data. Do not
change unless your source
systems are also using a non-
English default language.
GLOBALRI_INV_HIST_DAYSThe 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.
GLOBALRI_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.
GLOBALPO_COST_ELC_INDSet to Y to include MFCS
expenses and duties in the
calculation of total on order
cost based on ELC
components associated with
the orders. It is disabled by
default, meaning ELC
components are not included in
the order cost.
GLOBALRI_PART_DDL_CNT_LIM
IT		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.
GLOBALRA_CLR_LEVELDisables the mapping of
clearance event IDs to
clearance inventory updates,
set to N to disable. Disabling
may improve batch
performance.
GLOBALANCHOR_TO_YEARSNumber of years of data to
consider for Same Stores
method of Comparable Store
reporting. Set to 3 if LLY is
required.

Table 2-1 (Cont.) C_ODI_PARAM Initial Setup

ScenarioParameterUsage
GLOBALSAME_STORESEnable or disable Same Stores
method of Comparable Store
reporting.
GLOBALGIFT_CARD_TENDER_T
YPE_ID		The tender type ID associated
with gift cards in RMS, for gift
card fact usage.
GLOBALCLSTR_GRP_TYPEControls the type of data
loaded to the Cluster
interfaces, either CLSTR for
store clusters or PRICE_ZONE
for price zones.
GLOBALITEM_CFA_VISIBILTY
LOC_CFA_VISIBILTY
SUPS_CFA_VISIBILTY
ITEM_LOC_CFA_VISIBIL
TY
MCAL_DAY_CFA_VISIBI
LTY		Controls access to CFAS
attributes in reporting, set to 0
to enable, 1 to disable.
GLOBALLY_SHIFT_TYPEControls 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).
GLOBALITEM_GRP1_IS_INCRE
MENTAL		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.
GLOBALRTVR_REASON_CAT_C
ODE		RMS code type for RTV reason
codes.

Table 2-1 (Cont.) C_ODI_PARAM Initial Setup

ScenarioParameterUsage
GLOBALRI_TRX_COUNT_RECL
ASS_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.
GLOBALRI_GEN_PROD_RECLA
SS_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
GLOBALITEM_GRP_DIV_RECLA
SS_ENABLED		By default, deleted items are
not included in product
reclassifications, and history
data for such items will remain
under their last known
hierarchy levels. If you need
deleted items to be included in
reclassifications, then you must
update the parameter to a
value of Y. Default = N.
GLOBALRI_INT_ORG_DS_MAND
ATORY_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.
GLOBALRI_PROD_DS_MANDAT
ORY_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.
GLOBALCURRENCY_CODESet the default currency code
to use when loading CSV-
based fact data files if none are
provided on the files
themselves. Defaults to ‘USD’.

Table 2-1 (Cont.) C_ODI_PARAM Initial Setup

ScenarioParameterUsage
GLOBALRI_EXCLUDE_VAT_INDDetermines if special Sales
metrics are displayed in RI 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.
GLOBALSTART_OF_YEAR_MON
TH		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
GLOBALHIST_ZIP_FILEChange the default name for
the ZIP file package used by
the history file load process.
Default=RAP_DATA_HIST.zip
GLOBALRI_AGG_FULL_LOAD_T
YPE		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
GLOBALRI_ITEM_REUSE_INDEnable 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

Table 2-1 (Cont.) C_ODI_PARAM Initial Setup

ScenarioParameterUsage
GLOBALRI_ITEM_REUSE_AFTE
R_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
GLOBALRI_LOC_CLOSE_AFTER
_DAYS		The number of days a location
record will be kept active after it
has been dropped from the
inbound interfaces such as
ORGANIZATION.csv. After the
number of days is passed, the
location will be closed, marked
inactive, and may not be re-
added to the interface again.
Default = 0
GLOBALRI_LAST_MKDN_HIST_I
ND		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.
GLOBALPDS_PROD_INCLUDE_I
TEM_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

Table 2-1 (Cont.) C_ODI_PARAM Initial Setup

ScenarioParameterUsage
GLOBALRI_LAST_MKDN_HIST_I
ND		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.
GLOBALPDS_PROD_INCLUDE_I
TEM_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
GLOBALPDS_PROD_INCLUDE_
NS_ITEMS		Control if the PDS product
export should include non-
sellable items in
W_PDS_PRODUCT_D or not.
When changed to a value of
”Y”, the export will include non-
sellable items. Default = N
GLOBALPDS_TRANSFER_SOUR
CE_TYPE		Control the export of inventory
transfers data to PDS based on
the source system and data
structure used. Must be set to a
value of “MFCS” or “NON-
MFCS”. When the value is
”NON-MFCS” (default setting),
the data from the input file is
directly exported to PDS
without transformation. When
set to “MFCS”, the data is
transformed to aggregate total
transfer in/out activity based on
the to-locations on the
transfers.

Table 2-1 (Cont.) C_ODI_PARAM Initial Setup

ScenarioParameterUsage
GLOBALPDS_EXPORT_DAILY_O
NORD		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.
GLOBALPDS_EXPORT_DAILY_I
NV		Set to ‘Y’ if you want daily data
to be sent from the data
warehouse to
W_PDS_INV_IT_LC_WK_A.
This flag only affects the
EOW_DATE value as the
inventory export is always a full
snapshot of current positions.
Default = N
GLOBALPDS_EXPORT_DAILY_S
LS		Set to ‘Y’ if you want daily data
to be sent from the data
warehouse to
W_PDS_SLS_IT_LC_WK_A
and
W_PDS_GRS_SLS_IT_LC_W
K_A. Default = N
GLOBALPDS_EXPORT_DAILY_S
LSWF		Set to ‘Y’ if you want daily data
to be sent from the data
warehouse to
W_PDS_SLSWF_IT_LC_WK_
A. Default = N
GLOBALPDS_EXPORT_DAILY_I
NVRC		Set to ‘Y’ if you want daily data
to be sent from the data
warehouse to
W_PDS_INVRC_IT_LC_WK_A
. Default = N
GLOBALPDS_EXPORT_DAILY_I
NVTSF		Set to ‘Y’ if you want daily data
to be sent from the data
warehouse to
W_PDS_INVTSF_IT_LC_WK_
A. Default = N
GLOBALPDS_EXPORT_DAILY_I
NVADJ		Set to ‘Y’ if you want daily data
to be sent from the data
warehouse to
W_PDS_INVADJ_IT_LC_WK_
A. Default = N

Table 2-1 (Cont.) C_ODI_PARAM Initial Setup

ScenarioParameterUsage
GLOBALPDS_EXPORT_DAILY_I
NVRTV		Set to ‘Y’ if you want daily data
to be sent from the data
warehouse to
W_PDS_INVRTV_IT_LC_WK_
A. Default = N
GLOBALPDS_EXPORT_DAILY_I
NVRECLASS		Set to ‘Y’ if you want daily data
to be sent from the data
warehouse to
W_PDS_INVRECLASS_IT_LC
_WK_A. Default = N
GLOBALPDS_EXPORT_DAILY_M
KDN		Set to ‘Y’ if you want daily data
to be sent from the data
warehouse to
W_PDS_MKDN_IT_LC_WK_A.
Default = N
GLOBALPDS_EXPORT_DAILY_D
EALINC		Set to ‘Y’ if you want daily data
to be sent from the data
warehouse to
W_PDS_DEALINC_IT_LC_WK
_A. Default = N
GLOBALPDS_EXPORT_DAILY_I
CM		Set to ‘Y’ if you want daily data
to be sent from the data
warehouse to
W_PDS_ICM_IT_LC_WK_A.
Default = N
PLP_RETAILFACTCLOSEFACTRI_PRICE_G_MOVE_MI
N_CNT		Controls the behavior of the
pricing fact update during a
major reclassification, specific
to the FACTCLOSE program.
When the reclass would require
updating more rows than this
parameter specifies, a more
performant logic is used for
extremely high volumes.
PLP_RETAILFACTCLOSEFACTRI_BCOST_G_MOVE_MI
N_CNT		Controls the behavior of the
base cost fact update during a
major reclassification, specific
to the FACTCLOSE program.
When the reclass would require
updating more rows than this
parameter specifies, a more
performant logic is used for
extremely high volumes.
PLP_RETAILFACTCLOSEFACTRI_NCOST_G_MOVE_M
IN_CNT		Controls the behavior of the net
cost fact update during a major
reclassification, specific to the
FACTCLOSE program. When
the reclass would require
updating more rows than this
parameter specifies, a more
performant logic is used for
extremely high volumes.

Table 2-1 (Cont.) C_ODI_PARAM Initial Setup

ScenarioParameterUsage
PLP_RETAILFACTCLOSEFACTRI_INV_G_MOVE_MIN_
CNT		Controls the behavior of the
inventory fact update during a
major reclassification, specific
to the FACTCLOSE program.
When the reclass would require
updating more rows than this
parameter specifies, a more
performant logic is used for
extremely high volumes.
PLP_RETAILFACTOPENFACTRI_PRICE_G_OPENCLO
SE_MIN_CNT		Controls the behavior of the
pricing fact update during a
major reclassification, specific
to the FACTOPEN program.
When the reclass would require
updating more rows than this
parameter specifies, a more
performant logic is used for
extremely high volumes.
PLP_RETAILFACTOPENFACTRI_BCOST_G_OPENCL
OSE_MIN_CNT		Controls the behavior of the
base cost fact update during a
major reclassification, specific
to the FACTOPEN program.
When the reclass would require
updating more rows than this
parameter specifies, a more
performant logic is used for
extremely high volumes.
PLP_RETAILFACTOPENFACTRI_NCOST_G_OPENCL
OSE_MIN_CNT		Controls the behavior of the net
cost fact update during a major
reclassification, specific to the
FACTOPEN program. When
the reclass would require
updating more rows than this
parameter specifies, a more
performant logic is used for
extremely high volumes.
PLP_RETAILFACTOPENFACTRI_INV_G_OPENCLOSE
_MIN_CNT		Controls the behavior of the
inventory fact update during a
major reclassification, specific
to the FACTOPEN program.
When the reclass would require
updating more rows than this
parameter specifies, a more
performant logic is used for
extremely high volumes.

Table 2-1 (Cont.) C_ODI_PARAM Initial Setup

ScenarioParameterUsage
PLP_RETAILINVPOSITIONITLCWKAGGR
EGATE		INV_CLR_REQ_INDControls 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_DAYDIMENSIONEND_DTEnd 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_DAYDIMENSIONSTART_DTStart 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_DAYDIMENSIONWEEK_START_DT_VALStarting day of the week for the
system calendar (1 = Sunday).
SIL_RETAILINVPOSITIONFACTRI_INVAGE_REQ_INDDisables calculation of first/last
receipt dates and inventory age
measures. Disabling may
improve batch performance.
SIL_RETAILINVPOSITIONFACTRI_PRES_STOCK_INDDisables usage of
replenishment data for
presentation stock to calculate
inventory availability measures
Disabling may improve batch
performance.
SIL_RETAILINVPOSITIONFACTRI_BOH_SEEDING_INDDisables 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_RETAILINVPOSITIONFACTRI_MOVE_TO_CLR_INDDisables calculation of move-
to-clearance inventory
measures when an item/
location goes into or out of
clearance status. Disabling may
improve batch performance.

Table 2-1 (Cont.) C_ODI_PARAM Initial Setup

ScenarioParameterUsage
SIL_RETAILINVPOSITIONFACTRI_MULTI_CURRENCY_
IND		Disables recalculation of
primary currency amounts if
you are only using a single
currency. Disabling may
improve batch performance.
SIL_RETAILINVPOSITIONFACTINV_FULL_LOAD_INDControl 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_ITEMDIMENSIONIS_INCREMENTALControls if the product
dimension is full snapshot or
incremental changes only for
the daily load.
SIL_RETAILITEMCFADIMENSIONIS_INCREMENTALControls if the product CFAS
dimension is full snapshot or
incremental changes only for
the daily load.
SIL_RETAILITEMLOCCFADIMENSIONIS_INCREMENTALControls if the product loc
CFAS dimension is full
snapshot or incremental
changes only for the daily load.
SIL_RETAILLOCATIONCFADIMENSIONIS_INCREMENTALControls if the location CFAS
dimension is full snapshot or
incremental changes only for
the daily load.
SIL_RETAILSUPPCFADIMENSIONIS_INCREMENTALControls if the supplier CFAS
dimension is full snapshot or
incremental changes only for
the daily load.
SIL_RETAILSUBSTITUTEITEMDIMENSIO
N		IS_INCREMENTALControls if the substitute item
dimension is full snapshot or
incremental changes only for
the daily load.
SIL_RETAILPROMOTIONDIMENSIONRI_INCREMENTAL_INDControls if the promotion
dimension is full snapshot or
incremental changes only for
the daily load.

Table 2-1 (Cont.) C_ODI_PARAM Initial Setup

ScenarioParameterUsage
SIL_RETAILPOONORDERFACTPO_FULL_LOAD_INDControl 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_SEEDEMPLOYEEDIMENSIONRI_MIS_CASHIER_REQ
_IND		Seed missing Cashier IDs from
sales fact to Employee
dimension.
SIL_SEEDCOHEADDIMENSIONRI_MISSLS_COHEAD_R
EQ_IND		Seed missing customer order
(CO) head IDs from sales fact
to CO Dimension.
SIL_SEEDCOLINEDIMENSIONRI_MIS_COLINE_REQ_I
ND		Seed missing customer order
(CO) line IDs from sales fact to
CO Dimension.
SIL_SEEDCOUPONDIMENSIONRI_MIS_COUPON_REQ
_IND		Seed missing coupon IDs from
sales discount fact to Coupon
dimension.
SIL_SEEDCUSTOMERDIMENSIONRI_MIS_CUSTOMER_R
EQ_IND		Seed missing customer IDs
from sales fact to Customer
dimension.
SIL_SEEDCUSTOMERLOYALTYAWARDAC
COUNTDIMENSION		RI_SEED_AWARD_ACC
OUNT_IND		Seed missing award account
IDs from loyalty fact to Award
Account dimension.
SIL_SEEDDISCOUNTTYPEDIMENSIONRI_MIS_DISC_TYPE_RE
Q_IND		Seed missing discount type
codes from sales discount fact
to Discount Type dimension.
SIL_SEEDPROMOTIONDIMENSIONRI_MISSLS_PROMO_RE
Q_IND		Seed missing promotions from
the sales promo fact to the
Promotion dimension.
SIL_SEEDSTOCKCOUNTDIMENSIONRI_MIS_STOCK_CNT_R
EQ_IND		Seeds missing stock counts
from the systemic (RMS) stock
count fact to the stock count
dimension.
SIL_RETAILSALESPROMOTIONTRANSA
CTIONFACT		RI_EXT_PROMO_COLSelect 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.

Table 2-1 (Cont.) C_ODI_PARAM Initial Setup

ScenarioParameterUsage
SIL_RETAIL_SALESPOS_DATA_HANDLIN
G		PURGE_POSXML_DAYSNumber 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

ScenarioParameterUsage
GLOBALRDE_UA_ROW_DELI
MITER		Default row delimiter on incoming data
files (both RMS and external).
GLOBALRI_UA_ROW_DELIMIT
ER		Default row delimiter on data files going
out to RI, must match with RI incoming
UA delimiter value.
GLOBALRPM_PROMO_EVENT
_LEVEL		Enable (set to ‘Y’) if using legacy RPM
on-premise functionality.
GLOBALRETURN_REASON_C
AT_CODE		RMS code type for customer return
reason codes.
GLOBALRTVR_REASON_CAT
_CODE		RMS code type for RTV reason codes.
GLOBALWHOLESALE_CHANN
EL		Identify if there is a wholesale channel
setup in RMS.
GLOBALITEM_GRP1_IS_INCR
EMENTAL		Controls if item attributes are incremental
or full snapshot on the daily load. Must
be in sync with the same-named RI
parameter.
GLOBALRMS_VERS_CHECKControls the behavior of code that is
linked to a specific RMS version.
GLOBALRA_INV_WAC_INDControls 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.
GLOBALRA_INV_TAX_INDControls 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.

Table 2-2 (Cont.) C_ODI_PARAM RDE Configurations

ScenarioParameterUsage
GLOBALRA_SLS_TAX_INDControls 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.
GLOBALPO_EXCHANGE_DT_
TYPE		Controls the date used to convert
currency amounts on purchase orders
extracted from Merchandising
Foundation CS. By default, the system
will convert currency amounts using the
current business date (C), which means
a change to exchange rates will also
change the converted cost and retail
amounts on the order. Update this
parameter to a value of ‘A’ to use the PO
Approval Date, which does not change
each time the order data is extracted,
resulting in fixed cost/retail rates over
time.
GLOBALPO_PACK_LEVEL_IN
D		When set to ‘Y’, extracts purchase orders
from MFCS at pack item level. When set
to ‘N’, pack items are converted to their
components. Default value is N, meaning
that all PO data is extracted at
component item level and packs are split
into their components. This setting may
be needed for Inventory Planning &
Optimization (IPO) projects where pack
items will be allocated or transferred at
pack level.
SDE_RETAILINVRECEIPTSFACTVWH_NO_ALC_RCPT
S		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_RETAILINVRECEIPTSFACTSTORE_NO_ALC_RC
PTS		Specify a type of store that cannot
receive allocations in the feed to RI.
Converts the allocs to normal transfer
receipts.
SDE_RETAILITEMDIMENSIONIS_INCREMENTALControls if the product dimension is full
snapshot or incremental changes only for
the daily load.
SDE_RETAILITEMLOCATIONRANG
EDIMENSION		IS_INCREMENTALControls if the product location range
dimension is full snapshot or incremental
changes only for the daily load.
SDE_RETAILITEMSUPPLIERDIME
NSION		IS_INCREMENTALControls if the supplier-item dimension is
full snapshot or incremental changes
only for the daily load.

Table 2-2 (Cont.) C_ODI_PARAM RDE Configurations

ScenarioParameterUsage
SDE_RETAILSUBSTITUTEITEMDI
MENSION		IS_INCREMENTALControls if the substitute-item dimension
is full snapshot or incremental changes
only for the daily load.
SDE_RETAILITEMLOCATIONDIME
NSION		IS_INCREMENTALControls if the product location attr
dimension is full snapshot or incremental
changes only for the daily load.
SDE_RETAILITEMLOCCFADIMENS
ION		IS_INCREMENTALControls if the product loc CFAS
dimension is full snapshot or incremental
changes only for the daily load.
SDE_RETAILSUPPCFADIMENSIONIS_INCREMENTALControls if the supplier CFAS dimension
is full snapshot or incremental changes
only for the daily load.
SDE_RETAILLOCATIONCFADIMEN
SION		IS_INCREMENTALControls if the location CFAS dimension
is full snapshot or incremental changes
only for the daily load.
SDE_RETAILITEMCFADIMENSIONIS_INCREMENTALControls if the product CFAS dimension
is full snapshot or incremental changes
only for the daily load.

Configuration Recommendations

Table 2-3 Configuration Recommendations

ScenarioDetails
First Time Dimension
Loads (Full vs.
Incremental)		When frst loading data into RI, you will need to ensure all
IS_INCREMENTAL fags 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 frst 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 identifer, 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 LoadsWhen loading inventory history, you should disable (set to N) all
confguration options that you do not need in the fnal 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 fles. 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 fle).

Table 2-3 (Cont.) Configuration Recommendations

ScenarioDetails
Report BehaviorsSeveral parameters in C_ODI_PARAM actually affect reporting
behaviors and need to be confgured 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 midline 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 NameUsage
E$_W_RTL_BCOST_IT_LC_DY_TMP
E$_W_RTL_NCOST_IT_LC_DY_TMP		Rejection of base cost, net cost, and price records.
The most common rejection reason is due to
E$_W_RTL_PRICE_IT_LC_DY_TMPinactive 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_TMPRejection of fexible fact records. The most common
E$_W_RTL_FLEXFACT2_TMPreason for rejections is due to dimension identifers
E$_W_RTL_FLEXFACT3_TMP
E$_W_RTL_FLEXFACT4_TMP		on the incoming fle not aligning with the
confgured data levels of the fex fact (such as
department identifers in a fle 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		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_INVTSF_IT_LC_DY_TMP
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		Rejection of planning and forecast data. The most
common reason for rejections is due to dimension
identifers on the incoming fle not aligning with the
confured data levels of the fact (such as
E$_W_RTL_PLAN4_PROD4_LC4_T4_TMPg
department identifers in a fle intended for class
E$_W_RTL_PLAN5_PROD5_LC5_T5_TMPlevel data).
E$_W_RTL_PLAN6_PROD6_LC6_T6_TMP
E$_W_RTL_PLANFC_PROD1_LC1_T1_T
E$_W_RTL_PLANFC_PROD2_LC2_T2_T
E$_W_RTL_FACT1_PROD1_LC1_T1_TMP
E$_W_RTL_FACT2_PROD2_LC2_T2_TMP		Rejection of fact aggregate interface data. The most
common reason for rejections is due to dimension
identifers on the incoming fle not aligning with the
E$_W_RTL_FACT3_PROD3_LC3_T3_TMP
E$_W_RTL_FACT4_PROD4_LC4_T4_TMP
confgured data levels of the fact (such as
department identifers in a fle intended for class
level data).
E$_W_RTL_SLSDSC_TRX_IT_LC_DY_TRejection 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_TMPRejection 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.)

Table 2-4 (Cont.) Core Data Load Error Tables

Table NameUsage
E$_W_RTL_TRX_TNDR_LC_DY_TMPRejection 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 NameUsage
ATTR_NAMEThe attribute group ID for the UDA you want to
place in a pivoted column.
SOURCEReference code for the source system providing the
attributes data. Is not used at this time and could be
hard-coded as “RMS”.
PHYSICAL_COL_NAMEThe target column in RI where this UDA will be
stored. Column names start from
UDA_ATTR01_NAME and go through
UDA_ATTR50_NAME.
TABLE_NAMEReference code for the RI data table. Currently
should be hard-coded as “W_PRODUCT_ATTR_D”
DESCRIPTIONDescriptive value for the attribute group specifed
on ATTR_NAME.
DATA_TYPENot used at this type – leave blank in the fle
DATASOURCE_NUM_IDHard-code as “1”
INTEGRATION_IDSet equal to the ATTR_NAME
TENANT_IDNot used at this type – leave blank in the fle
X_CUSTOMNot used at this type – leave blank in the fle

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.

Oracle Analytics Configuration

Oracle Analytics Cloud (OAC) will be provisioned with a fixed set of configurations depending on the hardware levels purchased. These settings cannot be changed by the end user from the application and may result in errors while working in Retail Insights, so it is important to understand them. The most impactful OAC settings for Retail Insights are listed below.

Parameter2-12 OCPU Sizing16+ OCPU Sizing
Max input rows returned from any
data source query		2,000,0004,000,000
Max summarized rows displayed
from any data source query		500,0001,000,000
Query timeout from any data
source query		660 Secs660 Secs
Limits Exporting Data (DV
Workbooks) to CSV		2,000,0004,000,000
Limits Exporting Data (DV
Workbooks) to XSLX		50,000 (100,000 for 12 OCPU)100,000
Limits Exporting Data (Classic) to
CSV		2,000,0004,000,000
Limits Exporting Data (Classic) to
XSLX		200,000400,000
Data Size Limits (Classic Pixel-
Perfect Reports Offline)		2GB4GB
Data Size Limits (Classic Pixel-
Perfect Reports Bursting)		4GB8GB
BI Publisher (SQL Query timeout)1800 Secs3600 Secs
BI Publisher Max rows for CSV
output		4,000,0006,000,000
BI Publisher Max number rows in
XPT layout		200,000300,000
Max number of concurrent
scheduled jobs		410

The maximum row limits shown in the table are based on exports that contain up to 40 columns. Additional columns may reduce the maximum number of rows you can export. While these settings are generally equal to or greater than past Retail Insights configurations on Oracle Analytics Server, you may encounter some limits which are more restrictive than past versions. In those cases, you will be expected to adjust your export or delivery processes to fall within allowable row counts and data volumes.

For a complete list of all configurations scaled by OCPU count, refer to Administering Oracle Analytics Cloud on Oracle Cloud Infrastructure (Gen 2) documentation.

In this guide

Graph View

Backlinks