This is what happens in the video
When you export forecast data to Excel from Business Central, the number of split parameters you select has a direct impact on performance. The slowdown comes from the number of records being created, and each parameter you split on multiplies that number.
A simple export filtered down to two items and four salespersons creates 75 forecast lines and takes about 5 seconds. Add more customers and split per dimension across 15 dimensions, and the same export jumps to 3,480 lines.
In a large scenario with 2,000 items, 15 locations, 20 salespersons, 200 customers, and 10 dimensions, you end up with 1.2 billion records, which makes the export practically unusable.
Before you export, limit your parameters and use filters on locations, salespersons, customers, and items to keep the line count manageable.
How the number of export parameters affects performance
When you export to Excel, the export speed depends almost entirely on how many records you create. The more split parameters you select, the more lines the export generates, and the longer it takes to run.
Each split parameter multiplies the number of records. If you split per location, per salesperson, per customer, and per dimension, the totals from each split are multiplied together. That is why a seemingly small change in your selection can lead to a dramatic increase in processing time.
A simple export example with 75 forecast lines
Here is a straightforward case. You select split per location, but filter on location code Basic. You split lines per salesperson, and you have four salespersons. You split lines per customer, but place a filter on customers, so you are only looking at two items.
This export takes about 5 seconds. When you open the Excel sheet, you can see that even though it only covers two items, it creates forecast lines for nine customers across four salespersons. The result is 75 forecast lines.
How splitting per dimension increases the line count
Now take a slightly different export from the same database. You export on one location, four salespersons, and two items, but this time you do not place a filter on customers, so you have more customers included. You also export per dimension, and you have 15 different dimensions.
Instead of 75 lines, the export now creates 3,480 lines. The jump comes from adding the dimension split and removing the customer filter.
How large exports reach billions of records
The examples above are small. Consider a real-world scenario where you export 2,000 items across 15 locations, because you have people driving around in cars setting up each location. Add 20 salespersons, 200 customers, and 10 dimensions.
Multiply all of those together, and you end up with 1.2 billion records exported to Excel. An export that size will take an impractical amount of time to complete, on the order of two weeks.
How to keep your Excel exports fast
The export gets slow very quickly, so be aware of how many lines you split into. Every time you add a split on a parameter, you multiply the number of records you create.
To keep performance reasonable, limit the parameters you split on and apply filters. Filter on location code, on customers, and on items so that the total number of forecast lines stays manageable.
Q&A
Why does my Excel export run slowly?
The export speed depends on how many records you create. Each split parameter you add multiplies the number of forecast lines. Selecting too many parameters generates a huge number of records, which slows the export significantly.
How can I make my export faster?
Limit the number of split parameters and apply filters. Filter on location, customers, and items to reduce the total number of lines. The fewer records you create, the faster the export runs.
How many records can a large export create?
In a scenario with 2,000 items, 15 locations, 20 salespersons, 200 customers, and 10 dimensions, the export creates 1.2 billion records. An export that large takes an impractical amount of time to complete.
What happens when I split per dimension?
Splitting per dimension multiplies your line count by the number of dimensions. In one example, adding a split across 15 dimensions and removing the customer filter took an export from 75 lines to 3,480 lines.