Table Partition for Multiple Data Source and History Data

Purpose of Table Partition Improved performance and ease of data management.
Multiple Data Source Primavera Data Warehouse supports multiple P6 and Primavera Unifier databases as the data source.
By default, the related tables are value partitioned based on the DATASOURCE_ID. The Partitioned tables are visible by viewing \scripts\create_star_tables_part.sql and searching for tables with PARTITION.
History Data For Primavera Data Warehouse, you can control history interval and levels settings on the project level in P6 or Primavera Unifier. History data can grow quickly. For example, if you select Activity as the History Level, this automatically sets the History Interval for Activity and Resource Assignment history to Daily, which can affect the ETL process performance.
By default, these tables are interval-partitioned based on the PERIOD_END_DATE value, then sub-partitioned by the value of the DATASOURCE_ID. Oracle recommends you allocate of separate tablespace for each partition.

The P6 EPPM history tables are as follows:
- Project history
  W_PROJECT_HISTORY_F
- WBS history
  W_WBS_HISTORY_F
- Activity history
  W_ACTIVITY_HISTORY_F
The Primavera Unifier history tables for as follows:
- Business Process history
  W_BP_HIST_F
- Cash Flow history
  W_CASH_FLOW_HIST_F
- Cost Sheet history
  W_COST_SHEET_HIST_F
The indexes on these tables are LOCAL indexes per each partition.
Slowly Changing Data for projects with Activity as the History Level This automatically sets the History Interval for Activity and Resource Assignment history to Daily, which can affect the ETL process performance. By default, these tables are interval partitioned based on the effective_end_date value, then sub-partitioned by the value of the DATASOURCE_ID. The tables related to this feature end in _HD and _HF.
Number of Partitions are dynamically determined for the interval partition. Ten data source partitions are created by default. The following is an example of how to add more data source partitions:
- Value Partition Addition
  alter table W_ACTIVITY_D add partition P11 values (11) tablespace star_hst1;
Interval Partition Management The merging of partitions may be applicable in some environments if the record counts found in each partition are relatively small. The <INSTALL_HOME>/scripts folder contains a history_partition_management.sql script. This script has examples on how to merge partitions based on an upper thresholds and a function to drop empty partitions (that may have been created in error). This script serves as a template in partition management.

Related Topics