KPI Best Practices

Maximo provides a powerful engine for KPIs; this can be used to measure your operational outcome, but is also a very useful tool to measure data compliance in Maximo. Every implementation of Maximo is unique, in its own industry, culture and company maturity, therefore it is not easy to have standard KPIs for everyone. It is one of the main reasons why Maximo does not provide KPI templates out of the box however making your own KPIs is a very simple task.

In your standard Maximo you already have the option to build a KPI from almost any application, by using the Search capability (e.g. show me all PM work orders) and with a single click, you can use that search as the basis of your KPI.

But what if your KPI needs to be more sophisticated? We at Amosa offer you services specific around building KPIs and ensuring the right results for your business requirements. To find out more you can get in touch with us via our Contact Form.

If you build your own KPI, and the result has a division in it, you need to ensure, it can never result in 'Divide by Zero'. Imagine this scenario: your KPI testing is working fine, and you set up the KPI in Maximo, and attach a daily or hourly schedule to auto update the value. If the KPI results in a 'Divide by zero' error, you would not see that error on your desktop (it is however in your logs, but those are long and likely to be lost in the volume of data). The result is your KPI never gets updated again for as long as the situation exists. Maximo does display a date/time when the last update was successful, but who is paying attention to that? The result is, you may be making business decisions based on outdated and incorrect data.

So, if you cannot guarantee the division in your formula will never be zero, you can add a simple test to your SQL and return a zero in case of an error[MM1] .

Let’s say you want to calculate your work orders in a status of APPR as a percentage of your INPRG work orders. So you would create an SQL query like this:

select 
(select count(*) from workorder where status='APPR')
 / 
(select count(*) from workorder where status='INPRG') * 100 from dummy_table

In a normal situation this will work fine, but what if you do not have any INPRG work orders in your selection criteria, your formula would fail. You could do a quick test by changing the INPRG in this example to a status that does not exist for example ABC. See the error message?

By adding a test layer to the SQL, you can ensure an output is guaranteed when you set up this KPI:

SELECT
 Case WHEN Y.NUM = 0 Then 0
 ELSE x.NUM / CAST((y.NUM) as decimal(10,2) ) * 100 
END as Result
FROM
(
(select count(*) as NUM from workorder where status='APPR')
) X
join
 (
(select count(*) as NUM from workorder where status='INPRG')) Y ON 1=1

With this statement, the KPI will now run the SQL as two separate queries and use the result in a CASE to test if the Y result is zero. If the Y result is zero (and your division would fail), the CASE function will return a zero, otherwise it will calculate the percentage of the two. Do the same test as before, changing the INPRG status with a non-existing status like ABC and your result would return a clean zero.

Please take the formula below as an empty template for your own KPIs:

SELECT
 Case WHEN Y.NUM = 0 Then 0
 ELSE x.NUM / CAST((y.NUM) as decimal(10,2) ) * 100 
END as Result
FROM
(
(<Insert your SQL here for larger data set>)
) X
join
 (
(<Insert your SQL here to divide by>) Y ON 1=1

Note: This has been tested on DB2 and Oracle only.

Was this helpful? Click like to let us know. Want to talk to us about what Amosa can do for you with KPIs? Contact us via our contact form for more information.

Louis Stoop

Amosa Group