Wednesday Sep 26, 2012
Wednesday Sep 19, 2012
By Jeffrey McDaniel 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.
Provide new information on P6 Analytics and Reporting Database.
- Handling Codes and UDFs in a Multiple Data Source Environment
- Updated Burn Down Whitepapers
- What's a WID? Where is it used?
- Before you upgrade...what to expect (P6 Reporting Database)
- P6 Extended Schema - advanced techniques and additional information
- Report on ETL Status of a Multiple Datasource Environment
- How to find resources without any assignments?
- Role Usage in P6 Analytics
- EPS changes in Analytics (and P6 Extended Schema)
- Analytics Date Range is Out of Date