Like this article? We recommend
Like this article? We recommend
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.