6 ODI Program Dependency

This chapter presents ETL dependency for all Retail Insights dimension and fact data processing. Included are descriptions of the source system's programs that are required to be completed before starting Retail Insights programs, along with the Retail Insights programs that are required to be subsequently executed. Before setting up an Retail Insights program schedule, familiarize yourself with the functional and technical constraints associated with each program and also read through Chapter 7, "Program Reference Lists" for additional details.

Batch Scheduling

The following explains the order constraints of the Retail Insights batch schedule. This section includes:

• Overall batch schedule details like dependencies of Retail Insights program on source system programs, and also interdependencies between dimension and fact programs.

• Functional interdependencies, including functional constraints, such as that fact programs must run after dimension programs.

Setting Up the Batch Schedule

Note: The number of programs that can be run in parallel at any given time is dependent upon the retailer's hardware capacity.That is, product dimension and employee programs, such as prditmsil.ksh and emplysil.ksh, can be run in parallel after their respective pre-dependencies. Fact programs, such as sinvcstilsdsil.ksh, can run in parallel with other, unrelated fact programs provided their respective pre-dependencies (including dimension predecessors) complete successfully first.

The batch flows on the following pages are best read from top to bottom. Such a review of the Retail Insights batch schedule allows retailers to both set up program dependencies and to optimize their batch window through the concurrent running of unrelated programs.

Scheduling As Is Aggregate ETL Programs in the Batch Scheduler

Note the following regarding scheduling of the As Is ETL programs in the batch scheduler tool. As Is or current aggregate tables have two sets of programs:

• Programs that roll up the daily data for the product hierarchy components. (Reclassification, refers to the changes to the product hierarchy within the source system. For example, few items within a particular SubClass are moved to a new SubClass). These programs should be scheduled on daily basis. For example: slsfcsldcurrplp.ksh

• As Is program that reclassifies data processing. This program processes data and updates the aggregate table data for the rows that are impacted due to the reclassification.

  This program should be scheduled to be executed at the beginning of the batch before the business date is advanced to the current business date. For example, let's consider a scenario that the business date 01-Jan-2009 data has already processed in the previous nightly batch and today is business date 02-Jan-2009 and there were some reclassification of product hierarchy in the source system on 01-Jan-2009. In this scenario dimension tables will insert the new hierarchy changes to be effective from 02-Jan-2009 in the dimension tables on the business date 01-Jan-2009 nightly batch. The fact tables will not have any transactions for 02-Jan-2009 during the nightly batch of 01-Jan-2009. For the As Is aggregate tables to be updated with new changes, the reclassification program for aggregate tables should be executed on business date 02-Jan-2009 without changing the business date in the Retail Insights system; which means etlrefreshgenplp.ksh should not be executed on 02-Jan-2009 nightly batch until reclassification program for As Is have completed successfully. For example: slsfcrcslwcurrplp.ksh

  The programs that are related to reclassification for As Is aggregates have these details in the details provided below in the 'Comments' section.

Extract, Transform, Load Dependencies