Better BPM – 2




This second post in the Better BPM series on Interaction Profiling presents a simple, generic design for the Interaction Profile metadata that defines the individual profiles required in a business application.   A later post will investigate possible approaches to establishing the profile metadata requirements from a business analysis perspective. This short post introduces the simple designs that form the premise for an Interaction Profiling framework that can be re-used any number of times in custom application development.


The formal definition of an Interaction Profile is:

The set of attributes of an interaction that define a distinct validation and business processing workflow rule set that is required to process that interaction.

We will investigate how to derive the profiles required for a custom application development project in a subsequent post.  Suffice to say, for now, that an application will probably require between 10 and a couple of hundred profiles, depending upon the size and complexity.  The prototype system uses 23 profiles, plus 2 for reporting, as it so happens that Interaction Profiling provides (as a side benefit) an easy-to-use way of doing Ad Hoc Reporting in an application, that can be used by the Users directly.

The formal definition of a Profile Attribute is:

 An application Field (calculated or physical), one or more values of which are required to define a specific Interaction Profile.

The concept will become clearer as we continue to develop our Interaction Profiling framework.  Typical profile attributes are items such as Customer Type, Product Type, Priority, Age in System (how long we have had a transaction in our system) and any other type of lookup field (often drop-down box enumerations in an application).  The prototype has 15 Profile Attributes, only one of which is a calculated field (Age in System). As many as 50 to 200 might be required in a much larged application.  These numbers are insignificant in enterprise database terms.




A specific (as opposed to generic) design for Interaction Profiling could be used for a one-off implementation by identifying the various Profile Attributes required and creating a Profile table that contains one field for each Attribute. While simple (one table only), this is inflexible and not re-usable and so should not be considered. The basic design for a generic implementation of Interaction Profiling abstracts the Profile Attributes into their own table and only four tables are required for the Profile definition metadata. Figure 1 depicts the simple design for a generic implementation of Interaction Profiling.  Click on the graphic for a clearer image.



We see the many-to-many relationship between the Profile (Interaction Profile) table and the Attribute table implemented via the ProfileAttribute table. This design allows for one profile to have many attributes and conversely, one attribute to be used in any number of profiles. This is the most flexible design and is recommended for production use. The child ProfileDefinition table contains the actual definitions of the actual profiles in terms of the Profile Attribute values for the individual profiles. A one-to-many relationship is all that is required for the definition value data. This allows for any number of values (or ranges of values) for each attribute to be specified in the definition of a specific profile. Note that the UniqueIdentifier value range may not be necessary. The prototype from which this design is drawn uses SQL Server, uniqueidentifier, globally unique identifier (GUID) primary keys (the NewSequentialId function that returns Sequential GUID values), which is only really a good idea if the application requires (or may require) data replication for use in disconnected scenarios. The String value range can be used for other primary key values (any numeric, alphanumeric or alpha value range), as the Attribute table specifies the actual SQL data type for the Profile Attribute and input data would be validated as such on data entry. The String, Date and Boolean value fields cover all scenarios that may be encountered in an actual development situation.


Note that the “xxxNullDefault” fields shown on the diagram are used in the profile matching logic to provide a default action in the situation where the actual input data field on a transaction does not have a value (is Null in database term). This enables the User specification of a default depending upon business requirements. An example will clarify this feature. Assuming the system contains an input field called Priority which may assume values Urgent, High, Normal, Low, then a business rule that states, “if the input priority is missing, a Normal priority is to be assumed” can be implemented by entering a Null default value of Normal in the appropriate Profile definition data row. The profile matching logic will apply the specified default when matching the profile definitions against any transaction where the input Priority field is missing (null). This default will only be required on profile definitions that use the Priority field as a Profile Attribute. Note that the “EtcEtc” field is purely a placeholder to denote the possibility that additional information may be required in specific applications. This convention is used throughout this paper.


The diagram shows that a Profile Attribute may be defined as an Application Field (input field) value, or as a SQL query or statement (that returns a scalar result) or as an actual function call. The ApplicationFieldId is actually a foreign key to the ApplicationField table (not shown in figure 1), that is described in detail in a subsequent post. This table contains the metadata for those application fields that have to be validated by the application that is consuming the Interaction Profiling designs, or that serve as a profile attribute. The vast majority of Profile Attributes will be application field values, but the SQL and function options provide ultimate flexibility in the Interaction Profiling capabilities. The prototype contains one function, but it was not actually required and merely included to test out the function calling capabilities of the prototype Interaction Profiling implementation. Note that the Attribute table contains the default application field / SQL query / function call, but this default may be overridden for any specific Profile Attribute definition by entering the overriding information in the ProfileAttribute table. The SQLQuery field is actually a bit of a misnomer, as the prototype allows for this field to contain either a SQL statement that returns a scalar value, or a code snippet or expression that returns a value also. This is very useful and extremely flexible but does require a mechanism, such as Microsoft’s .Net dynamic reflection, that can evaluate and execute source code at runtime. The prototype actually uses Visual Basic for Applications (VBA), which is the language common to all Microsoft Office products and has been around for a couple of decades. VBA includes an “Eval” statement that does exactly what is required and is somewhat simpler to implement than dynamic reflection.


Finally, we clarify the “Precedence” field on the Profile table. The profile matching logic is called whenever the transaction data is changed in some way, as this may well trigger a change in the corresponding profile set for the transaction and the system is about to perform some validation or BPM – related functionality. Given that there may well be (and usually are) multiple matching profiles for any transaction, the design needs to cope with this eventuality when only a single profile has to be identified for processing purposes. This only occurs at the point of validating a single field, when it does not make any sense to have more than one set of validation rules for validating a single field on a specific transaction. The Precedence number is only used if the matching logic produces more than one profile for validating a single field. This occurs in the real world much less often than you might think. From the BPM perspective, multiple matching profiles is perfectly fine and the system needs to identify all of the tasks that are represented by the various matching profiles. This is typically implemented simply and efficiently by a “Select Distinct” SQL statement at the appropriate point.


This is all that is required for the Profile metadata, yet it provides a fully generic and completely flexible basis for the powerful Interaction Profiling framework that we will continue to develop in subsequent posts. The next post, Better BPM – 3, Table-Driven Validation Designs, will present the designs for persisting the actual validation rules in the database in a relatively simple manner that will allow the User community to maintain their own validation rules. This alone will reduce the impact on IT dramatically with regard to application maintenance.


Previous post:   Better BPM – 1, Success through Simplicity

Next post:          Better BPM – 3, Table-Driven Validation Designs


Leave a Reply

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