cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Marco_Iac
Frequent Visitor

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 RED the piece of code i think generate the problem:

 

// This file contains your Data Connector logic
section MySql_ODBC_DQ;

[DataSource.Kind="MySql_ODBC_DQ", Publish="MySql_ODBC_DQ.Publish"]
shared MySql_ODBC_DQ.Contents = (dsn as text) as table =>
let
ConnectionString = [
Dsn = dsn
],
Credential = Extension.CurrentCredential(),
CredentialConnectionString =
if (Credential[AuthenticationKind]?) = "UsernamePassword" then
[ UID = Credential[Username], PWD = Credential[Password] ]
else if (Credential[AuthenticationKind]?) = "Windows" then
[ Trusted_Connection="Yes" ]
else
...,
OdbcDatasource = Odbc.DataSource(ConnectionString, [

ClientConnectionPooling = true,
HierarchicalNavigation = true,
TolerateConcatOverflow = true,
HideNativeQuery = true,
SoftNumbers = true,
SqlCapabilities = [
SupportsTop = false,
Sql92Conformance = 8,
GroupByCapabilities = 4, // () 0, 1, 2, 3, 4 (SQL_GB_NO_RELATION)
SupportsNumericLiterals = true,
SupportsStringLiterals = true,
SupportsOdbcDateLiterals = true,
SupportsOdbcTimeLiterals = true,
SupportsOdbcTimestampLiterals = true,

SupportsDerivedTable = true,
LimitClauseKind = LimitClauseKind.None
],
SQLGetFunctions = [
SQL_API_SQLBINDPARAMETER = false,
SQL_CONVERT_FUNCTIONS = 0x2
]
])
in
OdbcDatasource;


// Data Source Kind description
MySql_ODBC_DQ = [
Authentication = [
Windows = [],
UsernamePassword = []
],
Label = Extension.LoadString("DataSourceLabel")
];


// 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
];


MySql_ODBC_DQ.Icons = [
Icon16 = { Extension.Contents("MySql_ODBC_DQ16.png"), Extension.Contents("MySql_ODBC_DQ20.png"), Extension.Contents("MySql_ODBC_DQ24.png"), Extension.Contents("MySql_ODBC_DQ32.png") },
Icon32 = { Extension.Contents("MySql_ODBC_DQ32.png"), Extension.Contents("MySql_ODBC_DQ40.png"), Extension.Contents("MySql_ODBC_DQ48.png"), Extension.Contents("MySql_ODBC_DQ64.png") }
]; 

 The connector work fine but it generate a DAX query with a TOPN 501 clause, so scrolling for example a table when pass 501 row power bi generate error.

The strange thing is that in power query all rows are loaded

Here the query:

 

// DAX Query
DEFINE
VAR __DS0Core =
DISTINCT('aziende'[idazienda])

VAR __DS0PrimaryWindowed =
TOPN(501, __DS0Core, 'aziende'[idazienda], 1)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
'aziende'[idazienda]

 

Does anyone know a way to extend the TOPN row limit?

 

 

_________________________________________________________
Marco Iacaruso
iacaruso.marco2@outlook.it
www.linkedin.com/in/marco-iacaruso

4 REPLIES 4
RicoZhou
Community Support
Community Support

Hi @Marco_Iac 

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. 

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

Hi @Marco_Iac 

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.

1.png

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. 

Hi @RicoZhou,

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

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors