1. Essbase Independent Deployment
  2. Configure Oracle Essbase
  3. Advanced Configuration Topics
  4. Expand Limit for SQL IN Clauses in Drill Through Reports

Expand Limit for SQL IN Clauses in Drill Through Reports

Resolve a drill through reports error by increasing the SQL IN clause limit beyond the default of 1000 members.

If drill through reports fail with error

'ERROR: relation <member name> does not exist'

The failure occurs because SQL IN clauses are limited to 1000 members by default. This default exists for Essbase and for Oracle Database.

Caution:

The Essbase platform includes scripts in <DOMAIN HOME>/bin that can customize the environment and behaviors of Essbase functionality. However, making changes to these domain environment or startup scripts can have unintended effects, including startup failure. Oracle recommends making changes in a test environment first. Before editing these scripts, always:

  1. Stop the Essbase managed servers, using <DOMAIN HOME>/esstools/bin/stop.sh (on Linux), or <Domain Home>\esstools\bin\stop.cmd (on Windows).

  2. In <DOMAIN HOME>/bin, make a backup copy of the file you want to edit. For example,

    On Linux

    cp setStartupEnv.sh setStartupEnv_bak.sh

    On Windows

    copy setStartupEnv.cmd setStartupEnv_bak.cmd

  3. Edit carefully, using only Oracle’s documented instructions, or working with Oracle Support.

  4. Restart Essbase, using <DOMAIN HOME>/esstools/bin/start.sh (on Linux), or <Domain Home>\esstools\bin\start.cmd (on Windows). Check that startup completed normally.

To increase the SQL IN clause limit for drill through, if expanded limits are supported by your external database,

  1. Stop the Essbase services (see Stop, Start, and Check Servers).
  2. On the machine where Essbase is deployed, navigate to <Domain Home>/bin (see Environment Locations in the Essbase Platform).
  3. Make a backup copy of the startup script, setStartupEnv.sh (Linux) or setStartupEnv.bat (Windows). Save it with a different name; for example: cp setStartupEnv.sh setStartupEnv_orig.sh
  4. Open setStartupEnv.sh or setStartupEnv.bat for editing.
  5. Add the configuration lines to increase the limit beyond 1000.

    Linux Example (setStartupEnv.sh):

    JAVA_OPTIONS="${JAVA_OPTIONS} -Ddtr.in.clause.limit=15000" export JAVA_OPTIONS

    Windows Example (setStartupEnv.bat):

    set JAVA_OPTIONS=%JAVA_OPTIONS% "-Ddtr.in.clause.limit=15000"
  6. Save setStartupEnv.sh or setStartupEnv.bat.
  7. Start the Essbase services (see Stop, Start, and Check Servers).