Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

MySQL ODBC Direct Query Custom Connector

Hi, i create a MySql ODBC Direct Query Custom Connector following Microsoft Instruction https://github.com/microsoft/DataConnectors/tree/master/samples/ODBC/SqlODBC here the VisualStudio code, in ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

LimitClauseKind:

A number value that controls how SQL is generated for Table.FirstN and Table.Skip. Because SQL-92 didn't specify this functionality, there's a lot of variation between different SQL dialects. When not set, these functions are not folded to the ODBC source. LimitClauseKind currently has four variations, which generate SQL text as follows.

LimitClauseKind.Top: `SELECT TOP 100 * FROM table`

LimitClauseKind.Limit: `SELECT * FROM table LIMIT 100`

LimitClauseKind.LimitOffset: `SELECT * from table LIMIT 100 OFFSET 200`

LimitClauseKind.AnsiSql2008: `SELECT * from table OFFSET 200 ROWS FETCH FIRST 100 ROWS ONLY`

Default: LimitClauseKind.None

LimitClauseKind = LimitClauseKind.None should be no limite in TopN. The restriction may not be caused by this code.

Power BI has specific MySQL connector, you can try it to connect to your data source.

For reference: MySQL database

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

HI, thanks for your reply,

unfortunately for the moment the microsoft connector for MySql does not support direct query mode.

The problem is the TOPN(501) which is generated automatically in the DAX Query.

 

Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    MySQL connector and ODBC connector are either currently not supported Direct Query.

     You can see a list of all currently supported data sources for Power BI that can be used with Direct Query here.

    I found some blogs for enale direct query for custom ODBC, hope they could help you.

    For reference: 

    Enabling direct query for a data source via an odbc driver

    Enabling Direct Query for an ODBC based connector

    Your demand is a good idea, while it is not supported to implement in Power BI currently.
    You can vote up this idea for this function：Direct Query MySQL support here.
    Or you can submit a new idea to improve the Power BI.
    It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous,

      thanks for your reply

       

      The table refers to standard microsoft connectors.

      I built a custom sql odbc connector following the Micrsoft code example on github: 

      https://github.com/microsoft/DataConnectors/blob/master/samples/ODBC/SqlODBC/SqlODBC.pq

       

      This type of connector should allow for direct query mode,

      here the piece of code:

      // Data Source UI publishing description
      MySql_ODBC_DQ.Publish = [
      Beta = true,
      Category = "Other",
      ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
      LearnMoreUrl = "https://powerbi.microsoft.com/",
      SourceImage = MySql_ODBC_DQ.Icons,
      SourceTypeImage = MySql_ODBC_DQ.Icons,
      SupportsDirectQuery = true
      ];

       

      However, the connector is functional and returns up to 501 rows, so direct mode works partially.

       

      Best Regards,

      Marco Iacaruso