Pivot tables

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?

1. SQL-query is fired into database
2. All resulting rows are loaded into temporary directory of BI server
3. If there are calculations to be performed — initial temp file is read, calculated and new temp written out

There are two important instanceconfig.xml parameters for Pivot tables:

• CubeMaxPopulatedCells The maximum number of populated cells in the Pivot Table. If this maximum is exceeded, the user receives an error when rendering the pivot table. * CubeMaxRecords The maximum number of records returned by a query for the Pivot Table engine to process. This roughly governs the maximum number of cells that can be populated in a pivot table (unpopulated cells in a sparse pivot table do not count). If this maximum is exceeded, the user receives an error when rendering the pivot table.

All instanceconfig.xml options can be found here.

Temp files.

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.

Speeding things up.

Large files are written to temporary directory, so the IO performance becomes an obvious bottleneck for process. What can be done:

1. Use multiple tmp\ directories on different disks to balance the load
2. Use Ramdisks for tmp: I’ve tried this way and got a some fantastic 5x speedup on most IO bound reports. But it’s pretty hard to find free 30 Gb of RAM, so we’re using multiple disks for now
3. Use an SSD for tmp\ – that’s what I plan to try next. I bought an SSD for my laptop about a year ago and my life has never been the same, so stay tuned )