I am setting 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 field. When I filter on one LinkID (which is a field), I get the following error.
"ErrorMessage":"Failed to convert type bigint to integer, expression `LinkID` to 1506780."
"ErrorMessage":"This ODBC driver doesn't support SQL_FN_CVT_CONVERT or SQL_FN_CVT_CAST. You can override this by using SqlGetInfo for SQL_CONVERT_FUNCTIONS."
It looks like I'm seeing a conversion error, but I'm not sure how to resolve the issue within the connector.
Here is my SqlGetInfo function:
SQLGetInfo = [
// place custom overrides here
SQL_SQL92_PREDICATES = ODBC[SQL_SP][All],
SQL_AGGREGATE_FUNCTIONS = ODBC[SQL_AF][All],
SQL_CONVERT_FUNCTIONS = ODBC[SQL_CVT][BIGINT]
],
//this is from OdbcConstants file that is called in Power Query file
SQL_CVT = [
BIGINT = 0x00004000
]
Here is more of the Power BI Trace Log:
SqlTranslator/SqlParser/Parse {"Start":"2019-09-
03T19:55:54.6245555Z","Action":"SqlTranslator/SqlParser/Parse","HostProcessId":"12228","SQL":"\nSELECT MAX([t29].[LastUpdate])\n AS [a0],MIN([t29].[LastUpdate])\n AS [a1]\nFROM \n(\n(SELECT * FROM [tbllinktrafficdata (2)])\n)\n AS [t29]\nWHERE \n(\n[t29].[LinkID] = 1506780\n)\n ","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.1356012"}
SqlExpressionTranslator/Translate {"Start":"2019-09-03T19:55:54.7606768Z","Action":"SqlExpressionTranslator/Translate","HostProcessId":"12228","IsRecognized":"True","Result":"(environment) => Table.RenameColumns(let\r\n t1133 = Table.RenameColumns(Table.PrefixColumns(environment[#\"tbllinktrafficdata (2)\"], \"tbllinktrafficdata (2)\"), {{\"tbllinktrafficdata (2)._id\", \"t29._id\"}, {\"tbllinktrafficdata (2).AgencyID\", \"t29.AgencyID\"}, {\"tbllinktrafficdata (2).DataType\", \"t29.DataType\"}, {\"tbllinktrafficdata (2).LastUpdate\", \"t29.LastUpdate\"}, {\"tbllinktrafficdata (2).LinkID\", \"t29.LinkID\"}, {\"tbllinktrafficdata (2).Occupancy\", \"t29.Occupancy\"}, {\"tbllinktrafficdata (2).Speed\", \"t29.Speed\"}, {\"tbllinktrafficdata (2).TravelTime\", \"t29.TravelTime\"}, {\"tbllinktrafficdata (2).Volume\", \"t29.Volume\"}}),\r\n t1135 = Table.SelectRows(t1133, (t1134) => Value.NullableEquals(t1134[t29.LinkID], 1506780)),\r\n t1140 = Table.Group(t1135, {}, {{\"a0\", (t1136) => List.Max(t1136[t29.LastUpdate])}, {\"a1\", (t1137) => List.Min(t1137[t29.LastUpdate])}}),\r\n t1141 = Table.SelectColumns(t1140, {\"a0\", \"a1\"})\r\nin\r\n t1141, {{\"a0\", \"a0\"}, {\"a1\", \"a1\"}})","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.1279102"}
SimpleDocumentEvaluator/GetResult/Evaluate {"Start":"2019-09-03T19:55:54.6208186Z","Action":"SimpleDocumentEvaluator/GetResult/Evaluate","HostProcessId":"12228","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.2835266"}
OdbcQuery/FoldingWarning {"Start":"2019-09-03T19:55:54.9495926Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"12228","Function Name":"VisitInvocation","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0000162"}
OdbcQuery/FoldingWarning {"Start":"2019-09-03T19:55:54.9498087Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"12228","Function Name":"VisitValueEqualsShared","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0000058"}
OdbcQuery/FoldingWarning {"Start":"2019-09-03T19:55:54.9498281Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"12228","ErrorMessage":"Failed to convert type bigint to integer, expression `LinkID` to 1506780.","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0057948"}
OdbcQuery/FoldingWarning {"Start":"2019-09-03T19:55:54.9498205Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"12228","Function Name":"AdjustForCompatibility","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0000034"}
OdbcQuery/FoldingWarning {"Start":"2019-09-03T19:55:54.9556351Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"12228","Function Name":"AdjustNumberValuesToPreventOverflow","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0000045"}
OdbcQuery/FoldingWarning {"Start":"2019-09-03T19:55:54.9556433Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"12228","Function Name":"SoftConvertSeries","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0000034"}
OdbcQuery/FoldingWarning {"Start":"2019-09-03T19:55:54.9556496Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"12228","ErrorMessage":"This ODBC driver doesn't support SQL_FN_CVT_CONVERT or SQL_FN_CVT_CAST. You can override this by using SqlGetInfo for SQL_CONVERT_FUNCTIONS.","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0002851"}
OdbcQueryDomain/ReportFoldingFailure {"Start":"2019-09-03T19:55:54.9566845Z","Action":"OdbcQueryDomain/ReportFoldingFailure","HostProcessId":"12228","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.CallConvertOrCast(SqlExpression from, OdbcTypeMap toType)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.TryVisitConvert(OdbcTypeInfo fromType, OdbcTypeInfo toType, SqlExpression expression, SqlExpression& convertedExpression)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.TryConvert(OdbcTypeInfo typeInfo, OdbcScalarExpression expression, OdbcScalarExpression& convertedExpression)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.<SoftConvertSeries>d__191.MoveNext()\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.AdjustNumberValuesToPreventOverflow(OdbcScalarExpression left, OdbcScalarExpression right, Precision precision)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.AdjustNumberValuesForCompatibility(OdbcScalarExpression left, OdbcScalarExpression right, Precision precision)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.AdjustForCompatibility(OdbcScalarExpression left, OdbcScalarExpression right, Precision precision)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.VisitEquals(OdbcSqlExpression leftExpression, OdbcSqlExpression rightExpression, Precision precision, Boolean nullable)\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":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0036644"}
I was able to convert by transforming in SqlColumn function. Odbc SQL types taken from ODBC constants SQL_TYPE (included below)
SQLColumns = (catalogName, schemaName, tableName, columnName, source) =>
let
OdbcSqlType.BIG_INT = -5,
OdbcSqlType.INTEGER = 4,
FixDataType = (dataType) =>
if dataType = OdbcSqlType.BIG_INT then
OdbcSqlType.INTEGER
else
dataType,
Transform = Table.TransformColumns(source, { { "DATA_TYPE", FixDataType } })
in
// the if statement conditions will force the values to evaluated/written to diagnostics
if (Diagnostics.LogValue("SQLColumns.TableName", tableName) <> "***" and Diagnostics.LogValue("SQLColumns.ColumnName", columnName) <> "***") then
let
// 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(Transform, each Diagnostics.LogValue("SQLColumns", _)),
toTable = Table.FromRecords(rows)
in
Value.ReplaceType(toTable, Value.Type(Transform))
else
Transform,
SQL Data Types
SQL_TYPE =
[
// Base data types (sql.h)
UNKNOWN = 0,
NULL = 0,
CHAR = 1,
NUMERIC = 2,
DECIMAL = 3,
INTEGER = 4,
SMALLINT = 5,
FLOAT = 6,
REAL = 7,
DOUBLE = 8,
DATETIME = 9, // V3 Only
VARCHAR = 12,
// Unicode types (sqlucode.h)
WCHAR = -8,
WVARCHAR = -9,
WLONGVARCHAR = -10,
// Extended data types (sqlext.h)
INTERVAL = 10, // V3 Only
TIME = 10,
TIMESTAMP = 11,
LONGVARCHAR = -1,
BINARY = -2,
VARBINARY = -3,
LONGVARBINARY = -4,
BIGINT = -5,
TINYINT = -6,
BIT = -7,
GUID = -11, // V3 Only
// One-parameter shortcuts for date/time data types.
TYPE_DATE = 91,
TYPE_TIME = 92,
TYPE_TIMESTAMP = 93,
// SQL Server Types -150 to -159 (sqlncli.h)
SS_VARIANT = -150,
SS_UDT = -151,
SS_XML = -152,
SS_TABLE = -153,
SS_TIME2 = -154,
SS_TIMESTAMPOFFSET = -155
],