cancel
Showing results for 
Search instead for 
Did you mean: 
0

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

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?

Status: Delivered
Comments
Moderator

Hi @xsgao_pbi,

 

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

 

Support Ticket.gif

 

Best Regards,
Qiuyun Yu 

Established Member
Status changed to: Delivered
 
Visitor

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

 

Capture.PNG

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.

Visitor

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

 

Visitor

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.