Workflow analysis with SAP Lumira [Data Geek Challenge]
In this post I will try to make a brief analysis of Workflow data from running SRM System.
SRM also known as Supplier Relationship Management software provided by SAP.
SRM is a powerful purchasing tool with the flexible functionality for Approval strategies.
Let’s see how it works
Typically after creation the shopping cart goes to multiple level approval process and finally the purchase order to be created. So the shopping cart has to be approved.
Idea is to make an analysis running workflow, determine average approval times, effectiveness of agent determination rules, etc.
Technically are the data of 3 database tables to be used: HRS1000, SWWWIHEAD and SWWLOGHIST.
- Table HRS1000 has an information for workflow tasks – will be used to determine descriptions of Workflow Steps
- Table SWWWIHEAD has main information for workitems (e.g. workflow steps) – will be used to determine processing time of workflow step, workitem status, type etc.
- Table SWWLOGHIST has a log with all actions for workitem, such as “Workitem completed” or “Workitem forwarded to another approver”
Data from these tables will be exported to SAP HANA and there prepared for visualization with SAP Lumira.
For the data export I’ve used the approach described in blog post Simple sql table export in ABAP for HANA.
As a result I have got 105 data files with SQL Scripts for SAP HANA.
After import to HANA got following results.
From this export I decided to analyze one single workflow schema for german shopping carts.
So, as one can see, workflow has 7 steps (step 999 is the automatic system approval). Important for the analysis is, that all workflow steps has it’s own workflow task: 98700005, 98700006, 98700007, 98700008, 98700009, 98700010, 98700015. For the analysis I need only these tasks.
For mentioned tasks an anonymization need to be done.
UPDATE "ZKSH_LUMIRA"."HRS1000" SET UNAME = 'UNKNOWN'; UPDATE "ZKSH_LUMIRA"."HRS1000" SET SHORT = 'WF_STEP_1', STEXT = 'Approval Step 1', MC_SHORT = 'WF_STEP_1', MC_STEXT = 'APPROVAL STEP 1' WHERE OBJID = '98700005'; UPDATE "ZKSH_LUMIRA"."HRS1000" SET SHORT = 'WF_STEP_2', STEXT = 'Approval Step 2', MC_SHORT = 'WF_STEP_2', MC_STEXT = 'APPROVAL STEP 2' WHERE OBJID = '98700006'; UPDATE "ZKSH_LUMIRA"."HRS1000" SET SHORT = 'WF_STEP_3', STEXT = 'Approval Step 3', MC_SHORT = 'WF_STEP_3', MC_STEXT = 'APPROVAL STEP 3' WHERE OBJID = '98700007'; UPDATE "ZKSH_LUMIRA"."HRS1000" SET SHORT = 'WF_STEP_4', STEXT = 'Approval Step 4', MC_SHORT = 'WF_STEP_4', MC_STEXT = 'APPROVAL STEP 4' WHERE OBJID = '98700008'; UPDATE "ZKSH_LUMIRA"."HRS1000" SET SHORT = 'WF_STEP_5', STEXT = 'Approval Step 5', MC_SHORT = 'WF_STEP_5', MC_STEXT = 'APPROVAL STEP 5' WHERE OBJID = '98700015'; UPDATE "ZKSH_LUMIRA"."HRS1000" SET SHORT = 'WF_STEP_6', STEXT = 'Approval Step 6', MC_SHORT = 'WF_STEP_6', MC_STEXT = 'APPROVAL STEP 6' WHERE OBJID = '98700009'; UPDATE "ZKSH_LUMIRA"."HRS1000" SET SHORT = 'WF_STEP_7', STEXT = 'Approval Step 7', MC_SHORT = 'WF_STEP_7', MC_STEXT = 'APPROVAL STEP 7' WHERE OBJID = '98700010';
In HANA I have compiled the data for analysis in SAP Lumira. Data entries are mapped as shown…
Resulting T_WORKITEMS table has been created with the following SQL Command.
CREATE COLUMN TABLE "ZKSH_LUMIRA"."T_WORKITEMS" AS ( SELECT T1."WI_ID", T2."WI_RH_TASK", T2."WI_LANG", T2."WI_CD", T2."WI_AED", T2."TOP_WI_ID", T2."WI_STAT", T1."METHOD", T1."METH_EDATE", T1."METH_ETIME", T0."SHORT", T0."STEXT", DAYS_BETWEEN(T2."WI_CD", T2."WI_AED") as DATE_DIFF FROM "ZKSH_LUMIRA"."SWWWIHEAD" T2 INNER JOIN "ZKSH_LUMIRA"."SWWLOGHIST" T1 ON T2."CLIENT" = T1."CLIENT" AND T2."WI_ID" = T1."WI_ID" LEFT OUTER JOIN "ZKSH_LUMIRA"."HRS1000" T0 ON T2."WI_RH_TASK" = CONCAT(T0."OTYPE", T0."OBJID") AND T2."WI_LANG" = T0."LANGU" WHERE T2."WI_RH_TASK" IN ( 'TS98700005', 'TS98700006', 'TS98700007', 'TS98700008', 'TS98700009', 'TS98700010', 'TS98700015'))
Here is a Result.
After table creation I have exported this table into CSV. Exported File is ready for import into SAP Lumira.
After import I had to rename columns according to it’s meaning. And now is ready for analysis.
Common Workflow Analysis
Selection runs over completed workitems. Distinct number of workitems and average duration selected as criteria.
- Workflow step 1 is not active. Not yet used.
- Most used steps are: 4, 2 and 7.
- Most longest processing runs by the steps 2 and 6. Step 6 is not critical – not very often. Step 2 should be analyzed.
Selection runs over forwarded workitems. Number of forwarded workitems is a measure criteria.
- Workflow step 1. Almost every workitem is forwarded. Sometimes more than 1 time. It means, that agent determination strategy is wrong, or (as it is) all shopping carts are routed to common pool and forwarding occurs according to customer process.
- Workflow step 7 has more than 50% forwarded workitems. Agent determination strategy needs to be reviewed.
Selection runs over completed workitems with the running average over duration.
Conclusions: situation is stabilizing. Not bad – needs further monitoring.
Of course, there are more analysis and conclusions possible. You can make more advanced analysis based on your own data,
P.S.: English language is not my native language, and any person is not insured from mistakes and typing errors. If you have found an error in the text, please let me know – I’ll correct the post.
P.P.S.: If you have some ideas, how to correct/improve this post – please don’t hesitate to leave a comment.