4 Integration with Merchandising
This chapter describes the various integrations between Retail Merchandising Foundation Cloud Services (RMFCS) and the Retail Analytics and Planning platform. RMFCS can be used as the primary source of foundation data for RAP and pre-built integrations and batch programs exist to move data between cloud applications. You may also use an on-premise installation of the Retail Merchandising System (RMS), in which case you must establish the integration to the RAP cloud following the guidance in this chapter.
Architecture Overview
In prior releases, the integration between RMFCS and RI/AIF used a tool named the Retail Data Extractor (RDE) to generate data files for RI/AIF to consume. These programs have been fully integrated to the RAP batch flow and directly insert the data from RMFCS to RAP. The integration uses an instance of Oracle Golden Gate to copy RMFCS tables to the local database, where the Data Extractor jobs can source all required data and transform it for use in RAP. If you are familiar with the prior RDE architecture, then you need to be aware of the following major changes:
-
RDE_DM01
database objects are now located in theRADM01
schema.RDE_RMS01
database objects are now in theRABE01USER
schema. TheRABE01USER
now has access to extract records from RMFCS through the Golden Gate replicated schema. -
The
C_ODI_PARAM
configuration tables have been merged and all RDE configurations are accessed from the Control & Tactical Center. -
File-based integration has been removed. All data is moved directly between database source and target tables with no option to produce flat files.
-
RDE’s batch schedule has been merged with RI’s schedule in POM. Jobs have been renamed and assigned modules such that it is easy to identify and disable/enable RDE jobs as needed.
-
Customer Engagement (CE) integration jobs have been included in RDE for when CE is set up to replicate data to RAP. File-based integration is no longer required.
-
All jobs relating to file extraction, ZIP file creation, or data cleanup in RMS have been removed.
Because RDE jobs are now a part of the RAP nightly batch cycle, they have been assigned modules in the Customer Modules framework (accessed using Retail Home) and can be enabled or disabled in bulk depending on your use-cases.
-
RDE_RMS
– These are the RDE components in relation to RMS -
RDE_CE
– These are the RDE components in relation to CE
If you are not integrating data from RMFCS or CE then you will need to disable these modules to prevent them from running in your nightly batch cycles. RI and AIF jobs are programmed to start automatically after the RDE jobs complete, but if the RDE jobs are disabled then the dependencies will be ignored.
Batch Schedule Definitions
The RDE jobs have been labeled and categorized by their primary purpose, so you can easily identify jobs you may want to
enable or disable for your implementation. The bulk of the RDE jobs are divided into two main classifications, which are dimension
jobs (those with RDE_EXTRACT_DIM_*
in their job names) and fact jobs (those with RDE_EXTRACT_FACT_*
in their job names). They are also grouped into execution phases based on their functional usage, as described in the tables
below. The phases are all run in parallel to each other and don’t have dependencies between them, they are purely for ease
of use.
Table 4-1 RDE Dimension Phases
DIMENSION PHASE No. | GROUPING FACTOR |
---|---|
P1 |
Consists of dimension jobs that populate the |
P2 |
Consists of the Security jobs (RAF table entries) |
P3 |
Consists of dimension jobs related to the item (for example, Merch hierarchy, item charges, item season, |
P4 |
Consists of dimension jobs related to Custom Flex Attributes (CFA) |
P5 |
Consists of dimension jobs related to Replenishment |
P6 |
Consists of dimension jobs related to Merch Organization Hierarchy (for example, location list, location traits, organization financial information, and so on) |
P7 |
Consists of dimension jobs related to Transfers (for example, transfer details, transfer charges, and so on) |
P8 |
Consists of dimension jobs related to extract of codes used by Merchandising (for example, Inventory Adjustment reasons, Inventory status types, codes, and so on) |
P9 |
Consists of dimension jobs related to the supplier (for example, supplier traits, supplier information, and so on) |
P10 |
Consists of dimension jobs related to Purchase Orders (for example, PO Details, Shipment Details, ELC, and so on) |
P11 |
Consists of dimension jobs related to allocation (for example, Allocation detail, allocation charges, and so on) |
P12 |
Consists of dimension jobs related to Merchandising Calendar information |
P13 |
Consists of dimension jobs related to Merchandising promotions |
P14 |
Consists of dimension jobs related to employee information |
P15 |
Consists of dimension jobs related to competitor information |
P16 |
Consists of dimension jobs related to Merchandising Parameter tables ( |
P17 |
Consists of dimension jobs related to buyer information |
P18 |
Consists of Merchandising Lookup Dimension information. |
Table 4-2 RDE Fact Phases
FACT PHASE No. | GROUPING FACTOR |
---|---|
P1 |
Consists of cost fact information jobs (for example, Base Cost, net Cost, and so on) |
P2 |
Consists of price fact information jobs (for example, item price, competitor price, and so on) |
P3 |
Consists of purchase order fact jobs |
P4 |
Consists of transfer / RTV fact jobs |
P5 |
Consists of Supplier fact jobs |
P6 |
Consists of Stock Ledger fact jobs |
P7 |
Consists of fact jobs related to inventory (for example, unavailable inventory, inventory receipt, and so on) |
P8 |
Consists of fact jobs related to transactions - they mostly read from |
P9 |
Consists of Allocation fact jobs |
P10 |
Consists of stock count fact jobs |
Ad Hoc Processes
There are several standalone ad hoc processes available for executing the RDE programs outside of a normal batch cycle. These processes can be used to integrate dimension or fact data to RAP during initial implementation, or simply to run the extracts and validate the outputs without actually loading them into the platform. The table below summarizes these processes and their usage.
Table 4-3 RDE Ad Hoc Processes
Process Name | Usage |
---|---|
RDE_EXTRACT_DIM_INITIAL_ADHOC |
Execute the dimension data extracts from RMFCS and write the result
to RAP input staging tables directly. Data can then be moved into RAP if desired by using the |
RDE_EXTRACT_CE_DIM_INITIAL_ADHOC |
Execute the dimension data extracts from CE and write the result
to RAP input staging tables directly. Data can then be moved into RAP if desired by using the |
RDE_EXTRACT_FACT_INITIAL_ADHOC |
Execute the fact data extracts from RMFCS and write the result to RAP input staging tables directly. This is mainly intended to allow a complete run of RDE fact jobs outside the normal batch process, loading the data into RI would require the use of numerous fact jobs and processes depending on the data needed. This process may run as full or incremental loads depending on the POM system options used for ODI runs. |
RDE_POSITIONALFACT_SEED_ADHOC |
Run all of the positional fact seed jobs needed to create full snapshots
of positional data in RAP. After running these jobs, you may use the |
RDE_INVPOS_SEED_ADHOC |
Run the inventory position fact seed job needed to create full snapshots
of inventory data in RI. After running these jobs, you may use the |
RDE_INVRTVFACT_INITIAL_ADHOC |
Extract RTV transaction history from RMFCS to RAP staging tables.
Load the data into RAP using the |
RDE_IVADJILDSDE_INITIAL_ADHOC |
Extract adjustment transaction history from RMFCS to RAP staging
tables. Load the data into RAP using the |
RDE_IVTSFILDSDE_INITIAL_ADHOC |
Extract transfer transaction history from RMFCS to RAP staging tables.
Load the data into RAP using the |
RDE_DEALINILDSDE_INITIAL_ADHOC |
Extract deal income transaction history from RMFCS to RAP staging
tables. Load the data into RAP using the |
RDE_INVRECLASSSDE_HIST_ADHOC |
Extract inventory reclass transaction history from RMFCS to RAP
staging tables. Load the data into RAP using the |
RDE_INTCMPMRGINSDE_HIST_ADHOC |
Extract intercompany margin transaction history from RMFCS to RAP
staging tables. Load the data into RAP using the |
RDE_EXTRACT_SALES_ADHOC |
Extract daily sales data from the Sales Audit staging tables to the RAP staging tables. This process is mainly intended to test the sales extracts outside the normal batch and to validate the data transformations, or use the RAP sales history load ad hoc processes to bring the sales into the platform. |
RDE_TSFILDSDE_INITIAL_ADHOC |
Extracts a full snapshot of data from RMFCS for the transfer details
fact ( |
RDE_SHIPDETAILSDE_INITIAL_ADHOC |
Extracts a full snapshot of data from RMFCS for the shipment details
dimension ( |
RDE_SHIPILDSDE_INITIAL_ADHOC |
Extracts a full snapshot of data from RMFCS for the shipment details
fact ( |
RDE_ALLOCDETAILDYSDE_INITIAL_ADHOC |
Extracts a full snapshot of data from RMFCS for the allocation dimension
( |
RDE_POONALCILDSDE_INITIAL_ADHOC |
Extracts a full snapshot of data from RMFCS for the PO orders on
allocation fact ( |
RDE_REPLDAYSDE_INITIAL_ADHOC |
Extracts a full snapshot of data from RMFCS for the Replenishment
Days dimension ( |
RDE_REPLSUPDIMSDE_INITIAL_ADHOC |
Extracts a full snapshot of data from RMFCS for the Replenishment
Supplier Dims dimension ( |
Batch Dependency Setup (Gen 2 Architecture)
RDE jobs have pre-defined dependencies with RI, as well as interschedule dependencies with RMFCS. When you enable the RDE jobs, the dependencies with RI/AIF will be enabled automatically, but you will need to manually enable/disable the RMFCS interschedule dependencies based on your needs.
You should start with all dependencies enabled, and only disable them if you are trying to run the batch cycle out of sync
from the RMFCS batch. The inter-schedule dependencies fall into two categories: discreet jobs that perform some check on RMFCS
data, and POM dependencies that cross-reference another RMFCS batch program. The first category of jobs check the availability
of data from the RMFCS signaling table called RMS_RDE_BATCH_STATUS
. The RDE jobs that check the signaling
table in RMFCS are:
-
RDE_INTERSCHED_CHECK_RESAEXTRACT_PROCESS
/RDE_INTERSCHED_CHECK_RESAEXTRACT_JOB
- Checks the completion of theRESA_EXTRACT
job in RMFCS -
RDE_INTERSCHED_CHECK_INVSNAPSHOT_PROCESS
/RDE_INTERSCHED_CHECK_INVSNAPSHOT_JOB
- Checks the completion of theINVENTORY_SNAPSHOT
job that signifies that theITEM_LOC_SOH_EOD
table in RMFCS is now available for the RDE extract -
RDE_INTERSCHED_CHECK_STAGETRANDATA_PROCESS
/RDE_INTERSCHED_CHECK_STAGETRANDATA_JOB
- Checks the completion of theSTAGE_TRAN_DATA
job that signifies whether theIF_TRAN_DATA
table in RMFCS is now available for the RDE extract
If the RDE jobs run in parallel with the RMFCS batch, then all these jobs must be enabled. If you are running RDE jobs outside the RMFCS batch, then these jobs must be disabled during those runs. The jobs will wait indefinitely for a signal from the RMFCS batch, which they will never receive if you are running RDE jobs independently.
The second category of dependencies are found on the RDE jobs themselves when you click on a job to view its details in POM or click the Interschedule Dependencies link in Batch Monitoring UI. These jobs are listed below, along with the RMFCS jobs they depend on. You must verify these are enabled before trying to run RDE batches (unless the associated RMFCS job is disabled, in which case the RDE dependency can be turned off as well).
-
CSTISLDSDE_PROCESS
/CSTISLDSDE_JOB
– This RDE job waits for the following RMFCS jobs to complete:-
ALLOCBT_PROCESS / ALLOCBT_JOB
-
BATCH_RFMCURRCONV_PROCESS / BATCH_RFMCURRCONV_JOB
-
COSTCOMPUPD_ELCEXPRG_PROCESS / ELCEXCPRG_JOB
-
EDIDLCON_PROCESS / EDIDLCON_JOB
-
EXPORT_STG_PURGE_PROCESS / EXPORT_STG_PURGE_JOB
-
EDIUPAVL_PROCESS / EDIUPAVL_JOB
-
LIKESTOREBATCH_PROCESS / LIKESTOREBATCH_JOB
-
POSCDNLD_PROCESS / POSCDNLD_POST_JOB
-
REPLINDBATCH_PROCESS / REPLINDBATCH_JOB
-
SALESPROCESS_PROCESS / SALESUPLOADARCH_JOB
-
STKVAR_PROCESS / STKVAR_JOB
-
-
RDEBATCH_INITIAL_START_PROCESS
/RDEBATCH_INITIAL_START_MILEMARKER_JOB
– This RDE job waits for the RMFCS jobSTOP_RIB_ADAPTOR_INV_PROCESS
/STOP_RIB_ADAPTOR_INV_JOB
to complete.
If you cannot see any dependencies in the POM UI, then your POM system options may have them disabled. Make sure to check the System Configuration for AIF DATA and ensure the dependency options are set to Enabled.
![External and InterSchedule Dependencies Enabled External and InterSchedule Dependencies Enabled](img/extern-depend-intersched-depend-enable.png)
Batch Link Setup (Gen 2 Architecture)
Aside from the dependencies, you also need to be aware of the Schedule Link that is defined between MERCH and AIF DATA
schedules. By default, the schedule link will be disabled, so you must go into the AIF DATA schedule links section from Batch
Monitoring and enable any links shown. The schedule link will allow RDE and RI jobs to start automatically after Merchandising
jobs are run in their nightly batch cycle. There is one schedule link defined that will trigger the RDE job RDEBATCH_INITIAL_START_MILEMARKER_JOB
after Merchandising runs. If the MERCH and AIF DATA schedules are in the same POM instance, then you must enable the Schedule
Link before running any batches. If they are in different instances, then the link will not be visible and cannot be used.
Module Setup in Retail Home (Gen 2 Architecture)
Before you configure any individual jobs in POM itself, it is best to set up your Customer Modules in Retail Home to reflect your planned data flows. Go to the Customer Modules Management screen as an administrator user and review the options following this guidance:
-
Under the RAP > RAP_COMMON section:
-
Enable or disable the BATCH modules per your functional needs (enable everything if unsure).
-
Disable the HISTORY module if you are planning to immediately start loading Merchandising data. Enable it if you will load history data using CSV file interfaces.
-
Enable or disable the AIF modules based on your AI Foundation solution plans. Ensure AIF > CONTROLFILES is disabled as RDE jobs supercede this functionality.
-
Enable the RDE_CE and RDE_RMS modules fully, depending on which source applications are available in your environments.
-
Disable RDXBATCH if you are not using any Planning solution in RAP or enable it if you do plan to use MFP, AP, or IPO.
-
Disable SIBATCH and SICONTROLFILES module sets, because they are superceded by RDE integration.
-
Disable the ZIP_FILES modules unless you have a need for one of them for non-Mechandising data.
-
-
(For RI customers) Under the RI > COMMON section, disable all modules if you already configured them using the RAP module, or enable any needed ZIP files here.
-
(For RI customers) Under the RI > RCI section, enable or disable the module depending on your plans to use Retail Insights customer-related functionality. Disable the RI > RCI > CONTROLFILES and SICONTROLFILES modules, because RDE jobs replace this functionality. The BATCH modules can be left enabled if you are unsure whether these modules are needed.
-
(For RI customers) Under the RI > RMI section, enable or disable the module depending on your plans to use Retail Insights merchandising-related functionality. Disable the RI > RMI > CONTROLFILES and SICONTROLFILES modules, because RDE jobs replace this functionality. The BATCH modules can be left enabled if you are unsure whether these modules are needed.
Once all modules are configured, go back into the POM Batch Administration UI and perform a Sync with MDF action on the AIF DATA schedule. This is a one-time activity to streamline the POM schedule setup, after which you will want to perform a review of the POM schedule and refine the enabled/disabled jobs further to cover any specific file or data requirements.
Even if you are not syncing with MDF at this time, you must still perform the Retail Home setup because the CONTROLFILES
and SICONTROLFILES modules are used implicitly by AIF DATA schedule programs to know which data files to expect in the batch
runs. Any misconfiguration could lead to the program DAT_FILE_VALIDATE_JOB
failing or running for several
hours while waiting for data files you didn’t provide. Similarly, if you misconfigure the ZIP_FILES modules, then you will
encounter errors/delays in the ZIP_FILE_WAIT_JOB
as it looks for the expected ZIP files.
Batch Job Setup (Gen 2 Architecture)
The way you configure the integration with RMFCS varies depending on where your Merchandising applications reside. In this scenario, you have both RMFCS and CE in our 2nd generation architecture and the version number is 22.1 or greater.
If you followed the Retail Home setup steps prior to this section, then most of the jobs listed below should already be configured to your specifications. However, it is still good to validate the necessary jobs are enabled/disabled per the requirements before attempting any batch run. Take the following steps to review/configure the RDE portion of the AIF DATA batch schedule:
-
Enable the jobs
RDE_SETUP_INCRMNTL_RESA_JOB
andRDE_INCRMNTL_AUDIT_PRG_JOB
which are mandatory for sales integration with Sales Audit. -
Disable the batch schedule link between
BATCH_INITIAL_START_PROCESS / GENERIC_BATCH_MILE_MARKER_JOB
and RDE schedule’sRDE_BATCHFINAL_PROCESS / RDE_BATCHFINAL_EXTLOAD_SUCCESS_JOB
(if one is visible). This batch link should be disabled now because RDE is a part of AIF DATA’s schedule and is on the same POM and batch pod, which was not true in prior architectures. -
If the client opts not to integrate the ORCE customer data, the ORCE jobs can be disabled (those with
RDE_EXTRACT_CE_*
). These jobs are under the following modules:-
RDE_CE
-
RDE_CE_BATCH
-
RDE_CE_REQUIRED
-
RDE_CE_OPTIONAL
-
RDE_CE_CUSTOMER
-
RDE_CE_CUSTSEG
-
RDE_CE_LOYALTY
-
-
If the client opts to integrate the ORCE customer data (which means that the ORCE jobs are enabled - those with
RDE_EXTRACT_CE_*
), the following RI jobs should be disabled as the customer data will directly be populated without an input file:-
W_RTL_CUST_DEDUP_DS_COPY_JOB
-
W_RTL_CUST_DEDUP_DS_STG_JOB
-
W_RTL_CUST_LYL_AWD_TRX_DY_FS_COPY_JOB
-
W_RTL_CUST_LYL_AWD_TRX_DY_FS_STG_JOB
-
W_RTL_CUST_LYL_TRX_LC_DY_FS_STG_JOB
-
W_RTL_CUST_LYL_TRX_LC_DY_FS_COPY_JOB
-
W_RTL_CUST_LYL_ACCT_DS_COPY_JOB
-
W_RTL_CUST_LYL_PROG_DS_COPY_JOB
-
W_RTL_CUSTSEG_DS_COPY_JOB
-
W_RTL_CUSTSEG_DS_STG_JOB
-
W_RTL_CUSTSEG_DS_ORASE_JOB
-
W_RTL_CUST_CUSTSEG_DS_COPY_JOB
-
W_RTL_CUST_CUSTSEG_DS_STG_JOB
-
W_RTL_CUST_CUSTSEG_DS_ORASE_JOB
-
W_RTL_CUSTSEG_ATTR_DS_COPY_JOB
-
W_RTL_CUSTSEG_ATTR_DS_STG_JOB
-
W_RTL_CUST_HOUSEHOLD_DS_COPY_JOB
-
W_RTL_CUST_HOUSEHOLD_DS_STG_JOB
-
W_RTL_CUST_ADDRESS_DS_COPY_JOB
-
W_RTL_CUST_ADDRESS_DS_STG_JOB
-
W_PARTY_PER_DS_COPY_JOB
-
W_PARTY_PER_DS_STG_JOB
-
W_RTL_PARTY_PER_ATTR_DS_COPY_JOB
-
W_RTL_PARTY_PER_ATTR_DS_STG_JOB
-
W_HOUSEHOLD_DS_COPY_JOB
-
W_HOUSEHOLD_DS_STG_JOB
-
-
Disable most of the RI copy jobs (those with
*_COPY_JOB
) except ones needed for non-RMFCS sources. These jobs should be disabled because these jobs will copy files and upload them from object storage. This is not needed because data is loaded directly into the staging tables and flat files are not expected to arrive for processing. Most of these jobs are under the following modules:-
RI_DAT_STAGE
-
RSP_DAT_STAGE
-
-
Disable most of the RI stage jobs (those with
*_STG_JOB
) ) except ones needed for non-RMFCS sources. These jobs should be disabled as these jobs read from a flat file which are not available if using this integration. Most of these jobs are under the following modules:-
RI_DAT_STAGE
-
RSP_DAT_STAGE
-
-
Disable the RAP Simplified Interface jobs (those with
SI_*
,COPY_SI_*
, andSTG_SI_*
at the start of the name) as RMFCS will be the source of data to feed RI. Most of these jobs are under the modules with the patterns below:-
RI_SI*
-
RSP_SI*
-
-
Disable the RI program
W_PROD_CAT_DH_CLOSE_JOB
, which is used to close unused hierarchy levels when non-Merchandising incremental hierarchy loads are used in RAP. It must not run with Merchandising, because the product hierarchy data is already being managed by RDE extracts. -
Disable the RI programs
ETL_REFRESH_JOB
andBATCH_START_NOTIFICATION_JOB
(specifically the versions belonging to processSIL_INITIAL_PROCESS
) because these are redundant with jobs included in the RDE schedule. -
If you are not providing any flat file uploads and using only RMFCS data, you may disable the jobs in
CONTROL_FILE_VALIDATION_PROCESS
, which will prevent any data files from being processed (and potentially overwriting the RMFCS data). -
Disable the job named
TRUNCATE_STAGE_TABLES_JOB
, which is used only for data file loads and cannot be run when RDE jobs are used for direct integration. A similar job namedRDE_TRUNCATE_STAGE_TABLES_JOB
should remain enabled as this does apply to RDE job execution.
Batch Job Setup (Gen 1 Architecture)
If your RMFCS version is in the Oracle cloud but the version number is 19.3 or earlier, then follow this process to configure the integration. In this case, RDE is a separate module installed in the RMFCS cloud, so the batch must be reconfigured accordingly to have the correct dependencies.
-
Disable all the inter-schedule dependencies related to RDE in the RI schedule, as there is a separate batch schedule used for RDE that contains these:
-
CSTISLDSDE_PROCESS / CSTISLDSDE_JOB
dependency with RMFCSALLOCBT_PROCESS / ALLOCBT_JOB
-
CSTISLDSDE_PROCESS / CSTISLDSDE_JOB
dependency with RMFCSBATCH_RFMCURRCONV_PROCESS / BATCH_RFMCURRCONV_JOB
-
CSTISLDSDE_PROCESS / CSTISLDSDE_JOB
dependency with RMFCSCOSTCOMPUPD_ELCEXPRG_PROCESS / ELCEXCPRG_JOB
-
CSTISLDSDE_PROCESS / CSTISLDSDE_JOB
dependency with RMFCSEDIDLCON_PROCESS / EDIDLCON_JOB
-
CSTISLDSDE_PROCESS / CSTISLDSDE_JOB
dependency with RMFCSEXPORT_STG_PURGE_PROCESS / EXPORT_STG_PURGE_JOB
-
CSTISLDSDE_PROCESS / CSTISLDSDE_JOB
dependency with RMFCSEDIUPAVL_PROCESS / EDIUPAVL_JOB
-
CSTISLDSDE_PROCESS / CSTISLDSDE_JOB
dependency with RMFCSLIKESTOREBATCH_PROCESS / LIKESTOREBATCH_JOB
-
CSTISLDSDE_PROCESS / CSTISLDSDE_JOB
dependency with RMFCSPOSCDNLD_PROCESS / POSCDNLD_POST_JOB
-
CSTISLDSDE_PROCESS / CSTISLDSDE_JOB
dependency with RMFCSREPLINDBATCH_PROCESS / REPLINDBATCH_JOB
-
CSTISLDSDE_PROCESS / CSTISLDSDE_JOB
dependency with RMFCSSALESPROCESS_PROCESS / SALESUPLOADARCH_JOB
-
CSTISLDSDE_PROCESS / CSTISLDSDE_JOB
dependency with RMFCSSTKVAR_PROCESS / STKVAR_JOB
-
RDEBATCH_INITIAL_START_PROCESS / RDEBATCH_INITIAL_START_MILEMARKER_JOB
dependency with RMFCSSTOP_RIB_ADAPTOR_INV_PROCESS / STOP_RIB_ADAPTOR_INV_JOB
-
Disable the batch link related to RMFCS in the RI schedule:
-
SETUP_PROCESS / REFRESHODIVARIABLES_JOB
dependency with RMFCSSTOP_RIB_ADAPTOR_INV_PROCESS / STOP_RIB_ADAPTOR_INV_JOB
-
-
Disable all the ORCE jobs (those with
RDE_EXTRACT_CE*
). These jobs should not be executed because RDE is not in Gen 2 Architecture. The Customer Data jobs in RI should be enabled instead (for example,W_RTL_CUSTSEG_DS_COPY_JOB
,W_RTL_CUSTSEG_DS_STG_JOB
) -
Disable all the RDE jobs (those with
RDE_EXTRACT_*
). These jobs should not be executed as a separate RDE job schedule in POM will be setup for it. These jobs are under theRDE_RMS_*
modules. -
Make sure that the RI copy jobs (those with
*_COPY_JOB
) are enabled. These jobs should be enabled as these jobs will copy files and upload them to the object storage which is the source of the files for RI processing. -
Make sure that the RI stage jobs (those with
*_STG_JOB
) are enabled. These jobs should be enabled as these jobs will read from flat files and load them into the RI staging tables. -
Disable the RI Simplified Interface jobs (those with
SI_*
) as RMS will be the source of data to feed RI. Most of these jobs are under the following modules with the pattern below:-
RI_SI*
-
RSP_SI*
-
Batch Setup for RMS On-Premise
If your RMS application is installed on a local server outside the Oracle cloud, then you will need to integrate your local RDE installation with the RAP cloud following the guidance below. Additionally, you should also perform all the steps in the prior section to disable the RDE components of the RI POM schedule and enable the file load procedures, since you will be sending in files from RDE.
-
Download the latest RDE version 22 patch from My Oracle Support, as the changes to support Object Storage upload are deployed by running the installer and upgrading your RDE environment.
-
Disable FTP Configuration in RDE by setting the
input.do.install.ftp
tofalse
in theant.install.properties
file. This must be disabled because File Transfer Services (FTS) for the Retail Analytics and Planning cloud services are made available in this release, replacing the current SFTP process -
Check that the FTS configuration file
ra_objstore.cfg
is available in RDE's$MMHOME/etc
directory. The FTS configuration file contains the following variable set-up used for the Object Storage:-
RA_FTS_OBJSTORE_IND
– This will be set toY
so that FTS will be enabled -
RA_FTS_OBJSTORE_URL
– This is the Base URL -
RA_FTS_OBJSTORE_ENVNAMESPACE
– This is the Tenant -
RA_FTS_OBJSTORE_IDCS_URL
– This is the IDCS URL appended with/oauth2/v1/token
at the end -
RA_FTS_OBJSTORE_IDCS_CLIENTID
– This is the Client ID -
RA_FTS_OBJSTORE_IDCS_CLIENTSECRET
– This is the Client ID Secret -
RA_FTS_OBJSTORE_IDCS_SCOPE
– This is the IDCS Scope -
RI_OBJSTORE_UPLOAD_PREFIX
– This is the Storage Prefix and is set toris/incoming
pointing to the correct Object Storage directory for RI input files
Refer to the File Transfer Services section of this document for instructions on how to get the values for each of the variables above.
-
-
Enable the File Transfer Service (FTS) in RDE by setting the
RA_FTS_OBJSTORE_IND
toY
in the FTS Configuration filera_objstore.cfg
found in RDE’s$MMHOME/etc
directory. This must be enabled so that the RDE nightly zip file job (RTLRDEZIP_PROCESS
/RTLRDEZIP_JOB
) and all existing ad hoc zip file jobs (RTLUASDE_INITIAL_DIMMENSION_LOAD_ADHOC
/RTLRDEZIP_HIST_JOB
,RTLRDEZIP_HIST_PROCESS_ADHOC
/RTLRDEZIP_HIST_JOB
,INVRTVFACT_ADHOC
/ADHOCINVRTVSDE_JOB
,SEEDPOSITIONALFACT_ADHOC
/SEEDRDEZIP_JOB
) will automatically upload files to the Object Storage through FTS for RI to pick up and download for further processing. -
Once these changes are applied, it will no longer be possible to upload to SFTP; you will be sending the ZIP files only to Object Storage as specified in the install properties and configuration changes.
RDE Job Configuration
RDE programs have many configuration options available in the C_ODI_PARAM_VW
table in the Control &
Tactical Center. These must be reviewed prior to running RDE jobs. For any RDE job having an incremental flag below, the
associated jobs in the RI/AIF DATA batch schedule also have incremental flags that must be updated at the same time. Having
the RDE and RI flags be out of sync can result in data loss.
Scenario | Parameter | Usage |
---|---|---|
GLOBAL |
RPM_PROMO_EVENT_LEVEL |
Enable (set to |
GLOBAL |
RETURN_REASON_CAT_CODE |
Merchandising code type for customer return reason codes. |
GLOBAL |
RTVR_REASON_CAT_CODE |
Merchandising code type for RTV reason codes. |
GLOBAL |
WHOLESALE_CHANNEL |
Identify whether there is a wholesale channel setup in Merchandising. |
GLOBAL |
ITEM_GRP1_IS_INCREMENTAL |
Controls whether item attributes are incremental or full snapshots on the daily load. |
GLOBAL |
RMS_VERS_CHECK |
Controls the behavior of code that is linked to a specific Merchandising version. |
GLOBAL |
RA_INV_WAC_IND |
Controls the inventory cost
calculation in RDE. When set to |
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 |
SDE_RETAILINVRECEIPTSFACT |
VWH_NO_ALC_RCPTS |
Specify a type of warehouse that cannot receive allocations in the feed to RI. Converts the allocations to normal transfer
receipts. Uses codes from |
SDE_RETAILINVRECEIPTSFACT |
STORE_NO_ALC_RCPTS |
Specify a type of store that cannot receive allocations in the feed to RI. Converts the allocations to normal transfer receipts. |
SDE_RETAILITEMDIMENSION |
IS_INCREMENTAL |
Controls whether the product dimension is a full snapshot or incremental changes only for the daily load. |
SDE_RETAILITEMLOCATIONRANGEDIMENSION |
IS_INCREMENTAL |
Controls whether the product location range dimension is a full snapshot or incremental changes only for the daily load. |
SDE_RETAILITEMSUPPLIERDIMENSION |
IS_INCREMENTAL |
Controls whether the supplier-item dimension is a full snapshot or incremental changes only for the daily load. |
SDE_RETAILSUBSTITUTEITEMDIMENSION |
IS_INCREMENTAL |
Controls whether the substitute-item dimension is a full snapshot or incremental changes only for the daily load. |
SDE_RETAILITEMLOCATIONDIMENSION |
IS_INCREMENTAL |
Controls whether the product location attribute dimension is a full snapshot or incremental changes only for the daily load. |
SDE_RETAILITEMLOCCFADIMENSION |
IS_INCREMENTAL |
Controls whether the product location CFAS dimension is a full snapshot or incremental changes only for the daily load. |
SDE_RETAILSUPPCFADIMENSION |
IS_INCREMENTAL |
Controls whether the supplier CFAS dimension is a full snapshot or incremental changes only for the daily load. |
SDE_RETAILLOCATIONCFADIMENSION |
IS_INCREMENTAL |
Controls whether the location CFAS dimension is a full snapshot or incremental changes only for the daily load. |
SDE_RETAILITEMCFADIMENSION |
IS_INCREMENTAL |
Controls whether the product CFAS dimension is a full snapshot or incremental changes only for the daily load. |
The flags that impact the incremental/full load behavior for the RI jobs linked to RDE jobs are also provided below and should be configured at the same time.
Scenario | Parameter | Usage |
---|---|---|
SIL_ITEMDIMENSION |
IS_INCREMENTAL |
Controls whether the product dimension is a full snapshot or incremental changes only for the daily load. |
SIL_RETAILITEMCFADIMENSION |
IS_INCREMENTAL |
Controls whether the product CFAS dimension is a full snapshot or incremental changes only for the daily load. |
SIL_RETAILITEMLOCCFADIMENSION |
IS_INCREMENTAL |
Controls whether the product location CFAS dimension is a full snapshot or incremental changes only for the daily load. |
SIL_RETAILLOCATIONCFADIMENSION |
IS_INCREMENTAL |
Controls whether the location CFAS dimension is a full snapshot or incremental changes only for the daily load. |
SIL_RETAILSUPPCFADIMENSION |
IS_INCREMENTAL |
Controls whether the supplier CFAS dimension is a full snapshot or incremental changes only for the daily load. |
SIL_RETAILSUBSTITUTEITEMDIMENSION |
IS_INCREMENTAL |
Controls whether the substitute item dimension is a full snapshot or incremental changes only for the daily load. |
SIL_RETAILPROMOTIONDIMENSION |
RI_INCREMENTAL_IND |
Controls whether the promotion dimension is a full snapshot or incremental changes only for the daily load. |
Using RDE for Calendar Setup (Gen 2 Architecture)
RDE can be used to integrate the calendar from Merchandising for initial setup of a new RAP environment; however there are several manual steps you must take to perform this activity. You have the option of following the steps below, or you can perform the file-based calendar load as defined in Calendar and Partition Setup. Whichever option you choose, the calendar must be set up before you can load any other data from Merchandising.
-
The out-of-box Merchandising calendar does not conform to the preferred RAP calendar structure. The RAP calendar must start from Day 1 of a fiscal year, and the Merchandising calendar does not (if no changes were made to it after provisioning). You must use the Calendar Maintenance functions of Merchandising to edit the business calendar, adding or deleting periods such that the first fiscal period defined is period 1 of the fiscal year, and the first date for that period is day 1 of that fiscal year. Refer to the Configure Calendar section of the Merchandising Implementation Guide for details.. For example, if your fiscal calendar starts in February 2020, then the first record in the
CALENDAR
table must be for this same month.YEAR_454
will be2020
andMONTH_454
will be2
. -
Configure
C_ODI_PARAM
for yourSTART_DT
andEND_DT. START_DT
must be set in one of these ways:-
The first day of a fiscal year in the calendar (that is, the same date as the
FIRST_DAY
value for the first fiscal period of an FY). -
Some date earlier than anything in the
CALENDAR
table. If your firstCALENDAR
period is February 2020, thenSTART_DT
can be set to20190101
. The only reason to do this is if you want the Gregorian calendar to have additional Gregorian years available for any downstream use.
-
-
Configure the jobs in the AIF DATA schedule for loading the data. You will need the RDE job
RDE_EXTRACT_DIM_P12_MCALPERIODSDE_JOB
to extract the data, which is part of theRDE_EXTRACT_DIM_INITIAL_ADHOC
process. Configure theCALENDAR_LOAD_ADHOC
process to load this data and perform initial environment setup activities. You must disable the following jobs:-
COPY_SI_CALENDAR_JOB
-
STG_SI_CALENDAR_JOB
-
SI_W_MCAL_PERIODS_DS_JOB
-
-
Run
RDE_EXTRACT_DIM_P12_MCALPERIODSDE_JOB
to extract the data from Merchandising and verify that the tableW_MCAL_PERIOD_DS
contains the full calendar information by querying it from APEX. -
Run the modified
CALENDAR_LOAD_ADHOC
process and all later steps starting with step 4 in the Calendar and Partition Setup. -
If you have any failure on the
DIM_CALENDAR_VALIDATOR_JOB
, it means your Merchandising calendar may need more changes to align with the RAP data requirements. Modify the data in the source and start over, being sure to useC_LOAD_DATES_CLEANUP_ADHOC
to clear any failure statuses from the database before restarting.
If the validator job rule CAL_R2
continues to happen after updating the Merchandising calendar, but you
know that your first calendar year is not meeting the requirements and want to bypass the error for now, then you would need
to update the table C_DIM_RULE_LIST
from the Control & Tactical Center. Change the error type for this
rule to W
so that it only throws a warning in the batch program instead of failing. For example, if your
Merchandising calendar starts in 2010 and you are not going to load any data until 2020 in RAP, then having an invalid first
year of the calendar is not going to block you from other data load activities.
Using RDE for Dimension Loads (Gen 2 Architecture)
RDE can be used to integrate the foundation dimensions such as Product and Location hierarchies from Merchandising for initial setup of a new RAP environment. However, there are several manual steps you must take to perform this activity.
- The AIF DATA ad hoc process
RDE_EXTRACT_DIM_INITIAL_ADHOC
must be configured and run. Enable all jobs in the process if you want to extract all foundation dimensions, or selectively disable jobs you do not wish to run. Restart your POM schedule and then execute the process. The process will extract data from Merchandising tables (replicated to RAP through Golden Gate Hub) and directly insert data to RAP foundation staging tables. For example, the jobRDE_EXTRACT_DIM_P3_PRDHIERSDE_JOB
will source data from Merchanhdising hierarchy tables likeCLASS
andDEPS
and directly insert them into theW_PROD_CAT_DHS
table in RAP. - The AIF DATA ad hoc process
LOAD_DIM_INITIAL_ADHOC
will then be used to import the foundation data from the staging tables to the target tables. Before running the process, you must make sure all jobs are disabled relating to flat file loads, because you are bypassing those steps with the RDE jobs. Disable all jobs having a name withCOPY
,STG
,STAGING
, orSI
in the job name. Following the same flow for product hierarchy data as in step 1, you need to disable these jobs relating to the product hierarchy flat file load:- COPY_SI_PRODUCT_JOB
- STG_SI_PRODUCT_JOB
- SI_W_PROD_CAT_DHS_JOB
The remaining job that is active for the product hierarchy load is
W_PROD_CAT_DH_TYPE1_JOB
, which loads the RDE data from
W_PROD_CAT_DHS
to the W_PROD_CAT_DH
table.
- Once you have disabled all flat file jobs, restart the POM schedule as needed and then run
the
LOAD_DIM_INITIAL_ADHOC
process. This moves all Merchandising data from the RAP staging tables into the data warehouse internal tables. Once all jobs are complete, the remaining steps to move data to AIF and PDS are the same as documented in the Data Loads and Initial Batch Processing chapter.
Using RDE for Initial Seeding (Gen 2 Architecture)
RDE nightly batch programs can be used to perform initial seeding and full snapshots of positional facts without running any ad hoc processes. This provides a way to seamlessly transition from history data loads to nightly batch loads.
Prerequisites for starting this process are:
-
You must have already initialized the RAP calendar and performed database partitioning (either by using RDE as described in the prior sections or by loading a calendar file following the Calendar and Partition Setup process).
-
If you will load any historical data for prior dates, you must do that first and come back to this section when you are ready to cut over from history loads to RDE direct integration.
Follow the steps below to perform this transition to nightly batches:
-
Navigate to the System Options in POM for the RDE/RI batch schedule.
-
Update the variables RDE_RunFactVersion and RDE_RunFactODIVersion. By default, they should have a value of I as their rightmost input parameter, which is the normal incremental batch run. Change the value to F, which will trigger a full snapshot batch run. Do not change any other values already in these options except the letter
I
orF
at the end. -
Schedule and run the full Merchandising and AIF nightly batch cycle and validate that all nightly data was processed as expected in your RAP solutions. If you want to validate the RDE extracts prior to running the rest of the nightly batches in RAP (for example, to confirm the full extracts worked as intended) you may place a Hold on one of the first RI jobs in the schedule, such as
BATCH_START_NOTIFICATION_JOB
. If you are running the extract outside the Merchandising nightly batch, then remember that you must disable or skip the interschedule check jobs (any job starting withRDE_INTERSCHED*
) in the RDE flow that will prevent you from running without Merchandising.
The RDE_EXTRACT_FACT_INITIAL_ADHOC
process also uses these parameters to extract either full or incremental
datasets, so you may also use that to pull data out of Merchandising before you are ready to use the full nightly batch process.
Using RDE for Initial Seeding (Gen 1 Architecture)
RDE ad hoc batch programs in the RMFCS 19.x cloud can be used for initial seeding of RAP but the process is different from the 2nd generation architecture, as the integration is through flat files, not direct loads. This section assumes you have already set up your RAP applications, including calendar loads and partitioning, following the file-based approach documented in Setup and Configuration and Data Loads and Initial Batch Processing. Do not proceed with these steps until you have at least done the initial calendar and partition setup.
For daily batches, integration from RDE (in Merchandising) to RAP occurs automatically as part of the RDE ZIP file upload
jobs. RDE will push the ZIP file to the File Transfer Services location used by AI Foundation for incoming data (ris/incoming
path in FTS). At this point, you have the option to run the AI Foundation batch jobs to use that ZIP or download it from
FTS to manually modify it and re-upload it. Once the AI Foundation nightly batches are enabled, you would also enable the
batch link connecting AIF to RDE, and then the entire end-to-end process will occur without user intervention.
For initial seeding, you would follow the process below:
-
Set up a full RDE batch (by enabling batch links/dependencies to the RMFCS schedule) and let it run nightly to get the full set of RDE files for dimensions and facts.
-
The file will be pushed automatically to RAP FTS. Download the
RI_RMS_DATA.zip
file from FTS; do not load it into RAP yet. -
Run the process
SEEDPOSITIONALFACT_ADHOC
, which will extract full snapshots of all positional data, zip them, and push them to the RAP FTS location. -
Download the
RIHIST_RMS_DATA.zip
file from FTS and copy the full snapshots of positional facts into theRI_RMS_DATA.zip
file generated by the RDE nightly process (replacing the incremental files that were extracted). -
Upload the modified RDE nightly ZIP file to RAP FTS at the
ris/incoming
location (same as you would for all nightly batches going forward). Upload any additional ZIP files you need for the nightly batches, such asORASE_WEEKLY.zip
orRAP_DATA.zip
, if you want these other files loaded in the same batch. -
Advance the ETL business date in AIF to one day before the current batch, if it’s not already set to that date, using the ad hoc process
LOAD_CURRENT_BUSINESS_DATE_ADHOC
. Review any configurations inC_ODI_PARAM
andRSE_CONFIG
tables which may have been altered for your historical loads but need updates for nightly batch data. For example, you may want to updateRI_INVAGE_REQ_IND
inC_ODI_PARAM
if you need calculations of first/last receipt dates and inventory age from the RMFCS data. -
Schedule a run of the full AIF nightly batch. Ensure your AIF POM schedule dates for the nightly batch run is aligned with the completed run of RMFCS/RDE, because, from this point forward, the batch schedules will need to remain in sync.
Your transactional facts, such as sales and receipts, should already have history loaded up to this first run of nightly
batches, because the next RDE nightly batch will only extract data for the current vdate in RMFCS (for example, it will use
the contents of the IF_TRAN_DATA
daily transaction table for most fact updates besides sales, which come
from Sales Audit directly). Once this first AIF batch completes using the full snapshot of positional data, you may prepare
for regular nightly batches which will use the incremental extracts from RDE.
The calendar validator job rule CAL_R2
may cause the batch to fail if this is the first time using Merchandising
calendar data directly. This is because the default system calendar in Merchandising does not follow RAP recommendations,
which is that the first year of the calendar must be a complete fiscal year. If this happens, verify that the first year
of the Merchandising calendar exists much earlier than any actual data in RAP (for example, the Merchandising calendar starts
in 2010 but RAP data only exists from year 2020 onwards). If this is confirmed, you may change the validation rule to be a
warning instead of an error. Update the table C_DIM_RULE_LIST
from the Control & Tactical Center. Change
the error type for this rule to W
so that it only throws a warning in the batch program instead of failing,
and then restart the failed validator job as needed.