About Slowly Changing Dimensions and Daily Snapshot Fact Tables

Previous TopicNext TopicContents

Slowly changing dimensions, or Type 2 dimensions, 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, but is not limited to project, resource, activity, and resource assignment dimensional data. Slowly changing dimensions are used when is it important to preserve the historic context of facts.

When source data elements change, or when the ETL 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 an activity's Planned Start Date 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 ETL process was 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 ETL 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 17-Nov-2014 08:00:00, then the Effective End Date for the previous version becomes 17-Nov-2014 07:59:59. 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.

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.

Turning off slowly changing dimensions

If you turn off the slowly changing dimensions for a field, the current dimensional row can be updated with a new value without the addition of a new dimensional row.

Slowly changing dimensions can be controlled on a field-by-field level by making adjustments to the mappings.tcsv file in the \res folder in the Star install path. A Y or N flag for each field in the mappings file controls whether a change to this field would cause a new row to be added to the dimensional table. Y indicates that a change will cause a new row to be added. N indicates that a new row will not be added if a change is made to this field. For example, if the Activity Description field has a Y flag, then changes made to this field will cause a new row to be added to the dimensional table the next time the ETL process is run. If the EPS Name field has an N flag, then the ETL process will not update the dimensional table with a new row. Configuring the Y or N flag for each field can prevent cascading updates to fact tables. For example, if a change is made to the EPS table, that change would cause a new row to be added to the dimension table for all of the activities affected by the change.

Related Topics

Enabling Daily History Snapshots and Slowly Changing Dimensions in a P6 Project



Legal Notices
Copyright © 1999, 2014, Oracle and/or its affiliates. All rights reserved.

Last Published Friday, January 31, 2014