Resource Availability attribute location in database

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

As a report developer, looking to know where in the database the resource Availability is stored and if it can be included in the Data Warehouse.

Environment

Release: All Clarity releases
Component: CLARITY RESOURCE MANAGEMENT

Resolution

The resource Availability field is not stored in the database as a field that can be directly queried. It is stored as a BLOB field and sliced into slice tables.

The PRJ_RESOURCES table in the database contains a PRAVAILCURVE field. This is the blob field that is cracked into slices.

None of the resource tables will contain the Availability value that is directly entered into the field in the UI. This is because the value can be recalculated by the application if the resource's calendar shift changes.

However, the Time Slice tables can be used to read resource availability by period.

For example, the following query can be used to read the resource's Daily Availability (using slice ID = 1).

Substitute '?????' with the mixed case Resource Code ID

     SELECT 
     s.Slice, 
     s.slice_date, 
     r.full_name 
     FROM 
     prj_blb_slices s, 
     srm_resources r 
     WHERE 
     s.prj_object_id = r.id and 
     r.unique_name like '?????' and 
     slice_request_id = 1

NOTE: The result of the query above are dictated by the Time Slice definition 'From Date' and 'Number of Periods'.  

Additional Information

Please also see:

Resource calendars: working and non-working days