MySQL ODBC Direct Query Custom Connector

[Marco_Iac]

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

[v-rzhou-msft]

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. 

[Marco_Iac]

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

[v-rzhou-msft]

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.

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. 

[Marco_Iac]

Hi @v-rzhou-msft,

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