Oracle^® Business Intelligence Applications Installation and Configuration Guide > Configuring Oracle's Supply Chain Analytics family of products > Process of Aggregating Supply Chain Analytics Tables >

Configuring the Product Transaction Aggregate Table

This task is a step in the Process of Aggregating Supply Chain Analytics Tables.

You need to configure the parameterfileDW.txt parameters file, and run the initial ETL and then the incremental ETL to load the Product Transaction aggregate table.

To configure the parameterfileDW.txt parameters file

1. Open the parameterfileDW.txt file using Microsoft WordPad or Notepad in the OracleBI\DAC\Informatica\parameters\input folder.
2. Replace the default parameter values with your new values.

   For a list of values for each parameter see the About Configuring the Product Transaction Aggregate Table.

   The default values are shown in the following table.

   Table 58. Default values in the parameterfileDW.txt file

   SESSION	PARAMETER	VALUE
   PLP_ProductTransaction_Trim	$$KEEP_PERIOD	MONTH
   PLP_ProductTransaction_Trim	$$NUM_OF_PERIOD	3
   PLP_ProductTransactionAggregate	$$REFRESH_PERIOD	MONTH
   PLP_ProductTransactionAggregate	$$GRAIN	MONTH
   PLP_ProductTransactionAggregate	$$NUM_OF_PERIOD	3
   PLP_ProductTransactionAggregate_Full	$$GRAIN	MONTH
   PLP_ProductTransactionAggregate_Full	$$NUM_OF_PERIOD	3
   PLP_ProductTransactionAggregate_Full	$$REFRESH_PERIOD

   NOTE:  You need to use single quotes for the values of the KEEP_PERIOD, GRAIN, and REFRESH_PERIOD parameters. The KEEP_PERIOD value must be equal to or greater than the GRAIN value. The REFRESH_PERIOD value must equal the GRAIN value.

3. Save and close the file.

To configure the Product Transaction aggregate table for the initial ETL run

1. Retrieve the records in the Product Transaction fact (W_PRODUCT_XACT_F) table, and aggregate the records to the Product Transaction aggregate (W_PRODUCT_XACT_A) table at a certain grain level.

   For example, if GRAIN=MONTH then the records in the W_PRODUCT_XACT_F fact table are retrieved and aggregated to the W_PRODUCT_XACT_A table at a monthly level.

   Running the SPLP_ProductTransactionAggregate_Full workflow implements this step.

To configure the Product Transaction aggregate table for the incremental ETL run

1. Delete the refreshed records from the Product Transaction aggregate (W_PRODUCT_XACT_A) table for a certain time.

   The REFRESH_PERIOD and the NUM_OF_PERIOD parameters determine the time period for the deletion.

   For example, if REFRESH_PERIOD=MONTH, NUM_OF_PERIOD=1, and the date is May 15, 2005, then all records for April and the current month (May) are deleted in the W_PRODUCT_XACT_A table.

   Running the PLP_ProductTransactionAggregate workflow implements this step.

2. Retrieve the records in the Product Transaction fact (W_PRODUCT_XACT_F) table, and aggregate the records to the W_PRODUCT_XACT_A table at a certain grain level.

   For example, if GRAIN=MONTH then the records in the W_PRODUCT_XACT_F fact table are retrieved and aggregated to the W_PRODUCT_XACT_A table at a monthly level.

   Running thePLP_ProductTransactionAggregate workflow implements this step.

3. Remove the old records from the W_PRODUCT_XACT_FW_PRODUCT_XACT_A fact table.

   To remove old records you need to use the KEEP_PERIOD and the NUM_OF_PERIOD parameters. For example, if KEEP_PERIOD=YEAR, NUM_OF_PERIOD=3, and the date is May 1, 2005, then the records for the years 2002, 2003, and 2004, and the current year (2005), are kept and the older records are deleted.

   Running the PLP_ProductTransaction_Trim workflow implements this step.

   NOTE:  This workflow is not invoked at the initial ETL run, because data needs to be loaded to W_PRODUCT_XACT_F and then aggregated to W_PRODUCT_XACT_A. During the incremental ETL run, the old transactions are no longer important for the aggregations and can be trimmed to reduce data size in this table. It's important to emphasize that you will not be able to see the old transaction records. But you will still be able to see the aggregated summary records in the aggregate table W_PRODUCT_XACT_A. So please make sure you adjust the NUM_OF_PERIOD values to reflect your data volume and data recency requirement.