Export Query Tracking (Aggregate Storage)

The MaxL export query_tracking statement helps you export query tracking data from an Essbase aggregate storage (ASO) cube to a text file.

Query tracking captures user retrieval statistics against an ASO cube, enabling Essbase to make view-based optimizations to improve the performance of aggregations.

When an ASO cube is refreshed or restarted, query data is not persisted in the cube. As an optimization technique, before refreshing or restarting, you can export query tracking data to a text file. To rebuild aggregate views after a refresh or restart, import the query tracking data from the text file. Essbase uses the query data to select the most appropriate set of aggregate views to materialize.

Before exporting query tracking data, query tracking must be enabled and working.

Query tracking is enabled by default for ASO cubes. To check whether it's enabled, you can confirm by running the following MaxL statement (example is for ASOSamp Basic cube):

query database ASOSamp.Basic get cube_size_info;

If you need to enable query tracking, use the alter database (aggregate storage) statement with the enable query_tracking grammar.

Syntax

Description of the illustration exp_qry_tracking_as.gif

Keywords

You can use MaxL export query_tracking to export ASO query tracking information in the following ways. The minimum application permission required to export query tracking data is Database Manager. Do not edit the text file with the exported query data.

export query_tracking DBS-NAME to server file...: Export query data from the specified aggregate storage cube to the specified file. Unless the administrator has specified a different export location, the file is created in the <Application Directory>/app/appname/dbname folder. If you do not know where <Application Directory> is in your environment, refer to Environment Locations in the Essbase Platform.
export query_tracking DBS-NAME to file...: You can omit the server keyword, but the result is the same.

Note:

Query tracking and query tracing are different.

Query tracking enables you to capture user retrieval statistics against an aggregate storage cube, so that Essbase can make view-based optimizations to improve the performance of aggregations. It is on by default. Related MaxL statements include:

import query_tracking
export query_tracking
alter database enable query_tracking
query database appname.dbname get cube_size_info

Query tracing helps you monitor Essbase query performance metrics for block storage cubes (including hybrid mode). It is off by default. If you enable it, Essbase logs metrics in a trace report. Related configuration parameters: TRACE_REPORT, QUERYTRACE, QUERYTRACETHRESHOLD, LONGQUERYTIMETHRESHOLD.

Example

export query_tracking ASOsamp.Basic to server file 'query_data_aso_sample.txt';

Exports query data from the ASOsamp.Basic database to the named file. The export location is the cube directory (ASOSamp/Basic/), or whichever Essbase Server directory the administrator has specified for FILEGOVPATH.