In which time slice are the allocations for roles kept

[Legacy User]

I have a need to report about the demand by role on Ideas and projects. In the case of the Ideas its Team tab is for the most part populated with roles within our organization. While running a SQL query against the PRJ_BLB_Slices for Daily allocations, I noticed that it holds the daily allocations just for named resources and not for roles..

 

1. So is there there is a specific time slice for roles or is it in the same slice as that of named resources. The PRID column of the PRTeam table is what I used to query the PRJ_BLB_Slices and got only the rows for named resources on the team.
2. If the allocation of Roles are kept in a different slice then what is the name of the Slice Request and what ID does one use to query this slice

[Senthil Kumar Lakshmanan]

Roles and Resource allocation information stores on same time slice. You can check either slice table or insta Slice table

 

Slice Table:

PRJ_BLB_SLICES

 

Insta Slices:

 

PRJ_BLB_SLICES_M_ALC OR PRJ_BLB_SLICES_W_ALC

 

Thanks

Senthil

[Senthil Kumar Lakshmanan]

Query as below

 

SELECT I.CODE, I.NAME, R.FULL_NAME, SLICE_DATE, SLICE FROM PRJ_BLB_SLICES SL JOIN PRTEAM TE ON TE.PRID = SL.PRJ_OBJECT_ID
JOIN PRJ_BLB_SLICEREQUESTS SLR ON SL.SLICE_REQUEST_ID = SLR.ID AND SLR.REQUEST_NAME = 'MONTHLYRESOURCEALLOCCURVE'
JOIN INV_INVESTMENTS I ON I.ID = TE.PRPROJECTID AND I.CODE = 'PR1037'
JOIN SRM_RESOURCES R ON R.ID = TE.PRRESOURCEID AND R.FULL_NAME = 'Developer'

[Legacy User]

Hello Senthil,

 

My sincere apologies for the delay.

 

I was using a similar query (shown here) that inner joins the PRTeam table with the PRJ_BLB_Slices for Daily Resource Allocations (Slice Request ID of 10). The output did not include the daily allocation details for Roles.

 

My SQL Query
Select teamdb.prresourceid, teamdb.prroleid, sum(slicedb.slice), min(slicedb.slice_date), max(slicedb.slice_date) From     prj_blb_slices sliceDB Inner join  prteam teamdb on sliced.prj_object_id - teamdb.prid and                  teamdb.prprojectid = <five million number for the prj on your system> Group by teamdb.prresourceid, teamdb.prroleid

 

 

 

I did not attempt running against the monthly slices but am assuming that the outcome would be similar.

 

The steps I had followed were

• Open a Project that already has the team tab loaded with named resources
• Go to the team tab
• Add 1 role that spans the project start and finish dates
• Ensure that the data mart extraction and timeslice jobs are run
• Run the SQL query shown below

The output did not include the allocation details for the role that was added.

 

I even changed the query such that prj_object_id was compared against the five million number for the role added to the team and there were no results.

 

I then had my colleague run the same steps and query on a higher version of CA PPM and his outcomes were similar to mine, (i.e.) no data in the Prj_Blb_Slices table for Daily resource allocations.

 

This is very puzzling and I was of the opinion that all allocation details irrespective of roles or named resources are stored under the same slice request id.

 

Getting to this data is crucial because one can create allocation segments for the roles being added to the team and attempts to extract the daily demand for the roles has hit this road block.

 

This is why my earlier question about the slice request ID pertinent to allocation data for Roles.

 

 

 

Kind regards.

 

 

Ashwin