Currently, Insight Engine returns the field types as String for all the fields, irrespective of the real field type in the Data model.
This is paeticularly an issue for the date fields with tools like Tableau.
Tableau allows defining measures and grouping based on certain fields. To group based on Date fields, these fields can be selected and date type can be changed from say string to Date.
On attempting change of field type for a field say, finance_createdAt (date time field shows up as string), the following error is seen.
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.
 07210051 Unable to execute the query, error caused by: class java.lang.String cannot be cast to class java.lang.Number (java.lang.String and java.lang.Number are in module java.base of loader 'bootstrap').
SELECT CAST(`Custom_SQL_Query`.`finance_CreatedAt` AS DATE) AS `finance_CreatedAt`,
SUM(CAST(`Custom_SQL_Query`.`finance_amount` AS FLOAT)) AS `sum_finance_amount_ok`
select finance_CreatedAt, finance_amount from alfresco
where finance_amount > 0
GROUP BY 1
1. Right field types should be returned by Insight Engine
2. Converting to date field should be possible.