The formula in a calculated member needs to retrieve values from the cells in a multidimension-al cube . If you have worked with a spreadsheet application, you multidimension-already know how to retrieve values from the cells in a two-dimensional worksheet using spreadsheet formulas . Working with multidimensional data is not much different, although it is a bit harder to visualize and requires a different language, MDX, to retrieve values from the cells in a cube . To see how MDX refers to the values in the cells in a multidimensional cube, consider first how a Microsoft Office Excel formula in one cell refers to values in other cells in a two-dimensional worksheet .
Imagine that you are creating a worksheet that calculates the margin (sales minus cost) for three product categories . Traditionally, spreadsheet formulas have used row and column ad-dresses to refer to cells .
The formula in cell D3 uses the address B3 to refer to Clothing Sales . The address B3 refers to the position of the cell, not to its meaning . When you copy the formula from cell D3 to cell D4, the address B3 must change to B4 . This type of formula is called a relative reference; the formula’s operands are relative to the cell’s position and each copy of the formula appears slightly different .
In a worksheet, you can create a label for a range of cells and then use those labels in formu-las . The same worksheet could have formuformu-las that look like these:
The new formula in cell D3 uses the label Sales to refer to Clothing Sales . This time, the word Sales refers to the meaning of the cell, not to its position . The formula assumes that you want the Sales value that is on the same row as the formula . When you copy the formula from cell D3 to cell D4, the formula doesn’t change . In MDX terminology, the formula implicitly uses the current member of the row axis .
Excel formulas that use labels are easy to understand . MDX expressions work in a very similar way . You can create a new calculated member in the Measures dimension that automatically refers to the current member of the Product dimension, or you can create a new calculated member in the Product dimension that automatically refers to the current measure . By creat-ing a calculated member uscreat-ing MDX, you are in effect entercreat-ing formulas into a set of cube cells the same way that you copy and paste formulas across a set of cells in an Excel work-sheet . The power of an MDX calculated member comes from the fact that you don’t have to enter the formula into each cell; a single formula automatically propagates to all the neces-sary cells . This works even when you change the dimensions on the axes .
A spreadsheet formula must often refer to a cell that is not on the same row or column as the cell containing the formula . For example, suppose you want to calculate the percentage that a specific product category contributed to total sales . Using traditional spreadsheet refer-ences, you would need to create a formula similar to the following:
The formula in cell C3 refers to cell B5 . To be able to copy the formula to the other cells in column C, it’s necessary to add a dollar sign to “anchor” the row . This notation technique works, but it’s clumsy and difficult to understand, and doesn’t translate well to the multidi-mensional world . If you create the formula using labels, it looks like this:
The reference Total Sales refers to cell B5 . In this worksheet, each row and column has been given a name . You can refer to any cell by entering its row name and column name separated by a space . The result is remarkably readable . MDX, however, cannot simply use member names separated by spaces, because an online analytical processing (OLAP) cube typically contains many more than two dimensions . MDX uses a notation and terminology for referring to an explicit cell that can be best understood by seeing how to specify a point on a chart . Imagine a mathematical line . A line has one dimension . In basic charting, if you want to spec-ify a point on the line, you specspec-ify a single number or a single coordinate . On the following line, the coordinate for the marked point is 3 .
Now imagine an OLAP cube with one dimension . If you want to specify a value from the cube, you specify a single member from the dimension . This single value is the coordinate for the cell . In the following hypothetical cube, the coordinate for the marked cell is [February] .
January February March April
1,555 1,619 1,653 1,813
Imagine a mathematical grid . A grid has two dimensions . In basic charting, if you want to specify a point on the grid, you specify a pair of numbers or a double coordinate, typically enclosed in parentheses . On the following grid, the coordinate for the marked point is (3, 4):
Now imagine an OLAP cube with two dimensions . If you want to specify a value from the cube, you specify a single member from each dimension . This double value is the coordi-nate for the cell . In the following hypothetical cube, the coordicoordi-nate for the marked cell is ([March],[Road Bikes]) .
January February March April Mountain Bikes 283 305 319 328
Road Bikes 311 343 358 413
Touring Bikes 185 199 204 234
Imagine a three-dimensional mathematical space . In charting, to specify a point in the space, you specify three numbers, or a triple coordinate . In the following space, the coordinate for the marked point is (4, 2, 3) .
Now imagine an OLAP cube with three dimensions . Since three dimensions are harder to vi-sualize, imagine the three dimensions of the cube as key columns . To specify a member from the cube, you specify a single member from each dimension . In the following hypothetical cube, the coordinate for the marked cell is ([Europe],[Road-750 Black, 52],[Qtr 1]) .
Region Product Date Units
All All All 10,762
Europe All All 3,305
Europe Bikes All 1,957
Europe Road-750 Black, 52 All 80 Europe Road-750 Black, 52 Qtr 1 32 Europe Road-750 Black, 52 Mar 13
With more than two or three dimensions, it’s usually easier to visualize a cube as a table with dimensions represented by key columns . To retrieve a single value from the cube—that is, to specify a single cell in the cube—you must specify one member from each dimension . Watch for a pattern in the terminology for a coordinate as it includes more dimensions: A coordinate containing one dimension is a single coordinate . A coordinate containing two dimensions is a double coordinate . With three dimensions, it is a triple coordinate . With four dimensions, it is a quadruple coordinate; with five, a quintuple; with six, a sextuple; with seven, a septuple; and with eight, an octuple . When you get past four dimensions in a dinate, each of the coordinate numbers ends in the suffix -tuple . The generic term for a coor-dinate that includes one or more dimensions is a tuple .
Note Some people pronounce the first syllable of tuple to rhyme with cup (as in the word quin-tuple) . Others pronounce it to rhyme with coop (as in the word quadruple) . Each group considers the other uncivilized . Because this is a book, you don’t have to know how I pronounce it .
As is the case with a mathematical coordinate, a tuple that contains more than a single di-mension must be enclosed in parentheses . A tuple that contains one member from each attribute hierarchy of each dimension identifies a unique value in the cube . Remember that measures are considered to be a dimension, and you must include a measure within a tuple to uniquely identify a value .
Dimensions may include user-defined hierarchies as well as attribute hierarchies . If your tuple includes a member from a user-defined hierarchy, the ancestors of the member (parent, grandparent, and so on) are implicitly included in the tuple . When Analysis Services evaluates the tuple, it maps the members of the user-defined hierarchy to the corresponding members of the attribute hierarchies used to create the user-defined hierarchy .
If a cube contains several dimensions that have several attributes each, a tuple that contained the name of one member from every attribute hierarchy would be very long . Fortunately, you can use a very convenient bit of shorthand . When you omit a member of an attribute hierarchy, the current member of the hierarchy is used in its place . Most of the time, you can use the current member for all but one or two dimensions . The current member is under-stood by Analysis Services to be the member specified in report rows, columns, and filters . For example, in a report in the Browser tab, a cell at the intersection of Bikes and CY 2009 has current members of Bikes in the Product dimension and CY 2009 in the Date dimension . A member name must be written using correct syntax . The basic syntax for a member name is [DimensionName].[HierachyName].[LevelName].[MemberName] or [DimensionName].
[HierarchyName].[AncestorName].[MemberName] . If necessary, you can include multiple ances-tor names . That is, you may need to include the parent, grandparent, and so on in the mem-ber name . You can also replace a memmem-ber name with &[Memmem-berKey] . For example, [Product].
[Product by Category].[Subcategory].[Touring Bikes] and [Product].[Product by Category].[All].
[Bikes].[Touring Bikes] are both valid names . [Product].[Product by Category].[All].&[1].&[3] is
also a valid name, because 1 is the member key for Bikes and 3 is the member key for Touring Bikes .
Using this syntax uniquely identifies a member, but the member name syntax rules don’t re-quire inclusion of all of the parts of a name . However, leaving out a part of a member name may result in ambiguity that Analysis Services may not resolve as intended . For example, the member name [All].[Bikes].[Touring Bikes] is correctly formed but will return no data .