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:
When finance_amount pulled into columns: An error occurred while communicating with the Other Databases (ODBC) data source 'Custom_SQL_Query (CData)'.
Query such as below would work but will be a problem where date field is a timestamp:
By adding support to Day, Month, Year functions, Insight engine can support BI tools better.
- Manage dates as strings
- Support for operations such as DAY, MONTH, YEAR, DATE
- in "select", "where", "group by" clauses acting on a single field.