Working with the P6 Extended Schema
The P6 Extended Schema consists of multiple views. The number of fields in each view varies greatly, depending on what kind of data the view contains. To view a functional description of the type of data contained in a view, use the Oracle SQL Developer Data Modeler to view the DMD for the schema.
Oracle provides support for reports written against the P6 Extended Schema views only.
Note: In SQL databases you can select tables, views, and materialized views from the schema (for Oracle and Oracle Autonomous Databases, synonyms are also listed). See the Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher for further information.
Naming Convention
The synonyms that make up the P6 Extended Schema use the same naming convention as many of the logical views found in the ODS database, with the addition of a P6 prefix. For example, ACTIVITY in the ODS is P6ACTIVITY in the P6 Extended Schema. Every synonyms in the P6 Extended Schema has the prefix P6.
In addition, the purpose of some views can be inferred from common elements in their names:
- Hierarchy: Views with names containing the word HIERARCHY contain data needed to rebuild one of the parent-child structures from P6. For example, P6EPSHIERARCHY indicates the EPS nodes under which projects fall.
- Spread: Views with names containing the word SPREAD contain data in time-based segments. For example, P6ACTIVITYSPREAD contains units for activities by time period.
Joining
Since the P6 Extended Schema consists of views, and not actual database tables, primary and foreign keys are not hard-wired into the schema. However, a view often contains an OBJECTID field which can be used to join it to another view.
For example, to join the P6PROJECT view to the P6ACTIVITY view, you could use the OBJECTID field from the P6PROJECT view and the PROJECTOBJECTID field from P6ACTIVITY. Here, the OBJECTID field in P6PROJECT serves as a unique identifier for projects, and PROJECTOBJECTID in P6ACTIVITY also serves as a unique identifier for projects. P6ACTIVITY contains its own OBJECTID field, but it cannot be used to join P6ACTIVITY to P6PROJECT because the OBJECTID in the P6ACTIVITY view serves as a unique identifier for activities.
Security
If your organization uses an Oracle or Oracle Autonomous Database database, your P6 security privileges determine which P6 Extended Schema view data you can access.
If your organization uses Microsoft SQL Server, P6 security privileges are not enforced in your P6 reporting software. Instead, you must add security to each report query via a WHERE clause.
Last Published Wednesday, December 13, 2023