topic Re: Error Visualizing Data from __time column in Druid in Developer

Error Visualizing Data from __time column in Druid

padiga-isw — Mon, 07 Jul 2025 10:13:59 GMT

Hi Everyone,
I have created a Power BI custom connector for Simba Druid Driver to connect through DirectQuery. I am getting "Couldn't load the data for this visual OLE DB or ODBC error: [Expression. Error] We couldn't fold the expression to the data source. Please try a simpler expression.." when I try to fetch data from __time column but it works fine when other columns/datatypes get called.

Issue when connected through __time column:

Works fine with other columns (These are string/long values as druid doesn't support timestamp datatype for non __time columns):

I have monitored the driver logs and requests, I found that no request is sent by driver to fetch about __time column.
I am also able to see the data during preview and data transformation.

But when I try to apply any filter on this column, it throws an error saying

"Error Message":"Data Type of column __time with searchable property UNSEARCHABLE should be SEARCHABLE or ALL_EXCEPT_LIKE. You can override the supported data types from ODBC driver using SQLGetTypeInfo."

We can see the __time is of TIMESTAMP datatype, and when we run SQLGetInfo on this datatype
```
"TYPE_NAME", "DATA_TYPE", "COLUMN_SIZE", "LITERAL_PREFIX", "LITERAL_SUFFIX", "CREATE_PARAMS", "NULLABLE", "CASE_SENSITIVE", "SEARCHABLE", "UNSIGNED_ATTRIBUTE", "FIXED_PREC_SCALE", "AUTO_UNIQUE_VALUE", "LOCAL_TYPE_NAME", "MINIMUM_SCALE", "MAXIMUM_SCALE", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "NUM_PREC_RADIX", "INTERVAL_PRECISION", "USER_DATA_TYPE"
"TIMESTAMP", 93, 29, "'", "'", <Null>, 1, 0, 3, <Null>, 0, <Null>, "TIMESTAMP", 0, 0, 9, 3, <Null>, <Null>, 4193
```
Where it shows SEARCHABLE property is set to 3, which is SQL_SEARCHABLE.

Can you please help me with this issue and please let me know if you need any more error logs.

Thanks

Re: Error Visualizing Data from __time column in Druid

v-hashadapu — Mon, 07 Jul 2025 13:12:46 GMT

Hi @padiga-isw , Thank you for reaching out to the Microsoft Community Forum.

This is because the Simba Druid ODBC driver is reporting the time column as not searchable at the column level. Even though SQLGetTypeInfo might report the TIMESTAMP type as SEARCHABLE = 3, Power BI makes its actual decision based on the SEARCHABLE property returned by SQLColumns for that specific column. In your case, Power BI tries to apply a filter on time but sees SEARCHABLE = 0 (SQL_UNSEARCHABLE), so it refuses to fold the expression. That’s why no query is sent and you get the error.

Power BI DirectQuery still requires columns to be marked as SEARCHABLE (2 or 3) to apply filters or comparisons. This applies whether you're using Microsoft Fabric or the standard Power BI Desktop or Service. If you want filtering to work on time, you need to make sure the driver, either through its native code or metadata config returns SEARCHABLE = 2 or 3 for that column via SQLColumns. Simba drivers built on the SimbaEngine SDK support this, but it must be explicitly set in the metadata layer.

If you can't modify the driver, the practical workaround is to create a new Druid column that mirrors time but isn’t treated as special by the driver and is returned with the correct metadata. That column will work with filters in Power BI.

Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn

SQLColumns Function - ODBC API Reference | Microsoft Learn

SQLGetTypeInfo Function - ODBC API Reference | Microsoft Learn

DirectQuery in Power BI - Power BI | Microsoft Learn

ODBC Data Source Administrator - Open Database Connectivity (ODBC) | Microsoft Learn

If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

Re: Error Visualizing Data from __time column in Druid

padiga-isw — Tue, 08 Jul 2025 13:24:23 GMT

Hi @v-hashadapu ,
Thank you for the detailed explanation. I have a doubt regarding SQLColumns, please correct me if I am wrong
https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlcolumns-function?view=sql-server-ver17
In the above link I couldn't find any SEARCHABLE property that can be set in SQLColumns, is it a custom property that needs to be added?
I have a Trino custom connector where the SQLColumn returns
```
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE", "USER_DATA_TYPE"
"tpch", "tiny", "nation", "name", -9, "varchar", 25, 25, <Null>, <Null>, 0, <Null>, <Null>, -9, <Null>, 25, 2, "NO", 0
```
Where it doesn't define any SEARCHABLE property in SQLColumns similar to Druid, but it is able to display data with Timestamp datatype

I also ran SQLColAttribute to check the SEARCHABLE property of __time column it returns 3(SQL_SEARCHABLE).

Thank you.

Re: Error Visualizing Data from __time column in Druid

v-hashadapu — Thu, 10 Jul 2025 07:18:05 GMT

Hi @padiga-isw , Thank you for reaching out to the Microsoft Community Forum.

Power BI does not expect a SEARCHABLE field in the SQLColumns result, that attribute isn’t part of the standard output. Instead, it uses the SQLColAttribute call with SQL_DESC_SEARCHABLE to determine if a column supports filtering. However, in your case, even though SQLColAttribute returns SEARCHABLE = 3 for __time, Power BI still blocks folding. This means it likely treats the column as reserved or non-searchable during schema discovery, either because of the driver’s internal metadata or due to Power BI recognizing the column name (__time) as special.

The only reliable workaround is to expose a duplicate of __time under a neutral name like event_time, typed as a standard TIMESTAMP and not flagged internally as special. That column avoids reserved-name logic and will allow filters to fold properly. If you're maintaining the driver, the long-term fix is to make sure the __time column is not treated as special or non-searchable in the driver’s schema metadata, not just at runtime.