Maximo List Archive

This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.

From: in2data (2011-12-09 17:04)

Hi,
We have one to measure the On-Time PM rate. PMs are on-time only if they are completed within the allowed window. For instance 90 day or less PMs have +/- 7 days of the scheduled start date to be completed. The system copies the target start date from the PM work order to the scheduled start date just after the work order is generated. The stored procedure also does this if the scheduled start date is still null. The site maintenance planner can then move the scheduled start date to align with available downtime.
The way it's calculated is a stored procedure is run on the database each night that updates a custom table OSC_KPI_PM. You can and should create the custom table in Maximo but the stored procedure has to be run from the database. The code is below and it's for SQL Server. Your DB admins can convert to Oracle.
I also included the code to create the table in the DB. You can use this for the table attributes in Maximo.
Then you create a report using the query at the end. again that's for SQL and will need some editing for Oracle. I've added some conditional colors in the report to show where on-time rates are out of the KPI metric limit.
Why did I use a stored procedure? It takes a long time to calculate the KPIs and having a table already populated with the results makes the report run much faster. The users like to run this on demand.
In the code where you run across poolyear and other values like that those are internal calendar periods we use. OSC is an agricultural co-operative and we measure everything by the pool year calendar. Those periods do not align with the normal Jan-Dec calendar. Our pool year starts the end of Feb and has varying pool period lengths from 25 to 32 days. Our financial periods in Maximo are set to those pool periods.
OK. That's a lot of fire hose time. I'll turn it off now.
Enjoy the briar patch :-)
Dave Bone
Ocean Spray Cranberries
/*Stored Procedure Create Code Begins*/
USE [maxprod]
GO
/****** Object: StoredProcedure [dbo].[OSC_KPI_PM_Update] Script Date: 12/09/2011 09:01:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Drop PROCEDURE OSC_KPI_PM_Update
-- exec OSC_KPI_PM_Update
-- select * from OSC_KPI_PM
CREATE PROCEDURE [dbo].[OSC_KPI_PM_Update]
AS
update workorder set schedfinish = targcompdate
where ((schedfinish is null) and (targcompdate is not null)
and (worktype = 'PM' or worktype = 'PMYR' or worktype = 'REG'))
Delete from OSC_KPI_PM
Declare
@90daykpi as varchar(8),
@over90daykpi as varchar(8),
@fiscalyr as varchar(8)
set @90daykpi = '7'
set @over90daykpi = '15'
set @fiscalyr = (Select LEFT(f.financialperiod, 4) FROM financialperiods f Where orgid = 'OSC' and (f.periodstart <= getdate()) AND (f.periodend > getdate()))
Insert into OSC_KPI_PM (poolperiod,periodstart,periodend,siteid,orgid,comp,can,[open],open_late,comp_ot,comp_early_late,noactfinish,total)
SELECT financialperiod as poolperiod, periodstart, periodend, w.siteid, w.orgid,
Case When (count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status = 'COMPCLOSE') THEN wonum ELSE null END)) = 0 THEN 1 ELSE (count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status = 'COMPCLOSE') THEN wonum ELSE null END)) END AS COMP,
--this is the old comp the one above keeps from having a divide by 0 error when no pm is comp,can or open_late count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP') THEN wonum ELSE null END) AS COMP,
count(CASE WHEN (w.status = 'CAN' or w.status = 'CANHOL' or w.status = 'CANOPS') THEN wonum ELSE null END) AS CAN,
count(CASE WHEN (w.status <> 'CLOSE' and w.status <> 'COMP' and w.status <> 'COMPCLOSE' and w.status <> 'CAN' and w.status <> 'CANHOL' and w.status <> 'CANOPS')
THEN wonum ELSE null END) AS 'OPEN',
count(CASE WHEN (w.status <> 'CLOSE' and w.status <> 'COMP' and w.status <> 'COMPCLOSE' and w.status <> 'CAN' and w.status <> 'CANHOL' and w.status <> 'CANOPS')
and
(((w.schedfinish + 7 <= getdate())
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) <= '90')
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) >= '28')
and (abs(datediff(dd,w.schedfinish,getdate())) > @90daykpi))
or
((w.schedfinish + 15 <= getdate())
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) > '90')
and (abs(datediff(dd,w.schedfinish,getdate())) > @over90daykpi)))
THEN wonum ELSE null END) AS 'OPEN_LATE',
count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status = 'COMPCLOSE')
and
((((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) <= '90')
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) >= '28')
and (abs(datediff(dd,w.schedfinish,w.actfinish)) <= @90daykpi))
or
(((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) > '90')
and (abs(datediff(dd,w.schedfinish,w.actfinish)) <= @over90daykpi)))
THEN wonum ELSE null END) AS COMP_OT,
count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status = 'COMPCLOSE')
and
((((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) <= '90')
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) >= '28')
and (abs(datediff(dd,w.schedfinish,w.actfinish)) > @90daykpi))
or
(((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) > '90')
and (abs(datediff(dd,w.schedfinish,w.actfinish)) > @over90daykpi)))
THEN wonum ELSE null END) AS COMP_EARLY_LATE,
count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status = 'COMPCLOSE')
and actfinish is null
THEN wonum ELSE null END) AS NOACTFINISH,

count(wonum) AS TOTAL
FROM workorder w
left outer join pm p on (p.pmnum = w.pmnum and p.orgid = w.orgid and p.siteid = w.siteid
and (Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end) >= '28')
join financialperiods f on (w.schedfinish >= f.periodstart and w.schedfinish < f.periodend and w.orgid = f.orgid)
left outer join pmsequence s on (s.jpnum = w.jpnum and s.pmnum = w.pmnum and s.orgid = f.orgid and s.siteid = w.siteid)
WHERE (istask = 0 and w.pmnum is not null) and (woclass = 'WORKORDER' or woclass = 'ACTIVITY')
and (w.worktype = 'PM' or w.worktype = 'PMYR' or w.worktype = 'REG')
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) >= '28')
and (left(financialperiod,4) >= (@fiscalyr - 5))
and w.wonum not in (select wonum from wostatus where status = 'DELSCHED')
GROUP BY financialperiod, periodstart, periodend, w.siteid, w.orgid
order by financialperiod
/*Stored Procedure Create Code Ends*/
/*Table Create Code Begins*/
USE [maxprod]
GO
/****** Object: Table [dbo].[OSC_KPI_MSP] Script Date: 12/09/2011 08:46:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OSC_KPI_MSP](
[poolyear] AS (left([poolperiod],(4))) PERSISTED,
[poolperiod] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[periodstart] [smalldatetime] NULL,
[periodend] [smalldatetime] NULL,
[siteid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[orgid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[comp] [decimal](5, 0) NULL,
[can] [decimal](5, 0) NULL,
[open] [decimal](5, 0) NULL,
[open_late] [decimal](5, 0) NULL,
[comp_ot] [decimal](5, 0) NULL,
[comp_early_late] [decimal](5, 0) NULL,
[noactfinish] [decimal](5, 0) NULL,
[total] [decimal](5, 0) NULL,
[comp_ot_cal] AS ([comp_ot]/(([comp]+[open_late])+[can])) PERSISTED
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/*Table Create Code Ends*/
/*Report Query Code Begins*/
maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();
var temppoolyearString = new String();
var tempsiteidString = new String();
if(params["poolyear"].value)
{
temppoolyearString = reportContext.getParameterValue("poolyear").toUpperCase();
}
else
{
temppoolyearString = "%";
}
if(params["siteid"].value)
{
tempsiteidString = reportContext.getParameterValue("siteid").toUpperCase();
}
else
{
tempsiteidString = "%";
}
var sqlText = new String();
// Add query to sqlText variable.
sqlText = "select poolyear, poolperiod, periodstart, periodend, siteid, orgid, comp, can, [open], open_late, "
+ " comp_ot, comp_early_late, noactfinish, total, comp_ot_cal, getdate() as 'currentdate' "
+ " from osc_kpi_pm "
+ " where " + params["where"]
+ " and siteid like '%" + tempsiteidString + "%' "
+ " and poolyear like '" + temppoolyearString + "' "
+ " order by siteid, poolperiod"
;
maximoDataSet.setQuery(sqlText);
/*Report Query Code Ends*/
--- In MAXIMO@yahoogroups.com, Chad Stringer <cstringer@...> wrote:
>
> You might try getting a hold of an SMRP (Society of Maintenance and Reliability Professionals) handbook. They have a lot of great metrics that are industry standards to look into. That is a great place to start, and where I pull most of my ideas for KPIs.
> The SQL code is a whole different matter. Many companies use custom fields or different parameters based on how they measure things. I'm sure there are some really good base queries available to use though. Best of luck, and keep us all in the loop on your progress.
>
> CJ Stringer
>
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of john_gould14
> Sent: Friday, December 09, 2011 8:28 AM
> To: MAXIMO@yahoogroups.com
> Subject: [MAXIMO List] KPI Roundup
>
>
>
> Hello All - in attempting to further develop our start center KPI's to get better visualization of our orgnaizations performance, I'm always wondering what other KPI's people are using. I have the standard, KPI's like percetnage of work type, average age of work orders, # of work orders reported versus completed, inprogress, open emergencies, PM's overdue - things like that.
>
> If you have some more 'interesting' ones to share I'd love to hear about them. I'm a SQL novice so if you have the SQL code - even better. I'm running version 7.5.0 with Oracle DB.
>
>
>
>
>

KPI Roundup

From: john_gould14 (2011-12-09 13:27)

From: Chad Stringer (2011-12-09 08:54)

From: in2data (2011-12-09 17:04)