OBIEE - Local minima and maxima in graph

Local minima and maxima (extrema) in graphs emphasis the points where the graphs change the trend. For example (green dots are the local maxima and red dots are the local minima):

How to achieve it in OBIEE?

Assuming local maxima is the point that is higher then the 2 neighbors it has (value on the left and on the right), and local minima is the point that is lower then the 2 neighbors. And assuming most cases are to do with time progress; I can easily find them using PERIODROLLING function. For example, I created an Analysis based on Sample  sales with 2 columns: Month and Revenue.

To the 2 columns I'll add local minima and local maxima calculation. In this case local min is:
case when 
        "Base Facts"."1- Revenue"<PERIODROLLING("Base Facts"."1- Revenue", -1 ,-1) and 
        "Base Facts"."1- Revenue"<PERIODROLLING("Base Facts"."1- Revenue", 1 ,1) 
     then "Base Facts"."1- Revenue" 
     else NULL 
end

To make it more readable what that means is:
Case when 
               "Revenue"<"Revenue one period ago" and 
               "Revenue<"Revenue next period" 
         then "Revenue" 
         else NULL 
end

The same goes with local maxima, anly use > instead of <.

So the result is:

I used conditional format on the min and max points. (Please note it's a simplistic calculation that omits the first and last points in a graph.)

Now, in "graph properties" -> "Style and Conditional Formatting"  I can create a graph with 3 lines, where the first line is line and the second and third are symbols only:

and the result is:

Something similar can be created with Line Bar graph:

(I created a unified scale and changed the colors).

The funny part is, after I did this I had a complaint there are too many local extrema points. And was requested to mark only the point that are lower / higher then 2 neighbors in each direction:

The result is:

And the function is:
case when 
    "Base Facts"."1- Revenue"<PERIODROLLING("Base Facts"."1- Revenue", -2 ,-2) and
    "Base Facts"."1- Revenue"<PERIODROLLING("Base Facts"."1- Revenue", -1 ,-1) and 
    "Base Facts"."1- Revenue"<PERIODROLLING("Base Facts"."1- Revenue", 2 ,2) and 
    "Base Facts"."1- Revenue"<PERIODROLLING("Base Facts"."1- Revenue", 1 ,1) 
  then "Base Facts"."1- Revenue" 
  else NULL 
end
And the full picture:

By the way. A very important information in line graph analysis is the Linear regression. Here are 2 post on the subject:
By Kurt Wolff, that should work on any data source.
From this blog, specific to Oracle DB functions. 
.

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.
   

OBIEE - Better looking Bar Graph

The basic aim of this post is to change the default bar graph, for example this:

to this:

You might have seen the Darkhorse Analytics post "Data looks better naked" and especially this gif: http://darkhorseanalytics.com/blog/wp-content/uploads/2013/08/data-ink.gif. I will follow similar guidlines.

When looking at the basic graph, 

We see the Revenue appears twice. It's redundant information. We can remove the title in the Graph properties:

And get this result:

But I prefer to give significant title (In this case "Revenue by Product Type") and remove both the axis titles (uncheck both the "use XXXX name as axis title"):

The result looks cleaner:

I prefer to make the header lighter:

Now it's time to change the bars interface. In graph properties - > Titles and Labels we can edit the Data Markers and change the Display Option from "Default (On Rollover)" to "Always": 

In most cases we don't really need the gridlines, they are just noise. Under Style I will remove the Gridlines (by changing from "Default" to "Specify"):

Last part is to Hide the Vertical Axis labels:

I like the result: 

But I think this would look better as horizontal bar and I prefer the Cylinder over the default Gradient. It looks cleaner:

 

I prefer to remove the animation. It's great for few first times but later becomes a bit annoying.

And if you follow this post, by Paul Cannon "Globally disable chart animations in OBIEE 11g" you will learn how to disable the animation globally and on the way remove the shadow from graphs.

(in few words: change the first line of the file  dvt-graph-skin.xml that is located under the s_blafp\viewui\chart\ folder for the pre 11.1.1.7 OBIEE and in 11.1.1.7 at s_FusionFX\viewui\chart 

From: <Graph>

To: <Graph visualEffects="NONE" animationDuration="0">

)

The final result is:

OBIEE - Sorting Graphs / Charts

Lets talk about sorting graphs in OBIEE. I will cover 3 options:

1. Basic sorting.

2. Sorting using Pivot.

3. Sorting by the Bar total.

1. Basic Sorting

I created a simple analysis, with the Revenue sorted descending.

The Table is sorted by Revenue but the default bar-graph is not. It's sorted by the horizontal axis. In this case, "Product Type".

Apparently, it's because of the "Per Name Year" column, that is in the "Vary Color by Horizontal Axis" Section above the Measures.

Once I Exclude it, the sorting seems fine.

 

When I place it ("Per Name Year") in the "Group by" part,  the sort is broken by it. That is an expected behavior.

Unfortunately, placing the "Per Name Year" in Graph Prompts or Section, spoils our sort again:

What can I do to have a complete control?

To use Pivot with Graph and select "Graph Only" in the presentation Position.

2. Sorting using Pivot

 

Now any sort I do in the pivot, is reflected in the Graph. I can hide the Pivot Table and show "Graph Only":

 For the previous example, when I sort the pivot by Revenue, it's reflected in the graph:

3. Sorting by the Bar total. 

Unfortunately, even when working with pivot, the default options are not always enough. Look at the example bellow. I sorted by Revenue of Year 2010, but what I really wanted is sorting by the total Revenue of all 3 years. 

 

For this I need a little extra work.

I created an extra column with Formula Sum("Revenue" by "Product Type"). That's actually the bar total.
 

 I'll sort the Analysis by this Sum:

In the Pivot I moved the sum from Measures to the leftmost column of the Rows axis. And... Nothing!

Why? Because I should have marked it "Attribute column" in the Formula:

 Now I'll just hide this Sum column it in the Column Properties (otherwise we will see the values in the Vertical axis):

This is the desired result.