MySQL Data Load takes ages. Any tips how to decrease the loading time?

[NicoSteininger]

Hey guys, first question here.

I´ve connected Power BI Desktop to a MySQL data source. I want to load 5-6 tables, but one of them is a big table with about 8 million rows and growing.

The report itself is super fast and I have no problem interacting within the report. However, updating the data and loading the latest data takes ages (+15 min.). Here´s what I´ve done so far: - Updating Power BI to its latest version. - Using SQL-Querys within the data import so only the columns I need are imported. - Filtering the data so I only get the rows I need.

Could it be solved if I used Views in MySQL instead of loading the table? Or do you have any other tips on how I can decrease the loading time?

Thanks in advance!

[v-piga-msft]

Hi @NicoSteininger ,

Actually, the way you used currently is good.

Normally, if your data is very large, the load process should take a long time.

I'm afraid that if you load the view instead of table, the performance may be better but not obvious. That is similar to load with SQL Query in power bi.

For another choice, you may try direct query mode which is always used for larger data model, but there are some limitations for direct query mode, please have a look at this before using it.

In addition, you could have a reference of the article which give us some advice for data import best practices in power bi.

Best Regards,

Cherry

[NicoSteininger]

Hey @v-piga-msft ,

thank you for your answer! Good to know that the query works as intended.

However, I took some time and narrowed the error sources down and I think it comes down to pivoting and unpivoting the data.

I have kind of the similar problem as presented here: https://community.powerbi.com/t5/Desktop/Combining-rows-based-on-unique-id-and-combining-information...

Since the data contains 2M+ rows and I need to pivot for data type, date and time and then unpivot again and so on, this takes ages.

Do you have any idea how I can optimize this again?

Here is a screen shot of how it looks and how I want it to look like:

The column data_type contains typee 9 and far further down 18. 9 marks the beginning of an ID, 18 marks the end of an ID. On the right in column "Benutzerdefiniert" I have Start and Endtime.

What I want it to look like is like this. ID number for this issue, value of data type 9, value of data type 18, start time, end time, start date, end date.

It worked when it only had a few thousand rows, but now it is too much.

I´d really appreciate if you could look into this once more, and thanks already for your advice!