Nothing irritates me more than redoing BI reports because of an unclear specification. This is a total waste of time that I try avoid at all costs. Decision tables are a simple tool to facilitate effective communication of report logic between business users and BI developers.
How many times have you sat through a meeting that followed the script below. The meeting is discussing which customers to include in a report.
- Business user 1 – If the customer has total purchases greater than US$3 000 then include him. If he is a doctor or lawyer from Gauteng province, the financial capital, then also include him.
- Business user 2 – Doctors from the Northern Cape province do not make as much money so they should not be included. Restaurant owners in the Western Cape should be included as it is such a major tourist destination.
On further probing you find out that the user actually meant that if a province has a population of less than ten million then exclude low value doctors and lawyers.
At this point the discussion digresses to whether other professions should be considered (no conclusion is reached, a decision is made to have another meeting to unpack this point further), the weather, how lawyers overcharge and invariably one of the meeting attendees will share their lawyer story. If you are lucky enough to have Business Analyst (BA) to write the report specification document, good for you. If on the other hand you have the task of creating a report resulting from the meeting described above then you have to find a way to make sure that what you are developing is what your clients, the business users, actually want.
I have found that the simplest way to capture the logic to be used in a report is to use a decision table. Below is my particular variation of a decision table for the scenario described above.
| Province(population) | Doctor | Lawyer | $3 000 | Restaurateur |
|---|---|---|---|---|
| Gauteng (12M) | Y | Y | Y | N |
| Northern Cape (1M) | N | N | Y | N |
| Western Cape (6M) | Y | N | N | Y |
The decision table results in the CASE statement below. I would wager it is far easier to explain the decision table to a non-technical person than it is to explain the CASE statement.
CASE
WHEN total_purchase > 3000 THEN 'Y'
WHEN province_population > 10000000 AND customer_occupation in ('Lawyer', 'Doctor') THEN 'Y'
WHEN province_name = 'Western Cape' AND customer_occupation = 'Restaurateur' THEN 'Y ELSE 'N'
END
My sample CASE statement is child’s play compared to statements I have written and inherited. Decision tables give a clear translation layer between business users and developers and you don’t need special software to create the decision table. Any spreadsheet application or word processor should be sufficient. Using an Office application gives you the added advantage of being able to ask the business users to modify the document thus getting the specification straight from the horse’s mouth.
I find it ironic that in Business Intelligence (BI), an industry that spends so much time trying to create easy to understand data visualisations we don’t spend much time visualising the processes and logic needed to create the data visualisations. Decision tables reduce the broken telephone effect result in more relevant and accurate BI implementations.