Title: CA Clarity Tuesday Tip: Time Slicing 101 - Part 4
CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 6/07/2011
Today we will take a look at the prj_blb_slices table.
In our last tip, we looked at the structure of the prj_blb_slicerequests table. One of the fields in the requests table is the table_name field. This field tells us which table stores the slices for the request record. The prj_blb_slices table was historically the 1st created slice table, it also stores slice values for a number of slice requests.
Some fields worth noting are the following:
ID: This is the unique identifier of the record.
SLICE_REQUEST_ID: This field represents the id of the slice request within the prj_blb_slicerequests table.
PRJ_OBJECT_ID: The prj_object_id field represents the "parent object" reference. This is the id for the type of parent object being sliced.
i.e. if the request is for availability, then this id would point to the srm_resources.id field
if the request is for assignment actuals, then this id would point to the prassignment.prid field
SLICE_DATE: This field represents the date that the slice amount applies to.
SLICE: This is the amount of the slice.
As an example, the following....
ID: 5000002
SLICE_REQUEST_ID: 2
PRJ_OBJECT_ID: 5002312
SLICE_DATE: 12-Jun-2011
SLICE: 8
....is interpreted as:
A slice record with the id of 5000002 is associated with slice request 2 (assignment actuals), in which 8 hours of actuals were tracked on 12-Jun-2011. The 8 hours on 12-Jun-2011 are associated with prassignment.prid = 5002312.
Some important points that we can derive from this are:
1) When an parent object's instance is changed, it could result in many records being updated in the prj_blb_slices table.
e.g. If we have an assignment that starts on 30-DEC-2010 and ends on 12-Jun-2011, then you could potentially have 6+ months of slice records, just for that request. If time was tracked on every day, that would represent over 190 records, each with a date and value.
2) Multiple requests for the same type (i.e. assignment actuals) could result in duplicate data in the prj_blb_slices table. If we take our example above and then create a new slice for actuals that covers the same range, we could end up with 2 slice records for the same date.
ID: 5000002
SLICE_REQUEST_ID: 2
PRJ_OBJECT_ID: 5002312
SLICE_DATE: 12-Jun-2011
SLICE: 8
ID: 5029073
SLICE_REQUEST_ID: 5000232
PRJ_OBJECT_ID: 5002312
SLICE_DATE: 12-Jun-2011
SLICE: 8
In this case the slice records unique id is different, as well as it's request id. However, the same amount of actuals for the same date for the same assignment is represented.
3) Join the prj_blb_slices table with the parent object's table to make useful queries.
i.e. This example joins in the assignment table to show all assignment slices for a specific assignment. It joins in the assignment table to show the resource and task id's.
select prj_object_id, prtaskid, prresourceid, slice_date, slice
from prassignment assgn, prj_blb_slices slice
where assgn.prid = slice.prj_object_id
and prj_object_id = 5126266
Enjoy!
-shawn