Slowly Changing Dimensions (Type 2) and Historical Facts-Part 2
By Jeffrey Mcdaniel-Oracle on Jul 11, 2013
In the first part we discussed the types of slowly changing dimensions available in P6 Analytics and P6 Reporting Database.
Now lets look at how you would view the data. If you connected to your Staruser schema and queries w_project_hd you would be looking at your type 2 SCD project table. In this table you could have multiple rows per project if there were changes on that project and that project was opted in for slowly changing dimensions (see Part 1 of this blog for how to opt in).
In this screen shot we are looking at sample for project - Baytown.You'll notice three rows here for the same project id. The main rows we want to focus in on are:
effective_start_date, effective_end_date, and current_flag
When a project is first inserted into the w_project_hd table it will have current flag set to 'Y' to confirm this is the most recent value for this project row. The effective start date will be when it was inserted and the effective end data will be 01-Jan-3000. If you made a change for this project, ran the ETL you would get a second row. Now the original row would have an effective_end_date set to the effective_start_date of the new row and the current flag on that row would be set to 'N' and the new row would have the new current flag value of 'Y' and so on as more rows are added. It is a basic concept to follow and something you will want to be aware of if directly querying any _HF or _HD tables in the STAR schema.
Another thing to be aware of is if you are using P6 Analytics and the Primavera - Activity History subject area this is using the type 2 dimension tables along with a historical fact table. This will show all versions of these historical rows. You can add filtering to go on just current flag if necessary, but the ability to see and compare all historical dimension data will be available in this subject area.
Slowly changing dimensions and historical facts provide a very powerful tool to analyze data change over time. The ability to see historical dimensional changes gives a total project picture along with the existing and new history subject areas added in P6 Analytics 3.1 and P6 Reporting Database 3.1.