Custom ODBC Connector Property Unsearchable Error

[zua]

Hello, 

I have a problem with a custom ODBC connector for a SAP HANA database. 

I use native queries to access tables in the database and to fold the calculation. 

But I have a problem with columns that have datatype double in the db and numeric in Power Query.

For example I filter a corresponding column, which will result in a query that is not supported by direct query.

The diagnosis says "data type of column XY with searchable property UNSEARCHABLE should be SEARCHABLE or ALL_EXCEPT_LIKE".

All other columns (with different data types) are filterable with direct query and query folding. 

I tried to fix the searchable property of the columns, but that does not fix the problem.

Does anybody else have this problem? And does anyone have got an idea what the problem is and how to fix it?

Thank you! 

[rohit1991]

Hi @zua ,

This error happens because Power BI relies on ODBC metadata to decide whether columns can be filtered (searchable). If your ODBC driver (especially with DOUBLE or DOUBLE PRECISION columns) flags them as “unsearchable,” Power BI can’t push filters down in DirectQuery mode, which triggers this issue.

In some cases, as Pieterjan pointed out, the root cause is a mismatch or bug in how the ODBC driver reports column data types and searchability (for example, a conflict between SQL_DESC_TYPE_NAME and SQL_DESC_CONCISE_TYPE). This can happen even if the connector says the column should be searchable.

What you can do is to, Create a view in your database that CASTs the DOUBLE column to DECIMAL, and point Power BI to that view. This usually makes the column searchable and gets around the ODBC metadata problem.

If you manage the ODBC driver or connector, double-check how it exposes column metadata (especially those attributes Pieterjan mentioned). Fixing the metadata at the source is ideal if possible.  Also, you can also try Import mode as a workaround, but I get that DirectQuery and query folding are important for you.

[pj-spoelders]

Hi  @zua and others who stumble upon this problem. 

We had a similar issue and we found out that in our case this was due to a bug in our ODBC driver:
NativeQuery uses 'GetColumnAttributeW', attribute 'SQL_DESC_TYPE_NAME' to look up the datatype of the columns you query. The datatypes themselves are being fetched/queried using 'SQLGetTypeInfo' and the values being used for the lookup are in the ' SQL_DESC_CONCISE_TYPE' column. The issue in this case was a mismatch between both values and this caused this exact error.

Late answer, but hope this helps you and whoever else who stumbles upon this problem.

Kind regards,

Pieterjan Spoelders

[zua]

Hello @Anonymous,

this connector does not work for my purpose, 

which is to offer direct query, query folding and ssl encryption. 

Do you have an idea what the error message is about?

[pj-spoelders]

Hi @zua Have you found a solution or workaround for this problem yet? We are experiencing exactly the same issue with a different custom connector we've built and are using .. - ODBC-based, - using DirectQuery connectivity mode, - using a NativeQuery and using [EnableFolding =true]. We also only have this issue with the DOUBLE PRECISION datatype. We tried similar workaround as to what you tried (Set the columns to SEARCHABLE via SQLGetTypeInfo ) to no avail.

[Anonymous]

Hi @zua , 

In power bi desktop, it offered the SAP HANA database connector, you can try to use the connector to connect the database.

And you can refer to the following link about the connector.

Power Query SAP HANA database connector - Power Query | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.