Monday Oct 14, 2013

Reference for STAR field mappings

Occasionally questions will come up about the STAR schema and how it maps to fields in P6.  The STAR schema is comprised of data pulled from the P6 EPPM Extended view schema, the rest of the data is calculated as part of the Transformation process. Calculated data includes - historical captures, hierarchies, burndown and work planning calculations, and many others.

If you would like to see how the Extended schema fields are mapped a good place to start is by looking in the ETL_MAPPING table.  Choose what your source schema version is (example: EPPM 8.3 = 83) 

select * from etl_mapping where source_version = 83;

From here you can view how these fields are directly mapped to the staging tables (example: W_PROJECT_DS).  These fields would map similarly to the corresponding dimension or fact tables. This information is also available in the P6 Reporting Database installation directory \res folder in the file mapping.tcsv. 

Wednesday Oct 02, 2013

Usage of Historical UDF's and Current Flags When Creating Analysis'

In the P6 Reporting Database 3.1 and higher, type 2 slowly changing dimensions were added. These changes are captured in tables with the suffix _HD. In each of these tables there is a field called current_flag. With type 2 SCD's the current flag let's you determine what is the most recent row vs.other rows which are the historical rows. For example a project, the project is created it has a row. The next day you change something on the project, maybe the project name, then run the ETL process. Now you'll have two rows. The most recent row will have current flag of '1', the original row will have current flag of '0.'  Having a current flag of '0' is a quick way to determine it is a historical record. In the STAR schema it is easy to evaluate these historical versions. 

In P6 Analytics 3.1 you can see the current flag on some historical records but not in every analysis. Take the Primavera - Activity History subject area, if you add Project Id, Project Name, and Current Flag from under the Project \ General section.  You will see the current flag represent as described above.  If there was a change in the project name you will see two rows and the current flag will have a '0' or '1' to represent the historical record and the current record. This is because the current flag that has been exposed is tied directly the Project dimension (w_project_hd).

If you use the current flag from under the General area of any section it will be the current flag representing that section.  Now let's look at how this could behave when combining different type 2 slowly changing dimensions. Stay in the Primavera - Activity History subject area, and for now remove the current flag and add in a Project Text UDF along with the Analysis containing Project Id and Project Name. If there are multiple changes to the text udf (for example -Primavera UDF1), there will be multiple rows showing the history of what this text udf contained. UDF's are contained in their own type 2 slowly changing dimension tables (w_udf_project_hd).  When adding the current flag back from the Project \ General section you will see a slight change in the Analysis and you may see some of the udf historical records with a 1 next to them.  This is because the current flag is actually representative of the project rows, not the udf rows. If you moved the Current Flag next to Project Name it would fit more appropriately in the Analysis. 

In the example of udf's, you will see the projects listed and if there is a udf assignment you will see the value. If there is no assignment for that project you will see an empty column. If you never had an assignment, then added a udf you will see two rows. One for the previously empty row on that project, another for the new row containing the udf assignment. This will allow you over the life of your project to all the changes to it. 

The Primavera - Activity subject area is a good place to work in if you are more interested in always seeing the current values. The Primavera - Activity History subject area gives you a look at the historical versions of a great deal of information. Usage of the current flag to determine what is the most current version works great when using the current flag under those dimensional areas. When combining historical dimensions and using the current flag you just need to be aware of where the current flag is coming from and what it represents. The current flag isn't always available for all historical dimensions. The history and data that exists in P6 Reporting Database and P6 Analytics 3.1 and is extremely powerful, helping to give a total picture of what has happened with your projects and associated data. How have these values changed over time? How has the project changed over time? You can get this information from the STAR schema or very easily from the P6 Analytics dashboards or Analysis. 


Provide new information on Primavera Analytics and Data Warehouse


« October 2013 »