1. Calculation and Query Reference for Oracle Essbase
  2. MDX
  3. MDX Query Limits

MDX Query Limits

Learn about query limits when using MDX with Essbase.

Overview

The following concepts are applicable to understanding MDX query limits.

Table 4-21 MDX Query Limit Concepts

Concept Description

NON EMPTY processing

Refers to how Essbase processes MDX queries and sets when the NON EMPTY keywords are used in an axis specification. The NON EMPTY specification optimizes processing by suppressing slices that would contain entirely #MISSING values.

Cluster elements/symmetric sets

Although an MDX set is a collection of tuples, internally, Essbase represents sets using clusters and tuples. A cluster is a type of set derived using the CrossJoin function, where the arguments to CrossJoin are sets from one dimension only.

A cluster can also be thought of as a symmetric set. The following set is a symmetric set and can be stored as one cluster.

CROSSJOIN(Products.LEVELS(0).MEMBERS, [Market].LEVELS(0).MEMBERS)

A tuple is a collection of members from different dimensions. The following set has one tuple. 

{([Product].Product_1, [Market].Market_1)}

The following set is a union of the above two sets. It is stored internally as a cluster and a tuple. 

UNION(
CROSSJOIN(Products.LEVELS(0).MEMBERS, [Market].LEVELS(0).MEMBERS)
,
{([Product].Product_1, [Market].Market_1)}
)

Compact set

A set is stored in compact form if it can be internally represented as a cluster or symmetric set.

Flattened set

A set that must be internally expanded into tuples is a flattened set. Flattened sets consume more memory to be processed. Certain MDX functions, such as Order, need to flatten sets in order to process them correctly. Therefore, certain functions, as listed in the next section, have different set size or query limits.

The following set is an example of a flattened set.

{(Colas, East)
(Colas, West)
(Colas, South)
(Colas, Central)
(Root Beer, East)
(Root Beer, West)
(Root Beer, South)
(Root Beer, Central
(Cream Soda, East)
(Cream Soda, West)
(Cream Soda, South)
(Cream Soda, Central)
(Fruit Soda, East)
(Fruit Soda, West)
(Fruit Soda, South)
(Fruit Soda, Central)}

Asymmetric set

The following set is stored internally as a collection of a tuple element and a cluster element. The two elements cannot be combined into a single element. Such sets are called asymmetric sets.

UNION({(Colas, East)}
       CROSSJOIN(
	    [Product].CHILDREN,
	    [Market].CHILDREN))

MDX Query Limits

The following size limitations apply to MDX queries, sets, and certain functions.

Note:

The following exception applies to the general query limits: If the database being queried is the target database of a partition, the maximum size of a cube region you can query using MDX is 232 potential cells.

Table 4-22 MDX Query Limit Descriptions and Units

Limitations Units

Number of cells in a query region defined by all axis sets in an MDX query with NON EMPTY clause

2640

Number of cells that can be returned to a client after NON EMPTY processing

232

Number of cells in a query region defined by all axis sets in an MDX query with no NON EMPTY clause

232

Number of tuples in an axis set with NON EMPTY directive after NON EMPTY processing

228

Size of a set in compact form

2640

Size of a set in flattened form

232

Number of elements in a set

232

Number of members (from all dimensions) in a cluster element

232

Number of cells in a query after applying non empty cell processing

232

Size of a set that can be processed by the following functions:

  • Distinct

  • Except

  • Filter

  • Intersect

  • Ntile

  • Order

  • Percentile

  • Rank

  • TopPercent

  • BottomPercent

  • TopSum

  • BottomSum

  • Hierarchize

  • Union (with removal of duplicates)

  • NonEmptySubset (output set size)

  • TopCount (output set size)

  • BottomCount (output set size)

Less than 228

IEssOpMdxQuery Java API interface or EssMdx C API functions

  • Maximum number of tuples/clusters on an axis—229-1

  • Maximum number of cells (when cell status is requested)—226-1

  • Maximum number of cells (when cell status is not requested)—approximately 227-1

MDX queries run through MaxL

  • Maximum number of columns—229-1

  • Maximum number of rows—229-1