cannot fold date column error when using custom ODBC via data connector

[xsgao_pbi]

We are connecting Power BI Desktop via a custom data connector to a ODBC DSN, the ODBC DSN is based on a custom ODBC driver which connect to Cognos BI server. This solution works well.

But we have one issue. When I drag a data column to an table, I got this error:

[Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.

I turned on trace within PBI Desktop and I fund following message:

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

So I fired ODBCTest, tested my DSN vs another SQL Server ODBC based DSN. I found that they return same information to SQLGetTypeInfo call.

Here is that SQL Server ODBC DSN returned:

"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"	 "USERTYPE"
"date"	 91	 10	 "'"	 "'"	 <Null>	 1	 0	 3	 <Null>	 0	 <Null>	 "date"	 <Null>	 0	 9	 1	 <Null>	 <Null>	 0
"datetime"	 93	 23	 "'"	 "'"	 <Null>	 1	 0	 3	 <Null>	 0	 <Null>	 "datetime"	 3	 3	 9	 3	 <Null>	 <Null>	 12

And here is my ODBC DSN returned:

"TYPE_NAME"	 "DATA_TYPE"	 "COLUMN_SIZE"	 "LITERAL_PREFIX"	 "LITERAL_SUFFIX"	 "CREATE_PARAMS"	 "NULLABLE"	 "CASE_SENSITIVE"	 "SEARCHABLE"	 "UNSIGNED_ATTRIBUTE"	 "FIXED_PREC_SCALE"	 "AUTO_UNIQUE_VALUE"	 "DATE"	 91	 10	 "'"	 "'"	 <Null>	 1	 0	 3	 <Null>	 0	 <Null>	 "DATE"	 0	 0	 9	 1	 <Null>	 <Null>	 0
"TIME"	 92	 8	 "'"	 "'"	 <Null>	 1	 0	 3	 <Null>	 0	 <Null>	 "TIME"	 0	 0	 9	 2	 <Null>	 <Null>	 0
"TIMESTAMP"	 93	 19	 "'"	 "'"	 <Null>	 1	 0	 3	 <Null>	 0	 <Null>	 "TIMESTAMP"	 0	 0	 9	 3	 <Null>	 <Null>	 0

Any idea why query folding failed on my date column, and what changes I need to make on custom data connector/ODBC driver?

[v-qiuyu-msft]

Hi @xsgao_pbi,

I would suggest you create a support ticket to get dedicated support.

Best Regards,
Qiuyun Yu 

[xsgao_pbi]

@v-qiuyu-msft How do I submit a support ticket on powerbi.microsoft.com? Seems like I already have a Pro license:

But every time I click on Report an Issue link (I did not see contact support link), it brings me back to community.powerbi.com.

[xsgao_pbi]

More informaton.

We discovered in the Power BI trace log that column_size was not populated correctly.

There are 3 trace files with 6 logging entries for the same column metadata.

The first one shows incorrect values (column_size should be 19):

TABLE_NAME = \"Time\", COLUMN_NAME = \"Date\", DATA_TYPE = 93, TYPE_NAME = \"TYPE_TIMESTAMP\", COLUMN_SIZE = 39,

The other 5 entries shows correct values (which is what our driver returned):

TABLE_NAME = \"Time\", COLUMN_NAME = \"Date\", DATA_TYPE = 93, TYPE_NAME = \"TYPE_TIMESTAMP\", COLUMN_SIZE = 19

[xsgao_pbi]

We managed to fix this problem by returning both TYPE_DATE as TYPE_NAME in SQLTypeInfo and SQLColumns call.

Before fix, SQLTypeInfo returns:

"SQLGetTypeInfo TIMESTAMP: [ TYPE_NAME = \"TIMESTAMP\", DATA_TYPE = 93, COLUMN_SIZE = 19,

SQLColumns returns:

COLUMN_NAME = \"Date\", DATA_TYPE = 93, TYPE_NAME = \"TYPE_TIMESTAMP\", COLUMN_SIZE = 19,

Ater changing both TYPE_NAME to TIMESTAMP or TYPE_TIMESTAMP (within our ODBC driver or within data connector), it starts working.

So you may close this case.