AnsweredAssumed Answered

In which time slice are the allocations for roles kept

Question asked by Ashwin.Kumar_Xerox.Com on Aug 24, 2017
Latest reply on Aug 28, 2017 by Ashwin.Kumar_Xerox.Com

Like • Show 0 Likes0
Comment • 3

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

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

No one else has this question

Outcomes

lakse01

Aug 24, 2017 5:55 PM

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

1 person found this helpful

Actions

lakse01

@ lakse01 on Aug 24, 2017 6:02 PM

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'

2 people found this helpful

Actions

Ashwin.Kumar_Xerox.Com Aug 28, 2017 4:26 PM

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

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

Actions

In which time slice are the allocations for roles kept

Outcomes

Related Content

Recommended Content