Applied Dimensionality

Pivot Tables and temp files in Oracle BI EE

Posted at — Aug 27, 2010
Pivot Tables and temp files in Oracle BI EE

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:

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 )
comments powered by Disqus