SQL Curves list and slice recalculation

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

We have a specific SQL Curve slice we need to recalculate as we are seeing mismatches in Per Period Metrics in Clarity MUX

Environment

Release : Any Supported Release

Resolution

SQL Curve Recalculation:

1. Identify the slice affected with mismatches from UI and find the table and column in the list (see list below)
2. If just a few mismatches, just update those single records as per Reset SQL Curves for a few records.
   If more records are affected, proceed with next steps to recalculate the entire SQL Curve. Note - this has to only be done with Broadcom Support as it involves removing data
3. Update the table, and SQL curve affected and identified, example (marked in color what to replace):
   update prteam set odf_nk_hardalloccost_curve =null where odf_nk_hardalloccost_curve is not null
   commit
4. Run Time Slicing Sync job to completion.

Additional considerations:

• On some environments the Time Slicing Sync may take a while after you reset for many records, test the timings on a lower environment first.
• It is recommended to run the Time Slicing Sync job over the weekend after a change like this in Production environments
• The query may run for a long time before completing on large dataset.
• Running this update query will temporarily remove the SQL Curves data in Modern UX: i.e. the corresponding to the SQL Curve screens in Staffing, Assignments, Financial, Roadmaps data in the TSV.
• The Time Slicing Sync job will recreate the data back.
• Classic UI is not affected.
• To be run with caution as it removes slice data and only when advised by Broadcom Support 

SQL Curves tables and columns list:

PRASSIGNMENT:

• odf_nk_prestcurve
• odf_nk_practcurve
• odf_nk_etccost_curve
• odf_nk_actcost_curve

PRTEAM:

• odf_nk_alloccost_curve
• odf_nk_actuals_curve
• odf_nk_estimates_curve
• odf_nk_hardallccost_curve
• odf_nk_odf_pralloccurve
• odf_nk_odf_hard_curve

FIN_COST_PLAN_DETAILS:

• odf_nk_billing_cost
• odf_nk_billing_revenue
• odf_nk_cost
• odf_nk_revenue
• odf_nk_units
• odf_nk_cost_variance
• odf_nk_revenue_actual
• odf_nk_units_variance
• odf_nk_cost_actual
• odf_nk_units_actual
• odf_nk_revenue_variance
• odf_nk_forecast    
• odf_nk_forecast_variance  

FIN_FINANCIALS

• odf_nk_planned_cost
• odf_nk_planned_cost_capital
• odf_nk_planned_cost_operating
• odf_nk_actual_cst
• odf_nk_actual_cst_capital
• odf_nk_actual_cst_operating
• odf_nk_forecast_cst
• odf_nk_forecast_cst_capital
• odf_nk_forecast_cst_operating
• odf_nk_budget_cost
• odf_nk_budget_cost_capital
• odf_nk_budget_cost_operating
• odf_nk_planned_benefit
• odf_nk_planned_actual_benefit
• odf_nk_budget_benefit
• odf_nk_budget_actual_benefit

PRJ_RESOURCES

• odf_nk_odf_pravailcurve
• odf_nk_total_alloc
• odf_nk_total_hard_alloc
• odf_nk_total_etc
• odf_nk_total_actuals
• odf_nk_total_usage

INV_INVESTMENTS   

• odf_nk_labor_alloc_curve
• odf_nk_labor_hard_alloc_curve
• odf_nk_labor_etc_curve
• odf_nk_labor_act_curve
• odf_nk_labor_eac_curve