To aggregate the Purchase Cycle Lines table for ETL, you configure the TIME_GRAIN parameter, which is preconfigured to the value of Month. Valid values are DAY, WEEK, MONTH, QUARTER, YEAR.
The Purchase Cycle Lines aggregate table is fully loaded from the base table in the initial ETL run. The table can grow to millions of records. The Purchase Cycle Lines aggregate table is not fully reloaded from the base table after an ETL run. Oracle Business Analytics Warehouse minimizes the incremental aggregation effort by modifying the aggregate table incrementally as the base table is updated.
When the Supply Chain - Purchase Cycle Lines Subject Area is included in a Load Plan in Oracle BI Applications Configuration Manager, the Purchase Cycle Lines data is extracted using these tasks:
SIL_PurchaseCycleLinesAggregate_Derive_PreSoftDeleteImage finds the records to be deleted in the base table since the last ETL run, and loads them into the W_PURCH_CYCLE_LINE_TMP table. The task is run in the source-specific window before the records are deleted from the base table.
SIL_PurchaseCycleLinesAggregate_Derive_PreLoadImage finds the records to be updated in the base table since the last ETL run, and loads them into the W_PURCH_CYCLE_LINE_TMP table. The measures in these records are multiplied by (-1). The task is run in the source-specific workflow before the records are updated in the base table.
PLP_PurchaseCycleLinesAggregate_Derive_PostLoadImage finds the inserted or updated records in the base table since the last ETL run, and loads them into the W_PURCH_CYCLE_LINE_TMP table, without changing their sign. The task is run in the post load-processing workflow after the records are updated or inserted into the base table.
PLP_PurchaseCycleLinesAggregate_Load aggregates the W_PURCH_CYCLE_LINE_TMP table, and joins it with the W_PURCH_CYCLE_LINE_A aggregate table to insert new or update existing buckets to the aggregate table.