Review Table Partitioning for Human Resource Analytics

The Human Resource application will benefit from table partitioning especially on larger systems where the amount of data is greater.
The main benefits of table partitioning are:

• Faster ETL, as indexes are rebuilt only over the table partitions that have changed.
  
• Faster reports, as partition pruning is a very efficient way of getting to the required data.
  

Optional or Mandatory

This task is optional, however by default no tables are partitioned.

Applies to

Systems where Oracle Business Analytics Warehouse is implemented on an Oracle database.

Dependencies

No dependencies.

Task

The latest recommendations for table partitioning of Human Resource tables can be found in Tech Notes in My Oracle Support. These should be reviewed before any action is taken.
There is a table partitioning utility provided in ODI which can be used to create partitioned tables. This utility can be run at any time to implement a particular partition strategy on a table. It is re-runnable and can be used to change the strategy if needed. It will backup the existing table, create the partitioned table in its place and copy in the data and indexes.
For example, to implement table partitioning on the table W_WRKFC_EVT_MONTH_F:

1. Execute the scenario IMPLEMENT_DW_TABLE_PARTITIONS passing in the parameters as follows:
   
   Table B-1 Parameters for Table Partitioning

   Parameter Name	Description	Value
   CREATE_SCRIPT_FILE	Whether or not to create a file with the partition table script.	Y(es)
   PARTITION_KEY	Column acting as partition key.	EVENT_MONTH_WID
   RUN_DDL	Whether or not to execute the script.	N(o)
   SCRIPT_LOCATION	Location on file system to create the script.	C:/Scripts/Partitioning
   TABLE_NAME	Name of table to partition.	W_WRKFC_EVT_MONTH_F

2. If required, then review the script and adjust the partitioning definition.
   For the workforce fact table, monthly snapshot records are created from a specified date (HR Workforce Snapshot Date, default value 1st January 2008). Therefore, it would be logical to make this date the cutoff for the first partition, and then partition monthly or quarterly thereafter.
   This is done by changing the script from:
   

   CREATE TABLE W_WRKFC_EVT_MONTH_F 
   …
   PARTITION BY RANGE (EVENT_MONTH_WID) INTERVAL(1)
    (PARTITION p0 VALUES LESS THAN (1)) 
   …
   

   To:
   

   CREATE TABLE W_WRKFC_EVT_MONTH_F 
   …
   PARTITION BY RANGE (EVENT_MONTH_WID) INTERVAL(3) 
    (PARTITION p0 VALUES LESS THAN (200801)) 
   …
   

3. Execute the script against Oracle Business Analytics Warehouse.