Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide > Configuring Siebel Strategic Sourcing Analytics > Process of Aggregating Siebel Strategic Sourcing Analytics Tables >

About Configuring the Purchase Cycle Lines Aggregate Table


To aggregate the Purchase Cycle Lines table (IA_PURCH_CYCLNS_A1), you need to configure the file_parameters_plp.csv parameters file and the source system parameters file, and run the initial ETL workflow and then the incremental ETL workflow.

For your initial ETL run, you need to configure the GRAIN parameter for the time aggregation level in the Purchase Cycle Lines Aggregate fact table.

For the incremental ETL run, you need to configure the time aggregation level and the source identification. The source identification value represents the source system you are sourcing data from.

You need to configure two parameters to aggregate the Purchase Cycle Lines table for your incremental run:

  • GRAIN
  • SOURCE_ID

The GRAIN parameter has a preconfigured value of Month. The possible values for the GRAIN parameter are:

  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

Table 72 lists the values for the SOURCE_ID parameter. The value of this parameter is preconfigured to reflect the ETL mapping's folder.

Table 72. Source System Parameter Values
Source System
Value

Oracle 11i

OAP11I

SAP R/3

SAPR3

Universal

GENERIC

NOTE:  You can change the default value for the Source ID parameter if you use multiple instances of the same source system. For example, you can run multiple instances of SAP R/3 and use separate Source IDs for each instance. You can name the first instance SAPR3_1, the second instance SAPR3_2, and so on.

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. Siebel Customer-Centric Enterprise Warehouse minimize the incremental aggregation effort, by modifying the aggregate table incrementally as the base table gets updated. This process is done in four steps:

  1. Siebel Customer-Centric Enterprise Warehouse finds the records to be deleted in the base table since the last ETL run, and loads them into the NU_PURCH_CYCLNS table. The measures in these records are multiplied by (-1). The mapping responsible for this task is suffixed with PRE_D, and it is run before the records are deleted from the base table. It is run in the source-specific workflow.
  2. Siebel Customer-Centric Enterprise Warehouse finds the records to be updated in the base table since the last ETL run, and loads them into the NU_PURCH_CYCLNS table. The measures in these records are multiplied by (-1). The mapping responsible for this task is suffixed with PRE_U, and it is run before the records are updated in the base table. It is run in the source-specific workflow.
  3. Siebel Customer-Centric Enterprise Warehouse finds the inserted or updated records in the base table since the last ETL run, and loads them into the NU_PURCH_CYCLNS table, without changing their sign. The mapping responsible for this task is suffixed with POST, and it is run after the records are updated or inserted into the base table. It is run in the post load-processing workflow.
  4. Siebel Customer-Centric Enterprise Warehouse aggregates the NU_PURCH_CYCLNS table, and joins it with the IA_PURCH_CYCLNS_A1 aggregate table to insert new or update existing buckets buckets to the aggregate table. This step is part of the post load-processing workflow, and the mapping is suffixed with INCR.
Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide