OBIEE - Value vs Target (or Actual vs Budget) graphs, Stephen Few's way and my

Very often we are required to create an Analysis that compares a value to it's target. In most cases it's called Actual vs. Budget report. In this post we will consider few options how to do it, beyond the naive approach.

 I'll use my Sample Application Revenue and Target Revenue on Month Level for the example (Actually, if you try to recreate it, it is enhanced Sample Sales and I'm using Target+400,000 as Revenue Target, since the Sample sales data is somewhat optimistic).

This is the result of the naive approach: 

What can we learn from it? Very little. Even if we make the lines thinner and focus on graph, it's hard to really see the important parts:

To have some real insight from the data we need to do some work. I will add 2 columns: Variance (Revenue - Target) and Variance as % of Target (100*(Revenue-Target)/Target):

Stephen Few (http://www.perceptualedge.com/) recommends, for example here (pages 29-47), to show only the Variance or % Variance of Budget. Something like this (only one of the 2 options):

----------------- This section is about how I created the above graph in OBIEE, you might want to return to it later. --------------------

To create the first graph (Variance as % of Target) I used the Variance as % of Target column. They are few other things at Graph Properties:

1. At General Tab I've set Legend to None.

2. At Style Tab, in Style and Conditional Formatting I changed the Line With to 2px and changed the Type to Off. (I also prefer to remove the Gridlines, it's a meter of taste).

3. At Scale Tab, in Scale Markers I created a line at Position 0. width 1px (it should be 2px if you are using Gridlines):

4. At Titles and Labels Tab I changed the legend to what you see.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I tried to implement this solution in few cases. Unfortunately the users didn't like it. They understand the problem with the naive solution but insist on seeing the original values as well. They also don't want it in 2 graphs, like this:

So my solution is to combing the 2 attitudes.

I will preserver the original lines of Revenue and Target, but will add the Variance (or % of Variance from Target) as Bar in the graph (this is a line-bar graph).

This is a graph with Variance in bars. 

This is a graph with % Variance in bars (and Gridlines).

I prefer the first one. In % variance I had to manually control the Axis limits in Scale Tab. While in Revenue vs. Target with Variance in bars, all I did was to "Use unified scale".

In most cases it's desirable, if possible, to use unified scale and not 2 Axis graphs. In 2 Axis graphs the viewer has to associate lines and bar with relevant axis. It's not always working, and always requires additional effort.