Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Custom Connector with DirectQuery support has Folding Error when scrolling and filtering

I'm developing a new custom data connector for Power BI based on a new ODBC driver.

 

I'm trying to fully support DirectQuery mode as well as the ODBC driver will allow.

 

However, receiving some folding errors when attempting simple functionality like scrolling past the first 501 records and trying to filter a column based on an existing value.

 

In both these cases, the following error occurs.

image.png

 

 

 

 

 

 

 

 

 

The question is how can we diagnose the underlying missing capability or property which is causing the folding error to occur.

 

With diagnostic tracing enabled the following errors are logged: 

 

DataMashup.Trace Warning: 24579 : {"Start":"2023-01-16T18:27:19.3263058Z","Action":"RemotePageReader/RunStub","HostProcessId":"29440","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Runtime.ValueException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryDomain.ReportFoldingFailure(NotSupportedException ex)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQuery.SelectRows(FunctionValue function)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query, Func`2 operation)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query, Func`2 operation)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query, Func`2 operation)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query, Func`2 operation)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.Optimize(Query query)\r\n at Microsoft.Mashup.Engine1.Language.Query.QueryTableValue.get_OptimizedQuery()\r\n at Microsoft.Mashup.Engine1.Language.Query.QueryTableValue.GetReader()\r\n at Microsoft.Mashup.Engine.Interface.Tracing.TracingDataReaderSource.get_PageReader()\r\n at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass10_1.<OnBeginGetDataReaderSource>b__1()\r\n at Microsoft.Mashup.Evaluator.RemotePageReader.<>c__DisplayClass1_1.<RunStub>b__0()\r\n at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(String entryName, IEngineHost engineHost, IMessageChannel channel, Action action)\r\n\r\nDetail: null\r\n\r\n","ProductVersion":"2.112.1161.0 (22.12)","ActivityId":"5397852b-7e33-48a5-be50-550c61c8a3f7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":28900,"Tid":1,"Duration":"00:00:00.0001674"}

 

 

 

 

Here is the essential contents of the custom connector file.

/* This is the method for connection to ODBC*/
[DataSource.Kind="OdbcBasedConnector", Publish="OdbcBasedConnector.UI"]
shared OdbcBasedConnector.Contents = (dsn as text) as table =>
      let
        //
        // Connection string settings
        //
        ConnectionString = [
            DSN=dsn
        ],

        //
        // Handle credentials
        // Credentials are not persisted with the query and are set through a separate
        // record field - CredentialConnectionString. The base Odbc.DataSource function
        // will handle UsernamePassword authentication automatically, but it is explictly
        // handled here as an example.
        //
        Credential = Extension.CurrentCredential(),
        encryptionEnabled = Credential[EncryptConnection]? = true,
        CredentialConnectionString = [
            UID = Credential[Username],
            PWD = Credential[Password]
        ],
   
        MySqlCapabilities = Diagnostics.LogValue("SqlCapabilities_Options", [
            GroupByCapabilities = ODBC[SQL_GB][SQL_GB_GROUP_BY_CONTAINS_SELECT],
            SupportsNumericLiterals = true,
            SupportsStringLiterals = true,                
            SupportsOdbcDateLiterals = true,
            SupportsOdbcTimeLiterals = true,
            SupportsOdbcTimestampLiterals = true,
            PrepareStatements = false,
            StringLiteralEscapeCharacters = { "\" },
            SupportsTop = false,
            LimitClauseKind = LimitClauseKind.LimitOffset,
            FractionalSecondsScale = 3
        ]),
        MySQLGetInfo = Diagnostics.LogValue("SQLGetInfo_Options", [
            SQL_SQL92_PREDICATES = ODBC[SQL_SP][All], // (SQL_SP) all
            SQL_AGGREGATE_FUNCTIONS = ODBC[SQL_AF][All], //all
            SQL_SQL_CONFORMANCE = ODBC[SQL_SC][SQL_SC_SQL92_FULL], // FULL
            SQL_GROUP_BY = ODBC[SQL_GB][SQL_GB_GROUP_BY_CONTAINS_SELECT]
        ]),
        MySQLGetFunctions = Diagnostics.LogValue("SQLGetFunctions_Options", [
            SQL_API_SQLBINDPARAMETER = false,
            SQL_CONVERT_FUNCTIONS = ODBC[SQL_FN_CVT][SQL_FN_CVT_CAST] // CAST
        ]),

        // Build AstVisitor
        // This record allows you to customize the generated SQL for certain
        // operations. The most common usage is to define syntax for LIMIT/OFFSET operators
        // when TOP is not supported.
        //
        MyAstVisitor = [
            LimitClause = (skip, take) =>
            let
                offset = if (skip <> null and skip > 0) then Text.Format("OFFSET #{0} ROWS", {skip}) else "",
                limit = if (take <> null) then Text.Format("LIMIT #{0}", {take}) else ""
            in
                [
                Text = Text.Format("#{0} #{1}", {limit, offset}),
                Location = "AfterQuerySpecification"
                ]
            ],

        //
        // Call to Odbc.DataSource
        //
        OdbcDatasource = Odbc.DataSource(ConnectionString, [
            // Enables client side connection pooling for the ODBC driver.
            // Most drivers will want to set this value to true.
            ClientConnectionPooling = true,
            // When HierarchialNavigation is set to true, the navigation tree
            // will be organized by Database -> Schema -> Table. When set to false,
            // all tables will be displayed in a flat list using fully qualified names.
            HierarchicalNavigation = true,
            //Expose Native Query
            HideNativeQuery = false,
            //Allows the M engine to select a compatible data type when conversion between two specific numeric types is not declared as supported in the SQL_CONVERT_* capabilities.
            SoftNumbers = true,
            TolerateConcatOverflow = true,
            // These values should be set by previous steps
            CredentialConnectionString = CredentialConnectionString,
            //Requires AstVisitor since Top is not supported in DocumentDB
            AstVisitor = MyAstVisitor,
            SqlCapabilities = MySqlCapabilities,
            SQLGetInfo = MySQLGetInfo,
            SQLGetFunctions = MySQLGetFunctions
        ])
       
    in OdbcDatasource;<div> </div>

 

Status: Needs Info
Comments
Anonymous
Not applicable

Hi @birschick 

Did you update your ODBC driver to the latest version ? Can you change the connection mode to Import ?

 

Best Regards,
Community Support Team _ Ailsa Tao

birschick
Regular Visitor

Hi @Anonymous 

 

Thanks for responding.

 

We are in-fact the driver developers, as well. So we have the latest version. Yes, import mode works as expected. But our goal is to make DirectQuery mode work fully. This mode can be highly desirable to our shared customers.

 

The issues described relates to how our driver interacts with Power BI via the Custom Connector. The challenge for us is that we don't have very much more information to go on when something isn't working as expected. So our ask/request is to provide some tracing/diagnostics when a folding error occurs so that we have some insight into how to resolve the issue.

 

As it turns out, we likely have solved the problem on our own by using some "deductive" reasoning about what is working and what is not working. But it has taken us many days to resolve this issue and we believe this could have been made easier with more tracing/diagnostics.

 

Thanks for your questions and I hope this may lead to an improvement that will make ours and other developer's job a little easier, in the future.

 

Thanks. Bruce Irschick