Monday, June 30, 2014

OBIEE - Marking Events on (timeline) Graphs

Customer uses timeline graphs and wanted to mark important events on the graph.
I suggested something like this, and here I will described how I did it.
(Done on OBIEE  11.1.7.140415)


There is no rocket science here.
I assume a table with date, event name and measure. in my case it's Revenue.
Something like this:


Since I didn't really had events data I faked it with a case formula:
CASE "Time"."Calendar Date"  
    WHEN DATE'2010-02-01' THEN 'Business Fair'  
    WHEN DATE'2010-02-17' THEN 'Business Fair' 
    WHEN DATE'2010-03-24' THEN 'Party' 
    WHEN DATE'2010-04-24' THEN 'Birthday' 
    WHEN DATE'2010-01-25' THEN 'Party' 
ELSE NULL END

We can't really use it directly in Time-line graph (we will get an Error, since Event is not date). And even trying to create a regular line graph will end up with ugly results:


Why only one event and not 5? They are too many point to show all dates on the dates scale. We get random results.


 Now I created filtered measure column that repeated the Revenue value only on Events rows. For example, assuming we have a column Events.Event the formula should be:
FILTER("Base Facts"."Revenue" USING (CASE "EVENTS".EVENT" = 'Birthday'))

Or actually 3 columns in our case:
Birthday:
FILTER("Base Facts"."Revenue" USING (CASE "EVENTS".EVENT" = 'Birthday'))
Business Fair: 
FILTER("Base Facts"."Revenue" USING (CASE "EVENTS".EVENT" = 'Business Fair'))
Party:
FILTER("Base Facts"."Revenue" USING (CASE "EVENTS".EVENT" = 'Party'))

(Lets be honest, since i used the terrible case above what I really have is something like:
FILTER("Base Facts"."Revenue" USING (CASE "Time"."Calendar Date"  WHEN DATE'2010-02-01' THEN 'Business Fair'  WHEN DATE'2010-02-17' THEN 'Business Fair' WHEN DATE'2010-03-24' THEN 'Party' WHEN DATE'2010-04-24' THEN 'Birthday' WHEN DATE'2010-01-25' THEN 'Party' ELSE NULL END = 'Business Fair'))
)

After excluding the events column what I have is the following table:

Now I can create a timeline graph (remember to exclude the events column):

In the graph properties I make sure the Revenue is thin line without marks, and all the rest are marks without lines: 



We should have 0px Width lines here as many as they are event types.

While we are in the graph properties, we can change the default legend:


 This is the result:




P.S.

Actually, John Minkjan did something similar, but different 7 years ago (here is the link).  I miss his blog.

1 comment:

  1. Great.
    Exactly the post I was waiting for.

    ReplyDelete