A few years ago, I was an executive at a large health care company. Every month, the officers and directors would meet to discuss matters of strategic importance: new product concepts, innovative marketing concepts, achieving customer intimacy. One month, in the midst of our strategy session, our non-technical president blurted out an interruption to these lofty discussions that deflated our balloon of global dominance--"Before we launch another product strategy, just tell me how many members do we have in our health plan?" This simple question yielded an answer from each department--but each department had answered with a different number.

Stumbling Upon Hidden Business Rules

The president's focus then turned from curiosity to frustration--"How can we run a billion dollar company effectively when we can't even agree on something as simple as the number of members (or customers) we have? Why does each department executive give a different answer?" The answer is that each executive calculated the number of members based on a hidden business rule that they didn't explain with the answer. Marketing counts a member as soon as a customer proposal is approved. Sales counts a member after the first premium payment, and underwriting counts a member as soon as the health plan is at risk to provide payment for a claim. Each scenario has a different business rule to arrive at the "correct" answer.

This example points out a key difference between OLTP systems and OLAP used in a data warehouse (DW)--a difference that is important to building a DW solution. In OLTP systems, administrative business functions and rules are predictable and well defined. In a DW used for OLAP, many of the information requests are not known in advance and, as such, are not well defined. In fact, data mining in the DW may involve business rules and calculations that can only be discovered "on the fly."

Business Rule Discovery

A DW will discover business rules normally in two places: data transformation and data access. Data transformation occurs first--mapping the source systems to the target DW tables. A word to the wise: Never assume that source data has integrity. We once built a DW that calculated the average length of stay (ALOS) in a hospital chain, something that was difficult to do without a DW since the data had to be combined from many disparate systems. The initial calculation produced an ALOS of negative five days. The DW was a piece of junk, or a "data outhouse," until it was discovered that the source system was passing bad data which caused the problem. How?

The DW calculated the ALOS based on a business rule ALOS=SUM ALL DISCHARGE DATE - ADMIT DATE. This worked fine, except in some cases, when the DISCHARGE DATE was not entered in the source system, even though the patient was discharged. The source system defaulted the DISCHARGE DATE=Blanks. Subtracting any ADMIT DATE from a blank DISCHARGE DATE yielded a large negative number, which skewed the total results. We added a business rule to the data transformation layer to check the integrity of the source system values prior to loading the DW. As Ronald Reagan said during the nuclear disarmament talks, "Trust, but verify."

Data access business rules discovery should begin with a definition of the basics of the business. How does everyone define total sales? Seems straightforward--the dollar amount of products shipped to customers, right? Well, usually, but should we exclude returned items? Okay, then TOTAL SALES=PRODUCTS SHIPPED - RETURNS. What about checks that bounce? Then TOTAL SALES=PRODUCTS SHIPPED - RETURNS - BAD DEBTS. And what if someone gives away some products as a promotion? Then TOTAL SALES=PRODUCTS SHIPPED - RETURNS - BAD DEBTS - PROMOTIONS. When describing total sales, the explicit business rules should be stated.

Better Living Through Business Rules

One of the primary goals in building a DW is to provide easy access to integrated information. However, if two people ask a similar question and get different answers, then no one will believe or use the DW. The discovery, encapsulation and sharing of business rules make the use of the DW more predictable, accurate and efficient for providing information to the enterprise. And most importantly, when two users ask a question of the DW, they will get the same answer--based on the business rule that is used to resolve the question.

Next month--more specifics about putting a data warehouse/data mart plan together.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access