Edit

Share via

Power BI dataset load performance

  • Article

The performance of Power BI datasets based on Business Central APIs and web service endpoints can be drastically improved if you know how to load only the data that you need into your Power Query data models.

Power BI performance patterns

When calling APIs and OData web services from Power BI, it's important that you understand the concepts of query folding. The Business Central connector uses the OData.Feed library behind the scenes, which translates numerous the Power BI constructs into OData query parameters. For example:

  • Table.RemoveColumns or Table.SelectColumns becomes $select
  • Table.SelectRows becomes $filter
  • Table.ExpandTableColumn becomes $expand

The following example illustrates how filters are pushed from Power BI to the Business Central connector.

It is common practice to have a query that refreshes the current date and time at every refresh in your Power BI dataset:

// LastRefreshDate query
let
    Source = DateTimeZone.UtcNow()
in
    Source

To apply a filter such as “the last 12 months of data” to other queries, simply use the PowerQuery Table.SelectRows on appropriate dates, for example:

// SalesInvoices
// Filtered to last 12 months
let
    Source = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, null),
    PRODUCTION = Source{[Name="PRODUCTION"]}[Data],
    #"CRONUS USA, Inc." = PRODUCTION{[Name="CRONUS USA, Inc."]}[Data],
    v2.0 = #"CRONUS USA, Inc."{[Name="v2.0"]}[Data],
    salesInvoices_table = v2.0{[Name="salesInvoices",Signature="table"]}[Data],
    filteredRows = Table.SelectRows(salesInvoices_table, each [postingDate] > Date.From(Date.AddMonths(LastRefreshDate, -12)))
in
    filteredRows

For a LastRefreshDate of 2022-08-17, the Power BI connector adds this $filter predicate to the API call: /v2.0/PRODUCTION/api/v2.0/salesInvoices?$filter=postingDate gt 2022-08-17&company=CRONUS USA, Inc.

For more information about query folding in Power BI, see Power Query query folding.

Note

Query folding optimizations might not be applied in a few cases:

  • Query folding is NOT applied when loading data preview (such as while you are editing your query and data updates in the query editor screen).
  • Be careful if you do joins with other data in Power Query. If you do, some OData.Feed limitations might apply, and the OData parameters will be applied client side instead. The Workaround from the Power BI team is to use Table.Buffer before doing any join. For more information, refere to Known issues and limitations in OData-based Power BI connectors.

For more information about OData/API client performance in Business Central, see OData query performance.

When refreshing a query in a Power BI semantic model, you might experience an error such as DataSource.Error: OData: Unable to read data from the transport connection: existing connection was forcibly closed by the remote host. This error can happen either when the query refresh was interrupted by a transient error (in this case, just implement a retry) or if the query can't finish within the limit defined for web service calls on the Business Central server. In the latter case, the Business Central server will cancel the call. If this happens consistently, you need to tune your query, either by applying data filters, by reducing the number of columns (do you use all of them?), or maybe by partitioning the table (using custom partitioning through the XMLA endpoint for the model).

Web service telemetry

All incoming calls to Business Central web services are logged to partner telemetry. Telemetry enables you to monitor which endpoints are being used and the category of the web service, like SOAP, OData, or API. You can also see possible failures, which are tracked in the HTTP status codes for the calls.

If you filter this telemetry data to the OData or API categories and also filter on the HTTP header ms-dyn-useragent, you can monitor usage, performance, and errors of Power BI traffic to Business Central.

To make it easier to get started using Azure Application Insights with Business Central, samples of KQL code are available in the Business Central BCTech repository on GitHub. For analyzing web service call telemetry from the usage of the Microsoft connector (Power BI, Power Apps, and so on), then the query MicrosoftConnectorUsage.kql might be useful.

For more information, see Web service telemetry.

See also

Web Services Overview
Web Services Best Practices
Web service telemetry
Analyzing Incoming Web Services Request Telemetry
Performance Articles For Developers