Understanding MDX CellSets

The results of an MDX query are provided as an MDX CellSet. This structure is a bit more complicated than just a two-dimensional table as most User Interfaces will actually present the results. This article explains the structure of an MDX CellSet, should you want to use query results in your own code rather than relying on an existing Graphical Interface.

The most important structures to understand in the MDX CellSet are the axes and the cells.

Axes

The MDX CellSet axes are the ones defined in the MDX query : usually COLUMNS (axis number 0) and ROWS (axis number 1), but also PAGES, SECTIONS and CHAPTERS, or even more axes (but axes with number higher than 4 have no specific alias). On those axes, there are the hierarchies (one might think there is one hierarchy per axis, but there are several hierarchies in the case of a crossjoin for example).

Then there are the positions : those are the actual member values taken by the hierarchies on the axis.

Cells

The cells each have an ordinal. The ordinal is what allows to find the positions to which each cell corresponds. The ordinal 0 corresponds to the first position on every axis. Then the ordinals are incremented as we move along the positions on axis 0, until the last position is reached, then back to first position on axis 0 but second position on axis 1, etc...

More specifically,

$$ordinal = positionOnAxis(0) + \sum_{a=1}^{maxAxis} positionOnAxis(a) \times \prod_{k=0}^{a-1} numberPositions(k)$$

There is an analogy with reading a book: reading the first character of the first line in the chapter, then reading the whole line sequentially, then going back to the first character on the second line... until you reach another chapter and repeat the process. However, as the analogy stands for how the ordinals are constructed, there is no guarantee that the cells in the CellSet will be provided by order of ordinal, and also many ordinals can be missing since we usually omit cells with Null results.

So, the "table" result has to be reconstructed from the ordinals of each cell. The ordinal's modulo to the number of positions in the COLUMNS axis will be the column index, while the integer division will be the row index.

On this schema we only presented 2 axes, which is a very frequent scenario, but nothing stops you from using any number of axes in your query and the MDX CellSet will reflect that.

IMdxResult

The result of an MDX query is an IMdxResult which wraps an MDX CellSet, providing useful iterators.

The axesIterator() method returns an iterator made to iterate over the axes of the MDX CellSet, then over each position on each axis, then over each member of each position.

The usage is explained in IMdxAxesIterator's javadoc :

/**
 * Iterator on all axes of a MDX Cellset.
 *
 * <p>How to use this type of iterator.
 *
 * <pre>
 * while(axesIterator.hasNextAxis()) {
 *     axesIterator.nextAxis();
 *     axesIterator.getAxisName();
 *     ...
 *     while(axesIterator.hasNextPosition()) {
 *         axesIterator.nextPosition();
 *         ...
 *         while(axesIterator.hasNextMember()) {
 *             axesIterator.nextMember();
 *             axesIterator.getPropertyValue(...);
 *             ...
 *         }
 *         ...
 *     }
 *     ...
 * }
 * </pre>
 *
 * @author ActiveViam
 */

The cellDataIterator() method returns an iterator over the cells, allowing access to their ordinal and property values. What users usually consider as the "result" of the query is the value of the property FORMATTED_VALUE, but there are the other cell properties, like VALUE (the unformatted value is sometimes more useful than the formatted one to do custom treatments in a custom UI), or even FORE_COLOR or BACK_COLOR... For more info on cell properties, you can have a look at the official MDX documentation.