I try to keep the pulse of the OTN Forum for OEID, or Oracle Endeca Information Discovery. Of late, a lot of questions have come up around how to handle temporal analysis with Endeca. Specifically, when producing visualizations by time (e.g. month), how do I ensure that I have a "bucket" for all months, even if my underlying data does not tie back to every month? A common pain point in the product, to be sure.
To illustrate, say I have "sales" records, like so:
When I load these into my Endeca server and attempt to produce a visualization that totals my sales by month, I wind up with:
RETURN foo AS SELECT SUM(SalesAmt) As "TotSales" GROUP BY Month
Almost immediately, it jumps out at me that there is no bucket for "5-May". Upon investigation, this is *accurate* as I had no sales in May, but far from the visualization I require to properly convey that fact.
The best practice here is to introduce a secondary "record type" that I usually call "Calendar". Each record in this record type is a different day, and I include all of the derived attribution I may want for the varying temporal analysis I'd like to perform in my application. Thus, my new "Calendar" record type might look like:
and so on...
Now when I issue the same EQL statement that powers my chart, all temporal "buckets" are covered by my calendar records. The calendar records ensure that my GROUP BY is offered any and all buckets, even if there are no sales to total in a particular bucket. After loading this second record type, I refresh my chart and voila:
I now have a bucket for "May" and my visualization properly conveys that sales tanked in May and someone needs to lose their job.