Slowly Changing Dimensions (Type 2) and Historical Facts-Part 1
By Jeffrey Mcdaniel-Oracle on Jun 14, 2013
This is going to be a two part series. In part 1 we'll cover what is a SCD (slowly changing dimension) and how is that determined in the P6 Reporting Database. In a future blog part 2 will cover from the database side how to determine which is the most up to date row, and how to look at your historical rows.
In P6 Reporting Database version 3.1 we added type 2 Slowly Changing Dimensions. We always had type 1. Type 1 is an overwrite. It really doesn't store any history.
For example, you are working on Project ABCD. The original budget on the project was $1 million dollars. If this was changed to $2 million dollars, the row would be removed and updated with the current value. So type 1 SCD's didn't really keep a historical value.
Type 2 SCD's leaves the old record, and inserts a new record. This way you can see what the value was in the past and what it is now. One thing to be aware of when opting in for Type 2 SCD dimensions is you can generate a large number of rows quickly. The ETL process is meant to run once per day. Say on average you make about 15% of changes to your data daily, new rows will be added to keep track of those new changes and close out old records. Certain changes can have cascading effects. A change to an EPS could cause changes to all lower levels - project, wbs, activitites. Changes to the project could cause creation of new spread records. So be careful in what projects really need SCD's and Historical fact data.
You can opt in a project to track type 2 SCD and historical facts by setting the History Interval to Activity for the project. Again this should be a limited set that gets this level of granularity. By default projects are not set at this level. By default most changes on a dimension would cause the capture of a new row of data. To view the field by field list of what captures new rows these can be viewed in the \star\res directory - mappings.tcsv file. This file has a list of each field mapping to a staging table.
Lets take this line for example:
For the 8.3 schema mappings of the Activity Dimension, any change on the name field through the Pxrtpuser Activity view will result in a new row in this dimension. The 'Y' flag at the end of this line is on\off switch for this field. You have the ability to flip the switch and turn off fields so they do not cause new SCD rows. If I didn't want activity name changes to cause a new row to be created set the flag on this row to be 'N'
You can also turn off all type 2 SCD's for everything. We have a 'kill switch' so you don't have to update every row in the mappings.tcsv. Go to your \res directory and edit the properties file. Look for the line:
if exists set to false. If does not exist insert the line and set to false. Also check your ETL_PARAMETERS table and validate after ETL process this value is set to false. This will override the type 2 SCD feature and turn it off for all.