Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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 @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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
13 | |
2 | |
2 | |
1 | |
1 |
User | Count |
---|---|
19 | |
3 | |
2 | |
2 | |
2 |