Query Database (Aggregate Storage)

The MaxL query database statement for ASO mode helps you retrieve advanced information about the current state of an Essbase aggregate storage cube that is running.

Click here for non-aggregate storage version

This statement requires the database to be started.

Syntax

Description of the illustration qrydb_as.gif

• DBS-NAME

• MEMBER-NAME

• ALT-NAME-SINGLE

• VIEW-FILE-NAME

Keywords

You can query for active ASO database information in the following ways using MaxL query database. The minimum application permission required for most query database actions is Database Access, with exceptions noted.

query database DBS-NAME get active alias_table

Display the active alias table for the user issuing the statement.

Example:

query database ASOSamp.Basic get active alias_table;

query database DBS-NAME get attribute_info MEMBER-NAME

Get attribute member, dimension, and name information for the specified ASO attribute member.

Example:

query database ASOSamp.Basic get attribute_info 'Area Code';

query database DBS-NAME get attribute_spec

Display the current attribute specifications for the database. These specifications include attribute member name format, Attribute Calculation dimension member names, Boolean and date member names, and numeric range specifications.

Example:

query database ASOSamp.Basic get attribute_spec;

query database DBS-NAME get cube_size_info

Display information about input data size, aggregated data size, and number of queries tracked (when query tracking is enabled).

Example:

query database ASOSamp.Basic get cube_size_info;

This statement returns the output listed in the following table:

Table 3-11 Get Cube_Size_Info MaxL Output Columns

Column Name	Contents
input_data_size_cells	Number of input-level cells in the cube.
input_data_size_bytes	Number of bytes used by the input-level data (approximate).
aggregate_data_size_cells	Total number of cells in all aggregate views in the cube.
aggregate_data_size_bytes	Number of bytes used by the aggregate cells (approximate).
kernel_queries_tracked	Number of kernel queries executed since the last time query tracking was enabled or query tracking information was reset.
total_query_cost	Total cost of all queries executed since the last time query tracking information was reset.
query_tracking_enabled	Values: True or False. Tells whether user retrieval statistics are being collected for the aggregate storage database. The statistics can be used by the following MaxL statements for query-based view optimization: query database <dbs-name> list existing_views execute aggregate process execute aggregate selection Query tracking is on by default.

query database DBS-NAME get dbstats dimension

Get information about dimensions. This action requires Database Access permission or higher.

Example:

query database ASOsamp.basic get dbstats dimension;

The index_type field values are numeric, and translate as follows:

0               Dense
1               Sparse
3               None (database is aggregate storage)

query database DBS-NAME get dbstats data_block

Get information about data blocks. This action requires Database Access permission or higher. The information returned has little relevance to aggregate storage databases.

Example:

query database ASOsamp.basic get dbstats data_block;

query database DBS-NAME get member_info MEMBER-NAME

Get information on a specific member. This action requires Database Access permission or higher.

Example:

query database ASOsamp.basic get member_info 'Original Price';

Output Columns for Member Info

The unary_type field values are numeric, and translate as follows:

0               Add
1               Subtract
2               Multiply
3               Divide
4               Percent
5               NoRollUp

The member_tag_type field values translate as follows:

0               SkipNone
16384           SkipMissing
32768           SkipZero
49152           SkipBoth
1               BalFirst
2               BalLast
4               TwoPass
8               Average
64              Expense

Variations are possible. The field value consists of one of the first four "skip" values plus any/all/none of the last five values. Some examples:

0               SkipNone
77              SkipNone, BalFirst, TwoPass, Average, Expense
16385           SkipMissing and BalFirst

The first four "skip" values are base values, and added to them are combinations of 1, 2, 4, 8, and 64.

The status field values are hexadecimal, and translate as follows:

0               Normal
1               Never Share
2               Label
4               Refer Share
8               Refer Share (with different name)
16              Implicit share
32              Virtual Member (stored)
64              Virtual Member (not stored)
2048            Attribute
32768           Referred

query database DBS-NAME get opg_state of member_data …

Display outline navigational information (for example, parent, child, or sibling), fixed-length information (for example, the line aggregation symbol or the number of children), and text strings (for example, member names or aliases).

Example:

query database ASOsamp.basic get opg_state of member_data for dimension 'Geography';

This action requires Database Access permission or higher. Refer to Outline Paging Dimension Statistics for a description of the output.

query database DBS-NAME get opg_state of member_name_namespace …

Display information that matches member names to internal member identifiers (one section per database, thus the information for all dimensions is the same).

Example:

query database ASOsamp.basic get opg_state of member_name_namespace for dimension 'Products';

This action requires Database Access permission or higher. Refer to Outline Paging Dimension Statistics for a description of the output.

query database DBS-NAME get opg_state of member_formula …

Display all formulas for the dimension.

Example:

query database ASOsamp.basic get opg_state of member_formula for dimension 'Measures';

This action requires Database Access permission or higher. Refer to Outline Paging Dimension Statistics for a description of the output.

query database DBS-NAME get opg_state of member_UDA …

Display all user defined attributes (UDAs) for the dimension.

Example:

query database ASOsamp.basic get opg_state of member_uda for dimension 'Measures';

This action requires Database Access permission or higher. Refer to Outline Paging Dimension Statistics for a description of the output.

query database DBS-NAME get opg_state of member_UDA_namespace …

Display information that matches UDAs to internal member identifiers.

Example:

query database ASOsamp.basic get opg_state of member_uda_namespace for dimension 'Measures';

This action requires Database Access permission or higher. Refer to Outline Paging Dimension Statistics for a description of the output.

query database DBS-NAME get opg_state of attribute_to_base_member_association …

Display information that identifies the attribute member associated with each base member of the dimension.

Example:

query database ASOsamp.basic get opg_state of attribute_to_base_member_association for dimension 'Stores';

This action requires Database Access permission or higher. Refer to Outline Paging Dimension Statistics for a description of the output.

query database DBS-NAME get opg_state of member_comment

Display all member comments for the dimension.

Example:

query database ASOsamp.basic get opg_state of member_comment for dimension 'Age';

This action requires Database Access permission or higher. Refer to Outline Paging Dimension Statistics for a description of the output.

query database DBS-NAME get opg_state of member_alias_namespace

Display information that matches member alias names to internal member identifiers (one section per alias table, thus the information for all dimensions is the same).

Example:

query database ASOsamp.basic get opg_state of member_alias_namespace for dimension 'Age';

This action requires Database Access permission or higher. Refer to Outline Paging Dimension Statistics for a description of the output.

query database DBS-NAME list aggregate_storage runtime_info

Display runtime statistics about the aggregate storage database.

Example:

query database ASOsamp.basic list aggregate_storage runtime_info;

This action requires Database Access permission or higher. For a description of the output returned by this statement, see Aggregate Storage Runtime Statistics.

query database DBS-NAME list aggregate_storage group_id_info

Display information about group IDs and their timestamps related to General Ledger cubes.

Note:

This grammar applies to General Ledger cubes, not to non-general-ledger aggregate storage databases. For normal aggregate storage databases, this table will be empty.

This MaxL grammar is disabled for previous release Essbase MaxL clients.

Example:

query database ASOsamp.basic list aggregate_storage group_id_info;

This statement returns the following output:

Table 3-12 List Aggregate_storage group_id_info MaxL Output Columns

Column Name	Contents
group_id	The allocation group id, according to the begin allocation command that created the allocation group. The number is an unsigned 64-bit integer.
transaction_id	The aggregate storage transaction ID that is used internally. The number is an unsigned 64-bit integer.
state	A string describing the state of the group ID. For example: BeginAllocation Done, Allocation In Progress, Allocation Done, EndAllocation In Progress.
time_last_used	The date and time the group ID was last used. The value is either the time the group ID was created or the time that an allocation or custom calculation was last performed with this group ID. The value is a string.
time_expired	The date and time when the group ID will time out (expire). The value is a string.
expired	Indicates whether the group ID has timed out. If the group ID has expired, the group ID will be rolled back the next time a begin allocation command is executed. The value is a boolean.

This action requires Database Access permission or higher. For a description of the output returned by this statement, see Aggregate Storage Group ID Information Output.

query database DBS-NAME list aggregate_storage slice_info

Display information about data slices and views, some information of which applies only to General Ledger cubes (not to non-general-ledger aggregate storage databases).

Note:

Small incremental slices may have fewer aggregate views than the primary slice (slice number 0). Incremental slices with less than 100,000 cells will never have any aggregate views built. However, if an incremental slice is larger than 100,000 cells and it is larger than the primary slice, then it will always have the same aggregate views as the primary slice.

Example:

query database ASOsamp.basic list aggregate_storage slice_info;

This MaxL grammar is disabled for previous release Essbase MaxL clients.

This statement returns the following output:

Table 3-13 List Aggregate_storage slice_info MaxL Output Columns

Column Name	Contents
transaction_id	(Applies to General Ledger cubes only) The ID of the transaction to which this slice and view belong. There is one transaction ID for each GL group ID. The number is an unsigned 64-bit integer. To find the corresponding group ID, use the following MaxL command: query database app.db list aggregate_storage group_id_info; For non-general-ledger aggregate storage databases, this number is always 0.
slice_id	ID number of the data slice. The number is an unsigned 32-bit integer.
slice_tag	(Applies to General Ledger cubes only) When an allocation or custom calculation is done within an allocation begin/end, this number is the rule_id of the allocation that made this data slice. The number is an unsigned 64-bit integer. For non-general-ledger aggregate storage databases, this number is always 0.
view_id	0 indicates an input view; otherwise, the view is an aggregate view. The number is an unsigned 64-bit integer. To list the levels in a given aggregate view, use the following MaxL command: query database app.db list existing_views;
size_cells	The number of cells in the given view of the slice. The number is an unsigned 64-bit integer.
size_kb	The size in KB of the given view of the slice. The number is an unsigned 64-bit integer.

This action requires Database Access permission or higher. For a description of the output returned by this statement, see Aggregate Storage Slice Information Output.

query database DBS-NAME list aggregate_storage uncommitted_transaction_info

Display information about uncommitted transactions that are related to General Ledger cubes.

Example:

query database ASOsamp.basic list aggregate_storage uncommitted_transaction_info;

Note:

This grammar applies to General Ledger cubes, not to non-general-ledger aggregate storage databases. For normal aggregate storage databases, this table will be empty.

This MaxL grammar is disabled for previous release Essbase MaxL clients.

This action requires Database Access permission or higher. This statement returns the following output:

Table 3-14 Aggregate Storage Uncommitted Transaction Info MaxL Output Columns

Column Name	Description
unc_transactions	The number of existing user transactions that are not yet committed.
unc_data_slices	The number of data slices used by uncommitted transactions.
unc_input_data_size_cells	The number of input cells used by uncommitted transactions.
unc_aggregate_views	The number of aggregate views used by uncommitted transactions.
unc_aggregate_data_size_cells	The number of aggregate cells used by uncommitted transactions.
unc_input_data_size_kb	The total disk space used by uncommitted input-level data.
unc_aggregate_data_size_kb	The total disk space occupied by uncommitted aggregate cells.

This action requires Database Access permission or higher. For a description of the output returned by this statement, see Aggregate Storage Uncommitted Transaction Information Output.

query database DBS-NAME list aggregate_storage compression_info

Display estimated compression for aggregate storage databases when different dimensions are hypothetically used as the compression dimension. These estimates can help you choose the best dimension to use as the compression dimension.

In aggregate storage databases, the compression dimension enables database compression. A good candidate for a compression dimension is one that optimizes data compression and maintains retrieval performance. The following table lists data for all non-attribute dimensions, even though it may not be possible to select them as the compression dimension without significant changes to the outline. For information on the requirements of a compression dimension, see Understanding the Compression Dimension for Aggregate Storage Databases.

Example:

query database ASOsamp.basic list aggregate_storage compression_info;

This action requires Database Access permission or higher. This statement returns the following output:

Table 3-15 Aggregate Storage Compression Info MaxL Output Columns

Column Name	Contents
dimension_name	Each dimension name in the database, hypothetically considered to be the compression dimension.
is_compression	Indicates whether the dimension is the aggregate storage compression dimension. (There can be only one compression dimension in an aggregate storage database.)
stored_level0_members	The number of leaf-level members in the dimension. A large number of stored level-0 members in a dimension indicates that it may not perform well as a compression dimension.
average_bundle_fill	Estimated average number of values per compression dimension bundle. Choosing a compression dimension that has a higher average bundle fill means that the database compresses better.
average_value_length	Estimated average number of bytes required to store a value. Dimensions with a smaller average value length compress the database better.
level0_mb	Estimated size of the compressed database, in megabytes. A smaller expected level-0 size indicates that choosing this dimension enables better compression. Except for the scenario in which there is no compression dimension (None), all estimates assume that all pages are compressed. Since compressed pages require additional overhead that uncompressed pages do not, the estimated level-0 database size for some dimensions may be larger than the value for None.

query database DBS-NAME list alias_table

Get a list of alias tables that are defined for the database. This action requires Database Access permission or higher.

Example:

query database ASOsamp.Basic list alias_table;

query database DBS-NAME list alias_names in alias_table …

List the alias names defined in an alias table. Alias tables contain sets of aliases for member names and are stored in the database outline. Use this grammar to see a list of alias names defined in the specified table. This action requires Database Access permission or higher.

Example:

query database ASOsamp.Basic list alias_table;

query database DBS-NAME list existing_views

Display information about all aggregate views. An aggregate view is a collection of aggregate cells based on the levels of the members within each dimension.

This action requires Database Access permission or higher.

The optional based on query_data clause causes the returned query cost information to be based on the collected cost of actual user queries. If this clause is not used, the default assumption is that all possible queries happen with the same probability.

To use the based on query_data clause, query tracking must be enabled. Query tracking is enabled by default, but if you need to enable it, use alter database <dbs-name> enable query tracking.

Example:

query database ASOsamp.Basic list existing_views based on query_data;

query database DBS-NAME list ... file information

Get accurate index and data file information. Provides index and data file names, counts, sizes, and totals, and indicates whether or not each file is presently opened by Essbase. The file size information is accurate. Note that the file size information provided by the Windows operating system for index and data files that reside on NTFS volumes may not be accurate.

Example:

query database ASOsamp.Basic list all file information;

The above statement returns:

 file_name        file_type        file_number      number_of_type   file_size_kb     file_size_bytes  opened
+----------------+----------------+----------------+----------------+----------------+----------------+----------------
 Index File Total                1                0                0                0                0            FALSE
 Data File Total                 2                0                0                0                0            FALSE
 Grand File Total                3                0                0                0                0            FALSE

This action requires Database Access permission or higher.

query database DBS-NAME list load_buffers

Display a list and description of the data load buffers that exist on an aggregate storage database.

Example:

query database ASOsamp.Basic list load_buffers;

This action requires Database Manager permission or higher.

Refer also to List Aggregate Storage Data Load Buffers

query database DBS-NAME list aso_level_info

Display the aggregation level count for each real dimension in the outline. Aggregation level count is the total number of aggregation levels in a real dimension (including associated attribute dimensions).

This action requires Database Manager permission or higher.

Example:

query database ASOsamp.Basic list aso_level_info;

This statement returns:

 dimension           num_levels
+-------------------+-------------------
 Measures                              1
 Years                                 1
 Time                                  4
 Transaction Type                      2
 Payment Type                          2
 Promotions                            2
 Age                                   3
 Income Level                          2
 Products                              6
 Stores                                7
 Geography                             7

query database DBS-NAME dump|force_dump existing_views...

Saves existing views of this ASO database to an aggregation script. This action requires at least Database Access permission.

If the specified script name already exists, you can use the force_dump keyword to overwrite it; otherwise, an error is returned if the file name already exists.

If the based on query_data phrase is used, the view selection that is saved will be based on previously collected query-tracking data. For more information about query tracking, see the based on query_data description in execute aggregate selection.

Example:

query database ASOsamp.Basic dump existing_views to view_file viewdump;