8 Replies Latest reply: Jul 15, 2013 3:00 PM by 1002570 RSS

    Temporal Visualization

    1005165
      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?
        • 1. Re: Temporal Visualization
          Dan at Branchbird
          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.

          Dan
          http://branchbird.com
          • 2. Re: Temporal Visualization
            1005165
            Hi Dan, that was very helpful. We will try that suggestion out. Thanks!
            • 3. Re: Temporal Visualization
              1005165
              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?
              • 4. Re: Temporal Visualization
                Dan at Branchbird
                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"
                ...
                FROM AllBaseRecords


                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"
                FROM MyCalendar
                GROUP BY "Month"

                Thus, your calendar view is always, statically and reliably, providing your "buckets" for your visualizations.

                Thanks,
                Dan
                • 5. Re: Temporal Visualization
                  970611
                  Hi Dan,

                  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"
                  FROM AllBaseRecords



                  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"
                  FROM MyCalendar
                  GROUP BY "Year-Month"
                  • 6. Re: Temporal Visualization
                    920261
                    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.)

                    Dave
                    • 7. Re: Temporal Visualization
                      970611
                      Hi Dave,

                      Thanks! I was able to overcome the bug using the work around that you have provided!


                      Thanks too Dan!
                      • 8. Re: Temporal Visualization
                        1002570

                        Do you know where can I find a full answer o solution to this problem? (If it posibble ,a guide or tutorial)

                         

                        I am a new OEID user.

                         

                        Thanks

                         

                        EBrauerP