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


Friday Apr 05, 2013

Adding custom data to Analytics - Part 1 - adding to the ETL

Starting with the Analytics 2.0\ RDB 3.0 releases a new user scripts section was added which appended a final step to the ETL process to run any additional scripts someone might have.  These user scripts could include SQL to add dimensional data to your Star that currently doesn't exist.  For example, Project Owner is a field that is related to Projects but is currently not exposed in the Extended schema or Project Dimension in Star. However if this is a field you feel you need the user scripts is the way to accomplish this. In this blog we will walk through an example of how to add Project Owner into your Project dimension. Please be aware this is just an example, not a patch or supported version.  Also the user scripts section is just an opening to allow for expansion. Make sure all scripts are approved by your DBA and are tested thoroughly for performance and data implications. If you add scripts to the user scripts folder and the ETL process begins to slow down at the final step where these scripts are executed remove the scripts and work on the sql performance.  Oracle does not support custom SQL that is not part of the product. Make sure users adding scripts to this section have the appropriate permissions. This step will connect as STARUSER unless otherwise changed. The scripts directory should only be accessible to Administrators.

Project Owner:

1- You need to add new columns to the dimension table. Add a new script to your \scripts\user_scripts folder called create_columns.sql. Here is a sample of SQL to add columns and ignore if exist:

  v_new_col1 number := 0;
  v_new_col2 number := 0;
  Select count(*) into v_new_col1 from user_tab_cols where column_name = 'PROJECT_OWNER' and table_name = 'W_PROJECT_D';
  if (v_new_col1 = 0) then
      execute immediate 'alter table W_PROJECT_D add PROJECT_OWNER varchar2(255)';
  end if;
  Select count(*) into v_new_col2 from user_tab_cols where column_name = 'PROJECT_RSRC_OWNER_OBJECT_ID' and table_name = 'W_PROJECT_D';
  if (v_new_col2 = 0) then
      execute immediate 'alter table W_PROJECT_D add PROJECT_RSRC_OWNER_OBJECT_ID varchar2(255)';
  end if;

In this example, we need to do some transformation of the data. In the Extended schema in the Project view there is a field for "ownerresourceobjectid" this is the object id of the resource that owns the project. For some this may be enough, but you probably want to have the actual username for the resource that owns this project. Because of this we are going to create 2 columns in the dimension but only expose one to Analytics. The first column will store the project resource owner id, then we will find the username based on this object id and update this other new column for project owner with the username.
2- Now we need to populate these columns. Add a new script to your \scripts\user_scripts folder called project_owner.sql. Here is a sample of SQL to populate these fields:

set serveroutput on size 100000
-- add project owner values
  v_src_id number;
  v_link_name varchar2(25);
  vsql varchar2(200);
  v_src_id := get_source_db_id;
  v_link_name := get_link_name;

vsql := 'update w_project_d set PROJECT_RSRC_OWNER_OBJECT_ID = (select ownerresourceobjectid from project@' || v_link_name || ' where objectid = w_project_d.project_object_id) WHERE datasource_id =' || v_src_id; 
execute immediate vsql;
UPDATE w_project_d set PROJECT_OWNER = (select user_name from w_resource_d where resource_object_id = w_project_d.project_rsrc_owner_object_id) WHERE datasource_id = v_src_id;

The content of this SQL is just an example. What we are doing in the first update is getting the ownerresourceobjectid from the Project view in the Extended schema through the database link.  Next we are going to match based on the resource object id and find the username from a table already  existing in STAR. Then we will commit and exit out of the process.

3- We need to define the order the scripts are run in the user_scripts folder.  In P6 Reporting Database you can define order that scripts get executed.  This is helpful in a case like this or if you see deadlock issues.  Go to \res\priority\ and edit the user_scripts.txt (if it does not exist, add it).  Set the following priority:

# Order of user scripts

4- Now these scripts are part of the process. Run STARETL.bat or .sh. This final step of the ETL process will execute ANY scripts in this folder. So beware adding anything you do not want to get executed. Test your scripts in a non production environment to make sure there are no side effects of your custom scripts.              

This is a basic sample of how to add data to your STAR data warehouse that may not be part of the product. Keep copies of these scripts so when upgrading you do not lose them. The user scripts concept has continued into the more recent versions of the P6 Reporting Database\P6 Analytics but during upgrade and new installation these scripts will need to be re-added to your new installation.     Upcoming will be another blog about part 2 of this process, how to get this new data to show in P6 Analytics.

Provide new information on Primavera Analytics and Data Warehouse


« April 2013 »