2 Setup and Configuration

The Setup and Configuration chapter provides parameters for setting up Retail Data Extractor. The following sections are included:

"Sizing Information"
"Data Initial Load from RMS"

Sizing Information

This section provides a list of factors that should be taken into account when making sizing plans.

There are two major hardware components that make up the Retail Data Extractor physical environment:

Middle Tier Application Server - The middle tier application server hosts software components such as Oracle WebLogic Server.
Database - The Oracle Database stores large amounts of data that are queried in generating Oracle BI reports. The daily data loading process and report query processing process are both heavily dependent on the hardware sizing decision.

Sizing is customer-specific. The sizing of the Retail Data Extractor application is sensitive to a wide variety of factors. Therefore, sizing must be determined on an individual installation basis.

Testing is essential. As with any large application, extensive testing is essential for determining the best configuration of hardware.

Database tuning is essential, just like any other database. The Oracle database is the most critical performance and sizing component of Retail Data Extractor. As with any database installation, regularly monitoring database performance and activity levels and regularly tuning the database operation are essential for optimal performance.

Factors to Consider

Back End Database

Functions Used Determine Tables to be populated - Retail Data Extractor is designed to be a functional system so that some functions (such as supplier compliance or order processing) that are available do not have to be used. To the extent that some functions are not used, the amount of resources may be reduced correspondingly.

Database Backup and Recovery - The importance of the data and the urgency with which a recovery must be made will drive the backup and recovery plan. The backup and recovery plan may have a significant impact on disk space requirements.
Data Storage Requirements

Transaction Volume - Sales - the higher the number of sales records, the higher the disk storage requirements and the higher the resource requirements to process.

Positional Data - Inventory, Price, Cost - Positional data (data that is a snapshot at a specific point in time, such as inventory data "as of 9:00AM this morning") can result in very large tables. The Retail Data Extractor concept of data compression (not to be confused with database table compression) is important in controlling the disk space requirement.

Extract, Transform, Load - Daily Processing - The daily Extraction process is a batch process of loading data from external systems into various stage tables. Disk space and other resources are necessary to support the ETL process.
Configuration issues

Archivelog mode - If the database is being operated in archivelog mode, additional disk space is required to store archived redo logs.

SGA and PGA sizing - the sizing of these memory structures is critical to efficient processing of report queries, particularly when there are multiple queries running simultaneously.

Initialization Parameters - The initialization parameter settings enable you to optimize the daily data loading and report query processing.
Hardware Architecture

Number and Speed of Processors - More and faster processors speed both daily data loading and report query processing in the database. The application server needs fewer resources than the database.

Memory - More memory enables a larger SGA in the database and will reduce the processing time for report queries.

Network - Since the data from the report queries needs to go from the back-end database to the application server, a faster network is better than a slower network, but this is a relatively low priority.

Disk - RPMs, spindles, cache, cabling, JFS - I/O considerations are very critical to optimal performance. Selection of disk drives in the disk array should focus on speed. For example, faster RPMs, more spindles, larger cache, fiber optic cabling, JFS2 or equivalent.

RAID - The selection of a RAID configuration is also a critical decision. In particular, RAID5 involves computations that slows Disk I/O. The key is to select the RAID configuration that maximizes I/O while meeting redundancy requirements for data protection.

Backup and Recovery - The backup and recovery strategy drives disk configuration, disk size, and possibly the number of servers, if Dataguard or Real Application Clusters are used.

Data Seeding of Positional Facts

To report positional data correctly in the Retail Data Extractor user interface, data seeding is required if clients launch Retail Data Extractor later than source system (such as RMS). For performance reasons, it is recommended that all date range partitioned positional fact tables must seed data on the first date or first week of each partition. This avoids searching the data across partitions. Data used for seeding can come from RMS or from client legacy systems. The following are some recommendations to seed data:

If seeding data is for new tables, you may need to provide snapshots of your positional fact data. See "Data Initial Load from RMS" for how to provide initial snapshots of positional fact data.

Data Initial Load from RMS

In order to report Retail Data Extractor positional data correctly, all Retail Data Extractor positional compressed tables need to be seeded with source data (RMS) correctly before they can be loaded using Retail Data Extractor batch ETL with daily data. This seeding process is to load positional fact data for each item location combination available from RMS to Retail Data Extractor as initial data. This can be done by using following recommended approach. This approach assumes that user uses RMS as Retail Data Extractor source system and the required data are available from RMS.

Inventory Position Initial Loading

This initial inventory position data loading includes loading seeding date from RMS to RDE W_RTL_INV_IT_LC_DY_FS and W_RTL_INV_IT_LC_G tables. Perform the following steps:

In RMS, set the RMS vdate to the date that the seeding data will be used for.
Execute the Retail Data Extractor SDE script etlrefreshgensde.ksh to load RMS system data (including vdate) to the Retail Data Extractor temporary table RA_SRC_CURR_PARAM_G which is under Retail Data Extractor RMS batch user schema.
Populate the RMS IF_TRAN_DATA table with all combinations of item and location that have SOH. This can be done by using the data from the RMS ITEM_LOC_SOH table. Only columns ITEM, LOCATION, and LOC_TYPE on the IF_TRAN_DATE table will be used by the Retail Data Extractor SDE program and other columns on this table can be given any dummy value for this seeding purpose.
Execute the Retail Data Extractor SDE script invildsde.ksh to populate the Retail Data Extractor inventory staging table W_RTL_INV_IT_LC_DY_FS.

Pricing Initial Loading

This initial Pricing data loading includes loading seeding date from RMS to Retail Data Extractor W_RTL_PRICE_IT_LC_DY_F and W_RTL_PRICE_IT_LC_G tables. Perform the following steps:

In RMS, set the RMS vdate to the date that the seeding data will be used for.
Execute the Retail Data Extractor SDE script etlrefreshgensde.ksh to load the RMS system data (including vdate) to the Retail Data Extractor temporary table RA_SRC_CURR_PARAM_G. This is under the Retail Data Extractor RMS batch user schema.
Modify the Retail Data Extractor SDE ODI program as follows:
1. In the ODI SDE interface SDE_RetailPriceLoad, modify the filter on PRICE_HIST table to change the filter condition from PRICE_HIST.ACTION_DATE = TO_DATE('#RA_SRC_BUSINESS_CURRENT_DT','YYYY-MM-DD') to PRICE_HIST.ACTION_DATE <= TO_DATE('#RA_SRC_BUSINESS_CURRENT_DT','YYYY-MM-DD').
2. Regenerate the SDE_RetailPriceFact and MASTER_ SDE_RetailPriceFact ODI scenarios.
Execute the Retail Data Extractor SDE script prcildsde.ksh to populate the Retail Data Extractor Price staging table W_RTL_PRICE_IT_LC_DY_FS.
In the Price staging table, only keep the records with the same PROD_IT_NUM, ORG_NUM, MULTI_UNIT_QYT, but maximum DAY_DT. This ensures that the staging table only contains the latest price for each combination of item, location, and multi unit quantity.
Update the Pricing staging table to replace DAY_DT with the current business date that will be used for seeding data.

Net Cost Initial Loading

This initial Net Cost data loading includes loading seeding date from RMS to the Retail Data Extractor W_RTL_NCOST_IT_LC_DY_F and W_RTL_NCOST_IT_LC_G tables. Perform the following steps:

In RMS, set the RMS vdate to the date that the seeding data will be used for.
Execute the Retail Data Extractor SDE script etlrefreshgensde.ksh to load the RMS system data (including vdate) to the Retail Data Extractor temporary table RA_SRC_CURR_PARAM_G. This is under the Retail Data Extractor RMS batch user schema.
Modify the Retail Data Extractor SDE ODI program as follows:
1. In the ODI SDE interface SDE_RetailNetCostTempLoad, modify the filter on FUTURE_COST table to change the filter condition from FUTURE_COST.ACTIVE_DATE = to_date('#RA_SRC_BUSINESS_CURRENT_DT','YYYY-MM-DD') to FUTURE_COST.ACTIVE_DATE <= to_date('#RA_SRC_BUSINESS_CURRENT_DT','YYYY-MM-DD').
2. Regenerate the SDE_RETAILNETCOSTFACT and MASTER_SDE_RETAILNETCOSTFACT ODI scenarios.
Execute the Retail Data Extractor SDE script ncstildsde.ksh to populate the Retail Data Extractor Net Cost staging table W_RTL_NCOST_IT_LC_DY_FS.
In the Net Cost staging table, only keep the records with the same PROD_IT_NUM, ORG_NUM, SUPPLIER_NUM, but maximum DAY_DT. This ensures that the staging table only contains the latest net cost for each combination of item, location and supplier.
Update the Net Cost staging table to replace DAY_DT with the current business date that will be used for seeding data.

Base Cost Initial Loading

This initial Base Cost data loading includes loading seeding date from RMS to Retail Data Extractor W_RTL_BCOST_IT_LC_DY_F and W_RTL_BCOST_IT_LC_G tables. Perform the following steps:

In RMS, set the RMS vdate to the date that the seeding data will be used for.
Execute the Retail Data Extractor SDE script etlrefreshgensde.ksh to load the RMS system data (including vdate) to the Retail Data Extractor temporary table RA_SRC_CURR_PARAM_G. This is under the Retail Data Extractor RMS batch user schema.
Modify the Retail Data Extractor SDE ODI program as follows:
1. In the ODI SDE interface SDE_RetailBaseCostTempLoad, modify the filter on the PRICE_HIST table to change the filter condition from PRICE_HIST.POST_DATE = TO_DATE('#RA_SRC_BUSINESS_CURRENT_DT','YYYY-MM-DD') to PRICE_HIST.POST_DATE <= TO_DATE('#RA_SRC_BUSINESS_CURRENT_DT','YYYY-MM-DD').
2. Regenerate the SDE_RETAILBASECOSTFACT and MASTER_SDE_RETAILBASECOSTFACT ODI scenarios.
Execute the Retail Data Extractor SDE script cstisldsde.ksh to populate the Retail Data Extractor Base Cost staging table W_RTL_BCOST_IT_LC_DY_FS.
In the Base Cost staging table, only keep the records with the same PROD_IT_NUM, ORG_NUM but maximum DAY_DT. This ensures that the staging table only contains the latest base cost for each combination of item and location.
Update the Base Cost staging table to replace DAY_DT with the current business date that will be used for seeding data.