Wednesday Jan 22, 2014

Report on ETL Status of a Multiple Datasource Environment

Do you have a Multiple Datasource Reporting Database environment?  In a scenario where you have multiple datasources, and multiple ETL processes running you may want to keep track of those ETL runs in an automated fashion. In an earlier blog we discussed how setup your own ETL monitoring system, in this Blog we will discuss how to use OBI to monitor your ETL runs.

Currently we have an ETL_PROCESSMASTER table in the STAR schema that tracks every ETL run - the start time, the finish time, and wheter it was successful. However there is not a column for datasource Id.  You can get this information by calling a function in the STAR schema - get_source_db_id.  

If you would like an OBI report to show this information here is a way to do it. First let's talk about how this can be accomplished in OBI.  This data for ETL_PROCESSMASTER is not exposed in any of the subject areas.  The reason is it is not for general user consumption.  The ETL_ tables in STAR are for internal trackings and usage during the ETL process.  While it can have useful information it's main intention is not for exposure in the subject areas of P6 Analytics where the focus is on your schedule and resource data, not the internal workings of the ETL process.  

But a report can be created outside of subject areas by using OBI's ability to query a data source directly.  In this type of Analysis you do not use any of the subject areas you use the OBI direct database query and formulate your own SQL. After the SQL has been generated this Analysis can be treated like any other and an administrator could use it to oversee the ETL process for all the organization's datasources. 

We need to add a new column to the ETL_PROCESSMASTER table. We will add a column called DSID. You can either run the SQL before running the ETL prcess:

ALTER TABLE etl_processmaster ADD (dsid   number);

Or another way is to add a script into the USER_SCRIPTS folder (located in \star\scripts directory).  All contents of the USER_SCRIPTS folder will be executed after the ETL process has completed.

Create a script called - addcolumn.sql and add the following contents:

  v_new_col1 number := 0;
  Select count(*) into v_new_col1 from user_tab_cols where column_name = 'DSID' and table_name = 'ETL_PROCESSMASTER';
  if (v_new_col1 = 0) then
      execute immediate 'ALTER TABLE etl_processmaster ADD (DSID number)';
  end if;

This script will look to see if the DSID column exists, if it does not it will be added.  If it does exist then this step will be skipped.  Now you have the new column added.  Next we need to populate the datasource id for the last\most recent ETL run.

In the USER_SCRIPTS folder add another new script called = datasource.sql

set serveroutput on size 100000
-- update datasourceid 
  v_src_id number;
  v_src_id := get_source_db_id;

update ETL_PROCESSMASTER set dsid = v_src_id where processstartdate =  (select max(processstartdate) from etl_processmaster); 

This script will go find the datasource id of this ETL run and update the new DSID accordingly for the most recent ETL run row.  Now you have all the data in place.  Next you just need to generate a simply query to extract this data, such as:

select processid, processstartdate, processenddate, processtype, DSID from etl_processmaster;

Thursday Jan 09, 2014

How to find resources without any assignments?

In P6 Analytics if you create an Analysis and use a value from a Dimension - Resource Dimension for example - you will see all resources regardless if they have any assignments. However when you join to another dimension or fact you may no longer see that resource in your Analysis if it doesn't have 1 fact row. In the OBI RPD joins are constructed based around the Facts. Dimensions are joined through the Fact tables. (See RPD Physical Layer diagram). 

For this reason if you are looking for resources without assignments you may need to use another method to view this data. The data is all there. One of the great things about the P6 Reporting Database is the data is there we just need to access it. This is true for many areas - slowly changing dimensions, history, etc.

In OBI you can use a direct SQL query to query directly against the STAR schema to get your desired result.  
(New> Create Direct Database Request) and choose:

Connection Pool: Oracle Primavera P6 Data Warehouse Connection Pool 

Add the following SQL: 

select r.resource_name, nvl(count(distinct ra.activity_object_id),0) num_assignments
from w_resource_d r left outer join w_resource_assignment_d ra
on r.resource_object_id = ra.resource_object_id
where r.resource_name is not null
group by r.resource_name
order by 1;

This will give you an output of all resources and the number of assignments that occur in the STAR schema.  You can add additional filtering or ordering to satisfy your requirements.

Wednesday Jan 08, 2014

Role Usage in P6 Analytics

In P6 Analytics there are some excellent reporting abilities for Roles I would like to focus on. The ability in P6 Analytics to slice and dice data by different areas allows you to combine information related to Roles and view it in an Analysis.For example, you are staffing multiple projects, you do not know the specific resource that will work on this task so a Role is assigned at the time. Later on as you staff those Role assignments you will need to see across your enterprise how your Role assignments look and which ones still require staffing.

Here is a screenshot of a very simple analysis which can show this.  

With P6 Analytics you can also add in other areas to help further slice your Analysis, such as Project.

If you are interested in seeing a static Resource Limit that goes along with the Resource assigned to the Role you can accomplish this by adding a new join into your RPD.
In the Physical Layer of the RPD you will add a join between the w_resource_role_d and w_resource_limit_f.  This will allow you to join the Primavera Resource Assignment and Primavera Resource Utilization subject areas and add Resource Limit into this current Analysis.  This is just a static Resource Limit, not related to Role or a Role limit. 

Here is a screenshot of the join in the RPD.

After making the change in the RPD the OBI services must be restarted.

There is more that can be accomplished with the Resource Assignment subject area around Role and Resource usage and staffing. By using this subject area you can see enterprise wide role and resource usage, or filter down by assignments. 


Provide new information on P6 Analytics and Reporting Database.


« January 2014 »