'Time Slicing Sync' job - Query to monitor progress and updated rows

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

The Time Slicing Group By job (Renamed Time Slicing Sync job starting in 15.9.2) is slow or stuck and you are looking for steps to monitor its progress and make sure it updates the rows. 

Environment

Release : 15.9.1+

Component : TIME SLICING

Resolution

Use the query below to identify the Updated vs Not Updated Slices with SQL Curve. You can just take the piece you need in case you would like to monitor a specific slice:

select 'ETC', count(*) , 'Not Updated' as Slices
from prassignment
where odf_nk_prestcurve is null
union
select 'ETC', count(*) , 'Updated' as Slices
from prassignment
where odf_nk_prestcurve is not null
union
select 'Actuals', count(*) , 'Not Updated' as Slices
from prassignment
where odf_nk_practcurve is null
union
select 'Actuals', count(*) , 'Updated' as Slices
from prassignment
where odf_nk_practcurve is not null
union
select 'ETC Cost', count(*) , 'Not Updated' as Slices
from prassignment
where odf_nk_etccost_curve is null and odf_ss_etccost_curve is not null
union
select 'ETC Cost', count(*) , 'Updated' as Slices
from prassignment
where odf_nk_etccost_curve is not null
union
select 'Actual Cost', count(*) , 'Not Updated' as Slices
from prassignment
where odf_nk_actcost_curve is null and odf_ss_actcost_curve is not null
union
select 'Actual Cost', count(*) , 'Updated' as Slices
from prassignment
where odf_nk_actcost_curve is not null
union
select 'Allocation Cost', count(*) , 'Not Updated' as Slices
from prteam
where odf_nk_alloccost_curve is null and odf_ss_alloccost_curve is not null
union
select 'Allocation Cost', count(*) , 'Updated' as Slices
from prteam
where odf_nk_alloccost_curve is not null
union
select 'Hard Allocation Cost', count(*) , 'Not Updated' as Slices
from prteam
where odf_nk_hardallccost_curve is null and odf_ss_hardallccost_curve is not null
union
select 'Hard Allocation Cost', count(*) , 'Updated' as Slices
from prteam
where odf_nk_hardallccost_curve is not null
union
select 'Allocation', count(*) , 'Not Updated' as Slices
from prteam
where odf_nk_odf_pralloccurve is null and odf_pralloccurve is not null
union
select 'Allocation', count(*) , 'Updated' as Slices
from prteam
where odf_nk_odf_pralloccurve is not null
union
select 'Hard Allocation', count(*) , 'Not Updated' as Slices
from prteam
where odf_nk_odf_hard_curve is null and odf_hard_curve is not null
union
select 'Hard Allocation', count(*) , 'Updated' as Slices
from prteam
where odf_nk_odf_hard_curve is not null
union
select 'Billing Cost', count(*) , 'Not Updated' as Slices
from fin_cost_plan_details
where odf_nk_billing_cost is null and billing_cost is not null
union
select 'Billing Cost', count(*) , 'Updated' as Slices
from fin_cost_plan_details
where odf_nk_billing_cost is not null
union
select 'Billing Revenue', count(*) , 'Not Updated' as Slices
from fin_cost_plan_details
where odf_nk_billing_revenue is null and billing_revenue is not null
union
select 'Billing Revenue', count(*) , 'Updated' as Slices
from fin_cost_plan_details
where odf_nk_billing_revenue is not null
union
select 'Cost', count(*) , 'Not Updated' as Slices
from fin_cost_plan_details
where odf_nk_cost is null and cost is not null
union
select 'Cost', count(*) , 'Updated' as Slices
from fin_cost_plan_details
where odf_nk_cost is not null
union
select 'Revenue', count(*) , 'Not Updated' as Slices
from fin_cost_plan_details
where odf_nk_revenue is null and revenue is not null
union
select 'Revenue', count(*) , 'Updated' as Slices
from fin_cost_plan_details
where odf_nk_revenue is not null
union
select 'Units', count(*) , 'Not Updated' as Slices
from fin_cost_plan_details
where odf_nk_units is null and units is not null
union
select 'Units', count(*) , 'Updated' as Slices
from fin_cost_plan_details
where odf_nk_units is not null
union
select 'Cost Variance', count(*) , 'Not Updated' as Slices
from fin_cost_plan_details
where odf_nk_cost_variance is null and cost_variance is not null
union
select 'Cost Variance', count(*) , 'Updated' as Slices
from fin_cost_plan_details
where odf_nk_cost_variance is not null
union
select 'Revenue Actual', count(*) , 'Not Updated' as Slices
from fin_cost_plan_details
where odf_nk_revenue_actual is null and revenue_actual is not null
union
select 'Revenue Actual', count(*) , 'Updated' as Slices
from fin_cost_plan_details
where odf_nk_revenue_actual is not null
union
select 'Units Variance', count(*) , 'Not Updated' as Slices
from fin_cost_plan_details
where odf_nk_units_variance is null and units_variance is not null
union
select 'Units Variance', count(*) , 'Updated' as Slices
from fin_cost_plan_details
where odf_nk_units_variance is not null
union
select 'Cost Actual', count(*) , 'Not Updated' as Slices
from fin_cost_plan_details
where odf_nk_cost_actual is null and cost_actual is not null
union
select 'Cost Actual', count(*) , 'Updated' as Slices
from fin_cost_plan_details
where odf_nk_cost_actual is not null
union
select 'Units Actual', count(*) , 'Not Updated' as Slices
from fin_cost_plan_details
where odf_nk_units_actual is null and units_actual is not null
union
select 'Units Actual', count(*) , 'Updated' as Slices
from fin_cost_plan_details
where odf_nk_units_actual is not null
union
select 'Revenue Variance', count(*) , 'Not Updated' as Slices
from fin_cost_plan_details
where odf_nk_revenue_variance is null and revenue_variance is not null
union
select 'Revenue Variance', count(*) , 'Updated' as Slices
from fin_cost_plan_details
where odf_nk_revenue_variance is not null

Additional Information

See also 'Time Slicing Sync' job - determine if progressing via UI