How does OBIEE generate the sql statements that are then run against the target database? I have a report that generates one SQL statement when executed against Oracle database and completely different when executed via jdbc driver against Apache Drill. My problem is that in the second case the query is not even syntactically valid.
I've read this - http://gerardnico.com/wiki/dat/obiee/query_compiler but still don't understand the mechanism through which Oracle decides on the actual query to be executed based on the driver.
OBIEE uses a "common metadata model" known as the RPD. This has a logical model of your data, along with the physical data source(s) for it. When a user runs a report it is submitted as a "logical" query that the BI Server then compiles using the RPD to generate the necessary SQL query (or queries) against the data sources.
Whilst Hive and Impala definitely work with OBIEE, I've not heard of Drill being successfully used. If you've got the connectivity working then to sort out the query syntax it generates you need to fiddle with the DBFeatures configuration which OBIEE uses to understand what SQL statements are valid for a given database. So if Drill doesn't support, for example, INTERSECT, you simply untick INTERSECT_SUPPORTED (I'm paraphasing the exact dialog terminology).