5 Retail Insights Program Overview

This chapter summarizes the Retail Insights ETL programs. Retail Insights ETL programs, which are korn shell scripts containing ODI calls, extract, transform, and load data to Retail Insights staging tables and data mart tables. There are three types of ETL programs in Retail Insights:

• SDE programs

  Retail Insights SDE programs are source dependent extraction programs that extract data from source system, transform data, and load data to Retail Insights staging tables. SDE programs name have "sde" as the suffix.

• SIL programs

  Retail Insights SIL programs are source independent loading programs that load data from Retail Insights staging tables to Retail Insights base level data mart tables. SIL programs name have "sil" as the suffix.

• PLP programs

  Retail Insights PLP programs are post loading programs that load data from Retail Insights base level tables or Retail Insights temporary tables, created and populated during batch cycle, to Retail Insights data mart tables. The PLP programs include ETL maintenance and fact aggregations. PLP programs name have "plp" as the suffix.

This chapter references the directory structure set up during Retail Insights product installation. Descriptions of these directories are available in the Oracle Retail Insights Installation Guide. More information about the ODI tool is available in the latest Oracle Data Integrator User's Guide.

Program Features

The Retail Insights ETL programs include the following features:

• Program return code

• Restart and recovery

• Message logging

• Program error file

• Multi-threading

Program Return Code

The Retail Insights ETL shell scripts contain an ODI scenario call. The scripts use return code to indicate successful completion. If the program successfully calls the ODI scenario, a zero (0) is returned. If the program fails, a non-zero is returned.

Restart and Recovery

Out of the box, Retail Insights does not provide restart and recovery features. However, ODI provides the restartsession command to restart the ODI session from the point where it fails. See the Oracle Data Integrator User Guide for details.

Message Logging

Message logs are written daily in a format described in this section.

Note: The ODI logging mechanism is handled as part of the ODI knowledge module. Therefore, the package writes the log files only if at least one of the interfaces is executed. For example, the PRODDIMRECLASSINITIALTMPPLP.KSH batch does not write any log event if no item got reclassified, even if the package executed until the evaluation step. However, a user can view the ODI operator log to see the execution details and the variable values that were returned up until the point the package was executed.

Daily Log File

Every Retail Insights ETL program writes a message to the daily log file when it starts and when it finishes. The name of the daily log file is set to 'RetailAnalytics_YYYYMMDD.log'. 'YYYYMMDD' is the business virtual date for which Retail Insights ETL programs are executed. The directory defaults to ${MMHOME}/log. All log files are encoded UTF-8.

That is, the location and the name of the log file for the business virtual date of January 5, 2001 would be the following:

${MMHOME}/log/RetailAnalytics_20010105.log

Format

As the following examples illustrate, every message written to a log file has the name of the package, name of the interface, session number, a timestamp, and an informational or error message:

11/26/10 10:20 AM :Package SIL_TIMEOFDAYDIMENSION started successfully.11/26/10 10:20 AM :Package SIL_TIMEOFDAYDIMENSION (82005) --> Interface ( SIL_TimeOfDayDimension.TIME_OF_DAY_D ) --> Target Table ( W_TIME_OF_DAY_D ) loading started..11/26/10 10:21 AM :Package SIL_TIMEOFDAYDIMENSION (82005) --> Interface ( SIL_TimeOfDayDimension.TIME_OF_DAY_D ) --> Target Table ( W_TIME_OF_DAY_D ) loading started..11/26/10 10:21 AM :Package SIL_TIMEOFDAYDIMENSION (82005) --> Interface ( SIL_TimeOfDayDimension.TIME_OF_DAY_D ) --> Target Table ( W_TIME_OF_DAY_D ) loading completed.11/26/10 10:21 AM :Package SIL_TIMEOFDAYDIMENSION (82005) --> Interface ( SIL_TimeOfDayDimension.TIME_OF_DAY_D ) --> Target Table ( W_TIME_OF_DAY_D ) loading completed.11/26/10 10:21 AM :Package SIL_DAYDIMENSION started successfully.

If a program finishes unsuccessfully, an error file is usually written that indicates what the problem was. There is also an error message written to the log file to indicate the location and the name of the error file.

Program Error File

In addition to the daily log file, each program also writes its own error messages when any error occurs. Rather than clutter the daily log file with these messages, each program writes out its errors to a separate error file unique to each execution.

The directory defaults to ${MMHOME}/error. All error files are encoded UTF-8. The error files contain error messages with Oracle ORA number or java error exception.

The naming convention for the program's error file defaults to "xxxxx.??????.log", where xxxxx is the name of the program that get error and ????? is the session number assigned to this execution. The session number can be found in the daily log file.

Batch user can also use ODI Operator for all detail routine processing message for a given program and a given ODI session. See the Oracle Data Integrator User Guide for detail.

Multi-threading

Retail Insights base fact extraction programs and base fact loading programs provide Multi-threading feature. The Multi-threading feature divides the source data into multiple segments based upon the location partitions defined in the database view RA_RESTART_LOC. Any intermediate temporary table that is used by Retail Insights base fact extraction and loading programs is divided into multiple partition based on column ETL_THREAD_VAL. The default number of partitions in Retail Insights DDL is 10. This allows maximum of 10 threads for each program. Users need to change DDL scripts for these intermediate temporary tables if they need more than 10 threading.

Under Multi-threading process, each thread is responsible for a portion of a dataset, rather than the entire dataset and all threads can be executed at parallel. As a result of this Multi-threading method, the processing of the entire dataset is much faster than in a single-thread environment.

It is your responsibility to choose the number of threads. The default number of threads for Retail Insights base fact extraction and loading programs is 1. Users can modify that value through installation data files C_ODI_PARAM (on both source system and loading system) during the installation. High number of threads can improve CPU usage, but it could also cause I/O and memory congestion. See the Oracle Retail Insights Installation Guide for details on setting Multi-threading.

Setting Up ETL_THREAD_VAL for Non-Oracle Retail Customers

ETL_THREAD_VAL for non-Oracle Retail Customers setup is required for implementations that use non-Oracle retail applications as a source for Retail Insights dimensions and facts. If the source applications for dimension and fact data are Oracle Retail applications then this section should be skipped.

ETL_THREAD_VAL is required to be set up for using the multi-threading feature of ODI extract and load fact programs. If non-Oracle retail applications are used as data source then customers are responsible for extracting data from the source systems and loading this data into Retail Insights staging tables. While inserting the data into staging tables, the following steps should be followed. This information is further used by the pre-packaged ODI load programs (SIL programs) for loading data to final facts tables.

1. Threads are created based on the locations (ORG_NUM column).

2. Set up the maximum number of threads to be used for multi-threading in the C_ODI_PARAM table for the associated scenario of SIL program based on the available hardware and performance requirements.

3. Once the maximum number of threads is finalized for the SIL program, the program that loads data into staging table (customer created program) should use the ETL_THREAD_VAL which is between 1 and the 'maximum number of threads' chosen for the SIL program.

4. While inserting data into staging table, ensure that every thread will have multiple locations. The same ORG_NUM should not be available in multiple threads. Every ORG_NUM will be available on only one thread. In addition, try to distribute the data evenly across threads (each thread should have similar data volumes for better performance).

5. For improved performance, the staging table should be partitioned by using ETL_THREAD_VAL as the partitioning key.

The First Time Retail Insights Batch is Run

To ensure that the correct current business date is entered in W_RTL_CURR_MCAL_G, the following must be considered:

• Verify that the ODI executable script startscenri.sh is in the path of your UNIX session by typing: which startscenri.Retail Insights installation is successful and default data and time are properly installed.The batch operator has read Chapters 1-4 of this Operations Guide. This ensures the batch operator understands the relationship between time tables and columns that are populated in the dimension and fact tables. Batch dependencies are understood. See the "Program Flow Diagrams" chapter for more information about the Retail Insights program flow and dependencies.MCAL_NUM on W_RTL_CURR_MCAL_G table with MCAL_TYPE equal to 'DT' needs to be updated to the day in 'YYYYMMDD' format before the first dimension/fact is loaded (that is, if you plan to load data and have all the items on the first day of history on '20100101', then the MCAL_NUM with MCAL_TYPE equal to 'DT' should be updated to '20091231').Run etlrefreshgenplp.ksh before the dimension and fact modules to update records on W_RTL_CURR_MCAL_G table to the intended dimension/fact load date.

Typical etlrefreshgenplp.ksh Run

To run etlrefreshgenplp.ksh, follow these steps:

Note: A program prerequisite is that the date entered in MCAL_NUM with MCAL_TYPE equal to 'DT' must exist in the W_MCAL_DAY_DM table.

1. Change directories to ${MMHOME}/src.

2. At a UNIX prompt enter:

   ./etlrefreshgenplp.ksh
   

   If the program runs successfully, the following results:

   • Table W_MCAL_DAY_DM is updated assuming that the current business date is the previous MCAL_NUM (MCAL_TYPE equal to 'DT') plus one.

   • The status table C_LOAD_DATES is deleted where PACKAGE_STATUS ='Success'.

Typical Run and Debugging Situations

The following examples illustrate typical run and debugging situations for each type of program within Retail Insights. The log, error, and so on file names referenced below assume that the program is run on the business virtual date of March 9, 2010. See the previously described naming conventions for the location of each file.

Retail Insights Dimension Load

This program calls ODI scenario SIL_INTERNALORGANIZATIONDIMENSION. To run orgsil.ksh:

1. Change directories to ${MMHOME}/src.

2. At a UNIX prompt, enter:

   ./orgsil.ksh
   

   If the program runs successfully, the following results are generated:

   • Log file: Today's log file, RetailAnalytics_20100309.log, contains "Package SIL_INTERNALORGANIZATIONDIMENSION started successfully" and "Package SIL_INTERNALORGANIZATIONDIMENSION completed successfully." messages.

   • Data: The records from the source table W_INT_ORG_DS are loaded into the target table.

   • Error file: There is no error file as the program completed successfully.

   • Program status control: The C_LOAD_DATES table is updated to 'Success' where PACKAGE_NAME = 'SIL_INTERNALORGANIZATIONDIMENSION' and TARGET_TABLE_NAME = 'W_INT_ORG_D'.

   • Reject data: Reject data are not created for Retail Insights dimension programs.

   If the program does not run successfully, the following results are generated:

   • Log file: Today's log file, RetailAnalytics_20100309.log, contains "Interface XXXXXXXX failed" message in which Interface 'XXXXXXX' is an interface within package SIL_INTERNALORGANIZATIONDIMENSION.

   • Data: Some of the records from source table may be loaded into the target table.

   • Error file: The program's error file, SIL_INTERNALORGANIZATIONDIMENSION.?????.log under $MMHOME/error directory, contains the program's error messages. '?????' is the ODI session number which you can find in the Retail Insights log file.

   • Program status control: The C_LOAD_DATES table is updated to 'InProgress' where PACKAGE_NAME = 'SIL_INTERNALORGANIZATIONDIMENSION' and TARGET_TABLE_NAME = 'W_INT_ORG_D'.

   To run the program again from the beginning, perform the following actions:

   1. Determine and fix the problem causing the error.

   2. Delete the row from C_LOAD_DATES table where PACKAGE_NAME = 'SIL_INTERNALORGANIZATIONDIMENSION' and TARGET_TABLE_NAME = 'W_INT_ORG_D'.

   3. Change directories to ${MMHOME}/src. At a UNIX prompt, enter:

      ./orgsil.ksh
      

Retail Insights Base Fact Load with Multi-threading

This program call ODI scenario SIL_RETAILWHOLESALEFRANCHISEFACT. To run wfslsildsil.ksh:

1. Change directories to ${MMHOME}/src.

2. At a UNIX prompt, enter:

   ./wfslsildsil.ksh
   

   If the program runs successfully, the following results are generated:

   • Log file: Today's log file, RetailAnalytics_20100309.log, contains "Package SIL_RETAILWHOLESALEFRANCHISEFACT started successfully" and "Package SIL_RETAILWHOLESALEFRANCHISEFACT completed successfully." messages. Since this is multi-threading enabled program, the log file should also contain message "…(……Thread # 1 of 2) loading started". The example here means that the first thread of total 2 threads has started.

   • Data: The records from the source table are loaded into the target table.

   • Error file: There is no error file is the program completed successfully.

   • Program status control: Since this is multi-threading enabled program, you should check status for each thread of this execution. If the first thread is completed successfully, the C_LOAD_DATES table is updated to 'Success' where PACKAGE_NAME = 'SIL_RETAILWHOLESALEFRANCHISEFACT' and TARGET_TABLE_NAME = 'W_RTL_SLSWF_IT_LC_DY_F' and ETL_THREAD_VAL = 1.

   • Reject data: Retail Insights base fact loading program checks dimension data integrity with dimension tables. Any source data that violate dimension data integrity are rejected. The rejected records are written to the reject ODI error table E$_${TARGET_TABLE}. The ${TARGET_TABLE} is the target table name of the interface that checks dimension data integrity. For this example, the error table that contains rejected data is E$_W_RTL_SLSWF_IT_LC_DY_TMP.

     Rejected data will not be loaded to Retail Insights. You must clean up data manually on source table and rerun the program again. To avoid the same records being loaded twice, the source table should ONLY have corrected data during the second run.

   If the program does not run successfully, the following results are generated:

   • Log file: Today's log file, RetailAnalytics_20100309.log, contains "Interface XXXXXXXX failed" message in which Interface 'XXXXXXX' is an interface within package SIL_RETAILWHOLESALEFRANCHISEFACT.

   • Data: Some of the records from source table may be loaded into the target table.

   • Error file: The program's error file, SIL_RETAILWHOLESALEFRANCHISEFACT.?????.log under $MMHOME/error directory, contains the program's error messages. '?????' is the ODI session number which you can find in the Retail Insights log file.

   • Program status control: The C_LOAD_DATES table is updated to 'InProgress' where PACKAGE_NAME = 'SIL_RETAILWHOLESALEFRANCHISEFACT' and TARGET_TABLE_NAME = 'W_RTL_SLSWF_IT_LC_DY_F' and ETL_THREAD_VAL = 1.

   To run the program again from the beginning:

   1. Determine and fix the problem causing the error.

   2. Delete the row from C_LOAD_DATES table where PACKAGE_NAME = 'SIL_RETAILWHOLESALEFRANCHISEFACT' and TARGET_TABLE_NAME = 'W_RTL_SLSWF_IT_LC_DY_F' AND ETL_THREAD_VAL = < FAILED_THREAD_NUMBER> And Commit.

   3. Change directories to ${MMHOME}/src. At a UNIX prompt, enter:

      ./wfslsildsil.ksh
      

Retail Insights Partitioning Strategy

The run_partition_util.ksh shell script runs the partition program. This programs is scheduled to run after the batch cycle email notification is sent out.

Retail Insights Knowledge Modules

The table below lists the ODI knowledge modules that are used in extract, load and post load programs along with their brief description and common usage.

Table 5-1 ODI Knowledge Modules

Name	Usage
IKM RA Oracle Generic Temp Load with Control	Steps where data needs to be inserted into a temporary table.The TEMP tables are always truncated and Loaded.
IKM RA Oracle Generic Insert with Control	Steps where data needs to be inserted and there is no requirement to update the target table data.
IKM RA Oracle Slowly Changing Dim with Control	Steps where slowly changing dimension needs to be maintained.Integrates data into an Oracle target table in SCD mode (Inserts /Updates). Inexistent rows are inserted; already existing rows are updated or inserted based on (Column property for SCD).
IKM RA Oracle Generic Delete with Control	Steps where data needs to be deleted from the target table.Existent rows are deleted based on the Alternate Key defined in the model.
IKM RA Oracle Generic Merge with Control	Steps where data needs to be inserted or updated based on the data input and business key of the target table integrates data into an Oracle target table in incremental update mode. Inexistent rows are inserted; already existing rows are updated.
IKM RA Oracle Generic Update with Control	Steps where data needs to be updated with or without a filter condition.This IKM has the ability to take up a target filter condition and other filter conditions at the Source and then update a given target column.
IKM RA Oracle Generic Merge with Control M-Thread	Steps where data needs to be inserted or updated using multi-threading, based on the data input and business key of the target table integrates data into an Oracle target table in incremental update mode.
RA CKM Oracle Fact Load	Steps where fact data is getting loaded and error records need to be identified.This KM requires that the alias name for the staging table to be prefixed with "STG" in ODI data mapping.
RKM Oracle	This is used by ODI when a table or view is imported from database to ODI.
LKM RA Oracle to Oracle (DBLink) with Control	This KM is used when data is moved from one database to another. DB Link is used for the extraction and loading purposes.