Supported requests

The SQL Query Engine can only be used in an XMLA request. On the client-side only Application Studio supports this feature.

This table describes escaping special characters in attribute and level names:

Character How to escape
\ \\
[ [ (no need to escape)
] ]

If a cube and a dimension have the same name:

  • The table name is resolved as a cube by default.
  • A prefix DIM_ must be added to resolve the table name as a dimension.

    This also applies to the dimension that already begins with DIM_ to address the dimension DIM_Products. For example, DIM_DIM_Products.

  • It is not possible to resolve a dimension name that begins with DIM_ as a cube.

SELECT category

This table describes the SELECT category:

Name Cube as table Dimension as table Note
Dimensions, levels, attributes, properties, measures x Measures:

Returns an error if a string measure is used in the query. The consolidated or ruled measures can be used in query.

Properties:

Allowed: UNIQUE_NAME, CAPTION. The properties refer to the dimensions.

ID, levels, attributes, properties x Properties:

Allowed: UNIQUE_NAME, CAPTION, LEVEL_NAME.

The properties refer to the ID.

Prefix LVL_, ATTx_, PROP_ x Levels, attributes and properties without prefix:

Resolving a level takes precedence rather than to an attribute or a property.

Resolving an attribute takes precedence rather than to a property.

Resolving an attribute from the first table takes precedence rather than from second and third table.

Prefix DIM_ x
Alias AS x x
SELECT TOP [number] x x
SELECT * x x Cube as table: A list of all dimensions and measure elements (except string measures).

Dimension as table: Column ID (contains element name) and a list of all levels and attributes.

SELECT CELL x Rules:

For the dimensions that are mentioned in the query, their lowest level will be used.

For the dimensions that are not mentioned in the query, their default member will be used.

Returns error if the level that is used in the WHERE clause lower than the level that is used in the SELECT. The level distance is negative: columnLevelIndex - whereLevelIndex.

FROM category

This table describes the FROM category:

Name Cube as table Dimension as table Note
Prefix DIM_ x In case dimension name and cube name are identical

WHERE category

This table describes the WHERE category:

Name Cube as table Dimension as table Note
Dimensions, levels, attributes, properties x
ID, levels, attributes, properties x
Prefix LVL_, ATTx_, PROP_ x x
Prefix DIM_ x
Comparison operator <, <=, =, <>, >, >=, IN, NOT IN, BETWEEN, NOT BETWEEN x x Properties: Only equality operator is supported.

ORDER BY category

This table describes the ORDER BY category:

Name Cube as table Dimension as table Note
Dimensions, levels, attributes, properties, measures x
Levels, attributes, properties x
Alias AS x x
ASC, DESC x x

RESULT category

This table describes the RESULT category:

Name Cube as table Dimension as table Note
Multiple parents x Rules:

For dimension queries, the element is listed as often as it occurs.

For cube queries, the element is listed once.

The element order is not hierarchized.

Element permission x x The result does not contain the elements that the user cannot view.

Keep these items in mind:

  • If the parent level is included in the query, each combination of base element and ancestors on the other levels is given in the result.
  • The Dimension element is listed many times as often as it occurs.
  • The Cube element is listed only once, the default one.
  • The order of returned rows for cube queries is undefined, if not otherwise defined in the SQL query.