ChristopherUseless
commented
2 years ago Found a solution finally, that hopefully could be implemented in the MariaDB connector.
Source = Odbc.DataSource("Driver={MariaDB ODBC 3.1 Driver};Server=YourServer;Port=3306"";", [CreateNavigationProperties=false])
The last part, [CreateNavigationProperties=false] solves the whole issue, instead of staging all the tables when they are read into your model, where linked tables and records are shown as links that can be opened or extracted, you will now have to create the realtionships yourself. So you get less help, but you get a "normal" relational database where you join your tables by choice. It will load data in no-time, with this solution.
ilyagithub1
When running on a database with few tables, connecting powerBI to MariaDB works like a charm. But when the number of tables grows, PowerBI becomes unresponsive, and can't open the smallest of tables. The problem is the way PowerBI checks for foreign keys between tables. When loading a preview of a table, PowerBI starts the query below. 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. This check is performed on the tables your user have access to, so limiting access can reduce the problem. But still this is a rather unnecessary check, where it checks the relations between hundreds of tables, even if you only ask for data in 1 table. The query seems to be the same when it enters the MariaDB, regardless if PowerBI has connected via ODBC or using the connector provided by MariaDB. Are anyone in here familiar with this problem, and more importantly, does anyone have a workaround that is better than splitting our databases over multiple servers and/or limiting user access?
I see that other ODBC connectors such as cData have an attribute "EnableForeignKeyDetection" which is by default set to False, this would probably solve this issue. https://cdn.cdata.com/help/SRG/powerbi/RSBHelpinfo_p_EnableForeignKeyDetection.htm
Enclosed is the code that enters our MariaDB:
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;