Details

      Description

      BI reporting tools rely on support for operations such as DAY, MONTH, YEAR, DATE for producing time series aggregation.
      Alfresco Insight Engine provides virtual time dimensions by using _day, _month, _year for date fields.

      This solution works for tools like Zeppelin but does not work well with Tableau, where data retrieved using CDATA ODBC driver was used to design and plot graphs
      The following error was seen:

      Query Used as an example:

      select finance_CreatedAt_Day, finance_amount  from alfresco 
      where finance_amount > 0
      

      When finance_amount pulled into columns: An error occurred while communicating with the Other Databases (ODBC) data source 'Custom_SQL_Query (CData)'.

      An error occurred while communicating with the Other Databases (ODBC) data source 'Custom_SQL_Query (CData)'.
      
      Bad Connection: Tableau could not connect to the data source.
      [400] 07210045 Unable to execute the query, error caused by: Error from server at http://10.0.75.1:8984/solr/alfresco: Error from server at http://10.0.75.1:8983/solr/alfresco: undefined field: "finance:CreatedAt_day". 
      SELECT `Custom_SQL_Query`.`finance_CreatedAt_day` AS `finance_CreatedAt_day`,
        `Custom_SQL_Query`.`finance_amount` AS `finance_amount`
      FROM (
        select finance_CreatedAt_day, finance_amount  from alfresco 
        where finance_amount >= 0
      ) `Custom_SQL_Query`
      GROUP BY 1,
        2
      

      Query such as below would work but will be a problem where date field is a timestamp:

      select finance_CreatedAt, finance_amount  from alfresco 
      where finance_amount > 0
      

      By adding support to Day, Month, Year functions, Insight engine can support BI tools better.

      Acceptance criteria:

      • Manage dates as strings
      • Support for operations such as DAY, MONTH, YEAR, DATE
        • in "select", "where", "group by" clauses acting on a single field.

        Attachments

          Issue Links

            Structure

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  mbhave Meenal Bhave [X] (Inactive)
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  1 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Structure Helper Panel