Wednesday Sep 26, 2012
Wednesday Sep 19, 2012
By Jeffrey Mcdaniel-Oracle on Sep 19, 2012
Beginning with the Primavera Reporting Database 2.2\P6 Analytics 1.2 release, the first release that supported the P6 Extended Schema, a new ability was added to filter which projects could be included during an ETL run. In previous releases, all projects were included in an ETL run. Additionally, all projects with the option to enable publication are included in the ETL run by default.
Because the reporting needs for P6 Extended Schema are different from those of STAR, you can define a filter that will limit the data that is included in the STAR schema. For example, your STAR schema can be filter to only include all projects in a specific Portfolio, or all projects with a project code assignment of 'For Analytics.' Any criteria that can be defined in a Where clause and added to a view can be used to filter the projects included in the STAR schema. I highly suggest this approach when dealing with large databases. Unnecessary projects could cause the Extract portion of the ETL process to take longer. A table in STAR called etl_projectlist is the key for what projects are targeted during the ETL process.
To setup the filter, perform the following steps:
1. Connect to your Primavera P6 Project Management Database as Pxrptuser (extended schema owner) and create a new view:
create or replace view star_project_view
select PROJECTOBJECTID objectid
from projectportfolio pp, projectprojectportfolio ppp
where pp.objectid = ppp.PROJECTPORTFOLIOOBJECTID
and pp.name = 'STAR Projects'
--The main field that MUST be selected in the view is the projectobjectid. Selecting any other field besides the projectobjectid will cause the view to be invalid and will not work. Any Where clause can be used, but projectobjectid is the key.
2. In your STAR installation directory go the \res folder and edit the staretl.properties file. Here you will define the view to be used. Add the following line or update if exists:
3. When running the staretl.cmd or staretl.sh process the database link to Pxrtpuser will be accessed and this view will be used to populate the etl_projectlist table with the appropriate projectobjectids as defined in the view created in step 1 above.
As of Primavera Analytics 15.1 Unifier was added as a possible source. This same filtering criteria based on projectobjectid through a view still applies and would need to be applied to the Unifier schema and the staretl.properties file so it can be used by the ETL process, just like it does for a P6 source.
Provide new information on Primavera Analytics and Data Warehouse
- Visual Analyzer - new to Primavera Analytics 16.1
- Role Utilization (New to Analytics 16.1)
- How to Apply Row Level Security to New Tables
- How to add more than 50 Codes or UDFs to Primavera Analytics
- Logging in the Data Warehouse Configuration Web Application
- Upgrade Considerations When Using Multiple Data Sources
- History Options for Unifier Data
- Why is Oracle_Home Required? What Would Happen if it is Not Set?
- Top Questions to Answer Before Setting Up Primavera Analtyics
- Refreshing RPD Translation Changes Without Restarting OBI Services