Using Slowly Changing Dimensions and Daily Snapshot Fact Tables for P6 EPPM Data
To capture history data in P6 projects and enable predictions in CIC, it is necessary to enable Slowly changing dimensions (also known as Type 2 dimensions) to preserve an attribute's history of values by capturing non-fact or dimensional data and associating that data with the correct version of the fact row. Captured data includes project, resource, activity, resource assignment dimensional data, and more. Slowly changing dimensions are used when is it important to preserve the historic context of facts.
When source data elements change or when the STARETL process is run, a new row is added to the dimension table and the previous row is left unchanged. Previously recorded facts remain associated with the old dimension row and new facts are associated with the new dimension row. These rows show the changes to an attribute over time. For example, if the Planned Start Date for an activity has changed between the current capture and the previous capture, the old and new date would both be recorded in the dimension table.
Dimension rows in the dimension table are distinguished by the Effective Start Date, Effective End Date, and Current Flag. The date and time the STARETL process starts becomes the Effective Start Date for the new dimension row. The new version of a dimension Effective End Date defaults to 01-Jan-3000 00:00:00. When the STARETL process is run again, the Effective End Date for the latest version of the attribute then becomes the Effective Start Date for the current version minus one second.
For example, if the Effective Start Date for the current version of the attribute is 04-Nov-2019 9:12:28 PM, then the Effective End Date for the previous version becomes 04-Nov-2019 9:12:27 PM. The most recent dimension row in the dimension table has a value of "1" in the Current Flag column. All other rows have "0" in the Current Flag column.
Dimension Table
Daily snapshot fact tables enable the daily capture of metrics at the Resource Assignment and Activity Levels. Daily snapshot fact tables are used in conjunction with slowly changing dimensions. For example, when the Activity History daily snapshot is recorded, new fact rows will be updated with the most current version of each record in the activity dimension. Previously recorded facts remain associated with the old dimension row version.
The daily snapshot makes it easy to determine what metrics such as Remaining Units looked like at any point in time across all projects that have been opted into this level of data capture.
Related Topics
Last Published Friday, July 5, 2024