Optimize Power BI dashboard by using Exact Online sync APIs

Nederlandse versie beschikbaar op Optimaliseer Power BI dashboard door Exact Online sync API's te gebruiken.

Exact Online is busy to reduce the load on the Exact Online API servers by reducing usage. The main focus is on intensive usage: importing a lot of data on a frequent basis.

The Microsoft Power BI connector for Exact Online on Invantive Cloud takes care of many optimizations as a result of which the load is significantly lower than of other Power BI connectors for Exact Online. But this also attracts especially the large Exact Online users to Invantive Cloud. For the larger users there are a number of options to quickly improve the performance and meet the demands of Exact Online.

A good step to optimize Power BI dashboards and reports is to study Overview of Power BI Performance and Download Size Improvement Techniques.

Additionally you may have received a message from Invantive because you are importing a table into Power BI for which a more efficient alternative exists. These alternatives are based on the Exact Online sync APIs and can be up to 100x faster when importing into Power BI.

You can replace the following tables in the first column with the alternative in the second column:

Old Exact Online table New Exact Online table
ExactOnlineREST.CRM.Accounts AccountsIncremental
ExactOnlineREST.CRM.AccountsBulk AccountsIncremental
ExactOnlineREST.CRM.Addresses AddressesIncremental
ExactOnlineREST.CRM.AddressesBulk AddressesIncremental
ExactOnlineREST.CRM.Contacts ContactsIncremental
ExactOnlineREST.CRM.ContactsBulk ContactsIncremental
ExactOnlineREST.CRM.QuotationLines QuotationLinesIncremental
ExactOnlineREST.CRM.QuotationLinesBulk QuotationLinesIncremental
ExactOnlineREST.Documents.DocumentAttachments DocumentAttachmentsIncremental
ExactOnlineREST.Documents.DocumentAttachmentsBulk DocumentAttachmentsIncremental
ExactOnlineREST.Documents.DocumentAttachmentFiles DocumentAttachmentFilesIncremental
ExactOnlineREST.Documents.DocumentAttachmentFilesBulk DocumentAttachmentFilesIncremental
ExactOnlineREST.Documents.Documents DocumentsIncremental
ExactOnlineREST.Documents.DocumentsBulk DocumentsIncremental
ExactOnlineREST.Financial.GLAccounts GLAccountsIncremental
ExactOnlineREST.Financial.GLAccountsBulk GLAccountsIncremental
ExactOnlineREST.FinancialTransaction.TransactionLines TransactionLinesIncremental
ExactOnlineREST.FinancialTransaction.TransactionLinesBulk TransactionLinesIncremental
ExactOnlineREST.FinancialTransaction.Transactions TransactionLinesIncremental
ExactOnlineREST.Logistics.Items ItemsIncremental
ExactOnlineREST.Logistics.ItemsBulk ItemsIncremental
ExactOnlineREST.SalesInvoice.SalesInvoiceLines SalesInvoiceLinesIncremental
ExactOnlineREST.SalesInvoice.SalesInvoiceLinesBulk SalesInvoiceLinesIncremental
ExactOnlineREST.SalesInvoice.SalesInvoicesBulk SalesInvoiceLinesIncremental
ExactOnlineREST.SalesInvoice.SalesInvoices SalesInvoiceLinesIncremental
ExactOnlineREST.SalesOrder.SalesOrderLines SalesOrderLinesIncremental
ExactOnlineREST.SalesOrder.SalesOrderLinesBulk SalesOrderLinesIncremental
ExactOnlineREST.SalesOrder.SalesOrders SalesOrderLinesIncremental
ExactOnlineREST.SalesOrder.SalesOrdersBulk SalesOrderLinesIncremental

You can also use the free Power BI performance audit as described at How do I get a free Power BI performance audit? for advice on optimization.

Please note that derived data such as the name of a customer in the AccountName field in TransactionLinesBulk is not present in the fast versions based on the design chosen in Exact. However, you can easily add the required table in your data model and establish a relationship (in this case from Id in Accounts to Account in TransactionLinesIncremental).

Note 2: for transaction tables (the ones with *Lines), LineNumber 0 has a special meaning: this is the header of the other lines. See also Special line number such as 0 and 9999 on financial transactions in Exact Online. Exact Online shows more of the internal structure with the sync APIs than with previous API’s.