Pivot tables are by far most used data view in Oracle BI Answers, since they provide ‘multidimensional’ analysis capabilities, aka dimension drag&drop, filtering and some basic calculations.
From server-side view: this component provides worst performance, since all future ‘cube’ data has to loaded into BI server, aggregations and formulas calculated and transformed to internal format.
So what happens when user clicks a pivot for 200,000 cells cube?
There are two important instanceconfig.xml parameters for Pivot tables:
All instanceconfig.xml options can be found here.
Temp files can be rather huge, I’m currently seeing files about 1gb size for medium sized reports. That, surely, because we’re using MS SQL server and BI Server cannot “push” some calculations in database, but it’s nice to keep in mind the fact that temp files can occupy some 20-30 Gb.
Temp files are cleaned out when session logs out, so some of them stay there for whole day.
Location of temporary files is set up by WORK_DIRECTORY_PATHS variable in NQSConfig.ini and by SATEMP enviroment variable. See this post for instructions on how to move tmp folder.
Large files are written to temporary directory, so the IO performance becomes an obvious bottleneck for process. What can be done: