Improve performance of loading Exact Online data into Power BI

I have built a connection with Power BI and Exact Online. I have built a PBI model with these 5 tables:

  • ExactOnlineREST Financial GLAccountClassificationMapping@eol
  • ExactOnlineREST Financial GLAccounts@eol
  • ExactOnlineREST Financial GLClassifications@eol
  • ExactOnlineREST Financial GLSchemes@eol
  • ExactOnlineREST Financial ReportingBalance@eol

The first four are to use the charts of accounts built into Exact Online and the last one is the one where I find the data to build the balance sheet and P&L.

But every time I refresh the data it takes more than 10 minutes

I have reduced the data to only 5 years (2017 to 2021)

Is that normal that it takes so much time or did I miss something or did I use the wrong tables?

Is it possible to add the approximate number of rows to each table and the approximate duration in seconds?

10 minutes for one or a few companies for these is long. I would recommend the following changes:

  • Replace GLAccounts by either GLAccountsIncremental or optionally GLAccountsBulk.
  • Do not use ReportingBalance but replace it by the much faster BalanceLinesPerPeriod (when not using cost centers nor cost units) or BalanceLinesPerPeriodCostAnalysis (when needing numbers per cost center and/or cost unit).

Also, GLClassifications has been changed by Exact Online to include very many mappings (this change may have been reverted in between time). Please refer to Efficient werken met GLClassifications voor rapportages over veel Exact Online administraties, but since that one is in Dutch a summary:

  • Replace GLClassifications by GLClassificationsBulk (10x faster).
  • Or filter out the “Referentie GrootboekSchema” entries, which are over 10.000 lines you are probably never using per Exact Online company. Do not use GLClassificationsBulk in that case and stick to GLClassifications , but add a Power BI filter step SourceDivision is not null` directly after data retrieval (300x faster).