Your Business Central Reports Are Slow Because You’re Asking BC to Be a Data Warehouse

Your Business Central reports take too long to run. Power BI refreshes fail or drag on. Someone exports a large dataset to Excel and waits. Meanwhile, users complain that Business Central itself feels slower.

The first instinct is usually to tune Business Central.

Sometimes that is exactly what you should do.

But sometimes the report is slow because you are asking an ERP system to do work that belongs in an analytics platform.

There is a difference between a poorly performing report and an analytical workload that should never have been running against your transactional system in the first place.

If you are dealing with Business Central slow reports, figure out which problem you have before you start changing code.

A posting engine and an analytics engine want opposite things

Business Central is fundamentally a transactional system. It is designed to post invoices, receive inventory, create purchase orders, apply payments, update ledgers and let users work with current operational data.

Those transactions tend to be relatively small and frequent. Analytical workloads behave differently. They can read large volumes of data, join information from multiple entities and run for much longer.

Microsoft specifically warns that mixing transactional and analytical workloads can create performance problems. Long-running analytical transactions can contribute to locking and can displace data from the database cache that transactional processes rely on.

That is why Microsoft supports Read Scale-Out, which is available automatically with Business Central Online. Eligible read-only workloads can use a secondary database replica rather than competing with the primary database handling transactions. Reports, API pages and query objects can be designed to use this capability.

It helps, however, it does not turn Business Central into a data warehouse.

A replica can isolate some analytical work from transactional work. It does not make an inefficient query efficient, reduce an unnecessarily large dataset or fix a report trying to calculate years of history every time somebody opens it.

That distinction matters.

Five report patterns that will always be slow inside Business Central

“Always” needs one qualification here: data volume, extensions and report design matter. But these five patterns become increasingly expensive as your Business Central database grows.

1. Reports reading far more rows than the user actually needs

A report may display 20 columns and one fiscal year while its underlying dataset retrieves considerably more. That extra work happened when it wasn’t required.

Microsoft recommends using partial records to retrieve only the fields required and applying filters as early as possible. The same principle applies when Business Central data is consumed through APIs or Power BI: retrieve the rows and columns you actually need.

2. Analytical reports joining multiple large datasets

Microsoft distinguishes between reports focused on a single entity, such as an invoice, and analytical reports joining multiple instances across multiple entities. It specifically identifies the latter category as the one where report performance problems typically occur.

A simple sales invoice is one thing. A report combining sales history, customers, items, and profitability across several years is another.

At some point, that stops being operational reporting and starts becoming analytics.

3. Reports repeatedly calculating information that could be prepared elsewhere

If users repeatedly request the same historical aggregations, the system repeatedly has to do work to produce them.

This is where a semantic model, data warehouse or other analytical layer starts earning its keep. Historical information can be prepared for analysis rather than retrieved from transactional data every time somebody wants a chart.

4. Power BI models pulling excessive Business Central data

Power BI does not automatically solve a Business Central performance problem.

A badly designed Power BI model can simply move the problem.

Microsoft recommends query folding, filtering data at the source and selecting only required columns when retrieving Business Central data. If your model retrieves everything and filters it afterward, fix the model before blaming Business Central.

5. Reports trying to serve as an enterprise data warehouse

This is the clearest warning sign.

You have multiple Business Central companies. You want several years of history. Then you add CRM data, POS transactions, e-commerce activity, manufacturing information or external operational data.

Business Central is now being treated as one component of an analytical data platform. Therefore, it’s important to first build the analytical platform. Don’t keep adding reports to the ERP and hope another index will make the architecture disappear.

What tuning actually fixes

There is plenty worth tuning before you move anything. Microsoft provides several tools for diagnosing Business Central performance, including telemetry, the AL Profiler, database performance pages, page inspection and long-running SQL query telemetry.

That means you can stop diagnosing performance by anecdote and start with the report itself. Ask the following questions:

• Is it reading more records than expected?
• Is SQL execution slow?
• Is report rendering taking most of the time?
• Is an extension adding expensive logic?
• Is there a missing index?
• Is the report running with read-only data access where appropriate?

Microsoft’s own troubleshooting process recommends establishing an acceptable report time, capturing baseline telemetry and examining long-running SQL queries and report rendering before making changes.

From there, the following tuning helps:

• Reduce the fields being loaded
• Filter earlier
• Use AL queries where appropriate
• Improve keys or indexes
• Change inefficient extension code
• Configure appropriate reports for read-only access

Report layout can matter as well. Microsoft notes that RDL layouts can be slower than Word layouts for some document-report operations.

These are real fixes. They can turn a badly designed report into a good one.

What they cannot do is turn the wrong architecture into the right one.

Where tuning stops helping

Tuning stops being the primary answer when the report is slow because the business requirement itself requires large-scale analytical processing.

If users routinely need broad historical datasets, complex cross-company analysis or information joined across several business systems, shaving time from an individual SQL query does not address the underlying problem.

You are optimizing the route when you should be changing the destination.

There is another warning sign: the same data is being extracted repeatedly for different reports.

Finance has one Power BI model. Sales has another. Operations has another. Each independently asks Business Central for overlapping sets of customers, items, sales entries and dimensions.

At that point, centralizing analytical data can be more sensible than making every reporting workload query the ERP separately.

Important note: do not move reporting out of Business Central just because one report is slow.

If an operational report that should complete quickly suddenly takes much longer, investigate it.

A slow report may be caused by an extension, inefficient AL code, unnecessary fields, poor filters, an inappropriate layout, missing indexes or a change introduced in an update.

Moving a badly written report into Fabric does not make the bad design good. Fix genuine Business Central performance problems first.

Cheapest and easiest option first: moving analytical load off Business Central

Start with the smallest change that solves the problem.

1. First, optimize the existing Business Central report: remove unnecessary data, improve filtering, examine the queries and use read-only access where appropriate. Cost: no new data platform required.
2. Second, use Power BI against purpose-built Business Central APIs: Business Central provides APIs and OData web services for external reporting, with Microsoft recommending API pages and queries for Power BI scenarios. For Business Central Online, Power BI reports created using the current connector defaults read from a secondary, read-only database replica — Microsoft says this has been the default for new reports since February 2022. For a controlled reporting requirement, that may be all you need.
3. Third, introduce a separate analytical data layer: once you are combining substantial Business Central history with multiple companies or external systems, consider moving the analytical workload into a dedicated architecture such as Microsoft Fabric.

Business Central remains responsible for transactions. Your analytical platform becomes responsible for analytics. Do not buy Fabric because a report takes 45 seconds instead of 10. Use it when the reporting requirement has outgrown the transactional system.

The part that sticks with you

If your Business Central reports are slow, do not start by buying more technology.

Fix inefficient reports, queries, extensions and Power BI models. Use Business Central’s read-scale capabilities where they fit. Then look at what remains.

If the remaining problem is large historical analysis, cross-company reporting or combining Business Central with multiple other systems, stop trying to tune Business Central into a data warehouse.

Move the analytical workload where it belongs.

Qixas Group works across Business Central performance tuning, Power BI and Microsoft Fabric. That matters because the recommendation should not depend on which product somebody wants to sell you. We also tune Business Central and NAV performance directly when that is the actual fix.