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 LOGHAMBRS ON | OFF QUERYLOG LOGPATH path-expression QUERYLOG LOGFORMAT CLUSTER | TUPLE QUERYLOG LOGFILESIZE n QUERYLOG TOTALLOGFILESIZE n QUERYLOG ON | OFF
| 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. Note: QUERYLOG [dimension_name] must precede all settings that track Hybrid Analysis members and members of generation and level ranges; otherwise, Hybrid Analysis and generation and level settings are ignored. |
| NONE GENERATION generation-range | 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 level-range | 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 generation-range | 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 level-range | 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. |
| LOGHAMBRS ON | OFF | Tracks Hybrid Analysis members of the specified dimension. By default, the setting is OFF. The QUERYLOG NONE, GENERATION, and LEVEL parameters do not apply to Hybrid Analysis members because Hybrid Analysis members are not actually members in an Essbase outline. If QUERYLOG LOGHAMBRS ON is set, the log output is always displayed in CLUSTER format, regardless of whether QUERYLOG LOGFORMAT TUPLE is set. If QUERYLOG LOGHAMBRS ON is set, but the database or dimension does not have any Hybrid Analysis members, the setting is ignored. |
| 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:
| 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. |
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.
You can place QUERYLOG settings in any order in the settings file, but QUERYLOG [dimension_name] must precede any settings that specify Hybrid Analysis members or any setting that specify members by generation or level.
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.
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.