Rule Harvesting Techniques for Excel Workbooks

RE

Robert Eaman

11/05/2019

Why Rule Harvesting Isn’t Usually Easy

Translating someone’s business logic into functional requirements is never a simple and straightforward effort. Existing business decisions must be reevaluated with an eye towards automation, and there are many common challenges we run into when we do this. Here are a few of the most common challenges we have noted:

  • Non-standardized methods are used to describe the logic of a business process; often those methods change depending upon the process being described
  • Some of the logic has not yet been thoroughly analyzed:
    • Dependencies to the logic may not be documented
    • Only a few of the possible scenarios have been accounted for
    • Conflicting logic may exist elsewhere in the document
    • The logic doesn’t make sense in unusual “edge” cases
  • The person attempting to code the logic might not understand the full context of the business process a decision requires

With respect to Microsoft Excel in particular—a very common modality for representing business logic—we have come to realize there actually IS a repeatable set of steps that will help you evaluate and articulate the harvesting process. We would like to offer those steps and a few examples in the rest of this post.

The Main Components of Rules

There are four main components to business rules:

  1. The decision you’re describing
  2. The data model that supports your decision
  3. Any reference data required by your decision
  4. The testing data that validates the logic your decision

All of these assume that you already know—or can make an educated guess at—when and how the decision logic will eventually be executed. We can use the above four bullet points to identify elements of that decision logic. Below are two quick examples of how this process works.

Pro tip: Before we analyze specific examples, we must remember that each of these specific examples occurs within the context of a larger business process. It’s important to use our contextual knowledge of the larger process to guide our understanding of what is happening in any given detailed example.

Example 1

Our first example is concerned with insuring property title documents. We have been presented with a workbook that has various worksheets providing different components of a title processing calculation. So in this specific worksheet, how do we determine which of the 4 main rule components we’re looking at here? What can our intuition tell us from the way the data is presented? What don’t we know and how does that affect our ability to interpret?

Excel Workbook Example 1

What We Know

We know that the data appears to be orderly, normalized, and consistent. We know the data varies depending upon a number of factors. We also see that there are no current gaps in the data.

What We Don’t Know

Unless there is accompanying text or an accompanying document, we do not know what to do with this table of data. There are no instructions to indicate rules, and there are no calculations that indicate logic in action.

Pro tip: In Excel you can use CTRL + ` to toggle between displaying cell values and formulas across the whole worksheet. This is excellent for determining at a glance if there are any calculations in any cells in the entire worksheet.

What We Can Safely Assume

At this point we can take what we know and make educated guesses about what comes next. For example, we know:

  • Based upon the columns, we can infer likely components of the data model as well as their associated data types
  • When certain values are encountered, it is likely we will also encounter (or need to derive) these other values
  • Because no decision logic appears obvious, this is probably reference data that will be consumed in another process. But where can we find that process?

Our next steps at this point are to a) make note of what we’ve discovered, and b) look for opportunities to leverage this information later as we evaluate other parts of our workbook or our decision making process. Perhaps this data table will need to be imported into your rule application’s Data folder for use with a lookup or a query.

Example 2

For our second example, we have a larger rating and underwriting workbook that contains many worksheets related to a specific insurance product. If we consider the same questions that we did in the previous example, what part(s) of our rule harvesting effort could this table represent?

Excel Workbook Example 2

What We Know

Various Classes are listed in column A, and various Questions are listed in column B. Underwriting questions usually must be answered before underwriting is considered complete. If the answer to these questions is true and certain other conditions are present, it appears a Factor should be applied to the base rate for the products named BL or AAL (or to both).

Pro tip: “Factor” is a tip-off word that we’re looking at some sort of calculation that should be applied. This usually means some kind of decision logic is called for.

What We Don’t Know

We don’t know the exact data model, or how we’ll find these underwriting questions. We do not know how to interpret the Condition column, and what data must be interrogated to determine that. We also don’t know how to handle the “territory” question in the first table row.

What We Can Safely Assume

This is probably not reference data. We have logic here, and that logic demands that we interrogate specific elements of a data model to discern whether underwriting questions and other criteria are met. We also know that some columns contain data that differentiates between classes, which implies decision making. Thus we can go back to our business experts and to our data model and begin to ask more detailed questions to drill into what should be interrogated here and what decisions should result.

Conclusion

Business processes and the people that document them can vary greatly. Knowing what to look for and identifying some repeatable steps can help harvest disparate rules from disparate sources. Hopefully these quick tips can help get you started or help you understand your decision artifacts more effectively. And of course, if you get stuck, please feel free to contact the ROAD Services Team at InRule for additional help.