Friday Jun 14, 2013

Slowly Changing Dimensions (Type 2) and Historical Facts-Part 1

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.

Thursday Jun 13, 2013

Validating Metadata Updates

In P6 Reporting Database and P6 Analytics versions 3.1 a metadata feature was added to allow for altering of calculations and a way to add new calculations. There is already an existing path for adding new scripts - user_scripts. User_scripts will allow you to establish new steps in the ETL process such as - adding additional tables or fields and custom calculations to those new objects. The metadata feature allows you to change calculations on existing fields in historical, burndown, or workplanning fact tables.

See documentation for more information:

Validating the metadata before running the ETL is key. If the changes you added will fail, the ETL process will revert back to a default calculation for all metadata calculations. For example, you change the metadata calculation on two fields. One of the calculations causes a failure, both will revert back to the default calculation column for what to run. Here are the steps to validate your metadata calculations:

  • After making change. Run StarETL for steps 17 through 20

- Linux: -from 17 -to 20

- Windows: staretl.bat 17 20

  • Check the log output for any errors or warnings.

A few more items to be aware of:

  • Calculations are per data warehouse/not per data source
  • Drop STARUSER and you will lose all custom calculations
  • Metadata calculations are stored in the ETL_CALCULATIONS table


Provide new information on Primavera Analytics and Data Warehouse


« June 2013 »