Wednesday Jul 17, 2013

Security for Multiple Data Sources

The default method of security for the P6 Reporting Database is row level security. With this security policies are applied to each logical level of security enforcement - project, resource, costs. Security is calculated out for each user with the Analytics module access. Security is written into STAR for each project this user has access to and if they do or do not have cost access, similar calculation based on resource access and written to a resource security table. 

Since the introduction of the ability to have multiple data sources into the STAR data warehouse we needed to now be able to enforce security based on these qualifiers as well as data source id. If user Jeff has access to Project Philadelphia in DS1 that security will be written out and applied by the policies. If user Jeff also exists in DS2 and there is also a Project Philadelphia in DS2 but user Jeff does not have access then he will not have access through P6 Analytics\P6 Reporting Database either. The project id, and data source id would be different and when filtering user Jeff would not gain access to that project because it is already calculated out and the query would only apply to that data source. This type of security enforcement allows for separation of data sources based on security but also allows for the aggregate abilities of multiple data sources if the user did have access to projects or resources in both data sources. In the end with access to both you can show a unified view of the enterprise wide data for all data sources. 

Thursday Jul 11, 2013

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

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. 


Provide new information on Primavera Analytics and Data Warehouse


« July 2013 »