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
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.
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:
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.
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...