MDX Provider API General Information

MDX queries that follow the grammar given in the MDX functional specification can be submitted to the server through the MDX-API. The query results can then be retrieved by the client using this API.

The grammar of MDX statements is covered in the MDX section of the Oracle Essbase Technical Reference.

A few basic MDX concepts and terminology are reviewed here. An MDX query consists of several axis specifications, and an optional slicer specification. Each axis specifies a set-valued expression. A set is an ordered collection of tuples, with tuples being a sequence of members from one or more dimensions. Tuples in a set are homogeneous in dimensionality (each tuple has members from the same dimensions in the same order).

An example of a set expression based on the Sample Basic database is:

   Union(
      CrossJoin({[Sales], [Profit]}, {[Actual], [Budget]}),
      Union(
         CrossJoin([Total Expenses].Children, {[Actual]}),
         {([Opening Inventory], [Variance]), ([Additions], [Variance %])}
      )
   )

This expression uses several MDX functions: Union, CrossJoin, Children. The value of this expression is the set:

   {
      ([Sales], [Actual]),
      ([Sales], [Budget]),
      ([Profit], [Actual]),
      ([Profit], [Budget]),
      ([Marketing], [Actual]),
      ([Payroll], [Actual]),
      ([Misc], [Actual]),
      ([Opening Inventory], [Variance]),
      ([Additions], [Variance %])
   }

Note that in the result of the CrossJoin, the tuples are ordered so that the first dimension changes slowest. The tuples in this set have the dimensionality: ([Measures],[Scenario]). The dimensionality of tuples across axis sets must not overlap.

In addition to the set expression, each axis specifies the name of the axis (COLUMNS, ROWS, PAGES, etc.) or the axis number (AXIS(0), AXIS(1), etc.). The cube consisting of all possible combinations of tuples, one from each axis, constitutes the result of the query. Dimensions that are not present in any axis and in the slicer default to having their root member included in defining the result cube. The slicer, if present, specifies a set, with a single tuple, which identifies the members of interest along the respective dimensions. This makes the final result a slice of the cube created from the axes. The result of an MDX query contains the metadata about each axis and the slicer, as well as the data values in the cells in the result cube.

Here is a complete MDX query:

   SELECT
      Union(
         CrossJoin({[Sales], [Profit]}, {[Actual], [Budget]}),
         Union(
            CrossJoin([Total Expenses].Children, {[Actual]}),
            {([Opening Inventory], [Variance]), ([Additions], [Variance %])}
         )
      ) ON COLUMNS,
      CrossJoin(
         [200].Children,
         {[East], [West]}
      ) ON ROWS
   FROM
      Sample.Basic
   WHERE
      {[Jan]}

The result of this query has 9 tuples on the column axis and 8 tuples on the row axis, which means there are 72 cells in all. Each cell has an ordinal, or offset, which depends on the position of its tuples along each axis. Offsets and positions start at 0. The cells are ordered so that the first axis position changes the fastest.

For example, the cell identified by tuple 3 in the column axis and tuple 4 in the row axis is at offset 3 + 9*4 = 39.

The concept of clusters is needed for reasons of efficiency. A set can be considered to be an ordered collection of tuples, or it can be considered to be an ordered collection of clusters. A cluster is a collection of tuples that involve all possible combinations of certain members from each of the set's dimensions. The tuples need to ordered in the same manner as in the output of the CrossJoin function (the first dimension changes the slowest). Use of the CrossJoin function causes clusters to be created, but the server may determine clusters from the results of other functions as well.