Automatic Case Prioritisation

This article applies to P2 Explorer 4.6.1 and later. These steps should be performed by administration users who have direct access to the installation directory.

This page describes how to create and maintain the AutoCaseCalculations spreadsheet, which enables cases to be automatically prioritised in Case Management.

When Explorer is installed, a template spreadsheet is added to the \P2 Explorer\Case Management\Template directory on the Explorer server. The Server service uses the rules defined here to update case priority. You may need to update the rules, or add rules for another category.

To enable auto-prioritisation, make a copy of this spreadsheet, customise it for your categories, and save it to the Case Management folder, so that the Server service can access it:

  1. Copy the sample spreadsheet from the spreadsheet in the CaseManagement\Template folder under your Explorer directory. E.g.
    C:\Program Files\P2 Energy Solutions\P2 Explorer\Case Management\Template\ AutoCaseCalculations.xlsx
     
  2. Update the spreadsheet (for detailed instructions, read the rest of this article).
    • Open the spreadsheet.
    • Update priority numbers and/or colours for a particular cell in the grid.
    • To add a new category, create a copy of a worksheet tab and give it exactly the same name as the category.
    • Save the spreadsheet.
  3. Copy the updated spreadsheet into the Case Management folder of that same directory, e.g. 
    C:\Program Files\P2 Energy Solutions\P2 Explorer\Case Management\AutoCaseCalculations.xlsx

The following sections describe how the Server service reads the spreadsheet.

Category Worksheets

The spreadsheet uses a separate worksheet for each category.

  • If a category does not have a worksheet, then no automatic priority updates are made.
  • If there is a category worksheet that does not match a category, it is not used.

For example, if your categories are Financial, Operational, Environmental, Occupational Health and Safety, and Maintenance, you can have a tab for each of these in your spreadsheet. In the following example, there are five categories, each with its own worksheet:

  • In the spreadsheet, optionally create a separate worksheet for each known category.

Components of the Worksheet

Here is an example of a worksheet for the Operational category:

All worksheets should follow the same structure, as follows:

  • Header row (Row 1)
  • Impacts (Row 2)
  • Metric and Urgency (Row 3)
  • Cross-reference (Rows 4 onwards)
  • Priority cells

Header Row

Give each worksheet a header row containing Metric (in Column C) for determining Impact. Each impact is an expression calculation in columns D onwards.

SNAGHTML8165cd23

(1) Type an expression in each of the Metric rows (for Urgency).

(2) Type an expression in each of the Impact columns (for Impact).

Read the Metrics section below, to understand how the Server service evaluates data in the update.

Row Two

This row contains the names of the impacts, ranging from Insignificant through to Severe. You may choose as few as you like of these.

The names below are only examples. You can have as many or as few of these as you choose.

SNAGHTML816ae8c1

Row Three

The third row contains headings for Metric and Urgency, in columns A and B.

Rows Four onwards

The next rows contain a metric expression corresponding to an urgency (ranging from Critical down to None), as well as a priority number. Each number can have a colour which is applied to the affected case when its priority is updated.

The Urgencies can be named differently from the ones shown below. You can have as many or as few of these as you choose.

SNAGHTML816c7916

Priority Cells

Each grid cell in the intersection between Impact and Urgency is given a number, in black or in colour. This is used to update the case’s priority. Cell D5 in the example below has a High Urgency, but an Insignificant Impact, and has a priority number of 6, colour black.

Metrics

When the service evaluates each case to update its priority it goes through the different metrics, evaluating at each point at the time of the update.

Where data is specified as part of the metric equation, P2 Server does a single point fetch, to get the data’s value at the time specified in PriorityCalculatorTime.

Metric Syntax

Each metric should evaluate to a True or False outcome. Using tokens that relate to each case, you can specify simple or complex expressions using logical operators (greater than, less than, equal to, etc.), comparison operators, Mathematical operators (addition, subtraction, etc.) Logical operators (And/Or), and tokens (for Case Comment Count, Case Entity, and Case Duration).

The syntax used here is based on Server's syntax (see Calculations for a complete reference).

Mathematical Operators

+ Addition
- Subtraction
* Multiplication
/ Division
% Modulus
** Exponent

Logical Operators

&& And
|| Or

Comparison Operators

== Equal
!= Not Equal
< Less Than
<= Less Than or Equal
> Greater Than
>= Greater Than or Equal

Using Tokens

As well as using attributes of named entities (as used in the example above), you can use the following Tokens in the metric expressions:

Duration

The duration is the total time that the case has been open, measured in hours, minutes and seconds (HH:MM:SS).

Syntax Example
{{Duration}}

{{duration}} > {du'06:00:00'}

Is duration of the case greater than 6 hours?

Entity

The entity that the case was raised against. This could be a calculation, a tag, or an attribute depending on what process input was used for the Sentinel test.

Syntax Examples
{{{Entity}}}
  • {{{Entity}}[FLOWING]:Gas Flowrate} < 5
    Is the gas flowrate for the case’s entity (for example , ‘Sapphire’), in the FLOWING template less than 5?
     
  • {{{Entity}}} > 90
    Using a tag, is the tag entity (example 15HH.PV) value greater than 90?

Count

For cases that belong to a category using Case Aggregates, Count = the number of comments against the case.

Case Aggregation is set in Sentinel, and only applies to Sentinel-raised cases.

For cases that belong to a category using not Case Aggregates , Count = 0.

Syntax Example
{{Count}}

{{Count}}  > 8

Where Case Aggregates is used for the category, does the latest open case for the category have more than 8 comments?

Examples

For more complex metrics, you can use calculations as part of a metric expression. 

Example 1: Is the gas flowrate for the case’s entity (for example , ‘Sapphire’), in the FLOWING template greater than or equal to 5 and less than 7?

{{{Entity}}[FLOWING]:Gas Flowrate} >= 5 && {{{Entity}}[FLOWING]:Gas Flowrate} < 7

 

Example 2: Is the gas flowrate for the case’s entity (for example , ‘Sapphire’), in the FLOWING template greater than or equal to 5 and is the cases duration greater than 6 hours?

{{{Entity}}[FLOWING]:Gas Flowrate} >= 5 && {{duration}} > {du'06:00:00'}

 

How the Service Evaluates the Rules

The service reads the spreadsheet and updates a rules table in the Server database. The service then uses the database table when it updates priorities and colours.

Here is an explanation of how the service updates each case, following the logic of the spreadsheet (although in reality using database tables).

To make it easier to visualise and understand the logic, the steps refer to the spreadsheet, rather than the database tables. This will also make it easier for you to update the spreadsheet, to match the same working logic.

The following example uses the worksheet for the Operational Category:

Step 1. The service evaluates each of the Impact cell’s rules (left to right) until it evaluates to FALSE. Below we are evaluating the Impact Row from Left to Right: first, second, third and fourth.

    Metric (1st Metric) (2nd Metric) (3rd Metric) (4th Metric)
    Impact Insignificant Minor Major Severe
Metric Urgency   First Second Third Fourth
(5th Metric) Critical          
(3rd Metric) (4th Metric)          
(3rd Metric) Medium          
(2nd Metric) Low          
(1st Metric) None          

Step 2. As soon as there is a FALSE outcome, the service moves one column to the left and evaluates that column from the bottom cell upwards (each of the Urgency cell’s metrics). Below we are evaluating the Major Urgency Column from Lowest to Highest: fifth, sixth, seventh, eighth, ninth.

    Metric (1st Metric) (2nd Metric) (3rd Metric) (4th Metric)
    Impact Insignificant Minor Major Severe
Metric Urgency          
(5th Metric) Critical       Ninth  
(3rd Metric) High       Eighth  
(3rd Metric) Medium       Seventh  
(2nd Metric) Low       Sixth  
(1st Metric) None       Fifth  

Step 3. As soon as there is a FALSE outcome, the service stops, and the row below is used (the last TRUE).

Step 4. The grid cell that is used is the intersection between the column and row that have been identified. This grid cell has a priority, in colour or in black, which are applied to the case that is currently being evaluated.

In the above scenario, the current case gets priority 3 in red, as this is what High Urgency, Major Impact uses.

    Metric (1st Metric) (2nd Metric) (3rd Metric) (4th Metric)
    Impact Insignificant Minor Major Severe
Metric Urgency          
(5th Metric) Critical   4 3 2 1
(3rd Metric) High   5 4 3 2
(3rd Metric) Medium   6 5 4 3
(2nd Metric) Low   7 6 5 4
(1st Metric) None   8 7 6 5

Example

This is the example we’ll use in the different steps. Urgency measures the duration from greater than 24 minutes to greater than 99 minutes. Impact measures the entity’s current choke position. For the case that is currently being evaluated, the entity is called ‘Saphire’.

The service measures the current choke position as well as the case duration at 01:00 AM (the time that the service is scheduled to run) for the date that the service is running. For this example, the value is 110.

The following screenshot shows a Case in P2 Explorer (unrelated to the example above).

Step 5. In the Example, the service measures the value (110) against the different metrics for impact (Actual values replace tokens).

    Metric

{{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>90

110>90: TRUE

{{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>100

110>100: TRUE

{{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>110

110>110: FALSE

{{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>120
    Impact Insignificant Minor Major Severe
Metric Urgency          
{{duration}} > {du'99:00:00'} Critical   4 3 2 1
{{duration}} > {du'96:00:00'} High   5 4 3 2
{{duration}} > {du'72:00:00'} Medium   6 5 4 3
{{duration}} > {du'48:00:00'} Low   7 6 5 4
{{duration}} > {du'24:00:00'} None   8 7 6 5

Step 6. The service stops evaluating when the expression evaluates to FALSE, and identifies the previous column as the one to evaluate next (as highlighted, below).

    Metric

{{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>90

110>90: TRUE

{{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>100

110>100: TRUE

{{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>110

110>110: FALSE

{{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>120
    Impact Insignificant Minor Major Severe
Metric Urgency          
{{duration}} > {du'99:00:00'} Critical   4 3 2 1
{{duration}} > {du'96:00:00'} High   5 4 3 2
{{duration}} > {du'72:00:00'} Medium   6 5 4 3
{{duration}} > {du'48:00:00'} Low   7 6 5 4
{{duration}} > {du'24:00:00'} None   8 7 6 5

Step 7. In the example, the service evaluates the Urgency from the bottom row. The urgency columns in this example all evaluate the duration of the case. In the example, the case duration at 01:00 AM for the date is 55 minutes. (See the first column, where actual values replace tokens).

    Metric {{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>90 {{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>100 {{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>110 {{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>120
    Impact Insignificant Minor Major Severe
Metric Urgency          
{{duration}} > {du'99:00:00'} Critical   4 3 2 1
{{duration}} > {du'96:00:00'} High   5 4 3 2
{{duration}} > {du'72:00:00'}
55 hours > 72 hours: FALSE
Medium   6 5 4 3
{{duration}} > {du'48:00:00'}
55 hours > 48 hours: TRUE
Low   7 6 5 4
{{duration}} > {du'24:00:00'}
55 hours > 24 hours: TRUE
None   8 7 6 5

Step 8. The service stops evaluating when the expression evaluates to FALSE, and identifies the previous row as the one to use (as highlighted, below).

Step 9. The priority number and colour (6, green) of the intersection of the highlighted row and column is applied to the case.

    Metric {{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>90 {{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>100 {{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>110 {{{Entity}}[Oil Producing Well]:Choke!CurrentPosition}>120
    Impact Insignificant Minor Major Severe
Metric Urgency          
{{duration}} > {du'99:00:00'} Critical   4 3 2 1
{{duration}} > {du'96:00:00'} High   5 4 3 2
{{duration}} > {du'72:00:00'}
55: hours > 72 hours: FALSE
Medium<   6 5 4 3
{{duration}} > {du'48:00:00'}
55: hours > 48 hours: TRUE
Low   7 6 5 4
{{duration}} > {du'24:00:00'}
55: hours > 24 hours: TRUE
None   8 7 6 5

The above example showed a progression of very similar metrics. You can use a variety of metrics, if you prefer. For example, measure the case entity’s pressure, then its temperature, and so on.

Grading the Metrics

Because of the order that the different metrics are evaluated, it makes sense to use metrics that would most likely follow that logical path as well. For example, if you are evaluating the tubing head temperature of well ABC, your metrics could progress from Impact Insignificant to Major as follows. Because the evaluation is from left to right, it makes sense for a TRUE condition to be preceded by a TRUE condition, such as here where the ABC:THT = 380. Here the Impact is Major.

    Metric

{{{Entity}}[Oil Producing Well]:THT}>250

(evaluates 380 > 250: TRUE)

{{{Entity}}[Oil Producing Well]:THT}>300

(evaluates 380 > 300: TRUE)

{{{Entity}}[Oil Producing Well]:THT}>400

(evaluates 380 > 400: FALSE)

    Impact Insignificant Minor Major
Metric Urgency        
{{duration}} > {du'10:00:00'} High   3 2 1
{{duration}} > {du'5:00:00'} Low   4 3 2

If any of the conditions cannot be parsed, the lowest urgency and the lowest impact are used (in this example, Urgency Low, Impact Insignificant), which assigns a green 4 to the case’s priority.

Assigning Priorities

You can assign any priority (numbers 1 to 100) to any of the grid cells. If you regard priority 1 to be the highest priority, then it makes sense to give the Sever/Critical cell a priority 1, with higher numbers as you go towards lower impacts and urgencies. Note that Sentinel gives a default priority of 1 to a case when it is raised.

Assigning Colours

When assigning colours to your spreadsheet priority grids, ensure that these are from the Standard Colors or Automatic (black), and not the Theme Colors.

You can have any combination of priority number and colour. For example, you may have the number 3 in four separate grid cells in the spreadsheet, with each occurrence in a different colour.

Urgency and Impact

You can have one or many Urgency rows, and one or many Impact columns. You should have at least one row and/or column, or there will be no logic to process.

Ensure that the Urgency names are unique, and likewise with the Impact names, and do not have any missing rows or columns.

Adding Categories

If you need to add categories for cases, you will need to insert rows into the NamedListItem table in the database.

1. First find the ID for the Category, in the NamedList table. In the following example, the ID is 1.

2. Now open the NamedListItem, and add rows for category.

a. For the Value column, add the category name.

b. For the NamedListId column, use the category Id found in the previous step (1 in this example).

c. Save the changes.

 

Comments are closed