Oracle Analyses related to processes

1 Prozessbezogen
- 1.1 Instanzbezogen
- 1.2 Definitionsbezogen
  - 1.2.1 Anzahl der Schleifen (>0) pro Definition

Prozessbezogen

Dieser Artikel bietet Beispiele für z.B. und definitionsbezogene Analysen mit dem Dashboard.

Instanzbezogen

Anzahl der gestarteten Instanzen pro Monat

SELECT
CONCAT(CONCAT(to_char(inst.CREATIONTIME,'yyyy'), '-'), to_char(inst.CREATIONTIME,'mm')) AS Month,
SUM(1) AS COUNT
FROM view_instance inst
GROUP BY CONCAT(CONCAT(to_char(inst.CREATIONTIME,'yyyy'), '-'), to_char(inst.CREATIONTIME,'mm'))
Order by CONCAT(CONCAT(to_char(inst.CREATIONTIME,'yyyy'), '-'), to_char(inst.CREATIONTIME,'mm')) DESC;

Durchschnittliche Anzahl gestarteter Instanzen pro Tag und Monat (22 Arbeitstage pro Monat)

SELECT
CONCAT(CONCAT(to_char(inst.CREATIONTIME,'yyyy'), '-'), to_char(inst.CREATIONTIME,'mm')) AS Month,
ROUND(SUM(1)/22,1) AS "Average per day"
FROM view_instance inst
GROUP BY CONCAT(CONCAT(to_char(inst.CREATIONTIME,'yyyy'), '-'), to_char(inst.CREATIONTIME,'mm'))
Order by CONCAT(CONCAT(to_char(inst.CREATIONTIME,'yyyy'), '-'), to_char(inst.CREATIONTIME,'mm')) DESC;

Anzahl der gestarteten und abgeschlossenen Instanzen für jede Definition

SELECT 
inst1.definitionname AS "Process", 
COUNT(DISTINCT(inst1.ID)) AS "Number of started instances", 
COUNT(DISTINCT(inst2.ID)) AS "Number of completed instances"
FROM view_instance inst1 
LEFT JOIN view_instance inst2 
ON inst1.ID=inst2.ID 
AND inst2.INSTANCEEND IS NOT NULL
INNER JOIN view_activity act ON act.INSTANCEID=inst1.ID
LEFT JOIN view_task task ON task.INSTANCEID=inst1.ID
GROUP BY inst1.DEFINITIONNAME;

Anzahl der gestarteten, abgeschlossenen, abgebrochenen und ausgeführten Instanzen für jede Prozessdefinition

SELECT
DEFINITIONNAME AS "Definition name",
SUM(1) AS started,
SUM(CASE WHEN ISARCHIV= '0' AND INSTANCEEND is NULL THEN 1 ELSE 0 end) AS running,
SUM(CASE WHEN ISARCHIV= '1' And INSTANCEEND is NULL THEN 1 ELSE 0 END) AS aborted,
SUM(CASE WHEN INSTANCEEND IS NOT NULL THEN 1 ELSE 0 end) AS completed
FROM view_instance GROUP BY DEFINITIONNAME

Aktivitäten, Aufgaben und zugewiesene Benutzer laufender Instanzen

SELECT
inst.INSTANCENAME AS "Instance",
inst.DEFINITIONNAME AS "Process",
LISTAGG(act.ACTIVITYNAME, ', ') WITHIN GROUP (ORDER BY act.ACTIVITYNAME) AS "Activity",
LISTAGG(task.TASKNAME, ', ') WITHIN GROUP (ORDER BY task.TASKNAME) AS "Tasks",
LISTAGG(CASE WHEN ident.ID IS NULL THEN ident2.IDENTITYNAME ELSE CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(ident.FIRSTNAME, ' '), ident.LASTNAME),' ('), ident2.IDENTITYNAME),')') END , ', ') WITHIN GROUP (ORDER BY ident2.IDENTITYNAME) AS "Username"
FROM view_instance inst
INNER JOIN view_activity act
ON act.INSTANCEID = inst.ID
AND act.ACTIVITYSTART IS NOT NULL
AND act.ACTIVITYEND IS NULL AND act.ACTIVITYTYPE = 'K'
INNER JOIN view_task task
ON task.ACTIVITYID = act.ID
LEFT JOIN view_identity ident
ON task.ACTORID = ident.ID
INNER JOIN view_identity ident2
ON task.POOLEDACTORID = ident2.ID
WHERE inst.ISARCHIV = '0'
AND inst.INSTANCEEND IS NULL
GROUP BY inst.ID, inst.INSTANCENAME, inst.DEFINITIONNAME
ORDER BY inst.DEFINITIONNAME, inst.INSTANCENAME;

Durchschnittliche Zykluszeit (in h) aller Instanzen aller Prozessdefinitionen pro Monat

SELECT
  CONCAT(CONCAT(to_char(CREATIONTIME,'yyyy'), '-'), to_char(CREATIONTIME,'mm')) AS Month,
  ROUND(AVG(24*ROUND(to_number(to_date(to_char(INSTANCEEND, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') -            to_date(to_char(INSTANCESTART, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi')),2))) AS "CYCLE TIME"
FROM view_instance
WHERE instanceEnd IS NOT NULL
  GROUP BY CONCAT(CONCAT(to_char(CREATIONTIME,'yyyy'), '-'), to_char(CREATIONTIME,'mm'))
  ORDER BY CONCAT(CONCAT(to_char(CREATIONTIME,'yyyy'), '-'), to_char(CREATIONTIME,'mm'));

Anzahl der gestarteten Instanzen pro Monat (Spalten) pro Jahr (Zeilen) für eine Definition

SELECT
Year as "Year",
SUM(CASE WHEN Month LIKE '01' THEN 1 ELSE 0 END) AS "January",
SUM(CASE WHEN Month LIKE '02'THEN 1 ELSE 0 END) AS "February",
SUM(CASE WHEN Month LIKE '03' THEN 1 ELSE 0 END) AS "March",
SUM(CASE WHEN Month LIKE '04' THEN 1 ELSE 0 END) AS "April",
SUM(CASE WHEN Month LIKE '05' THEN 1 ELSE 0 END) AS "May",
SUM(CASE WHEN Month LIKE '06' THEN 1 ELSE 0 END) AS "June",
SUM(CASE WHEN Month LIKE '07' THEN 1 ELSE 0 END) AS "July",
SUM(CASE WHEN Month LIKE '08' THEN 1 ELSE 0 END) AS "August",
SUM(CASE WHEN Month LIKE '09' THEN 1 ELSE 0 END) AS "September",
SUM(CASE WHEN Month LIKE '10' THEN 1 ELSE 0 END) AS "October",
SUM(CASE WHEN Month LIKE '11' THEN 1 ELSE 0 END) AS "November",
SUM(CASE WHEN Month LIKE '12' THEN 1 ELSE 0 END) AS "December"
FROM
  (SELECT
    to_char(CREATIONTIME,'mm') AS Month,
    to_char(CREATIONTIME,'yyyy') AS Year
    FROM view_instance
    WHERE definitionname = '<name_of_process_definition>'
  )
GROUP BY Year
ORDER BY Year;

Kumulative Anzahl gestarteter Instanzen pro Monat (Spalten) pro Jahr (Zeilen) für eine Definition

SELECT
Year as "Year",
SUM(CASE WHEN Month LIKE '01' THEN 1 ELSE 0 END) AS "January",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' THEN 1 ELSE 0 END) AS "February",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' THEN 1 ELSE 0 END) AS "March",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04' THEN 1 ELSE 0 END) AS "April",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04' OR Month LIKE '05' THEN 1 ELSE 0 END) AS "May",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04'OR Month LIKE '05' OR Month LIKE '06' THEN 1 ELSE 0 END) AS "June",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04'OR Month LIKE '05' OR Month LIKE '06' OR Month LIKE '07' THEN 1 ELSE 0 END) AS "July",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04'OR Month LIKE '05' OR Month LIKE '06' OR Month LIKE '07' OR Month LIKE '08' THEN 1 ELSE 0 END) AS "August",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04'OR Month LIKE '05' OR Month LIKE '06' OR Month LIKE '07' OR Month LIKE '08' OR Month LIKE '09' THEN 1 ELSE 0 END) AS "September",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04'OR Month LIKE '05' OR Month LIKE '06' OR Month LIKE '07' OR Month LIKE '08' OR Month LIKE '09' OR Month LIKE '10' THEN 1 ELSE 0 END) AS "October",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04'OR Month LIKE '05' OR Month LIKE '06' OR Month LIKE '07' OR Month LIKE '08' OR Month LIKE '09' OR Month LIKE '10' OR Month LIKE '11' THEN 1 ELSE 0 END) AS "November",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04'OR Month LIKE '05' OR Month LIKE '06' OR Month LIKE '07' OR Month LIKE '08' OR Month LIKE '09' OR Month LIKE '10' OR Month LIKE '11' OR Month LIKE '12' THEN 1 ELSE 0 END) AS "December"
FROM
  (SELECT
    to_char(CREATIONTIME,'mm') AS Month,
    to_char(CREATIONTIME,'yyyy') AS Year
    FROM view_instance
    WHERE definitionname = '<name_of_process_definition>'
  )
GROUP BY Year
ORDER BY Year;

Minimale, maximale und durchschnittliche Zykluszeit (in h) von Instanzen pro Prozessdefinition

SELECT
CONCAT(CONCAT(to_char(CREATIONTIME,'yyyy'), '-'), to_char(CREATIONTIME,'mm')) AS Month,
ROUND(AVG(24*ROUND(to_number(to_date(to_char(INSTANCEEND, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - to_date(to_char(INSTANCESTART, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi')),2))) AS "Average cycle time",
ROUND(MIN(24*ROUND(to_number(to_date(to_char(INSTANCEEND, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - to_date(to_char(INSTANCESTART, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi')),2))) AS "Min cycle time",
ROUND(MAX(24*ROUND(to_number(to_date(to_char(INSTANCEEND, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - to_date(to_char(INSTANCESTART, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi')),2))) AS "Max cycle time"
FROM view_instance
WHERE instanceEnd IS NOT NULL
GROUP BY CONCAT(CONCAT(to_char(CREATIONTIME,'yyyy'), '-'), to_char(CREATIONTIME,'mm'))
ORDER BY CONCAT(CONCAT(to_char(CREATIONTIME,'yyyy'), '-'), to_char(CREATIONTIME,'mm'));

Definitionsbezogen

Anzahl der Schleifen (>0) pro Definition

SELECT
defName AS Process,
SUM(lCount) AS NumberOfLoops
FROM
(
SELECT inst.DEFINITIONNAME AS defName, inst.INSTANCENAME AS instName, act.LOOPCOUNT AS lCount, inst.ID AS piId, act.ACTIVITYNAME
FROM view_instance inst, view_activity act
WHERE act.LOOPCOUNT IS NOT NULL
AND act.INSTANCEID = inst.ID
GROUP BY inst.ID, inst.DEFINITIONNAME, inst.INSTANCENAME,act.LOOPCOUNT, act.ACTIVITYNAME)
GROUP BY defName;