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 STAR 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 Star database will determine the size of these history tables. You should consider how features can optimize performance, such as:
Activity Level History & Slowly Changing Data In P6 Reporting Database, Activity Level History has been coupled with features that can impact performance of the ETL process. The most import of these features are 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 Star database. Note the requirements to determine which User Defined Fields and Codes are needed in Star. The more UDFs and codes that are mapped, the more they'll affect the ETL process.
Steps in ETL that have the most significant run time 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) |
|
|
Related Topics |
Legal Notices
Copyright © 1999, 2014,
Oracle and/or its affiliates. All rights reserved.
Last Published Friday, January 31, 2014