I am having trouble with Power bi and Dremio in direct query mode. When i work with boolean(nullable if it is important) columns (add slicer or add them to charts) i have wrong sql produced where it tries to cast boolean field to BIGINT first.
@vladislav-stolyarov please can you share de definition of the source? is ths a PDS or VDS? how is defined this fiel in the source? what database is?
this can be a problem with the connector to the datasource that return wrong data, it can be fixed with a custom PowerBi-Dremio connector
It is VDS (with raw reflection on it if its important). Its data source is aws s3 bucket json files.
above faceDetected column is defined in VDS as
case when source = ‘xxx’ and args[‘yyy’] = True then True end as faceDetected (boolean with nulls it might be important to repro the issue )
I am using power bi Dremio connector (just downloaded and installed) (in the Power bi selected: Dremio(beta) data source type)
In power bi import mode it works fine, in direct query all boolean columns fails.
- this is a problem with de dremio driver (internall odbc driver of dremio use apache drill developed by simba)
- currently I tested a C# app to capture the data type reported by the odbc driver and for boolean it is a integer, so this is the reason that power bi try to cast to number, And the AWS s3 connector not support
- so to fix them you can build your own Dremio connector to powerbi using visual studio and override the data type bolean to int
- I had the same problem and I solved it building a custom power bi connector
- https://docs.microsoft.com/en-us/power-query/creatingfirstconnector
If you need more info, tell me