Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Specifying Cells in MDX

MDX references specific cells in a way that embraces multidimensional concepts. The MDX notation system is fairly straightforward, and actually quite readable in stand-alone form. If we visualize a cube with two dimensions, we can express an "address" (that is, the coordinates) for a cell such that the coordinates are represented by two points (see Table 1). Here we show a simple two-dimensional cube structure, displaying the amount of expense incurred for each of three stores belonging to an international grocery chain.

Table 1 A Cell "Address" in a Two-Dimensional Cube

Expenses

Store

General and Administrative

Marketing

Lease

Beverly Hills

956.64

3,826.56

2,391.60

Los Angeles

1,003.68

3,960.00

2,509.20

San Francisco

84.72

388.88

211.80


As a means of uniquely identifying the cell with bold text in Table 1, we would express the "address" as (Los Angeles, Marketing), or, to state the address in MDX coordinates, as follows:

([Los Angeles], [Marketing])

If we extend the above illustration to illustrate three dimensions, we would specify a dimension coordinate for each of the dimensions to uniquely identify the cell. (In MDX, we call the Store and Expense elements members, as discussed in Part 1 of this series, "Basic Concepts and Navigation"). Table 2 shows a sample set of coordinates.

Table 2 A Cell "Address" in a Three-Dimensional Cube

Actual

Store

Expense

Time

Amount

All

All

All

15,333.08

Los Angeles

All

All

7,472.88

Los Angeles

Net Income

All

(7,472.88)

Los Angeles

Marketing

All

3,960.00

Los Angeles

Marketing

Q2

960.00

Los Angeles

Marketing

June

320.00


To specify the location of the highlighted cell in the cube depicted in Table 2, we would again provide an "address" that consists of a single member for every dimension in the cube. In Table 2, we would "address" the highlighted cell as follows:

([Los Angeles], [Marketing], [Time].[All])

The key concept here is that we need to specify a member (the "coordinates" for a dimension) for each dimension in the cube.

Coordinates are typically referred to as tuples. A tuple equates to the cell (or cell group) "address." Tuples that contain more than a single dimension are placed within parentheses in MDX notation, and each tuple represents a unique value within the cube. And while every tuple must specify a member (used as a coordinate) for every dimension in the cube, we don't have to specify dimensions in cases where we mean to refer to the current member. The current member is assumed as a default in the majority of cases, unless we need to specify the dimension explicitly.

In the following sections, we'll use a new calculated member, MyCalcMem2, to practice specifying cell and member values. We often use MDX expressions that are made up of multiple independent values combined by an operator such as +, -, *, or /. The values used in the expression can be constants, values from the cube (or tuples), or even the result set of an independent MDX expression. We'll become familiar with the MDX notation and its surrounding terminology as we progress through this series. As in Part 1 of this series, "Basic Concepts and Navigation," the calculated member will serve as a vehicle to illustrate these concepts as we put them into action.

  • + Share This
  • 🔖 Save To Your Account