Ralph Kimball

For most businesses the most compelling application of business intelligence (BI) is the 360-degree view of the customer — in other words, a comprehensive record of every transaction made through customer-facing processes. The 360-degree view is particularly potent if causal dimensions can be attached to these transactions. In its purest form, a causal dimension explains what the customer was experiencing at the moment of the transaction or as a result of the transaction. Obviously that is difficult to measure! Since we can't peer into the customer's head, we do the next best thing by collecting as many measures of customer satisfaction as we can. A good marketing analyst will be quite satisfied to know what satisfies, or doesn't satisfy, the customer.

Let's discuss three common design approaches for capturing customer satisfaction indicators: the standard fixed list; the list of indicators that are simultaneously dimension attributes and fact table measures; and the unpredictable, chaotic list that grows constantly over time.

The Standard Fixed List

In some businesses, a reliable set of data sources can be accessed to create a stable, standard set of satisfaction attributes attached to a set of transactions. For example, for an airline, where the transactions come from boarding passes used by frequent flyers, it may be possible to collect satisfaction indicators including:

-- Cancelled flight
-- Delayed arrival
-- Diversion to other airport
-- Lost luggage
-- Failure to get upgrade
-- Middle seat
-- Lavatory problem
-- Unavailable food choice
-- Personnel problem
-- Other

These indicators are not exclusive. Any or all of them can occur, and several of them have more texture than just a simple "Yes" or "No" flag. The recommended design in this case is a standalone satisfaction dimension with each of the above indicators as explicitly named columns. The data in the dimension records should be descriptive words, even when the choice could be "Yes" or "No." Remember that dimension attributes are used as the source of constraints as well as the labels of answer set rows. Descriptive words improve the user interface when building reports or posing queries, and descriptive words make final reports more readable. Finally, descriptive words are much more flexible than Yes/No flags, since new choices for particular satisfaction indicators can more gracefully be added.

Note that this style of dimension design is typical of causal dimensions, which we have used for many years to describe the exogenous conditions in a store such as media ads, price promotions, and competitive effects. This style of causal dimension is always wide, with individual columns reserved for each condition. Unless the Cartesian product of conditions is small and bounded, the dimension records are normally created on the fly as new combinations of conditions are encountered in the marketplace.

Finally, the "Other" attribute at the end of the list is a safety valve for handling unusual satisfaction situations, perhaps involving a free-text description. In this case, a separate comment dimension, also attached to the transaction fact record, should be made available. This dimension contains an empty comment, which is perhaps used most of the time, but has separate unique rows for each idiosyncratic comment that is recorded.