Hi.
We are generally using MSSQL as backend for our Tableau server. Now we intend to implement some more complex dashboards in Tableau which would aggregate millions of records. MSSQL is way to slow for the task and we want to evaluate if opensearch could be a suitable alternative to SQL Server.
We installed opensearch 2.11.0 and try to access it with the JDBC driver as described on sql-jdbc/bi-connectors/TableauConnector/README.md at main · opensearch-project/sql-jdbc (github.com)
Our data is structured in a star schema with fact and dimension tables. We have loaded some of these tables into OpenSearch and now, we try to query that data from the OpenSearch Dashboard as well as from Tableau. We managed to get Tableau querying OpenSearch. But as soon as we want to do some slightly complex SQL queries with joins, OpenSearch is unable to cope with it. According to the documentation it should be possible to create a join statements, to group by and to use subqueries. Now we tried the following rather simple query via the OpenSearch Dashboard:
select a.trxCity, a.minAmt, b.maxAmt
from
(
select trxCity, min(trxAmtOrig) as minAmt from transactions group by trxCity
) a
INNER join
(
select trxCity, max(trxAmtOrig) as maxAmt from transactions group by trxCity
)
b ON a.trxCity = b.trxCity
Upon click on the run button, the message "(: Bad Request, this query is not runnable. " is shown and OpenSearch says, that the query is not explainable.
We suspect, that we trying a thing that is way beyond the capabilities of OpenSearch. Is it really not possible to do some more complex queries with OpenSearch or is there some extension/feature that we are missing to do some advanced analytical stuff?
Any hint is highly appreciated.
Best regards