How to find resources without any assignments?
By Jeffrey McDaniel on Jan 09, 2014
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.