Hello I'm back again within a few hours of my last question. I am trying to create a custom Power BI connector to set up a DirectQuery connection from my local MongoDB environment to Microsoft Power BI. I created a custom connector using the sample ODBC connector from Microsoft (https://github.com/microsoft/DataConnectors/tree/master/samples). I am using the latest MongoDB ODBC Driver and MongoDB ODBC Driver for BI Connector. I created a System Data Source that is used as the input for my custom connector.
I am bringing in the following collection to Power BI for DirectQuery: tbllinktrafficdata. I try to create a Slicer with the LastUpdate (timestamp) field. When I filter on one LinkID (which is a field), I get the following error.
"ErrorMessage":"Data Type of column LinkID with searchable property UNSEARCHABLE should be SEARCHABLE or ALL_EXCEPT_LIKE. You can override the supported data types from ODBC driver using SQLGetTypeInfo."
I tried changing the SEARCHABLE column in the SqlGetTypeInfo function as instructed in the trace logs, but it does not to seem to work. Here is my function call in the connector code. OdbcSearchable is set to 3 which is pulled from the OdbcConstants.pqm file
SQLGetTypeInfo = (types) =>
let
OdbcSearchable = ODBC[SQL_SEARCHABLE][SEARCHABLE],
OdbcAllExceptLike = ODBC[SQL_SEARCHABLE][ALL_EXCEPT_LIKE],
SetSearchable = (dataType) =>
OdbcSearchable, // SQL_SEARCHABLE = 3
//OdbcAllExceptLike, // SQL_ALL_EXCEPT_LIKE = 2
// Outputting the entire table might be too large, and result in the value being truncated.
// We can output a row at a time instead with Table.TransformRows()
//rows = Table.TransformRows(types, each Diagnostics.LogValue("SQLGetTypeInfo " & _[TYPE_NAME], _)),
//toTable = Table.FromRecords(rows),
//Update SEARCHABLE column to SQL_SEARCHABLE
Transform = Table.TransformColumns(types, { { "SEARCHABLE", SetSearchable } })
in
Transform,
More trace logs if useful:
OdbcQuery/FoldingWarning {"Start":"2019-09-04T19:48:44.7842276Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"25156","Function Name":"VisitInvocation","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"ee0b51e3-2141-4a72-8f49-87a3e688314a","Process":"Microsoft.Mashup.Container.NetFX45","Pid":20256,"Tid":1,"Duration":"00:00:00.0000217"}
OdbcQuery/FoldingWarning {"Start":"2019-09-04T19:48:44.7842661Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"25156","Function Name":"VisitValueEqualsShared","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"ee0b51e3-2141-4a72-8f49-87a3e688314a","Process":"Microsoft.Mashup.Container.NetFX45","Pid":20256,"Tid":1,"Duration":"00:00:00.0000031"}
OdbcQuery/FoldingWarning {"Start":"2019-09-04T19:48:44.7842729Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"25156","Function Name":"VisitColumnAccess","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"ee0b51e3-2141-4a72-8f49-87a3e688314a","Process":"Microsoft.Mashup.Container.NetFX45","Pid":20256,"Tid":1,"Duration":"00:00:00.0000023"}
OdbcQuery/FoldingWarning {"Start":"2019-09-04T19:48:44.7842799Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"25156","ErrorMessage":"Data Type of column LinkID with searchable property UNSEARCHABLE should be SEARCHABLE or ALL_EXCEPT_LIKE. You can override the supported data types from ODBC driver using SQLGetTypeInfo.","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"ee0b51e3-2141-4a72-8f49-87a3e688314a","Process":"Microsoft.Mashup.Container.NetFX45","Pid":20256,"Tid":1,"Duration":"00:00:00.0002237"}
OdbcQueryDomain/ReportFoldingFailure {"Start":"2019-09-04T19:48:44.7848477Z","Action":"OdbcQueryDomain/ReportFoldingFailure","HostProcessId":"25156","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Runtime.FoldingFailureException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: Folding failed. Please take a look the information in the trace.\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.VisitColumnAccess(ColumnAccessQueryExpression expression)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.VisitValueEqualsShared(InvocationQueryExpression expression, Boolean nullable)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.VisitInvocation(InvocationQueryExpression expression)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQuery.SelectRows(FunctionValue function)\r\n\r\n\r\n","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"ee0b51e3-2141-4a72-8f49-87a3e688314a","Process":"Microsoft.Mashup.Container.NetFX45","Pid":20256,"Tid":1,"Duration":"00:00:00.0001043"}
