Thursday Nov 29, 2012

What is the difference between Row Level Security and RPD security?

Row level security (RLS) is a feature of Oracle Enterprise Edition database. RLS enforces security policies on the database level. This means any query executed against the database will respect the specific security applied through these policies. For P6 Reporting Database, these policies are applied during the ETL process. This gives database users the ability to access data with security enforcement even outside of the Oracle Business Intelligence application. RLS is a new feature of P6 Reporting Database starting in version 3.0. This allows for maximum security enforcement outside of the ETL and inside of Oracle Business Intelligence (Analysis and Dashboards). Policies are defined against the STAR tables based on Primavera Project and Resource security. RLS is the security method of Oracle Enterprise Edition customers. See previous blogs and P6 Reporting Database Installation and Configuration guide for more on security specifics.

To allow the use of Oracle Standard Edition database for those with a small database (as defined in the P6 Reporting Database Sizing and Planning guide) an RPD with non-RLS is also available. RPD security is enforced by adding specific criteria to the physical and business layers of the RPD for those tables that contain projects and resources, and those fields that are cost fields vs. non cost fields. With the RPD security method Oracle Business Intelligence enforces security. RLS security is the default security method. Additional steps are required at installation and ETL run time for those Oracle Standard Edition customers who use RPD security.

The RPD method of security enforcement existed from P6 Reporting Database 2.0/P6 Analytics 1.0 up until RLS became available in P6 Reporting Database 3.0\P6 Analytics 2.0.

Monday Nov 19, 2012

Data that has been deleted in P6, how is it updated in Analytics

In P6 Reporting Database 2.0 the ETL process looked to the refrdel table in the P6 PMDB to determine which projects were deleted. The refrdel table could not be cleared out between ETL runs or those deletes would be lost. After the ETL process is run the refrdel can be cleared out. It is important to keep any purging of the refrdel in a consistent cycle so the ETL process can pick up these deletes and process them accordingly. 

In P6 Reporting Database 2.2 and higher the Extended Schema is used as the data source. In the Extended Schema, deleted data is filtered out by the views. The Extended Schema services will handle any interaction with the refrdel table, this concern with timing refrdel cleanup and ETL runs is not applicable as of this release. In the Extended Schema tables (ex. TaskX) there can still be deleted data present. The Extended Schema views join on the primary PMDB tables (ex. Task) and filter out any deleted data.  Any data that was deleted that remains in the Extended Schema tables can be cleaned out at a designated time by running the clean up procedure as documented in the P6 Extended Schema white paper. This can be run occasionally but is not necessary to run often unless large amounts of data has been deleted.

Thursday Nov 15, 2012

Where are my date ranges in Analytics coming from?

In the P6 Reporting Database there are two main tables to consider when viewing time - W_DAY_D and W_Calendar_FS.  W_DAY_D is populated internally during the ETL process and will provide a row for every day in the given time range. Each row will contain aspects of that day such as calendar year, month, week, quarter, etc. to allow it to be used in the time element when creating requests in Analytics to group data into these time granularities. W_Calendar_FS is used for calculations such as spreads, but is also based on the same set date range. The min and max day_dt (W_DAY_D) and daydate (W_Calendar_FS) will be related to the date range defined, which is a start date and a rolling interval plus a certain range. Generally start date plus 3 years. 

In P6 Reporting Database 2.0 this date range was defined in the Configuration utility.  As of P6 Reporting Database 3.0, with the introduction of the Extended Schema this date range is set in the P6 web application. The Extended Schema uses this date range to calculate the data for near real time reporting in P6.  This same date range is validated and used for the P6 Reporting Database.  The rolling date range means if today is April 1, 2010 and the rolling interval is set to three years, the min date will be 1/1/2010 and the max date will be 4/1/2013.  1/1/2010 will be the min date because we always back fill to the beginning of the year. On April 2nd, the Extended schema services are run and the date range is adjusted there to move the max date forward to 4/2/2013.  When the ETL process is run the Reporting Database will pick up this change and also adjust the max date on the W_DAY_D and W_Calendar_FS. There are scenarios where date ranges affecting areas like resource limit may not be adjusted until a change occurs to cause a recalculation, but based on general system usage these dates in these tables will progress forward with the rolling intervals.

Choosing a large date range can have an effect on the ETL process for the P6 Reporting Database. The extract portion of the process will pull spread data over into the STAR. The date range defines how long activity and resource assignment spread data is spread out in these tables. If an activity lasts 5 days it will have 5 days of spread data. If a project lasts 5 years, and the date range is 3 years the spread data after that 3 year date range will be bucketed into the last day in the date range. For the overall project and even the activity level you will still see the correct total values.  You just would not be able to see the daily spread 5 years from now. This is an important question when choosing your date range, do you really need to see spread data down to the day 5 years in the future?  Generally this amount of granularity years in the future is not needed. Remember all those values 5, 10, 15, 20 years in the future are still available to report on they would be in more of a summary format on the activity or project. 
The data is always there, the level of granularity is the decision.


Provide new information on Primavera Analytics and Data Warehouse


« November 2012 »