All the following can affect ETL runtime.
Projects Published The number of projects and associated project data will have a direct correlation to the duration of the ETL run time. Carefully consider which projects require loading to the Primavera Data Warehouse database by filtering for these projects. Some considerations may be active projects or projects that meet some specific criteria by using a project filter.
Spread Interval In P6 EPPM, when configuring the service settings, the administrator must define the spread interval for each project. It is defined as a starting date to the current date plus a rolling interval. The definition of this interval has a direct correlation to the amount of spread data in Star: the larger the interval, the greater the number of spread records for each project. Oracle recommends keeping the rolling interval to a couple years from the current data.
Traditional History This feature keeps history at a level defined within a project at a specific interval. The number of projects that meet the designated history level and the intervals that have been crossed throughout the life cycle of the Primavera Data Warehouse database will determine the size of these history tables. You should consider how features can optimize performance, such as:
- Selectively determining the number of projects that require history.
- Determining the level of storage granularity for each project with history. The higher the granularity, the fewer rows there will be over time.
- Turning off project history over time as projects end or close to minimize the cost of writing records for projects that are not active anymore.
- Noting requirements during the initial configuration to correctly size the number of partitions for the Primavera Data Warehouse database and the months to include in each partition. An even distribution of data per partition is optimal to increase performance.
Activity Level History & Slowly Changing Data In Primavera Data Warehouse, Activity Level History has been coupled with features that can impact performance of the ETL process. The most important of these features is Slowly Changing Data. When a project has Activity Level History enabled, changes are tracked at the field level and new dimension and fact records are created with each change. You should limit the projects with this history setting to ones that need this level of granularity and require tracking of changed data over time. The more projects with this setting, the more records will be stored in the activity history fact table and the slowly changing dimensions and fact tables.
Burn Down and Work Planning These two features are driven by the project having Activity Level History and a set of predefined user-defined fields with appropriate values. The calculation of these projects with this feature enabled can be costly, so you should minimize the number of projects with these features.
The Number of User-Defined Fields and Codes mapped During the initial configuration, select user-defined fields and codes to include within the Primavera Data Warehouse database. Note the requirements to determine which user-defined fields and codes are needed. The more UDFs and codes that are mapped, the more they will affect the STARETL process.
Steps in the STARETL process that have the most significant runtime due to settings:
ETL Step | Definition | Affecting Factors | Considerations |
|---|---|---|---|
ActivityResourceAssignmentSpreads | Load activity and resource assignment spreads (fact_load) |
|
|
MergeProjectHistory | Merge Project History (hist_merge) |
|
|
SourceExtract | Extract data from source database (source_extract) |
|
|
DimensionLoad | Load Dimension Tables (dim_load) |
|
|