1. Configuration Reference for Oracle Essbase
  2. Query Logging Configuration
  3. Query Log Settings File Syntax

Query Log Settings File Syntax

The query log settings filename must be of the form dbname.cfg, where dbname represents the name of a database. The dbname.cfg file must be located in the ARBORPATH\App\appname\dbname directory of Essbase. The dbname.cfg file consists of the following syntax: 

QUERYLOG [dimension_name]
QUERYLOG NONE GENERATION generation-range
QUERYLOG NONE LEVEL level-range
QUERYLOG GENERATION generation-range
QUERYLOG LEVEL level-range
QUERYLOG LOGPATH path-expression
QUERYLOG LOGFORMAT CLUSTER | TUPLE
QUERYLOG LOGFILESIZE n
QUERYLOG TOTALLOGFILESIZE n
QUERYLOG ON | OFF

Table 4-1 QUERYLOG Parameters

QUERYLOG Parameter Description
[dimension_name] Identifies the dimension name to be tracked. The brackets around the dimension name are required. QUERYLOG [dimension_name] logs all members of a dimension. For example, QUERYLOG [Product] tracks all members of the Product dimension. Each dimension must be specified in a separate QUERYLOG [dimension_name] setting.
NONE GENERATION Table 4-2 Prevents tracking of members from the specified generation range. For example, QUERYLOG NONE GENERATION 2 excludes tracking of all members from generation 2 of the named dimension.
NONE LEVEL Table 4-2 Prevents tracking of members from the specified level range. For example, QUERYLOG NONE LEVEL 0-2 excludes tracking of all members of levels 0, 1, and 2 of the named dimension.
GENERATION Table 4-2 Tracks members of the specified generation range by generation number, rather than by member name. For example, QUERYLOG GENERATION 5-7 logs members of generations 5, 6, and 7 of the named dimension by their generation number in the log file.
LEVEL Table 4-2 Tracks members of the specified level range by level number, rather than by member name. For example, QUERYLOG LEVEL -3 logs members of levels 0, 1, 2, and 3 of the named dimension by their level number in the log file.
LOGPATH path-expression Specifies the location of the output log file. The log file name is dbname00001.qlg; for example, basic00001.qlg. Examples of the log path are QUERYLOG LOGPATH /usr/local/Essbaselogs/ and QUERYLOG LOGPATH d:\Essbaselogs\querylogs\. You must include a backslash \ (for Windows directories) or forward slash / (for UNIX directories) at the end of the path expression; otherwise, the query log file is not created.

By default, the location for the log output file is the ARBORPATH\App\appname\dbname\ directory. If the LOGPATH path-expression setting is missing, the default is used. Essbase writes log information to the query log file after an application stops running.

LOGFORMAT CLUSTER | TUPLE Specifies the format of the log output. CLUSTER and TUPLE provide the same log information, but display the information differently. CLUSTER provides information on how many members of a dimension were queried and lists queried members within their respective dimensions. TUPLE lists each queried member combination. By default, CLUSTER is the log format. Because the TUPLE format lists each member combination queried, TUPLE may have a greater impact on query performance than CLUSTER. See Sample Cluster Output for an example of a query log in cluster format. See Sample Tuple Output for an example of a query log in tuple format.
LOGFILESIZE n Specifies the maximum size of an individual query log file in megabytes (MB). The minimum value is 1 MB. The maximum value is 2048 MB (2 GB). If the LOGFILESIZE setting is missing, then, by default, the query log file size is 1 MB. If an initial query log file size exceeds the specification, log information is added to a new query log file. Each time a new file is created, the filename is incremented by one.
TOTALLOGFILESIZE n Specifies the maximum size of all query log files combined in megabytes (MB). The minimum value is 512 MB (1/2 GB). The maximum value is 4095 MB. If the TOTALLOGFILESIZE setting is missing, then, by default, the total query log file size is 1024 MB (1 GB). Query log files are created until the file size total exceeds the specified maximum. When the maximum is exceeded, a message is displayed and query logging automatically turns off.
ON | OFF Specifies whether the query logging feature is turned on or off. All query log settings are ignored if this setting is OFF or missing. By default, the setting is OFF.

Generation-range and level-range values are represented in one of the following ways:

Table 4-2 Generation and Level Range Specifications

Generation-Range or Level-Range Value Description
x A specific generation or level number. For example, QUERYLOG NONE GENERATION 2 excludes generation 2 from query logging.
x-y All generations or levels inclusive of number x through number y. For example, QUERYLOG GENERATION 1-3 or QUERYLOG LEVEL 1-3 includes generation or level numbers 1, 2, and 3.
-x For generation-range, all generations within the range 1 through x. For level-range, all levels within the range 0 through x. For example, QUERYLOG GENERATION -2 includes generations 1 and 2. QUERYLOG LEVEL -3 includes levels 0, 1, 2, and 3.
x- For generation-range, all generations within the range from number x through the highest generation. For level-range, all levels within the range from number x through the highest level. For example, QUERYLOG Level 1- includes levels 1, 2, 3 and so on up to the highest level.

Notes

  • When query logging is enabled, queries to the database may be slower. Performance depends on how many members are being tracked and the size of the query.

  • If the settings file name does not match the name of the database or the settings file is located in a place other than the ARBORPATH\App\appname\dbname directory, Essbase ignores query logging.

  • If, in the settings, QUERYLOG ON is missing or if QUERYLOG OFF is set, query logging is disabled.

  • If generation and level settings cause contradictions in the settings file, the following precedence rules apply:

    • generation numbers (highest priority)

    • level numbers

    • member names (lowest priority)

    For example, if a member belongs to both level 1 and generation 2 and the settings QUERYLOG GENERATION 2 and QUERYLOG NONE LEVEL 1 are in the settings file, the generation setting takes precedence, and members of generation 2 are logged by generation number.

Tips

  • To view query log output easily, change the file extension .QLG to .XML, and then using the Internet Explorer or Netscape browser view the .XML file.

    Note:

    You can import the .XML file to Microsoft Access or Microsoft Excel. However, you must first shut down the database.

  • If Essbase is not producing a query log file as expected, view the dbname.log file in the ARBORPATH\App\appname directory to search for query log messages.