Reporting KPI Charts

AMT_BPMKPIVALUE table provides raw KPI values of process instances. Aggregations and other calculation are supposed to be done by a reporting tool. Example SQLs to get data suitable for reporting are listed below.

select t1.pi, t1.v kpiTotal, t2.v kpiTotalToReject, coalesce(t1.v, t2.v) kpiTotal from (

  select kv.aprocessiid pi, kv.longvalue v from amt_bpmkpivalue kv

    inner join amt_bpmkpi k on k.kpi_id = kv.kpi_id

    inner join amt_bpmprocess p on k.process_id = p.process_id

  where p.guid = 'proces guid' and k.name = 'kpiTotal') t1

inner join (

  select kv.aprocessiid pi, kv.longvalue v from amt_bpmkpivalue kv

    inner join amt_bpmkpi k on k.kpi_id = kv.kpi_id

    inner join amt_bpmprocess p on k.process_id = p.process_id

  where p.guid = 'proces guid' and k.name = 'kpiTotalToReject') t2

on t1.pi = t2.pi;

Returns the total duration of the process instance calculated as the first not-null value from kpiTotal, kpiTotalToReject. Process design ensures that just one of those two will have a not-null value.

select tx.v person, avg(ty.v) avgTimeToAccept from (

  select kv.aprocessiid pi, kv.stringvalue v from amt_bpmkpivalue kv

    inner join amt_bpmkpi k on k.kpi_id = kv.kpi_id

    inner join amt_bpmprocess p on k.process_id = p.process_id

  where p.guid = 'proces guid'and k.name = 'kpiPersonName') tx

inner join (

  select kv.aprocessiid pi, kv.longvalue v from amt_bpmkpivalue kv

    inner join amt_bpmkpi k on k.kpi_id = kv.kpi_id

    inner join amt_bpmprocess p on k.process_id = p.process_id

  where p.guid = 'proces guid' and k.name = 'kpiAccept') ty

on tx.pi = ty.pi

group by tx.v;

Returns data for a chart "Average time spent per person to accept a request" for all process history.

KPIs of called processes

Example: There are processes "Software Fulfillment", "Hardware Fulfillment", both calling a global process "Approval", which consists of mandatory Level 1 Approval and optional Level 2 Approval tasks.

Note that to select kpi data of an Approval process, measured only in the context of e.g. Software Fulfillment, there must be at least one kpi defined for Software Fulfillment.

Chart - Software Fulfillment Average Approval Times

Average time spent in Level1Approval and Level2Approval when doing approval for Software Fulfillment.

The kpiSW is used to select only such kpiL1, kpiL2 values, which were measured in a context of the caller process instance - which produced kpiSW.

select name, avg(kpiL1), avg(kpiL2) from

  (select t4.pi, t4.cpi, t3.v as name, t4.kpiL1 as kpiL1, t4.kpiL2 as kpiL2 from (

    select kv.aprocessiid pi, kv.stringvalue v from amt_bpmkpivalue kv

      inner join amt_bpmkpi k on k.kpi_id = kv.kpi_id

      inner join amt_bpmprocess p on k.process_id = p.process_id

    where p.name = 'process SW' and k.name = 'kpiSW') t3

  inner join (

    (select t1.pi, t1.cpi, t1.v as kpil1, t2.v as kpil2 from (

      select kv.aprocessiid pi, kv.acallerprocessiid cpi, kv.longvalue v from amt_bpmkpivalue kv

        inner join amt_bpmkpi k on k.kpi_id = kv.kpi_id

        inner join amt_bpmprocess p on k.process_id = p.process_id

      where p.name = 'process Approval' and k.name = 'kpiL1') t1

     inner join (

      select kv.aprocessiid pi, kv.longvalue v from amt_bpmkpivalue kv

        inner join amt_bpmkpi k on k.kpi_id = kv.kpi_id

        inner join amt_bpmprocess p on k.process_id = p.process_id

     where p.name = 'process Approval' and k.name = 'kpiL2') t2

   on t1.pi = t2.pi)) t4

 on t4.cpi = t3.pi) t5

group by name;

Returns (kpiSW, kpiL1, kpiL2) for each execution of "process SW". If kpiSW is not created during process instance execution, e.g. due to another path in the process, Approval measurements will be omitted.

Comparison of Level1Approval median for Software and Hardware Fulfillments

Chart Level1Approval for HW and SW fulfillment

select processdef, median(kpil1) from

 (select t2.processdef, t1.pi, t1.cpi, t2.kpi_id as kpiid, t1.kpil1 as kpil1 from (

   select k.process_id processdef, kv.aprocessiid pi, k.kpi_id kpi_id from amt_bpmkpivalue kv

    inner join amt_bpmkpi k on k.kpi_id = kv.kpi_id

    inner join amt_bpmprocess p on k.process_id = p.process_id

   where p.name in ('process SW','process HW') and k.name in ('kpiSW','kpiHW')) t2

  inner join (

   select kv.aprocessiid pi, kv.acallerprocessiid cpi, kv.longvalue kpiL1 from amt_bpmkpivalue kv

    inner join amt_bpmkpi k on k.kpi_id = kv.kpi_id

    inner join amt_bpmprocess p on k.process_id = p.process_id

   where p.name = 'process Approval' and k.name = 'kpiL1') t1

  on t1.cpi = t2.pi) t5

group by processdef;

KPIs ('kpiSW', 'kpiHW') of the caller process must exist to make it possible to select 'kpiL1' values measured in the called process Approval.