Problem retrieving MariaDB datas from Power BI Desktop

[Anonymous]

Hello,

We've been struggling, for some time now, trying to retrieve our data from MariaDB databases with Power BI Desktop.
I've tryed a lot of thing, but I can't get Power BI to retrieve correctly the datas we need.

Let me explain :

We choose to connect to a MySQL Database in the connection manager, the database is correctly recognized and the avaiable tables are displayed. But when we try to get à preview of the datas in a table, the software is indefinitely loading with nothing displaying even after a lot of time. If we choose to load the datas even if it was not displayed in the preview, the software is sticking to the "evaluating" step with, after a lot of time, a timeout.
It seems, for me, that the problem come from the fact that there is a lot of databases (around 1000).
I came to this conclusion because I've tryed to move some databases on another server, and we got no problem loading the datas when there is less than around 30 databases on the server. If I try to move more databases, the loading time get longer and longer until I will not work at all.

We got not problem loading every databases and tables with another database client software.

I think that maybe there are some configuration to do on Power BI, unfortunately I can't figure out what I have to do, after a lot time looking for a solution.

Hoping my explainations are understandable.

Thanks a lot.

[Anonymous]

Good news. The latest version of Power Bi desktop of August 2022 fixes the problem. We must force the new parameter Auto-detect foreign key dependencies to "FALSE"

[gantonjo]

The problem is the way PowerBI checks for foreign keys between tables. When loading a preview of a table, PowerBI starts the following query:

SELECT
    A.REFERENCED_TABLE_SCHEMA PKTABLE_CAT,
    NULL PKTABLE_SCHEM,
    A.REFERENCED_TABLE_NAME PKTABLE_NAME,
    A.REFERENCED_COLUMN_NAME PKCOLUMN_NAME,
    A.TABLE_SCHEMA FKTABLE_CAT,
    NULL FKTABLE_SCHEM,
    A.TABLE_NAME FKTABLE_NAME,
    A.COLUMN_NAME FKCOLUMN_NAME,
    A.POSITION_IN_UNIQUE_CONSTRAINT KEY_SEQ,
    CASE update_rule
        WHEN 'RESTRICT' THEN 1
        WHEN 'NO ACTION' THEN 3
        WHEN 'CASCADE' THEN 0
        WHEN 'SET NULL' THEN 2
        WHEN 'SET DEFAULT' THEN 4
    END UPDATE_RULE,
    CASE DELETE_RULE
        WHEN 'RESTRICT' THEN 1
        WHEN 'NO ACTION' THEN 3
        WHEN 'CASCADE' THEN 0
        WHEN 'SET NULL' THEN 2
        WHEN 'SET DEFAULT' THEN 4
    END DELETE_RULE,
    A.CONSTRAINT_NAME FK_NAME,
    'PRIMARY' PK_NAME,
    7 AS DEFERRABILITY
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B ON
    (B.TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA
    AND B.TABLE_NAME = A.REFERENCED_TABLE_NAME
    AND B.COLUMN_NAME = A.REFERENCED_COLUMN_NAME)
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON
    (RC.CONSTRAINT_NAME = A.CONSTRAINT_NAME
    AND RC.TABLE_NAME = A.TABLE_NAME
    AND RC.CONSTRAINT_SCHEMA = A.TABLE_SCHEMA)
WHERE
    B.CONSTRAINT_NAME= 'PRIMARY'
    AND A.TABLE_SCHEMA = BINARY 'SOMEDATABASE'
    AND A.TABLE_NAME = BINARY 'sometable'
ORDER BY
    FKTABLE_CAT,
    FKTABLE_SCHEM,
    FKTABLE_NAME,
    KEY_SEQ,
    PKTABLE_NAME;

If you look at this query, you will see that it looks for (possibly missing) matches between all tables in the server, the table itself included. Even if the A.REFERENCED_% fields are empty (= NULL), the query looks for any possible matches to other tables in the server, causing an N^2 combination to look through. Similar for the REFERENTIAL_CONSTRAINTS. As you see, this quickly becomes a huge resultset to scan, even if the referenced A.column_names are empty.

[Anonymous]

Anybody have an idea, please ?

Thanks

[v-lionel-msft]

Hi @Anonymous ,

What's your version of PBI Desktop?

Mine is  2.83.5894.661 64-bit (July 2020).

And I must input the database parameter when I use the Mysql connector.

And only this database will be loaded.

Please update your version if you don't have the newest PBI Desktop, otherwise please create support ticket. 

Best regards,
Lionel Chen

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

[Anonymous]

Hello @v-lionel-msft 

I have the option for specifying the database when using the Power BI default option for connecting to MySQL database.

But NOT when using the MySQL provided connector (this one https://dev.mysql.com/downloads/connector/odbc/)

For me the problem is still that power BI cannot load anything when the DBMS contain more that around 150 distinct databases.

I opened a support ticket...

[amitchandak]

@Anonymous , hope you have installed latest ODBC https://dev.mysql.com/downloads/connector/odbc/

[Anonymous]

Hello @amitchandak, thanks for you answer.

Indeed, we tried with the MySQL ODBC connector, and even with MariaDB ODBC connector.
In both cases, the behaviour is almost the same that without connector.
Loading tables and then preview is taking time, and even more if there is more databases.
I just tried again with the ODBC connector you suggested. It allow me to access to the datas only if there is less thant around 30 databases, and it's slower than without connector, but acceptable.
So I tried with around 150 databases, and it takes a lot of time to load the datas (around 20 to 30 minutes).

If I try with even more databases, the tables list doesn't even seems to load...

It seems that Power BI need to load all the datas from every databases, even if we choose to load only one table from a particular database, am I wrong ?

[v-lionel-msft]

Hi @Anonymous ,

Power BI will only retrieve this database and return the tables in this database If you have entered the ‘Database' parameter.
 And you need to reset the timeout option if you have too many tables.

Best regards,
Lionel Chen

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

[Anonymous]

Hello @v-lionel-msft , thanks for your reply,

If I don't use connector, yes I specify the database and, indeed, PowerBI only displays this database and the tables in it.

But if the DBMS contains more than around 30 databases, the datas in the table will load really slowly, and if there is even more databases the datas will not load at all (which is the case here). And no databases contains a lot of tables.

Look (for the "mysql" database, with more than 1000 databases in the DBMS, for exemple) :

I can't get the preview of any table.

And if I still try to load only one table, I'm stuck here :

If I use the MySQL connector, I also specify the database in the connector configuration, but still it will display every databases.

It will also work with less than around 30 database, but if we add more, it will not be loadable at all because it won't even show the tables in a database :

Don't know what to do anymore. 😞