Multidimensional Mandates

Businesses need to know what's up. They also need to know what's down; and they need to know why. Following is a made up example of "OLAP Questions" to which executives need answers and insights.

An "OLAP question" is a question about how useful aggregates relate to other useful aggregates, across any needed dimensions, at any level of any hierarchy. An OLAP question, for example, is not, "How many pairs of shoes did we sell to Mrs. Y in Portsmouth on January 24th". OLAP questions are like those below. They are inherently multidimensional, hierarchical, and about aggregated and related measures.

In the following, every time the word “by” or “for” is used, the next word is a dimension.

• In the Asia Pacific market, by month, are sales increasing, or decreasing?
• Is profit increasing or decreasing?
• How are sales going by product?
• Which shoes are selling better? If they are selling better, am I selling more but making less?
• Give me profit by product by store by month by employee.
• Actually, now that I am thinking about it, add it all up for me for all of Asia, and give me total sales and total profit for Asia and compare it to Europe, Americas, and Middle East, by quarter for this year so far and for all of last year. And give it to me right now.
• And since I now see that total profit for Americas for all products, was terrible last quarter compared to the rest of the world, let me double click my mouse and drill down to see what products were terrible, for what months, for what stores.
• And show me a graph. And show me right now.

OLAP reporting tools permit an executive to ask these questions interactively and "fly through" their data up, down, and sideways. Arbor Software called this "Analysis at the speed of thought" for their Essbase product. Each answer fosters a new answerable question without going outside the model.

This is what data marts do. They answer any summary question in real time.

With information like this, in the right form at the right time, you can know where you are going, where you aren't going, and where else you can go.

Querying this way is simple. I have found that there are only three constructs used in OLAP queries, interactively on the fly:

1. Select (Essbase calls this a "Keep Only", it isolates what you're interested in.)
2. Pivot (swap columns and rows)
3. Drill (up or down any hierarchy)

Using just these three reporting concepts, any question in the model can be answered.

An OLAP query is approximately one page of output. I have challenged managers to explain the business meaning of 25 pages of reporting, and so far no one can. Tightly targeted queries in real time, which is what OLAP delivers, are powerful ways to wring insight from information.

Another Example

This example is designed to show that multidimensionality is part of our common experience. In spite of the perhaps forbidding vocabulary, much of our experience can be analyzed in multidimensional views.

Suppose that you buy an ice cream cone at a corner store.

• It costs $4.43
• The date is a March 25, 2008
• The store is in Middletown, CT
• You planned to pay only $3.00
• and you pay by cash (not debit / credit card).

Our common experience occurs within categories or lists of possible values.
Each of the facts above fits into a list or category.
Price could be in a category of ‘EXPENSES’ (like in Quicken)
March 25th lives within a TIME list.
Hartford is GEOGRAPHY.
Your plan to pay could be in a PLAN list (like budget in Quicken)

The categories or lists or factors of this transaction have come to be called dimensions in business analytical thought. Below is what could be called a "dimensional map" (not a standard term) of the event.

Notice there is a geography dimension with a hierarchy, since cities roll up to states. There is a time dimension, with months rolling to quarters, payment type, even a budget dimension, since you planned (budgeted) to spend $3.00 for the ice cream (and it actually cost more.)

Below is a spreadsheet view of this transaction, highlighted in yellow, and other purchase transactions also. (Notice the budget line for ice cream in Middletown!). Notice also the summary total row for your lumber budget for the whole year.

With this data in a data mart you could ask questions like, "How much did I spend on lumber in March this year compared to last year? How much did I spend compared to what I planned to spend? And so on. Much more complicated questions could be designed, for example, if I paid cash for lumber and saved the 3% does this offset the price increase of lumber over the same time period last year. You might not think at this level of detail in a home budget, but businesses do, and more.

This is multidimensional thinking.