Better BPM – 10





This tenth post in the Interaction Profiling series focuses on the SOPS Interaction-Profiling-enabled prototype system functional forms for BPM Task and Activity maintenance and illustrates some of the output reporting capabilities of SOPS that are enhanced by Interaction Profiling features.


Sales Order Inbox


This form (click on the graphic to view a clearer image) is used by Supervisors and Order Processing personnel alike. This view is that of a Supervisor who can see the work for anyone in their span of control. The SOPS prototype application does not actually have this functionality built in, but the Interaction Profiling paradigm enables this capability with the existing metadata.  A production implementation would have this ability included and it would not be hard to implement – just an additional field on the User table for a “Manager” foreign key field and a bit of additional logic in the stored procedure that populates the data grid. The legend explains the colour coding the users requested – Urgent orders at the top in red, followed by yellow overdue orders, then orange for Out of Stock, back-ordered Sales Orders. The remaining orders are then listed in ascending target date sequence, so the more critical appear first.  A form like this could be displayed automatically on signing in to SOPS, so Users can see their priority workload at a glance.


This form can be used to allocate / reallocate tasks to any of the orders. Just as for allocation of orders to Users on the Sales Order form, the Supervisor has the ability to specify a specific Task (and optionally a User), or simply allow SOPS to determine the next Task automatically for them (which is the normal case). If a specific Task is specified, SOPS checks that it is valid for the profile set that currently applies to the interaction instance. The User can update the order status in this form and can launch either the Sales Order form (previously illustrated), or the Tasks log which is shown below, for the selected Order in the grid, depending upon what changes they want to make to the order information.  The form offers the User various filtering capabilities using the dropdown boxes and check boxes at the top of the form.  The most important matching profile is displayed in the grid and of course a profile dropdown is included as one of the possible filtering criteria.


Sales Order Task Log


This is the form used to manage the specific business processing tasks for the Sales Orders. Please click on the graphic to see the details clearly. Tasks can be allocated / reallocated and SOPS will automatically determine the appropriate task, unless the User decides to override the default allocation at either the User or the Task level.  The Task Log form is a “split form” where the user can select a row in the bottom grid and see all the fields in the upper portion of the form, some of which can be edited.


Note that the Task database table and the ProfileTask table both contain a “UseSameUser” boolean field. If set to True for a specific Task (which is the default), SOPS will allocate tasks in sequence to the same user already working on the preceding task for the Order, as long, of course, as that User is authorized to perform the new task. This gives Users the option (by setting the UseSameUser flag to False) to force SOPS to pick the least loaded User from all of those Users authorized to perform the new task. Typically, this would be used in those cases where the next task is performed by a different organizational unit from the one processing the current Order task. Optionally, SOPS Users can decompose Tasks into constituent Activities, although this may not be required in practice ( if it is considered too granular and complicates operational management, depending upon the characteristics of a specific business). In the prototype, the last Order Processing form in SOPS is the Activity Log, which can only be reached from the Task Log form described earlier and is shown below.


Sales Order Activity Log Form



This form, figure 14, is the Activity Log used to manage Task Activities in detail.  The activities for the specific Order Task selected in the Task Log, from which this form is launched, are shown and can be edited and inserted here. The Activity dropdown in the grid only shows those activities specific to the Task indicated at the top right of the form. These Activities are determined by the content of the TaskActivity database table for the specific Task concerned. Of course, the Interaction Profiling concept defines the set of Tasks for each Order as part of the functionality of the Task Log form.  A User can select a Task Activity from the dropdown in the grid, Start date will default to today’s date. The User can select “Completed” from the Status dropdown and SOPS will enter the completion date, so it is simple and quick for a User to update the individual activity status as the work is performed.


Report Filtering Capabilities


When running any of the built-in SOPS reports, if the User chooses a report from the Reports menu that requires parameters, the report filtering parameter form is displayed:



Ten of the fifteen reports currently available in the SOPS prototype use the above form to filter the report content. The actual filtering capabilities of course depend entirely upon the Users’ reporting requirements and it is up to the individual designer to adapt this approach in any application implementation. Nevertheless, the options available in this basic filtering form are quite powerful and when combined with the “slice-and-dice”, “drill-down” pivot chart reporting features that come free with Microsoft Access when used as a GUI for a SQL database, provide a truly impressive management tool. This is purely my opinion of course, but to some extent you can judge for yourself after reviewing the material in this Post.


On the filter form, the User can specify  a date range and various other criteria which will then apply to the execution f the subsequent report.  This form is one of the forms in the framework (if included, which it should be) that will require some customization between applications, as the application field filters will clearly change from application to application. With regard to Interaction Profiling specifically, the Profile dropdown criterion allows the User to restrict the report content to a specific profile. This is important, because the Users can define profiles that are specifically only used for reporting. This gives the User almost an Ad Hoc reporting capability, given the power of the attribute specification that is available in SOPS for profile definitions. As an example, I created a Reporting profile for Retail Clients with Orders in excess of $500 total order value. I used two attributes – TradeCategory set to retail and OrderValue set to $500+. Another Reporting Profile allows reports to be restricted to Walmart orders only that have been over 60 days in the system. The Client and AgeInSystem Attributes were all that was required for this ad hoc request. These filtering capabilities are not that extensive, but the IT support requests for new or modified canned reports will be greatly reduced nevertheless by the functionality provided and that is our goal as designers and developers – to give the Users what they need.


There is one minor wrinkle in using Profiles for ad hoc reporting. The reports do NOT re-evaluate all Orders to determine the very latest profile state every time someone requests a report, because it would be too onerous on the system. The latest static data in the actual SalesOrder and SalesOrderProfile tables is used for reporting based on profile criteria. This means that if a new reporting Profile is created, the static data has to be updated prior to producing the report or it will not reflect the new profile. SOPS includes two functions for this that can be invoked from the Lookup maintenance menu – Re-evaluate ALL Current year Order Profiles and Re-evaluate Current Year Orders REPORTING Profiles. These options only take a few seconds to run actually on my laptop (which is also running the SQL database server etc.) In production, I would probably create a small batch job to perform this updating periodically throughout the work day. Now let us see what kind of reports are available in SOPS that avail themselves of the filtering capabilities.


Pivot Chart Reports


Microsoft Access, when used as a front-end GUI provides an impressive reporting capability. There is a very decent report writer available for the typical textual reports, plus a very powerful Pivot Chart / Pivot Table / Datasheet form capability that provides three different formats for inspecting the report data. The three formats are in the form of a “Drill Down” capability, starting with the graphical, Pivot Chart view that is identical to that provided in Microsoft Excel Charts. The Pivot Table view shows the summary data behind the chart in Pivot Table format and the detailed Datasheet view presents the underlying data in its raw form in a data grid.


What is impressive about these Pivot Charts is the highly interactive nature of the forms themselves. It takes a little bit of practice (just as in Excel), but an amazing analytical capability is available simply by dragging and dropping field elements of the underlying data to different areas of the chart. Combining this with Report Filtering, Profiling and the Ad Hoc Reporting Profiles empowers Users to a degree that you rarely see in a production application. The slice and dice and drill down capabilities make it easy to display the data in several different formats and styles and anomalous data can be investigated by drilling down into the more detailed views to investigate the individual data rows themselves. Here are a few example screen shots of the Pivot Charts in SOPS. The data is test data only for illustration purposes.



Please click on the graphic to view a clearer image. Let us take this pivot chart piece by piece. The Field list shown on the left is not normally displayed unless you want to change the data content in some way, but I show it in the example so you can see the specific fields available for analysis. The fields shown in bold in the list are already in use in the chart and the others are available for addition, or replacement of existing fields. This field list is simply the fields in the apHKAWPI_SalesOrdersRpt stored procedure that is the data source for the report form. The content of the field list therefore is completely up to the developer of the report. Generally, the more fields the greater the functionality of the pivot form.


The value axis is the Total Order Value (total sales). There is a “# Products” field that could be used instead as the value if the User wishes. So this one form can display an analysis of either total sales or total number of products. The X-axis is actually Trade Category broken down by Customer Type (two fields – one nested inside the other). The Y-Axis is simply the Sales Representative – three persons in my limited test data. It is important to realize that these fields for the X and Y axes can simply be dragged and dropped either from the Field list directly if it is visible, or from the Field Set area at the top of the chart. The Field Set is initially populated in the same manner by dragging and dropping fields from the field list to the field set area.


The Field set area fields enable “slicing and dicing” by clicking the small dropdown arrowhead to the right of a field set name and selecting from the dropdown list one or more specific values which are displayed. All distinct values that are in the data are listed, so this becomes a very useful feature for analysis purposes. Dates can be combined by Month, Quarter and Year at the Users choice. Any combination of checked entries in any field set can be combined with any other checked entries in any other field sets. This makes this one chart capable of answering numerous questions a user may have about the underlying data. In my experience, power users and any users familiar with Excel charting capabilities will love these pivot chart tools for analyzing their data. It certainly further reduces significantly the number of requests the IT support group will get for new and or modified, canned reports – the more so when combined with the filtering capabilities already provided in the Report Filter form. This same chart can be displayed in pivot table and in datasheet formats (top left View dropdown), as shown in the next two graphics.

Pivot Table:





As you can see, the interactivity, power and flexibility of these Pivot Chart report forms is incredibly useful to experienced Users, or those given a bit of special training in their use. Note that one of the Field Set fields is the Profile, which is completely User-definable (Reporting Profiles), so the analytical capability is quite mind-blowing. The imagination of the User is literally the limit.


The Pivot Chart is merely the first in the SOPS ten reports that are Profile and Report filter enabled. So let us quickly illustrate a few of the other prototype examples. The Sales Orders Products Pivot is similar to the preceding pivot chart, but displays total sales (or total products) by Sales Representative and Type of Product:SalesProductsPivot1_MISQ


Here are excerpts of a few of the simpler, canned text reports that indicate the kind of information you can get out of a system that uses the Interaction Profiling design paradigm.

This simple bottlenecks report extract shows the Users’ workload from heaviest-loaded to lightest-loaded. This is possible because SOPS “knows” who is working on what tasks and how long each task should take. This is just one example. The same information could be used to derive many other statistics relating to productivity and workload, some of which are exemplified below.


The Overdue Orders report (below) uses the task duration information to derive a list of Orders for each User that have passed beyond their target duration in the system (i.e. are overdue). The Urgent and High Priority orders are highlighted in Red and Yellow respectively.



The next example below illustrates the “Bring Forward” capabilities of Interaction Profiling in terms of notifying Users of what is targeted to be completed in the next week.ImpendingTargets_MISQ


Obviously, this kind of information could be made User-specific automatically or by the Users selecting their name in the Report Filter form. The Impending Targets report could be displayed at login or in summary form in a dashboard, that is often a good design for Users when they first invoke a system. I like dashboards if they are well-designed, not too resource intensive and contain summary information that is directly useful to the User. Impending Targets meets these criteria, as does productivity information in summary form, notification of serious problems etc. depending upon the User’s role and business characteristics.
Figure 23_MISQFinally, let us look at a couple of other examples of Pivot Chart displays using the Interaction Profiling approach. The above pivot chart shows the completed orders sales in terms of number of orders and Total Value (in thousands of dollars in this example). Strictly speaking, this example, as shown, only uses Interaction Profiling in terms of filtering interactively using the Profile dropdown at the top, or dragging and dropping it to one of the axes to display an analysis by Profile. This is of course significant because the Profile classification is completely User-managed and almost entirely User-maintained. This example also shows the little tooltip that can be displayed by hovering your mouse over one of the columns in the chart. The information indicates the actual values relevant to the specific column selected. Here is a final example of the kind of graph that is often useful in quarterly or annual reporting documents.

This report breaks down the Total Sales for the period specified (quarter or Year) by Trade Category and Product Type. Of course these axis metrics can be switched in seconds to any of the FieldSet variables displayed at the top of the pivot chart. Again, the tooltip message is shown (displayed by hovering the mouse over the largest column in this example). I hope you can see how the use of Interaction Profiling as an approach, when combined with powerful technology such as these pivot chart forms, can provide amazing analytical capabilities for the Users and surprisingly little special training is required for Users to benefit from these techniques. Pivot charts just like in Microsoft Access are also available in Excel (where they originated) and in SQL Server Reporting Services (SSRS), which is now ready for prime time and provides similar capabilities for browser-based applications, as opposed to using Microsoft Office products on the client workstation. The next post presents the application metrics for the SOPS prototype and analyzes the portion of SOPS that represents a re-usable Interaction Profiling framework..


Previous post:  Better BPM – 9    Profile Metadata Maintenance

Next Post:         Better BPM – 11  Prototype Design Framwork Scope and Metrics


Leave a Reply

Your email address will not be published. Required fields are marked *