Monday Feb 29, 2016

How to add more than 50 Codes or UDFs to Primavera Analytics

1 Background

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.

2 Launching and Logging into the Admin Tool

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:

  1. Right-click on the table you are adding columns to and select Properties (ex. W_UDF_ACTIVITY_HD → Properties).
  2. On the Columns tab, press the “green plus button” or press ALT-Insert. (see Diagram 4)
  3. Provide the name of the new physical column in the Name field (ex. UDF_COST_51) (see Diagram 5).
  4. Select the correct type from the Type Combo Box (ex. DOUBLE)
  5. Check the Nullable Check Box.
  6. Press OK
  7. 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.

Diagram 6

Diagram 7

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.

Diagram 8

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.

Thursday Dec 11, 2014

Why Aren't My Code Changes Being Reflected in Analytics?

The scenario is, you logged into P6, you made a series of activity code changes on your project.  An hour or two later you ran the ETL process, expecting to see the codes changes reflected when you viewed them in OBI or directly in your Star schema.  However they are not updated, why?

Let's walk through the process of how the data gets there and why codes are different than updating activities directly.

Step 1:  Project Publication - what counts as a change.

You are dependent on the thresholds being crossed for updating projects in the Extended schema (See earlier blog for more info on thresholds). Basically changes to a project are counted, then if you reach the threshold (say 100 changes) or the time threshold (say 24hrs. and 1 change) the project will be published.  However items like code assignments do not count as a change. The only updated areas that count as a change are updates to the following tables:

PROJWBS (WBS rows), TASK (Activities), TASKRSRC (Resource Assignments), and TASKPRED (Relationships)

Step 2: Publishing the Project

The design behind this is these are the most commonly updated areas in a schedule.  To throttle the system and avoid overloading the queue with unnecessary updates these are the core areas that drive updates.  If you are making major changes to codes it is good to be aware of this and you can choose to 'Publish Now' which will then force a publication of your project and capture your code changes.

Step 3: Run the ETL

OK now that we have the Extended Schema (Project publication) side understood and worked out you can run the ETL as normal.  Remember the Extended Schema is the heart of all the data that makes it into P6 Analytics.  P6 Analytics and your STAR schema is only as up to date as your Extended Schema.  

Friday Aug 08, 2014

Increase to Default Values of Codes and UDFs in P6 Analytics

In the P6 Analytics 3.3, the default value of all UDF's (subject area (Project, Activity, etc.) and type (Date, Text, etc.)) were increased from 20 to 40.  This includes the RPD changes.  When choosing your UDF's from the configuration utility these changes will automatically be reflected in the schema and no changes will be needed in the RPD until you hit 40.   We also increased the default value of Codes from 20 to 50.  Same updates apply as with UDFs where all schema and RPD changes are automatically reflected up to 50. If adding greater than 50 for Codes or 40 for UDF's the appropriate RPD changes will need to be made to add additional values but all schema side changes will be handled by the ETL process.

Provide new information on Primavera Analytics and Data Warehouse


« July 2016