1. Implementation Guide
  2. Data Loads and Initial Batch Processing
  3. Loading Data from Files
  4. Load History Data

Load History Data

Historical fact data is a core foundational element to all solutions in Retail Analytics and Planning. As such, this phase of the implementation can take the longest amount of time during the project, depending on the volumes of data, the source of the data, and the amount of transformation and validation that needs to be completed before and after loading it into the Oracle database.

It is important to know where in the RAP database you can look to find what data has been processed, what data may have been rejected or dropped due to issues, and how far along in the overall load process you are. The following tables provide critical pieces of information throughout the history load process and can be queried from APEX.

Table 3-5 Inbound Load Status Tables

Table Usage

C_HIST_LOAD_STATUS

Tracks the progress of historical ad hoc load programs for inventory and pricing facts. This table will tell you which Retail Insights tables are being populated with historical data, the most recent status of the job executions, and the most recently completed period of historical data for each table. Use APEX or Data Visualizer to query this table after historical data load runs to ensure the programs are completing successfully and processing the expected historical time periods.

C_HIST_FILES_LOAD_STATUS

Tracks the progress of zip file processing when loading multiple files in sequence using scheduled standalone process flows.

C_LOAD_DATES

Check for detailed statuses of historical load jobs. This is the only place that tracks this information at the individual ETL thread level. For example, it is possible for an historical load using 8 threads to successfully complete 7 threads but fail on one thread due to data issues. The job itself may just return as Failed in POM, so knowing which thread failed will help identify the records that may need correcting and which thread should be reprocessed.

W_ETL_REJECTED_RECORDS

Summary table capturing rejected fact record counts that do not get processed into their target tables in Retail Insights. Use this to identify other tables with specific rejected data to analyze. Does not apply to dimensions, which do not have rejected record support at this time.

E$_W_RTL_SLS_TRX_IT_LC_DY_TMP

Example of a rejected record detail table for Sales Transactions. All rejected record tables start with the E$_ prefix. These tables are created at the moment the first rejection occurs for a load program. W_ETL_REJECTED_RECORDS will tell you which tables contain rejected data for a load. These tables may not initially be granted to APEX for you to read from. To grant access, run the RABE_GRANT_ACCESS_TO_IW_ADHOC_PROCESS ad hoc process in the AIF APPS schedule in POM. This will allow you to select from these error tables to review rejection details.

When loading data from flat files for the first time, it is common to have bad records that cannot be processed by the RAP load procedures, such as when the identifiers on the record are not present in the associated dimension tables. The foundation data loads leverage rejected record tables to capture all such data so you can see what was dropped by specific data load and needs to be corrected and reloaded. These tables do not exist until rejected records occur during program execution, and are not initially granted to APEX unless you have run RABE_GRANT_ACCESS_TO_IW_ADHOC_PROCESS. Periodically monitor these tables for rejected data which may require reloading.

The overall sequence of files to load will depend on your specific data sources and conversion activities, but the recommendation is listed below as a guideline.

  1. Sales – Sales transaction data is usually first to be loaded, as the data is critical to running most applications and needs the least amount of conversion.

  2. Inventory Receipts – If you need receipt dates for downstream usage, such as in Lifecycle Pricing Optimization, then you need to load receipt transactions in parallel with Inventory Positions. For each file of receipts loaded, also load the associated inventory positions afterwards.

  3. Inventory Position – The main stock-on-hand positions file is loaded next. This history load also calculates and stores data using the receipts file, so INVENTORY.csv and RECEIPT.csv must be loaded at the same time, for the same periods.

  4. Pricing – The price history file is loaded after sales and inventory are complete because many applications need only the first two datasets for processing. Potentially, price history may also be the largest volume of data; so it’s good to be working within your other applications in parallel with loading price data.

  5. All other facts – There is no specific order to load any of the other facts like transfers, adjustments, markdowns, costs, and so on. They can be loaded based on your downstream application needs and the availability of the data files.

For your first time implementing this history load process, you may also leverage the reference paper and scripts in My Oracle Support (Doc ID 2539848.1) titled AI Foundation Historical Data Load Monitoring. This document will guide you through one way you can monitor the progress of history loads, gather statistics on commonly used tables, and verify that data is moving from the input tables to the target tables in the database.