Daily Archives: February 5, 2007

Spend Analysis V: New Horizons (Part 1)

Today I’d like to welcome back Eric Strovink of BIQ who, as I indicated in part I of this series, is authoring the first part of this series on next generation spend analysis and why it is more than just basic spend visibility. Much, much more!

Many of the limitations of spend analysis derive from its underlying
technology. As I’ve discussed in previous installments, the extent to which spend
analysis can be made more useful to business users is often the
extent to which those limitations can be hidden or eliminated.
In essence: an analysis tool is useful to business analysts only
if business analysts actually use it.
Which means that there is a
fine line that vendors must walk between delivering
technology to business users, and shielding them
from it — without going too far and creating an
unnecessary vendor dependency.

In this installment and the next, we’ll look at a few advanced features that aren’t
necessarily available today, but that should be
possible to provide in future, without crossing that line.

Meta Aggregation

By definition, a spend transaction contains the “leaf” level of a
hierarchy only. Consider the following:

   HR Consulting
         Mercer
         Deloitte
   IT Consulting
         IBM
         Accenture
   Management Consulting
         KPMG
         CGI

Low level transactions typically contain “Mercer” or “CGI,” but
not “IT Consulting” or “HR Consulting,” because those intermediate
hierarchy positions (“nodes”) represent an artificial organization
imposed by the user, and have no reality at the transaction level.

Suppose, though, that I’d like to be able to treat intermediate nodes as though
they had reality inside the transaction set itself. Simple example: I’d
like to derive a new range dimension based on the top level of the above
dimension. I want to know which consolidated groupings are at $0-$100K,
which are at $100-$500K, and so on. I don’t care about IBM or
KPMG any more; all I care about is aggregating my own groupings.

In mathematical terms, I’m asking for f(g(x)) — the ability to apply
dimension derivation to a previous aggregation step;
and, inductively and more generally, to do the same to the meta-aggregated dimension itself.

In OLAP implementation terms, I’m asking the engine to treat the
intermediate nodes from any dimension, at any hierarchy level, as virtual transaction columns
rather than as dimensional nodes.
The problem is, intermediate nodes aren’t static; they’re changing all the time. That means
a dimension derived on artificial rollup values must be
re-derived whenever the hierarchy of the source dimension is altered; and, since hierarchy
editing must be a real-time operation (as I have argued in this series and
elsewhere),
the dimension derivation must also be performed on-the-fly.

Tricky as this might be to implement, the logic is easy to specify from the business
user’s perspective. The user simply picks a previously-defined dimension and a
hierarchy level on which to base his new dimension, and he’s done.

Visual Crosstabs

The utility of Shneiderman diagrams (or “treemaps”) to display hierarchical information
is well known; click the thumbnail or here for a live example.


The treemap is useful because it is visually intuitive; in this example, the relative sizes
of the rectangles represent the relative magnitude of spending. The colors indicate
relative change in spending; red is bad, green is good; lighter green is better. Inner
rectangles show the breakdown at the next level of the hierarchy.

Clicking inside one of the white-bordered rectangles
provides an expanded lower-level view of the hierarchy; clicking the
up-arrow button moves back up a level.

Now, suppose that rather than the inner rectangles showing a lower level of
the same hierarchy, instead they showed the breakdown of spending
within another dimension entirely — i.e., a “visual crosstab.” The visual crosstab
would not only show magnitudes, but trends as well.

Unlike with meta aggregation, where the user interface is simple and
the implementation complex, here the user interface is complex and the
implementation fairly simple. The utility of the visual crosstab will
depend strongly on the user
interface — for example, how does the user change the resolution of
the outer dimension to a different hierarchy level? What might that do to
the level of the inner dimension? How might the user invert the view,
so that the inner dimension becomes the outer, and the outer becomes
the inner? Globally, how
can the user be kept aware of what’s being viewed/inverted/clicked, and therefore
be able to make sense
of the result?