Times Slicing job: Check the status / estimated completion time

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Your Time Slicing job has been processing for some time. How can you monitor or check the status of the records being processed? How can you estimate the amount of time it will take to complete?

Environment

Release: All Supported
Component: Clarity Time Slicing

Resolution

There is no one query that will give you the complete status of the Time Slicing job. You will need to do three things to get a complete picture of the status.

1. Look at the scheduled Time Slicing job to see the status of the job itself.
2. Look at the Time Slices Management page and determine if any slice requests are expired or have older last run dates. This will give you an idea of what the time slice is supposed to be doing.
   • If the expiration date for a slice request is less than today, the slice request needs to be rolled over.
   • If the slice request has an older Last Run date then it needs to catch up.
3. Run a query on the object records to check the slice_status column.
   
   Values for slice status are:
   • 1 = Needs to be Processed.
   • 2 = Currently being Processed.
   • 3 = Needs to be rolled over.
   • NULL = Record has been processed.

In the query below, the count of records that have a slice status of Null is omitted, because you cannot reliably assume they are processed. It processes the Slice objects one at a time. For example, if it is on Assignments and if availability, allocations, or time entries need to be rolled over, many of these records will have a status of null because time slice just has not gotten to them yet. The same is true for a modified or new request slice request.

SELECT 'Assignment' Slice_Object, Count(*), SLICE_STATUS
FROM prassignment
WHERE SLICE_STATUS in (1,2,3)
GROUP BY SLICE_STATUS
UNION SELECT 'Availability' Slice_Object,Count(*), SLICE_STATUS
FROM prj_resources
WHERE SLICE_STATUS in (1,2,3)
GROUP BY SLICE_STATUS
UNION SELECT 'Allocation' Slice_Object, Count(*), SLICE_STATUS
FROM prteam
WHERE SLICE_STATUS in (1,2,3)
GROUP BY SLICE_STATUS
UNION SELECT 'Timeentries' Slice_Object, Count(*), SLICE_STATUS
FROM prtimeentry
WHERE SLICE_STATUS in (1,2,3)
GROUP BY SLICE_STATUS

It will go table by table in the order above and process 1000 rows at a time. You will see the status go from 1 (needs to be sliced) or status 3 (needs to rollover) to status 2 (processing) then to NULL (completed).

By running the query in timed intervals, you can estimate how long the process will take to complete. Note: Objects will process at different speeds based on the slice request configuration, amount of records, and size of the slice objects.