MySQL Database access: What ist the most efficient way to minimize to database host load

[alexrobe]

Hi,

I am new to Database access via PowerBI, so please accept my apologies for raising a maybe newbie question.

I want to migrate my Excel imports currenly 7 files with production defect data from 4 mannufacturing sites to direct access to its originating databases.

All databases are currently hosted on the same server.

I have PowerBI 64 bit and mysql-connector-net-6.6.5.msi running.

Workflow:

1. I import all the defects lists (4 sites with 2 languages each, besides US, which is just english)
2. I do some reformatting merging für each
3. Then I merge all 7 tables into one IQM_World table

Sorry for german screenshots:

PROBLEM:

During my test phase I ecnountered a heavy load on the database server lasting for more than 15 minutes, resulting in more or less inaccessability for its production purposes.

Though the basic tables from the MySQL database load rather fast (max. 10s each) the aggregating table seems like the load killer, as there is unterneath the info, that it is connecting to the MySQL-server

In order to minimize the pbix-filesize, I disabled the loading of all previous tables and just enabled the final IQM_World table

NEED:

- I just want to pull the basic tables into Power BI

- all additional operations shall be done with the imported data and NOT reimported again

QUESTION:

- What do I have to set up, that my MySQL-interaction is minimized? (which table load settings - deactivate loading; include in updates?)

- What can I do to ensure, that only the basic tables are filled and afterwards no DB interaction?

- can i also deactivate the basic import date tables (blue)?

- Any other features I should disable in the main settings (like simultaneous loading; automatic date/time...)

ps.: the Code for IQM-World is:

let
    Quelle = IQM_AT_EN_DB,
    #"Angefügte Abfrage" = #!"Table.Combine({Quelle, IQM_AT_DE_DB, IQM_BR_EN_DB, IQM_BR_PT_DB, IQM_CN_EN_DB, IQM_CN_ZH_DB, IQM_US_EN_DB})",
    #"Gefilterte Zeilen" = Table.SelectRows(#"Angefügte Abfrage", each true),
    #"Datum eingefügt1" = Table.AddColumn(#"Gefilterte Zeilen", "Date", each DateTime.Date([Date of capture]), type date),
    #"Uhrzeit eingefügt" = Table.AddColumn(#"Datum eingefügt1", "Time", each DateTime.Time([Date of capture]), type time),
    #"Datum eingefügt" = Table.AddColumn(#"Uhrzeit eingefügt", "DateProd", each if [Time] >= #time(22, 0, 0) then Date.AddDays([Date], 1) else [Date] ),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Datum eingefügt",{"Date", "Time"}),
    #"Neu angeordnete Spalten" = Table.ReorderColumns(#"Entfernte Spalten",{"Date of capture", "UniqueLineName", "Article code", "Article serial", "ArtCode_SerNr", "Defect type", "Defect", "Location", "Shift", "Repair date"}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Neu angeordnete Spalten",{{"Article code", "Mat-Nr."}, {"Article serial", "Ser-Nr."}, {"ArtCode_SerNr", "Mat-Ser-Nr."}, {"Location", "Linename"}, {"Assignment", "Supplier?"}, {"Defect type", "Defect Location"}, {"Defect", "Defect Type"},{"Date of capture", "Capture Time"}}),
    #"Hinzugefügte bedingte Spalte" = Table.AddColumn(#"Umbenannte Spalten", "Points", each if [Classification] = "A" then "10" else if [Classification] = "B" then "5" else if [Classification] = "C" then "1" else "0" ),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Hinzugefügte bedingte Spalte",{{"DateProd", type date}, {"Points", Int64.Type}}),
    #"Zusammengeführte Spalte eingefügt" = Table.AddColumn(#"Geänderter Typ2", "Defect", each Text.Combine({[Defect Location], [Defect Type]}, " - "), type text),
    #"Sortierte Zeilen1" = Table.Sort(#"Zusammengeführte Spalte eingefügt",{{"Capture Time", Order.Descending}})
in
    #"Sortierte Zeilen1"

Thank you very much!

[v-yuezhe-msft · Accepted solution]

@alexrobe,

Based on my test, when merging tables in Power BI Desktop, Power BI will push the queries that select data from tables up and run it on the database server side.

Except the settings that you have disabled in Power BI Desktop, please add Table.Buffer function in the codes of your advanced editor to reduce the load on the database server.

Regards,
Lydia

[alexrobe]

Thank you @v-yuezhe-msft!

It indeed seems to improve loading times significantly!
Still I wonder where to put in this command? Currently I have it in the very first line only...

eg. for my table "ts_assemblyunit":

let
    Quelle = Table.Buffer(MySQL.Database("srvqm01", "iqm", [ReturnSingleDatabase=true, CreateNavigationProperties=false])),
    iqm_ts_assemblyunit = Quelle{[Schema="iqm",Item="ts_assemblyunit"]}[Data],
    #"Entfernte Spalten" = Table.RemoveColumns(iqm_ts_assemblyunit,{"DEPTH"})
in
    #"Entfernte Spalten"

- would it also/more make sense at the End of the import?

e.g.:

#"Entfernte Spalten" = Table.Buffer(Table.RemoveColumns(iqm_ts_assemblyunit,{"DEPTH"}))

- would you only recommend using Table.Buffer() for the imported tables or also for large tables based on the imported tables

let
    Quelle = Table.Buffer(#"ts_assemblyunit"),
   ....
   lots of manipulation steps
   ... 
   final result = Table.Buffer(#'last step')
in
    final result