Oracle EID does not seem to handle temporal visualizations well. For example, to create graphs by month, day, or hour we need to create specific variables for these as you cannot plot datetime objects under charts. However, if there is no record for a particular month or day, chart does not recognize it as null and simply ignores the value. So the graph would just skip a month or day. Is there a good way to solve this problem?
I typically load a second "record type" that I refer to as "date" or "calendar". Each record in this record type is a different day going back however long (a few years generally, but depends on the data). These calendar records are attributed with derived date information (day, month, weekofyear, daysago, weeksago, monthsago, year, yearsago, quarter, quartersago etc.)
I can use this record type in 2 ways: 1) during ingest, I can join by date on to my actual data records providing the wealth of temporal attribution and 2) by loading it as its own record type, I can provide temporal visualizations and ensure that I will provide a "bucket" in my group by for weeks/months/years/etc. that my data wouldn't support otherwise
Thus, an EQL statement like
RETURN foo AS SELECT
SUM("sales_amt") AS "TotSales"
GROUP BY "Month"
will offer all months since my calendar record type will offer its months where the data does not.
Hope that makes sense.
Hi Dan, one slight problem. My records include both unstructured / structured data fields. One of the use cases is to filter the records based on keyword search and visualize the results temporally. However, your "data/calendar" records would be filtered out in this case and not solve the problem at hand?
Yeah, very good point. You'll want to use "FROM AllBaseRecords" to get around that. Don't hold me to the syntax, but you'd need to create some views that accomplished the following
View 1: MyCalendar
DEFEINE MyCalendar AS SELECT
"Month" AS "Month",
"Day" AS "Day",
"Year" AS "Year"
View 2: MyVisualization
DEFINE foo AS SELECT
SUM("sales_amount") AS "TotSales"
GROUP BY "Month";
DEFINE MyVisualization AS SELECT
COALESCE(foo["Month"]."TotalSales", 0.0) AS "ChartTotal"
GROUP BY "Month"
Thus, your calendar view is always, statically and reliably, providing your "buckets" for your visualizations.
I am looking to implement the same temporal visualization as what the author has mentioned and while trying to do the same, I run into the following error:
Could not execute view config service request. This usually happens when an invalid view config service request is made, or when a read only Oracle Endeca Server receives a view config service request. Error message: In statement "ChartsVisualization": FROM statement ("MyCalendar") is not defined - Location:24:6-24:15
It seems that Studio is unable to find the view "MyCalendar" which I have created and saved previously. Do you know of any way that will allow me to overcome this issue?
Below are the views that I am using:
View 1: MyCalendar
DEFINE MyCalendar AS SELECT
CONCAT(CONCAT(TO_STRING(DimDate_CalendarYear), '-'), TO_STRING(DimDate_MonthNumberOfYear)) as "Year-Month"
View 2: ChartsVisualization
DEFINE foo1 AS SELECT
CONCAT(CONCAT(TO_STRING(DimDate_CalendarYear), '-'), TO_STRING(DimDate_MonthNumberOfYear)) as "Year-Month",
COUNTDISTINCT("Comment_Id") AS "TotComment_Id"
GROUP BY "Year-Month";
DEFINE ChartsVisualization AS SELECT
COALESCE(foo1["Year-Month"]."TotComment_Id", 0.0) AS "TotComment_Id1"
GROUP BY "Year-Month"
There is a known bug when saving views: LSTUDIO-7290 View Manager: Editing Views Referring to other Views causes Errors. A hotfix is forthcoming. But you can work around the issue in the meantime by deleting your MyCalendar view and recreating it. It is my understanding that views are marked as inactive when you edit and save them, but not when you add and save them in the first place. Your MyCalendar view is probably inactive, although Studio does not indicate it. (Studio does not yet have any functionality related to inactive views.)