4 Performance

Retail Data Extractor is a high performance data Extraction utility, capable of storing and moving the daily transaction data. For any BI solution, including Retail Insights, smart decisions on how to extract bulk data will ensure that you are getting the most out of it. This chapter contains information that will help you get the best performance out of Retail Data Extractor and identifies common contributors that can weaken performance, as well as best practices that will ensure Retail Data Extractor is running in the most optimal manner.

All implementations are unique and the factors that are beneficial for one implementation may not have the same effect for all the implementations. It is a good practice to test several settings/approaches for the factors and recommendations listed below and use the ones that work best for your environment. The factors listed in this chapter are the key factors that impact performance but no absolute values or settings can be provided for implementation purposes due to the uniqueness of each environment.

Oracle Retail Data Extractor includes ODI for extraction of data from source transaction systems, and transfers this data in a secure and efficient way to the Retail Insights Data warehousing system.

ETL Programs Performance

Based on the complexity of the report, Oracle BI EE sometimes generates complex SQL, causing the Oracle Database to pick a less than optimized execution plan. In order to avoid this scenario, it is recommended that the "SQL Plan Baseline" functionality of the Oracle 12c be enabled (it is disabled by default). For more details refer to the Oracle 12c Performance Tuning Guide.

ETL Programs Performance

Setting ETL Program Multi-threading

Retail Data Extractor base fact stage extract programs can be configured to run using multiple threads. The default number of threads for these programs is set to one and can be configured based on requirements. For additional information on how multi-threading works, see the Program Overview chapter of the Oracle Retail Data Extractor Operations Guide.

Finalize the multi-threading strategy for the base fact stage extract programs.
Number of threads for each program may vary based on the data volume that program handles and resource availability. Different thread numbers should be tested by clients during implementation to achieve optimal results from multi-threading.
In the C_ODI_PARAM table, update the value of the PARAM_VALUE column to the desired number of threads. This applies to all records with the value 'LOC_NUM_OF_THREAD' in the PARAM_NAME column and the name of the program that requires multi-threading set in the SCENARIO_NAME column. See an example below for scenario named SIL_Test, where the desired number of threads needs to be set to 2 from 1 (default).
```
UPDATE C_ODI_PARAMSET PARAM_VALUE = 2WHERE PARAM_NAME = 'LOC_NUM_OF_THREAD'AND SCENARIO_NAME = 'SIL_Test'
```
If the number of thread required is more than 10, you need to modify the DDL for intermediate temp tables used by the ODI scenario. DDL changes require adding extra partitions to hold the data. The number of partitions on the intermediate temp table must be the same or higher than the required number of threads (which is the value for LOC_NUM_OF_THREADS set in the previous step).
The value setup in the C_ODI_PARAM (in step 3) should be bigger or equal than the max value of column ETL_THREAD_VAL in the staging tables. Otherwise, some records could get missing.

ODI Configuration

ODI must be configured prior to implementing Retail Data Extractor. See the Oracle Retail Data Extractor Administration Guide for details on configuring ODI.

ETL Batch Scheduling

Set up the proper dependencies between the applications to ensure resources are fully utilized, which helps the nightly batch finish earlier.
Retail Data Extractor extraction programs (SDE programs) must not wait for all the extraction programs (sde) to finish before starting. Some of them can start executing in parallel if they are not dependent on other staging tables. For more information on setting up dependencies, refer to the Oracle Retail Data Extractor Operations Guide.
Ensure that your source applications batch is optimized. Retail Data Extractor runs towards the end of the nightly batch. Retail Data Extractor jobs are often the last jobs to start due to the dependencies on the source system jobs, so Retail Data Extractor is often the last to finish. Optimizing the source applications batch helps Retail Data Extractor jobs to start earlier.

Data Base Configuration

Retail Data Extractor is built on Oracle Database 12c and must be optimized and configured for a retailers' needs. Refer to the Setting up your Data Warehouse System chapter of the Oracle 12c Data Warehouse Guide.

Adequate Hardware Resources

ETL programs performance is dependent on the hardware resources. For more information, see Chapter 2, "Setup and Configuration".

Leading Practices

Customizations

Changes and modifications to the Retail Data Extractor delivered code or development of new code is considered customization. Retail Data Extractor does not support custom code developed by clients unless the issue related to customization can be recreated using Retail Data Extractor delivered objects. Listed below are recommendations that will help you in maintaining Retail Data Extractor code:

Naming convention: it is recommended that you use a good and consistent naming convention when customizing Retail Data Extractor delivered code or building new code in the Retail Data Extractor environment.

This strategy is helpful in identifying custom code and also helps when merging a retailer's Retail Data Extractor repository with future releases of the Retail Data Extractor repository. There is a possibility of losing customizations to Retail Data Extractor provided ODI scripts repository, if the customized code uses the same object/script names that are used by Retail Data Extractor.
As a best practice, keep all the documentation up-to-date for capturing any changes or new code that has been developed at a site. For example, if table structure has been customized, create or update the custom Data Model Guide with these changes.

ODI Best Practices

For customizations to existing ODI code or while creating new ODI code, refer to the ODI Best Practices Guide included with your product code.

Batch Schedule Best Practices

The following best practices are recommended for Retail Data Extractor:

Automation

The batch schedule should be automated as per the Oracle Retail Data Extractor Operations Guide. Any manual intervention should be avoided.

Recoverability

Set up the batch schedule in such a manner that the batch can resume from the point where it failed.

Batch Efficiency

Keep revisiting the batch timings on a periodic basis to identify the candidates for performance improvements.