-
Type:
Story
-
Status: Done
-
Resolution: Done
-
Affects Version/s: None
-
Fix Version/s: Search Services 2.0
-
Epic Link:
-
Release Train:Southall
-
Delivery Team:Search
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.
- duplicates
-
SEARCH-2141 Spike: Planning for CDATA Date support
- Done
- is related to by
-
SEARCH-2134 Create spike for CDATA date support
- Done
-
SEARCH-1905 [Spike] Support DATEDIFF function in JDBC queries
- Open