By Shawn Lafferty-Oracle on Feb 29, 2016
Primavera Analytics has the ability to Extract, Transform, and Load (ETL) up to 240 Project, Activity and Resource codes, as well as User Defined Fields (UDFs). These codes and UDFs are configured during the installation process of the P6 Reporting Database or by running the Primavera Data Warehouse configuration utility. However, out of the box, the Primavera Analytics Repository Definition File (.rpd) is only configured to take advantage of 50 codes and UDFs. Additional steps are necessary to allow more than 50 codes and UDFs to be visible in Oracle Business Intelligence (OBI). The steps for configuring the .rpd file to use and see these additional codes and UDFs are documented below.
1. Launch the Admin tool à Example: All Programs à Oracle Business Intelligence Edition Plus Client à Administrator
2. Go to File à Open à “Online”
3. Login using weblogic administrator credentials.
User will then see three panels from right-to-left: Physical, Business Model and Mapping, and Presentation.
3 Adding additional code and/or UDF columns beyond 50
Use this process to add one or multiple UDF and/or code mappings.
After following the previous section and Launching and Logging into the Admin Tool:
3.1 Add Column(s) to the Physical Layer
1. In the Physical window, expand “Oracle Primavera P6 Data Warehouse”. à Catalog à dbo.
2. Expand the table where you want to support additional columns, for example W_UDF_ACTIVITY_D and W_UDF_ACTIVITY_HD (Activity UDF Dimension and the Activity UDF Dimension History tables).
For each table you are adding columns to:
- Right-click on the table you are adding columns to and select Properties (ex. W_UDF_ACTIVITY_HD → Properties).
- On the Columns tab, press the “green plus button” or press ALT-Insert. (see Diagram 4)
- Provide the name of the new physical column in the Name field (ex. UDF_COST_51) (see Diagram 5).
- Select the correct type from the Type Combo Box (ex. DOUBLE)
- Check the Nullable Check Box.
- Press OK
- Repeat steps 1-6 for additional columns and tables.
3.2 Drag Columns over to the Business Layer
1. Right-click the table name you added columns to and click on Query Related Objects → Physical → Physical Table.
2. Select all table aliases in the list and press Mark, then Go To.
For each “marked” table alias in the Physical Layer (marked has a red check mark next to the icon):
1. Expand the table alias to reveal its Columns.
2. Right-click the table alias name and click on Query Related Objects → Logical → Logical Table.
3. Select the logical table in the list and press Go To.
4. Expand the logical table that was selected.
5. Drag the added columns (use Shift or CTRL click to select multiple columns) from the marked alias in the Physical Layer onto the name of logical table in the Business Layer.
For each newly dragged column in the Business Layer:
1. Double-click on the column (in the Business Layer still) and update the Name field to be like the other columns (ex. Activity UDF Date 51).
2. Click on the Aggregation tab. Depending on the type of the new column, the default aggregation rule in the aggregation tab may need to be modified. An example of this would be with a user defined cost field that uses “SUM” as the default aggregation rule.
3.3 Drag Columns to the Presentation Layer
For each new column in the Business Layer:
1. Right-click on the table the new columns were added to in the Business Layer.
2. Click Query Related Objects → Presentation → Presentation Table
3. Select all tables in the list
4. Mark each table by selecting every table in the list and press Mark
5. Press Go To
For each “marked” Presentation Table in the Presentation Layer (marked has a red check mark next to the icon):
6. Drag the new columns from the Business Layer over to the Presentation Table.
7. Highlight the new column in the Presentation layer and right-click Properties.
8. From the Admin menu click File à Save. Check in the changes and run consistency checker. The RPD file has now been updated with the new column.
4 Restart the OBIEE Services
1. Launch the Oracle Business Intelligence Enterprise Manager (http://<hostname>:<port number>/em where <hostname> is the server name or IP address and <port number> is the port number for the Oracle Business Intelligence deployment.
2. Login to Oracle Business Intelligence Enterprise Manager with a user account that has Administrator privileges (e.g. weblogic).
3. After successfully logging in to Oracle Business Intelligence Enterprise Manager, on the left hand navigation tree, expand the folder Business Intelligence, then single left mouse click on the coreapplication.
4. On the coreapplication page, select the Overview tab. In the System Shutdown & Startup portlet, click the Restart button to restart all of the Oracle Business Intelligence services.
5. Once the Oracle Business Intelligence services have successfully been restarted, Log out of Oracle Business Intelligence Enterprise Manager.
5 Verify New Column is in Oracle Business Intelligence Analytics
1. Launch the Oracle Business Intelligence Analytics application.
2. Select the Analysis link on the left and then select the Subject area which uses the new column.
3. Expand the folder that uses the new column.
4. Look for new column and verify the column and the column value.