Lookup Tables

Overview

Lookup tables form an important part of your Domain Model. Lookup tables hold constants such as thresholds for treatment triggering, calibration factors etc. that are needed by your model.

You should program your Domain Model such that it makes use of Lookup parameters wherever possible, rather than hard-coding thresholds and calibration factors into your model code. By following this practice, you make it possible to significantly modify and refine how your model performs, when and how many treatments are triggered, and how fast deterioration takes place - all without having to touch the C# code of your underlying model.

Thus, lookup tables form a bridge between the low-level logic contained in your Domain Model source code, and the high level constants that determine how that low-level logic is expressed from one network/project to the next.

Since Lookup tables are contained in an Excel template, it is easy to change key aspects of your model by simply editing thresholds and parameters in Excel, saving the file and then re-running your model.

Guidelines on how to define Lookup tables are given in the section below. You can download an example Lookups template by clicking the button below.

Download Lookups Definition Template Example

Lookup Template

Your lookup tables need to follow a specified template format, and must be contained in a file named ‘lookups.xlsx’ located in the ‘inputs’ folder of your Cassandra project folder.

Within your Lookups template, you can split your lookup tables over many sheets, provided that sheets containing lookup tables are prefixed with long as each sheet that contains a lookup table is prefixed with ‘lkp_’.

On each sheet prefixed with ‘lkp_’, you should have a template as shown in the example below. The required columns are:

  • lookup_set_name - a logical grouping of parameters/constants into a set.

  • setting_key - a Key that identifies the value to be lookued up within the set. Key values within a Lookup Set must be unique, otherwise a run-time error will occur.

  • setting_value - Value associated with the Lookup Set and Key combination.

  • comment - An optional comment that describes what the constant does. It is strongly recommended that you use this field for keeping documentation up to date and descriptive for other team members.

Note that you can split your lookup tables over several sheets (all prefixed with ‘lkp_’) and also categorise lookup parameters into Lookup Sets. This provides you with a highly flexible structure for breaking the constants of even highly complex models into ‘boxes’ that make it easy to document, understand and debug.

lookup_set_name setting_key setting_value comment
general base_date 30/06/2025 Date to be used as the basis on which Surface Age and Pavement Age is calculated.
general proxval_last_period 3 Last modelling period (inclusive) in which the ProxVal model will distinguish between Satelite and Non-Satelite segments
general short_term_periods 3 Number of modelling periods considered short term for purposes of trigger adjustment
candidate_selection min_periods_to_next_treat 6 Minimum periods to next treatment (i.e. do not consider treatment if periods to a committed future treatment is less than this)
candidate_selection min_sdi_to_treat 2 Minimum SDI to trigger candidate - use to throttle (EITHER condition applied with minimum PDI)
candidate_selection min_pdi_to_treat 2 Minimum PDI to trigger candidate - use to throttle (EITHER condition applied with minimum SPDI)
candidate_selection min_sla_to_treat_ac 50 Minimum Surface Life Achieved to consider for AC - gatekeeper that can be used to throttle treatments
candidate_selection min_sla_to_treat_cs 50 Minimum Surface Life Achieved to consider for Chipseals - gatekeeper that can be used to throttle treatments
treatment_suitability_scores rehab_excess_rut_thresh 15.83 Rut threshold above which a penalty (for Holding Actions) or boost (for Rehabs) is applied (see below)
treatment_suitability_scores rehab_excess_rut_fact 4.71 Multiply excessive rut with this value to get the boost for Rehab TSS based on excessive rut (if any)
treatment_suitability_scores rehab_pdi_rank 90 PDI rank below which TSS score for Rehab becomes zero
treatment_suitability_scores holding_pdi_rank_pt1 70 PDI rank below which score for holding action becomes zero
treatment_suitability_scores holding_pdi_rank_pt2 95.38 PDI rank at which score for holding action is maximal (100)
treatment_suitability_scores holding_pdi_rank_pt3 69.52 TSS for holding action based on PDI when PDI rank is 100
treatment_suitability_scores holding_max_rut 16.37 Do not consider holding action if rut is above this value (unless it is not a rehab route in which case it is ignored)
treatment_suitability_scores preserve_sdi_rank 5 SDI Rank below which score for Preservation becomes zero (we want to apply preservation where there is some surface distress)
treatment_suitability_scores preserve_max_pdi_chipseal 12.2 Do not consider Chipseal Preservation treatment if PDI is above this value
treatment_suitability_scores preserve_max_pdi_ac 9.98 Do not consider AC Preservation treatment if PDI is above this value (if above, AC holding will be triggered) - Keep this LOW because Preserve AC includes no repairs!!
treatment_suitability_scores holding_max_pdi_ac 95.04 Do not consider AC Holding treatment if PDI is above this value
treatment_suitability_scores preserve_max_rut 10.64 Do not consider preservation if RUT is above this value
treatment_suitability_scores preserve_min_sla 76.900000000000006 Do not consider preservation if Surface Life Achieved % is below this value
mcda_treatment_triggering ac_hmaint_maximum_sla 90 Maximum Surface Life Achieved (%) at which to consider a AC Heavy Maintenance (for SLA over this value, consider only Rehab and Preserve AC with Repairs)
mcda_treatment_triggering ac_hmaint_min_periods_between 5 Minimum number of periods between AC Heavy Maintenance and any previous treatment (excluding Routine Maintenance)
maint_pred maint_pdi_threshold 15 Maintenance PDI threshold (force maintenance cost to zero if PDI is below this value)
maint_pred cal_maint_pred 0.5 Factor to adujst the predicted maintenance cost per km
surf_life_exp 1_1chip_l 2 Expected surface life (in years) for First Coat, 1Chip Seals, Low Volume roads
surf_life_exp 1_1chip_m 2 Expected surface life (in years) for First Coat, 1Chip Seals, Medium Volume roads
surf_life_exp 1_1chip_h 1 Expected surface life (in years) for First Coat, 1 Chip Seals, High Volume roads
surf_life_exp 2_1chip_l 21 Expected surface life (in years) for Second Coat, 1Chip Seals, Low Volume roads
surf_life_exp 2_1chip_m 18 Expected surface life (in years) for Second Coat, 1Chip Seals, Medium Volume roads
surf_life_exp 2_1chip_h 14 Expected surface life (in years) for Second Coat, 1Chip Seals, High Volume roads
surf_life_exp r_1chip_l 19 Expected surface life (in years) for Resurfacing, 1Chip Seals, Low Volume roads
surf_life_exp r_1chip_m 16 Expected surface life (in years) for Resurfacing, 1Chip Seals, Medium Volume roads
surf_life_exp r_1chip_h 12 Expected surface life (in years) for Resurfacing, 1Chip Seals, High Volume roads
surf_life_exp h_1chip_l 19 Expected surface life (in years) for Holding Seal, 1Chip Seals, Low Volume roads
surf_life_exp h_1chip_m 16 Expected surface life (in years) for Holding Seal, 1Chip Seals, Medium Volume roads
surf_life_exp h_1chip_h 12 Expected surface life (in years) for Holding Seal, 1 Chip Seals, High Volume roads
surf_life_exp 1_2chip_l 2 Expected surface life (in years) for First Coat, 2Chip Seals, Low Volume roads
surf_life_exp 1_2chip_m 2 Expected surface life (in years) for First Coat, 2Chip Seals, Medium Volume roads
surf_life_exp 1_2chip_h 1 Expected surface life (in years) for First Coat, 2Chip Seals, High Volume roads
surf_life_exp 2_2chip_l 19 Expected surface life (in years) for Second Coat, 2Chip Seals, Low Volume roads
surf_life_exp 2_2chip_m 17 Expected surface life (in years) for Second Coat, 2Chip Seals, Medium Volume roads
surf_life_exp 2_2chip_h 12 Expected surface life (in years) for Second Coat, 2Chip Seals, High Volume roads
surf_life_exp r_2chip_l 17 Expected surface life (in years) for Resurfacing, 2Chip Seals, Low Volume roads
surf_life_exp r_2chip_m 15 Expected surface life (in years) for Resurfacing, 2Chip Seals, Medium Volume roads
surf_life_exp r_2chip_h 10 Expected surface life (in years) for Resurfacing, 2Chip Seals, High Volume roads
surf_life_exp h_2chip_l 17 Expected surface life (in years) for Holding Seal, 2Chip Seals, Low Volume roads
surf_life_exp h_2chip_m 15 Expected surface life (in years) for Holding Seal, 2Chip Seals, Medium Volume roads
surf_life_exp h_2chip_h 10 Expected surface life (in years) for Holding Seal, 2Chip Seals, High Volume roads
surf_life_exp 2_ac_l 20 Expected surface life (in years) for Second Coat, Asphalt Surfacing, Low Volume roads
surf_life_exp 2_ac_m 16 Expected surface life (in years) for Second Coat, Asphalt Surfacing, Medium Volume roads
surf_life_exp 2_ac_h 12 Expected surface life (in years) for Second Coat, Asphalt Surfacing, High Volume roads
surf_life_exp r_ac_l 20 Expected surface life (in years) for Resurfacing, Asphalt Surfacing, Low Volume roads
surf_life_exp r_ac_m 16 Expected surface life (in years) for Resurfacing, Asphalt Surfacing, Medium Volume roads
surf_life_exp r_ac_h 12 Expected surface life (in years) for Resurfacing, Asphalt Surfacing, High Volume roads
surf_life_exp h_ac_l 20 Expected surface life (in years) for Holding Seal, Asphalt Surfacing, Low Volume roads
surf_life_exp h_ac_m 16 Expected surface life (in years) for Holding Seal, Asphalt Surfacing, Medium Volume roads
surf_life_exp h_ac_h 12 Expected surface life (in years) for Holding Seal, Asphalt Surfacing, High Volume roads
Important

The table shown above must have left-upper corner in cell A1 of the sheet. You can use the cells below or to the right of the table but it is recommended that you rather make use of a seperate sheet to keep any notes etc.

Do not alter column names and always presume template column names are case-sensitive.

At run-time, Cassandra will fold all of the sub-tables on different ‘lkp_’ sheets into a single lookup table. This table is then held in memory and made available for the Domain Model to access during a model run. The C# code snippet below shows an example of how your Domain Model can extract and utilise a lookup parameter for a specific Lookup Set and Key combination:

// This block will be rendered but not run.

private void Example(ModelBase model)
{

    // Extract all lookup sets from the Framework Model Object
    Dictionary<string, Dictionary<string, object>> lookupSets = model.Lookups;
    
    // Extract Candidate Selection related constants
    _min_periods_to_next_treat = Convert.ToInt32(lookupSets["candidate_selection"]["min_periods_to_next_treat"]);
    _min_sdi_to_treat = Convert.ToDouble(lookupSets["candidate_selection"]["min_sdi_to_treat"]);
    _min_pdi_to_treat = Convert.ToDouble(lookupSets["candidate_selection"]["min_pdi_to_treat"]);
    _minSlaToTreatAc = Convert.ToDouble(lookupSets["candidate_selection"]["min_sla_to_treat_ac"]);
    _minSlaToTreatCs = Convert.ToDouble(lookupSets["candidate_selection"]["min_sla_to_treat_cs"]);
    
    // Extract boosting factor for Potholes
    _potholeBoostFactor = Convert.ToDouble(lookupSets["distress"]["poth_booster"]);
    
    // Extract Routine Maintenance related constants
    _maintenanceCostCalibrationFactor = Convert.ToDouble(lookupSets["maint_pred"]["cal_maint_pred"]);
    _maintenanceCostPDIThreshold = Convert.ToDouble(lookupSets["maint_pred"]["maint_pdi_threshold"]);
    
    // etc.
    // etc.

}
Note

Note that you can split your lookup tables over several sheets (all prefixed with ‘lkp_’) and also categorise lookup parameters into Lookup Sets. This provides you with a highly flexible structure for breaking the constants of even highly complex models into ‘boxes’ that make it easy to document, understand and debug.