Performance considerations
A pivot table report is capable of handling large amounts of data without exhausting memory, as long as some constraints are met.
If tabular output is selected and other constraints are met, output is produced without delay and memory consumption is nearly constant. The processing time is proportional to the input length; for very large data sets it is advisable to aggregate the data in the database.
Processing should be latency free
A chart displays on a single page. As such, it displays only after all data has been processed.
When outputting a table, the output can span multiple pages. Data can be output during processing, a page can be returned well before all data is processed. Yet selecting this visualization type alone does not ensure latency free processing; the data must be pre-sorted (See the hierarchiesInputOrder property). If the data is partially sorted, there can be periods of delay while the processor waits for the end of a block of data that needs to be sorted.
Pre-sorting data reduces memory consumption
Sorting is done in memory. Very large reports should therefore be run on (partially) pre-sorted data (See the hierarchiesInputOrder property). Output sorting is also done in memory (See the outputOrder property) and should be used with equal care. Suppressing the display of the fact rows (See the displayFactRows property) can significantly reduce memory consumption.
Not sending duplicate values reduces processing time
In the case that data is pre-sorted (see the hierarchiesInputOrder property), an optional, more compact form of data representation can be chosen that allows omitting dimension values that did not change from one row to another, thereby improving performance.
For example, after shipping the first fact row {“Afghanistan”,”1 North”,..} all subsequent rows that contain measure for north Afghanistan need not ship these two dimensions anymore. When the first row of the next block {“Afghanistan”,”3 South”,..} is reached only the value “3 South” needs to be reported once on the first row of the block. See Pivot Table Hierarchy Value.