I am using OBI 12c with Hadoop Imapala Database connection.
I am using a 'String' data type column which is the first dashboard prompt.The dashboard gets autofilled with Max(Column Value),but when we try to select a prompt list value ,it shows "Failed to Load ODBC Error".
I have used "SQL results" for Prompt choice list to limit the values,the SQL query contains "FETCH FIRST 65001 ROWS ", but still it throws error to load.
After my research, i observed the column used in prompt has total of 2.5 Lakh records in database and so the prompt also fetches all records out of its limit and throws error to load 'List of values' in the prompt for OBI.
I also checked the Physical SQL of the Prompt column , observed that the Physical SQL does not limit the values to "FETCH FIRST 65001 ROWS " as available in Prompt SQL results.
In order to limit the Prompt values, i also used "Top 10" or "Bottom 10" function in the dashboard Prompt SQL query,but it does not pass the functions to Physical SQL to limit rows count to 10,but still the prompt fetches all rows of 2.5 Lakh and finally giving error during prompt selection.
Any advice please to overcome the above mentioned issue.