Adding custom data to Analytics - Part 2
By Shawn Lafferty-Oracle on Apr 26, 2013
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.
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...