KPI Templates

As the name suggests a KPI Template is a way of generating multiple KPIs linked to the KPI Template. The KPI Templates application will be found in the Administration module and KPI sub-module.

In the MAXDEMO database there are a couple of good examples, we will look at Template 1001 – Schedule Compliance of Completed Work Orders. This is based on the application WOTRACK – Work Order Tracking. The Format and KPI Query fields are both required, the Format has options for Percentage or Decimal, the same as we saw for the KPI Manager. The KPI Query contains both the Select Statement and the Where Clause. Over on the right are fields to indicate who created and who last revised the KPI Template and their respective dates.

For KPI Template 1001 the Format is Percentage and the KPI Query is – select (select count(wonum) from workorder where status in (‘COMP’,’CLOSE’, ‘CAN’) and worktype = ${worktype}  and wopriority = ${priority} and istask=0 and woclass = ‘WORKORDER’ and actfinish <  targcompdate) /(NULLIF ((select count(wonum) from workorder where status in (‘COMP’,’CLOSE’, ‘CAN’) and worktype = ${worktype} and wopriority = ${priority} and istask=0 and woclass = ‘WORKORDER’ ),0)) * 100 from dummy_table

The query has inner Select statements either side of the dividing sign (/) and can be interpreted as select x/y * 100 from dummy_table. There are two variables priority and worktype in the KPI Query, identified by ${worktype} and ${priority} respectively. They appear twice each and are in both inner Select statements.

Each unique variable will need to be defined in the KPI Variables table indicating the Object and Attribute that the variable references and the type of data it represents which will be filled-in when you select the Attribute. There is a field to enter a remark. The priority variable references object WORKORDER and attribute WOPRIORITY, the worktype variable references object WORKORDER and attribute WORKTYPE.

In the KPI table you use the New Row button and the table window called “Variable Values for KPI” is populated for the KPI Number created. This has one record for each KPI Variable so that you can enter the variable values for this KPI. The KPI number is autokeyed and is read-only. The value will keep creeping up and will not be reset to 1; the purpose is to create a unique value for the record in the KPI Manager application when those KPI records are generated. The other fields for the Target, Caution At, Alert At, Public and Active can be modified.

The values entered for each new row are copied from the first KPI in the list for this KPI Template, this also applies to the variable values, but all the values can be changed. The Test Variables button is used to validate that the variable values are of the same data type as the defined variable, and you should receive the message “BMXAA8950I – The query and related variables were validated.”. 

After entering the Target, Caution At, Alert At and the Variable Values for each KPI you will be ready to use the Generate KPIs action. After generating the KPIs you will receive a message like “BMXAA8956I – 8 KPIs were created for the 1001 template”.

Additional validation occurs when generating the KPIs to verify that the resulting SQL is valid and that it does not include an Update statement, which could be used for SQL Injection threats.

You will then be able to find the generated KPIs in the KPI Manager application, the KPI Name will start with the Template name followed by the KPI Number from the template, for example the KPI template was 1001, and there were 8 KPI numbered 2 though 9, hence the KPIs in KPI Manager are called 1001_2, 1001_3, etc. The Template field has a reference when a KPI has been generated from a KPI Template.

If you update the KPI Template and use the Generate KPIs action again the corresponding KPIs will be updated, you do not get a second set of KPIs. If you update one of the generated KPIs in the KPI Manager application, perhaps by adding a link to another KPI then you will receive the messages:

Duplicate/Delete Template

There is a Duplicate Template which duplicates the KPI Template and places you in the new record. The KPI Variables, KPIs and Variable Values are all duplicated as you might expect.

The Delete Template action which like the Duplicate Template is only available from the Template tab will pop a message with Yes, No or Cancel options – “BMXAA8958I – To delete the KPI Template and the generated KPI records, click Yes. To delete the KPI Template, click No.”

KPI Entity Diagram

The Key Performance Indicator (KPI) objects are all at the System level in Maximo. There are no Organizations or Sites referenced and if a KPI is supposed to be specific to an Organization or Site then you would include this in the KPI’s query statement.

The left-hand side of the Entity Relationship Diagram (ERD) shows the KPI Template:

The right-hand side of the ERD shows the KPI Manager application:

The KPIMAIN object is language enabled.

KPIs are referenced on Service Level Agreements (SLA).

There is a KPIOEE table that has nothing to do with Maximo Key Performance Indicators (KPIs). This is used by manufacturing organizations and the three Overall Equipment Effectiveness BIRT reports for location, asset or site, the report names start oeekpi_. Some production data for this report would be entered manually into the KPIOEE table.

Leave a Reply


%d bloggers like this: