Last Updated on November 19, 2022 by maximosecrets
The KPI Manager application is used to create and maintain records that provide operational Key Performance Indicators (KPIs). I’ve deliberately described these as operational KPIs and not business KPIs. They will help to determine how Maximo is being used and the results can be tracked over time and displayed on a Start Center.
Each KPI is a discreet calculation, and while you could have thousands of KPIs being calculated in Maximo, they do not take advantage of a SQL Group By clause and hence would perform inefficiently if you attempted to use these for business KPIs. For example, a single Business KPI measuring work performance for 100 customers across 10,000 locationsA physical place where assets exist and where work can be performed. More might be possible with a single query with a Group By clause, in Maximo it would require 10,000 KPI records, and 10,000 individual calculations, in comparison it would perform poorly and be difficult to maintain. For business KPIs you should be considering using Cognos Analytics.
The KPI Manager application will be found in the Administration module and KPI sub-module.
KPI Manager – List Tab
The MAXDEMO database provides a few examples and from the List tab you can see that each record is colour coded with one of the traffic light colours, green, amber/yellow, or red. How the colour is derived I will explain in a while. The Target Value would be used to compare with the Current Value, but it plays no part in the calculation for the colour.
There are two calculation types of DECIMAL or PERCENT, and a KPI can be generated from a KPI Template which will be the subject of another article.
We’ll now look at some of the standard KPIs.
A KPI calculates a single numeric value based on a Select statement and a Where clause found in the Query Details section. As you can see in the example for the TYPE1REGUSER – Registered User – Type 1, the Select and Where clause when concatenated will create a single SQL statement, in this example – Select count(*) from maxuser where status = ‘ACTIVE’ and type = ‘TYPE 1’. There are 10 similar KPIs for type = ‘TYPE 1’ through ‘TYPE 10’.
While the Select field is mandatory the Where clause is not, and some consultants place the whole SQL statement in the Select field (SELECTSTMT) and leave the Where field (CLAUSE) empty. This makes sense as the KPI Templates application only has the one field and when you generate KPIs from the template it is the Select field that is populated.
The Calculation Type field defaults to DECIMAL and the one other allowed value is PERCENTAGE. It doesn’t matter whether you use DECIMAL or PERCENTAGE in how the KPI is calculated, and if you choose PERCENTAGE, it does not restrict the Target, Caution At or Alert At values to between 0 and 100. I would suggest you use PERCENTAGE when the Target, Caution and Alert values will be between 0 and 100, and where the Select/Where statements follow a particular format, which I will also show later.
In the KPI Parameters section there are three fields Target, Caution At, and Alert At. The Caution At (CAUTIONMIN) and Alert At (CAUTIONMAX) define the values which will derive a Amber/Yellow colour if the result of the calculation of the Current Value/Actual (KPIVALUE) falls between these values. When the Caution At value < the Alert At value then the colour will be:
- Green if the Current Value/Actual is less than or equal to Caution At
- Red if the Current Value/Actual is greater than or equal to Alert At
If you look at the Graph Details section the dial shows clockwise green, amber/yellow, then red, and this example has a Caution At of 120 and an Alert At value of 150, Caution At < Alert At. This is the typical arrangement to have the Alert At value > Caution At value.
For a different KPI record, KPI-8 – PM Performance, the Caution At value > the Alert At value, the colours will then be reversed:
- Red if the Current Value/Actual is less than or equal to Caution At
- Green if the Current Value/Actual is greater than or equal to Alert At
If you look at the Graph Details section for this example the dial shows clockwise red, amber/yellow, then green. In this example the Caution At value is 90 with an Alert At value of 80, Caution At > Alert At.
KPI-8 is an example where the Calculation Type is Percentage. While the dial shows 0 to 100, this will not always be the case for KPIs with a Calculation Type of Percentage. If the calculation of the Current Value/Actual is close to 100, or the Target is close to 100, then it is likely to show 0 to 100.
In the example of KPI-9 – % rework, the Target, Caution At and Alert At values are all low (10 or lower) and in this case the dial shows 0% – 11%. If it were 0-100% you would struggle to see the boundaries between the three colours.
KPI-9 is an example where only the Select Statement has been used, the Where Clause has been left empty. The Select Statement is – select (select count(wonum) from workorder where worktype = ‘CM’ and hasfollowupwork = 1) / (select count(wonum) from workorder where worktype = ‘CM’ and istask=0) * 100 from dummy_table
Notice that when the Calculation Type is PERCENTAGE and there is a structure to the Select Statement.
Select (1st Select statement) /
(2nd Select statement) * 100 from dummy_table
Both of the internal Select Statements are fully formed with From and Where clause and it is highly likely they will be of the form (select count(*) from …) so that they calculate a single value. The result of the whole Select Statement can be simplified as Select x / y * 100 from dummy_table.
Incidentally the dummy_table is an internal table in Maximo that has a single record containing the null values for 18 of the Maximo data types. You see it referenced occasionally, do not change this, or add additional records, it will almost certainly break multiple things in Maximo. I have used it in the past in Crossover Domains to set fields to null, which could be a justified approach if you were already going to use a crossover domain.
If the Select and Where clause concatenated together do not form a valid SQL statement you will receive the error message “The KPI could not be run successfully. Check the SELECT Statement and WHERE Clause for KPI. Invalid SQL queries are logged in the log file.”.
The Graph Details section shows the KPI details in a table window as it would be shown on a Start Center portlet, and the equivalent dial that would be shown in the KPI Graph portlet on the Start Center, more about these later. The Status field shows the colour of the last Actual reading, green, amber/yellow or red, and an up or down arrow showing the trend compared with the Last Reading.
I would consider the Target value as a value slightly above or below the median for the actual values, depending on whether the Caution At is greater than or less than the Alert At value. For example, consider overdue PMs, a Target of 0 may be completely unrealistic, and currently you run at between 150-200. Let’s say the median in 175, then set the target at a more realistic value of say 150, and once you have managed to lower the median you can readjust the target. The Target will nearly always be in the Green Zone, and it is represented as the grey arrow on the KPI Graph dial. If it isn’t in the green zone, then have another look at the definition of the KPI to verify it is doing what you want it to do.
The Variance is the Actual value minus the Target value, negative variances are generally good, positive variance are OK as long as the Actual value has not caused the KPI to enter the Red Zone. The Update button will cause the KPI to be recalculated.
You can link one KPI with one other or link a KPI to a report in the Links section. The KPI CURRENTLOGIN – Current Number of Logged In Users, is an example of this, its Link to KPI is set to the KPI USERSESSIONS – Current User Sessions. When there is a KPI link an icon is placed on the left-hand side of the banner of the KPI List table next to the date and time when the KPI was last calculated – Last Run date/time, this is the same field as the Last Updated field in the KPI Parameters section (LASTUPDATED).
The KPI SCHEDREP – Scheduled Report Jobs, is an example of a KPI where there is both a Link to KPI and Link to Report. The linked report in this case is reportusage.rptdesign with a description of Report Usage. There are hidden fields for the application name associated with the report (REPORTAPPNAME), and the report number (REPORTNUM). Notice there are now two icons in the left-hand side of the banner of the KPI List table.
Other KPIs of Interest
The EMWOSUPED provides an example of using a special bind variable in the Where clause, in this example it is :&DATE&. Here is an IBM Support link to the other Special Bind Variables – https://www.ibm.com/support/pages/username-appname-and-other-special-bind-variables-you-can-use
The two KPIs ACTIVEADHOC and ACTIVEREPORTS are not Public KPIs, so they are private to the user WILSON who created them. They are also both an example of linking to a report, reportusage.rptdesign.
The KPI INCSLAPERCENT and the four similar KPIs INCSLAPERCENTP1 through INCSLAPERCENTP4 are examples where the Caution At value is higher than the Alert At value leading to the KPI Graph colours (left to right) of red, amber/yellow and green.
The KPI SINGLINEPO has an inner select statement with a where clause of type Group By x Having Count(*) = y.
A trend can be calculated from multiple historic values using the three fields at the top of the main KPI tab.
- Calculate Trend (TRENDENABLED) – This YORN field determines whether to calculate a trend. The trend is calculated when a KPI value is saved, and so there are performance implications. You might consider making the default 0 instead of 1.
- Calculate Trend Size (TRENDBATCHSIZE) – Defaults to 100, is the number of KPIHISTORY records used to determine the trend.
- Calculate Trend Status (TRENDVALUE) – This should be a read-only field because the value is calculated, it is the value of the trend. The algorithm used is Least Squares Regression, you may know this as Line of Best Fit. The calculation uses the number of values set by Calculate Trend Size. The algorithm used is from apache commons math, you could use a different algorithm by extending the class file com.ibm.tivoli.maximo.trend.BaseTrendProvider and implementing the method – public double getKPITrend(String kpiname, int batchSize) throws MXException
If you modify the class you need to reference it in the System Property mxe.default.trendprovider, you will need to create the System Property first. The default implementation is provided by the com.ibm.tivoli.maximo.trend.DefaultTrendProvider (least squares regression model).
Positive trends are upwards, negative trends downwards and a value close to 0 would be level. I haven’t tested it, but most values are probably going to be between +0.5 and -0.5.
Hopefully IBM Support will write a Tech Note on this subject so that we can see how it works.
When the KPI Manager application was first provided in Maximo v6 there was no ability to set a schedule for each KPI independently of each other, this was introduced in Maximo v7.6. Previously the KPI was calculated either Real Time or through a single KPI crontask that was referenced in the mxproperties file. For the historians of you the KPI Manager was actually released in the first patch of Maximo 5.2 but in English only. Real Time initially meant it was calculated every time the KPI was displayed in the Start Center but by Maximo v6 when the KPI Manager became available for all languages then this had been replaced with the Update button.
The Cron Task KPICronTask – “KPI CronTask. Cron Task will run KPI that are not real time.” is currently used. It has one Cron Task Instance KPINOREALTIME – “KPI Cask Instance will run KPI that are not real time.” that is scheduled to run at midnight every day. You can create additional Cron Task Instances using the Duplicate or New Row buttons to create different scheduled frequencies that are used against a KPI. You would need to make the Cron Task Instance active, which I will now do.
I’ve also created another Cron Task Instance called 2Hours that runs on a schedule of every 2 Hours and made this Cron Task Instance active. I will retain a small history (Keep History) of the running of the Cron Task Instance, enough to record one day and have set Max Number of History Records to 13.
After you have created your first Cron Task Instance which you will probably do by using the New Row button, I would recommend thereafter using the Duplicate button. This copies the description field, otherwise New Row sets the description initially to be the same as the Cron Task description, which almost certainly you would overwrite.
Back on the KPI Manager application and for a test KPI called AJE01 you can use the Schedule KPI action to reference the Cron Task Instance for KPICronTask that will provide the schedule for when the KPI will be calculated. The screen shot is showing the Select Value, the Schedule KPI dialog is the same size as the Select Value dialog and is hidden from view. Notice the Select Value only shows the Cron Task Instance Name of 2Hours and does not show the Cron Task Instance called KPINOREALTIME that would otherwise be used if there was no individual schedule.
After selecting 2Hours from the Select Value you can now see the Schedule KPI dialog. This dialog allows the KPI to be calculated on multiple schedules, although in most cases only one would be used.
The Test KPI AJE01 has a Select Statement of – Select count(*) from workorder where status=’WAPPR’ – and when I ran this yesterday the Actual was 1361. I’ve just created a new work order, and we’ll wait to see if the KPI recalculates to 1362 this afternoon.
You can also add the same schedule to multiple KPIs on the List tab of the KPI Manager application using the action Add Schedule. There is a similar looking dialog for the action called Remove Schedule which can remove a specific schedule from one or multiple KPIs. In both actions you will need to create a selected set of KPIs first.
After the KPI schedule has run for KPI AJE01 at 06/01/22 14:00 the Actual has incremented by 1 to 1362 and the trend is getting further away from the target hence a red up arrow. I’ve since duplicated the work order at WAPPR status a few times enough I hope for us to see a trend graph being created, but I will have to wait a few more minutes yet for the Cron Task Instance to run again.
A history of data values for the KPI will be created if there is a Schedule.
In the Historical Trends tab, the subtab Historical Values shows the data values calculated through the scheduled Cron Task, they are held in the table KPIHISTORY. There is one value Recorded On 06/01/22 14:00 with a KPI Value of 1,362.00. The aim of the historical data is to calculate a set of points at a set frequency through a schedule, this would allow a trend to be calculated. If you use the Update button as I did yesterday when the value was calculated at 1361, these calculated values will not be added to the historical data points, it is only the scheduled calculations which are.
The Cron Task Instance schedule has now run again, and a second calculation has been made and recorded in the KPIHISTORY table. This was Recorded On 06/01/22 16:00 with a KPI Value of 1,366.00.
The Trend subtab shows a line graph between each of the KPI history records. The top of the graph is at 1366 and the bottom of the graph is 1362 although this is not shown in the screenshot. The horizontal axis shows one week but this is adjustable from the Predefined Date Range dropdown, although Week is the smallest interval. If you are making a change you will need to use the Refresh Chart button.
The Predefined Date Range has a lookup with the following values – Week, 2 Weeks, Month, 3 Months, 6 Months, Year, 2 Years and 5 Years. This field (DATERANGE) and the From and To dates are actually held in the object KPITRENDCFG which has a one to one relationship with KPIMAIN, the main object/table for the KPI Manager application.
The two KPIHISTORY data points are 2 hours apart which is just about the minimum to see that the line is not vertical. If you will show a historical trend over a longer period than one week then you need to consider making the schedule longer, for example a Predefined Date Range of Month would want a KPI Schedule of each day.
The From (CUSTSTARTDATE) and To (CUSTENDDATE) dates allow you to see the chart over a specific date range. You will receive an error message if you try to enter a From Date which is later than the To Date.
When you use the From/To date range then the Show Details In field (CUSTDATERANGE) becomes available for you to select the frequency, Days, Weeks Months or Years. If you selected a frequency of Months with a custom date range (From/To) which was less than two months you would receive the error message “BMXAA2492E – This Custom Date Range must have at least two months.”
The Predefined Date Range and the Custom Date Range are mutually exclusive, you cannot have a value in the Predefined Date Range and the From/To dates.
You can compare multiple KPIs in the same chart. As you can see, I will compare AJE01 with AJE02 when the Cron Task Instance is next scheduled. A maximum of 8 KPIs can be compared; if you try to compare more the error message “BMXAA2494E – A maximum of 8 KPIs can be compared at one time.” will be displayed.
In the Security tab you can associate one or more Security Groups with the KPI, this is granting access rights to see the KPI.
In the example, I have granted security access to the SUPERVISOR security group for KPI AJE01.
If the field Public is set, then an information message is displayed above the Security table window – The following security options are not used because this KPI is available to everyone. A Public KPI is available for everyone to view, a Private KPI would have the Public field unchecked, in which case it could only be seen by the person who created the KPI, in the same manner that a query can be either Public or Private.
The Security tab shows the KPIs that can be viewed in the KPI Viewer application by a user who belongs to the Security Group. Before going to the KPI Viewer application I will remove the Public setting for both KPI’s AJE01 and AJE02.
We’ll review the KPI Viewer application, the Start Center’s KPI Graph and KPI List, and the Create KPI action in another article. There is also an article on the KPI Template application which contains an Entity Relationship Diagram for KPIs.
Leave a Reply