Automated Process Auditing

NOTE: This originally appeared on this date at Quest Software's ToadWorld, on the expert blog "John Weathington's Quest for Compliance". The link to the actual ToadWorld article is at the bottom.

Are you doing what you say you’re doing?

In my survey of different compliance guidelines and best practices, a common theme always seems to emerge. It sounds like common sense, and it is ( like most compliance issues ), but there is a basic requirement for your documented process to match your actual process. You would be surprised ( or maybe not ) of how off base companies tend to be, when it comes to comparing there “As-Is” process with their “Really As-Is” process.

The biggest problem I find in companies when it comes to process control is their lack thereof. That is, they don’t have a documented process. The second biggest problem I see in companies is a documented process that doesn’t reflect what’s really going on.  In my view, this is much worse than the first problem.

So, you might ask yourself, “How can this happen? How can all this effort be expended, just to end up with a worthless pile of fire kindle?” Usually, this is a result of a bad approach taken to assemble the process documentation. What I’ve seen companies do, is build their process maps solely by interviewing people. They map out a process by asking people what happens, but they never bother to test or validate what is being said! As crazy as that might sound, this actually happens! Worse yet, I’ve seen companies pay accounting firms huge amounts of money to do the same thing. It’s a shame.

In almost all cases, interviewing people to build a process document will yield inaccurate documents. People tend to tell you the way things are “supposed” to happen, and represent that as what is actually happening.
First of all, anything that is said needs to be validated, and assertions need to be challenged. For instance, if you’re trying to document the process of building an order, and the interviewee says, “A quote is always created first, the configuration is validated, then the order is created,” I would look for orders that have no quotes. Or if the interviewee says, “Any order over $100,000 needs to be approved by a Regional Manager,” then I would look for orders over $100,000 that have no approval. You get the idea.

Secondly, the best companies will have a way to monitor this process, so that it doesn’t fall prey to what I call progressive dissonance. Entropy tends to erode the quality of a process over time, especially when people are involved. People can get complacent and reckless, or just lose interest after the “newness effect” wears off.
This is where you come in, as a database guru. Leveraging technology, your company can not only monitor their processes, but they can monitor it in an automated fashion that doesn’t draw on resources other than bits and bytes. Here’s one way to model an automated process auditing system.

Step # 1: Model the Process

After interviewing the appropriate people, and validating the process by testing assertions, have the “Really As-Is” process documented. Don’t get too crazy with detailed process flows. Just try to keep it as simple as possible, while still being effective for control. Your process will have two basic components:

•    Activities – These are actions that are taken as process steps. Examples are “approve order” or “validate configuration.”

•    Edges – These are connectors between activities. Edges may have conditions or “guards.” If two activities are connected together, and there are no guards, then when one activity is completed it immediately flows into the next activity. If a guard exists, it will prevent the activity flow, usually based on a condition of the payload ( see below for explanation of payload ).

To model this, we can simply create three tables:

•    ACTIVITIES – Contains all the activities in the process

o    ACTIVITY_ID – Internal identifier
o    ACTIVITY_CODE – Business process identifier, used in documentation
o    ACTIVITY_DESCRIPTION – Description of the activity ( i.e. “Approve order” )

•    ACTIVITY_EDGES – Contains the activity edges

o    ACTIVITY_EDGE_ID – Internal identifier
o    ACTIVITY_CODE_FROM – From activity
o    ACTIVITY_CODE_TO – To activity
o    GUARD_ID  ( nullable ) – Guard, if one exists

•    GUARDS – Contains all the guards on the activity edges

o    GUARD_ID – Internal identifier
o    GUARD_DESCRIPTION – Description of the guard

After the tables are created, populate them with data that represent the “Really As-Is” process.

Step # 2: Capture the Process Activities

Now that the process is modeled, you need a way to capture when activities happen. As the process executes ( i.e. an order is being processed ), a process instance is created, and a payload is attached. A payload is the collective artifact that flows through the process. Conceptually, it could be the order, the invoice, or the receipt. Logically, it’s just a collection of data. Some of the data may be purely informative, but if there are conditional guards on the activity edges, that data needs to be present in the payload.

To model this, create three tables:

•    PROCESS_INSTANCE S – All the process instances

o    PROCESS_INSTANCE_ID – Internal identifier
o    PROCESS_INSTANCE_DATE – Date / time process instance started

•    PROCESS_INSTANCE_ACTIVITIES – Records activities as the process is executed

o    PROCESS_INSTANCE_ACTIVITY_ID
o    PROCESS_INSTANCE_ACTIVITY_DATE
o    PROCESS_INSTANCE_ID
o    ACTIVITY_ID
o    PAYLOAD_ID

•    PAYLOAD – Payload data ( i.e. invoice, order, etc. )

o    PAYLOAD_ID
o    ( varies depending on data that needs to be collected )

It’s okay to violate normalization rules by putting PAYLOAD_ID at the INSTANCE_ACTIVITIES level. I find it’s more convenient that way.

A key thing to remember is that process activities are captured in the PROCESS_INSTANCE_ACTIVITIES table, without regard for the documented process model created in Step 1.

Step # 3: Validate the Process During Execution

As the process is executing, compare the PROCESS_INSTANCE_ACTIVITIES data with the documented process from Step 1. You want to make sure that subsequent rows in the PROCESS_INSTANCE_ACTIVITIES table are in alignment with the data in the ACTIVITY_EDGES table. Guards may get a little tricky, but only a little. For instance, if there’s a guard that says, “Go this way only if order amount is greater than $100,000,” then you need to check the payload’s order amount to make sure the navigated path is compliant.

Step # 4: Highlight Process Activity Violations

If something in the PROCESS_INSTANCE_ACTIVITIES table doesn’t line up with the ACTIVITY_EDGES table, you have a process activity violation. The final table in our model, is the table that highlights this situation:

•    PROCESS_VIOLATIONS – Process violation table

o    PROCESS_VIOLATION_ID
o    PROCESS_VIOLATION_DATE
o    PROCESS_INSTANCE_ACTIVITY_ID

In this way, a simple query against this table will highlight all process activity violations. You can even design an event trigger that notifies somebody when a process activity violation occurs.

Keeping grounded in how your important processes are flowing, is a critical component of most compliance programs. Building an automated auditing system for your process is an effort worth considering. Building the architecture for such a system can be broken down into four simple stages: Model, Capture, Validate, and Highlight Exceptions. Talk to your auditing team today about how this architecture might add value to their operation.

...read the article on ToadWorld