To configure the Inventory Monthly Balance (W_INVENTORY_DAILY_BALANCE_F) and Inventory Lot Monthly Balance (W_INV_LOT_MONTHLY_BAL_F) aggregate tables, you need to consider the aggregation level, the time period to update the aggregation, and the time period to keep records in the Inventory Balance tables.
You need to configure three parameters to configure the Inventory Monthly Balance tables:
GRAIN
The GRAIN parameter controls the time span for which the latest balance is kept. This parameter has a preconfigured value of Month. The possible values for the GRAIN parameter are:
DAY
WEEK
MONTH
QUARTER
YEAR
KEEP_PERIOD
The KEEP_PERIOD parameter, in conjunction with NUM_OF_PERIOD, controls how many periods worth of data are retained in the Inventory Daily Balance tables. For example, if KEEP_PERIOD is CAL_MONTH and NUM_OF_PERIOD is 3, then only the most recent 3 months of data are retained. This parameter has a preconfigured value of CAL_MONTH. Values for the KEEP_PERIOD parameter include:
CAL_DAY
CAL_WEEK
CAL_MONTH
CAL_QTR
CAL_YEAR
NUM_OF_PERIOD
The NUM_OF_PERIOD parameter has a preconfigured value of 3. The value for the NUM_OF_PERIOD parameter is a positive integer, for example, 1, 2, 3, and so on.
Note:
If you need Inventory Turns data for a period older than three months, you must change the parameter values for KEEP_PERIOD and NUM_OF_PERIOD. For example, if you want data for the last three years, then set KEEP_PERIOD to CAL_YEAR and NUM_OF_PERIOD to 3.About Configuring the Product Transaction Aggregate Table
There are two aggregation scenarios to configure the Product Transaction aggregate (W_PRODUCT_XACT_A) table—the initial ETL run and then the incremental ETL run.
For your initial ETL run, you need to configure the aggregation level, and the length of history kept in the Product Transaction fact table.
For your initial ETL run, you need to configure the aggregation grain, using the GRAIN parameter.
For the incremental ETL run, you need to configure the aggregation level, the update period in aggregation, and the length of history kept in the Product Transaction fact table, using the following parameters:
GRAIN
The GRAIN parameter specifies the aggregation level. Valid values are DAY, WEEK, MONTH (preconfigured value), QUARTER, YEAR.
REFRESH_PERIOD
The REFRESH_PERIOD parameter, together with NUM_OF_PERIOD, indicates the number of period of records that will be refresh from the transaction table to the aggregate table. Valid values are DAY, WEEK, MONTH (preconfigured value), QUARTER, YEAR.
NUM_OF_PERIOD
The NUM_OF_PERIOD parameter, together with REFRESH_METHOD, indicates the number of periods of records that will be refreshed from the transaction table to the aggregate table. Valid values are positive integers, for example, 1, 2, 3 (preconfigured value).
Before you run the initial ETL and then the incremental ETL to load the Product Transaction aggregate table, you need to configure the Product Transaction Aggregate Table, as follows.
To configure the Product Transaction Aggregate Table
You need to configure three parameters: REFRESH_PERIOD = 'MONTH', GRAIN = 'MONTH', and NUM_OF_PERIOD = 3.
To configure the Product Transaction aggregate table for the initial ETL run
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 PLP_ProductTransactionAggregate scenario implements this step.
To configure the Product Transaction aggregate table for the incremental ETL run
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_Update scenario implements this step.
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 the PLP_ProductTransactionAggregate scenario implements this step.