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.

Wednesday Apr 22, 2015

Spreads in Primavera Analytics

We get a lot of questions about spreads and how they are handled in Primavera Analytics. Spreads are really the core of P6 resource assignment and planning. 

What are Spreads? In a simple way, Spreads are the distribution of units and costs over the life of an activity or resource assignment. This includes both the early dates (forward scheduling pass) and late dates (backward scheduling pass). Spreads can include curves and calendars, but we will leave those for another time.

If we look at the following example in P6 EPPM:

Activity ID: MN1000 --- Assigned Resource:Automation System Engineer

Assignment Remaining Early Start Date: 01-MAY-2015 --- Early Finish: 11-MAY-2015

Assignment Remaining Late Start Date: 27-JUL-2015 --- Late Finish: 31-JUL-2015

Remaining Units: 6.78d

Below we look at the same Activity, MN1000, in Primavera Analytics. We see that the Remaining Units are being "spread" or distributed over the Early and Late Dates accordingly. We use Remaining Units for spreads over Early Dates (01-MAY-2015 to 11-MAY-2015) and Remaining Late Units for spreads over Late Dates (23-JUL-2015 to 31-JUL-2015).

For Remaining Labor Units, we have 8 hours per day, distributed between May 1, 2015 and May 8, 2015. May 2, May 3, May 9 and May 10th, 2015 are not shown because they are non-work time weekend days, and the remaining 6.20 hours are on May 11, 2015.

For Remaining Late Labor Units, we have 8 hours per day, distributed between July 31, 2015 and July 23, 2015 (backwards since this is the backward pass of the schedule). July 25, and July 26 are not shown because they are non-work time weekend days, and the remaining 6.20 hours appear on July 23, 2015.

Friday May 09, 2014

Indicator type UDFs Part 2

In a previous blog, we covered how to bring indicator type UDFs (User Defined Fields) into the STAR data warehouse by manually adding them as text type UDFs in the file and adding a script into the ETL process. In this post we’ll cover how to use conditional formatting in Oracle Business Intelligence to display these indicator UDFs in analyses.

First, create a new analysis in Oracle Business Intelligence, in this example we’re using the Primavera – Activity subject area, and a simple selection of two indicator type Project UDFs, Schedule Status and Overall Status.

Since the indicators UDFs are being stored as text in the STAR, the initial output from Oracle Business Intelligence is not what we want, we’ll need to apply some conditional formatting in order to display the indicators correctly.

To accomplish this, we first need to add some place holder columns that we can use to display the indicator images. You can just add a second occurrence of each indicator column and rename them so you know which column will contain the text and which will be used to display the actual indicator image.

Next, go to the Schedule Status column, click on the menu drop down and select Edit Formula.

We don’t want to display the text along with the indicator image, so select the Custom Headings option, then in place of the actual column name substitute a blank value(‘ ‘) in the Column Formula section.

Now we can take care of the conditional formatting. For the Schedule Status column, click on the drop down and select Column Properties.

Then go to the Conditional Format tab, click Add Condition, and select the column you want to base the formatting on, in this case Schedule Status Text.

Select the condition and click OK. From the Edit Format window click on Image.

Select an image to be displayed based on the Schedule Status Text, click OK.

Enter any additional format changes, in this case we’ve selected Center horizontal and vertical alignment for the column, then click OK.

Repeat the conditional formatting steps for the other values of the indicator, and then repeat the process for any other indicators in the analysis, in this example we also selected Overall Status.

Once finished, the only remaining step is to hide the text columns from the analysis so only the indicators will be displayed. From the Results tab, right-click on the heading for the text columns and select Hide Column from the drop down list.

Once the text columns have been hidden you will be left only with the indicator columns and the images that were selected in the conditional formatting steps.

Wednesday Mar 26, 2014

Updated Burn Down Whitepapers

An effort was made to explain in detail how information flows from P6 into P6 Analytics, specifically the Primavera - Burn Down Subject Area within Oracle Business Intelligence. The Primavera - Burn Down subject area captures data at a specific point in time (user defined), then uses that point in time capture to compare how a project should progress over time if everything goes "according to plan".  P6 Analytics has the ability to capture which activities are progressing according to plan and which ones are ahead or behind schedule by comparison to the original point in time capture of the project. The Primavera - Burn Down Subject Area captures units and costs, as well as activity status counts (Not Started, In Progress, Completed, etc...).

For details on the exact measures that P6 Analytics displays in Oracle Business Intelligence, and how those measures are calculated and stored, we have created three new whitepapers. The three whitepapers detail how units and activity counts are calculated and stored on a daily basis. In addition, we have a whitepaper that details how the overall flow of data moves through the data capture process (ETL), and what the expected results will be in Oracle Business Intelligence within the Primavera - Burn Down Subject Area of P6 Analytics.

To download the three New whitepapers, follow the links below:

Primavera - Burn Down Counts Details

Primavera - Burn Down Units Details

Primavera - Burn Down Data Flow Details

Friday Apr 26, 2013

Adding custom data to Analytics - Part 2

As an add on post to Jeff’s previous blog regarding how to add additional fields from P6 EPPM to the STAR schema, it may be necessary to add the new fields to your RPD file so they appear within P6 Analytics in Oracle Business Intelligence.

Adding new fields to the RPD field is a pretty straight forward process, assuming that the new fields already exist in the STAR schema. The process involves using the Oracle BI Administration tool to modify the OraclePrimaveraAnayltics.rpd file, manually adding the field to the Physical layer, then dragging it to the Business Layer and finally to the Presentation Layer.

Two recommendations before we get started. First, I would recommend that you make a copy of your existing RPD file prior to moving forward here. Even though the newest version of Oracle Business Intelligence handles this for you with auto RPD iterations, I still think it’s a good idea to keep a good “working” copy of the RPD in a safe place just in case.

Second, I would recommend doing this type of RPD manipulation in offline mode. Although it is quite possible that you could get through it all having the RPD “online”, my experience has been more times than not when performing RPD manipulations, that offline is the way to go.

Let’s Begin!

A quick check in SQL Developer of my STAR schema, select * from w_project_d;

Once I have validated that I have successfully added the fields correctly during my ETL run, the next step is to open the OraclePrimaveraAnalytics.RPD file in Oracle BI Administration tool.

In Oracle BI Administration tool, the far right column is the Physical section. This is where the connection pool to the database exists as well as the physical table objects. Expand the connection for your STAR schema all the way down until you reach the physical tables for STAR. Locate the W_PROJECT_D table here. Right click on the W_PROJECT_D table name, on the resulting menu choose New Object, then select Physical Column…

In the resulting physical column window, we need to add/change the following 3 options (NOTE: These 3 fields need to match identically the field(s) that was added to the STAR schema).

Name: PROJECT_OWNER Type: VARCHAR Length: 255

Click OK. This will added the new physical fields to the RPD. You should also notice that after clicking OK to add the new field, if you expand the logical representation of the physical table (Dim_W_PROJECT_D_Project), you shouldl see the newly added PROJECT_OWNER field here as well. From here, it’s an exercise in a series of dragging and dropping the newly added field into the other layers of the RPD, so the field appears where it’s needed.

Expand the Dim_W_PROJECT_D_Project logical table in the Physical Side of the RPD. Find the PROJECT_OWNER Field, highlight it and drag it from this location to the Business Model Layer under the Dim – Project Section.

The newly added field should appear at the bottom of the Column List. You can right click on the PROJECT_OWNER field and select Properties to see the details of this new field. On the General Tab of the Field Properties Window, you can aslo change the Name of the field here to match how you want the field to appear in the Presentation Layer. For Example, you could remove the underscore here and change the case so it appears more normal (see Below)

The final step in the RPD Administration tool is to drag the field from the Business Layer onto the Presentation Layer in the Subject Area(s) you want to have the new field available in Oracle Business Intelligence. In this example, I am only going to add the new field to the Primavera – Activity Subject Area, but if I needed it in the other ones, I could drag it to those as well.

Save the RPD, and deploy the updated RPD file in Oracle Business Intelligence Enterprise Manager. The new field should be available in the Primavera – Activity Subject Area

Until Next Time...



Provide new information on Primavera Analytics and Data Warehouse


« July 2016