Prozessbezogen
Dieser Artikel bietet Beispiele für z.B. und definitionsbezogene Analysen mit dem Dashboard.
Instanzbezogen
Anzahl der gestarteten Instanzen pro Monat
SELECT CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m')) AS Month, SUM(1) AS COUNT FROM view_instance inst GROUP BY CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m')) ORDER BY CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m')) DESC;
Durchschnittliche Anzahl gestarteter Instanzen pro Tag und Monat (22 Arbeitstage pro Monat)
SELECT CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m')) AS Month, ROUND(SUM(1)/22,1) AS "Average per day" FROM view_instance inst GROUP BY CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m')) ORDER BY CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m')) 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 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", act.ACTIVITYNAME AS "Activity", task.TASKNAME AS "Tasks", CASE WHEN ident.ID IS NULL THEN ident2.IDENTITYNAME ELSE CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(ident.FIRSTNAME, ' '), ident.LASTNAME),' ('), ident2.IDENTITYNAME),')') END 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(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m')) AS Month, IF((UNIX_TIMESTAMP(instanceEnd ) - UNIX_TIMESTAMP(instanceStart))>0, ROUND((AVG(UNIX_TIMESTAMP(instanceEnd) - UNIX_TIMESTAMP(INSTANCESTART)))/3600), '0') AS "CYCLE TIME" FROM view_instance WHERE instanceEnd IS NOT NULL GROUP BY CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m')) ORDER BY CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m')) DESC;
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 DATE_FORMAT(creationTime, '%m') AS Month, DATE_FORMAT(creationTime, '%Y') AS Year FROM view_instance WHERE definitionName = '<name_of_process_definition>' ) AS subqu 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 DATE_FORMAT(creationTime, '%m') AS Month, DATE_FORMAT(creationTime, '%Y') AS Year FROM view_instance WHERE definitionName = '<name_of_process_definition>' ) AS subqu GROUP BY Year ORDER BY Year;
Minimale, maximale und durchschnittliche Zykluszeit (in h) von Instanzen pro Prozessdefinition
SELECT CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m')) AS Month, IF((UNIX_TIMESTAMP(instanceEnd ) - UNIX_TIMESTAMP(instanceStart))>0, ROUND((AVG(UNIX_TIMESTAMP(instanceEnd) - UNIX_TIMESTAMP(INSTANCESTART)))/3600), '0') AS "Average cycle time", IF((UNIX_TIMESTAMP(instanceEnd ) - UNIX_TIMESTAMP(instanceStart))>0, ROUND((MIN(UNIX_TIMESTAMP(instanceEnd) - UNIX_TIMESTAMP(INSTANCESTART)))/3600), '0') AS "Min cycle time", IF((UNIX_TIMESTAMP(instanceEnd ) - UNIX_TIMESTAMP(instanceStart))>0, ROUND((MAX(UNIX_TIMESTAMP(instanceEnd) - UNIX_TIMESTAMP(INSTANCESTART)))/3600), '0') AS "Max cycle time" FROM view_instance WHERE instanceEnd IS NOT NULL GROUP BY CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m')) ORDER BY CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m')) DESC;
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 AS "Activity Name" FROM view_instance inst LEFT JOIN view_activity act ON act.instanceId=inst.id WHERE act.loopCount IS NOT NULL GROUP BY inst.id, inst.definitionName, inst.instanceName, act.loopCount, act.activityName) AS subqu GROUP BY defName;