Walk Through

C1 contains a CUBEMEMBER function call specifying the "Internet Sales Amount" member from the Measures dimension. C2 contains a CUBEMEMBER function call specifying "CY 2003" from the Date dimension. D2 contains a similar function specifying "CY2004" from the Date dimension. C3 contains a CUBEMEMBER function call specifying "All Products" from the Product dimension. Each of these cells contain simple string values (e.g., "All Products" in C3), and each of these cells is associated with mdx metadata which specifies the mdx expression identifying a particular member of a particular dimension (e.g., [Measures].[Internet Sales Amount]).

A5 contains a CUBESET function call specifying a set of members. Additionally, the CUBESET function call allows for specifying a caption for the cell ("Countries"), a sort order for sorting the set (in this case, "2" corresponds to descending), and a sort by field (in this case the set will be sorted by the member as expressed in the mdx associated with cell D5, labeled "2004 Sales"). Finally, it should be noted that CUBESET returns a set of members, not just a single member.

Cells A6:A11 use the CUBERANKEDMEMBER function to return the individual members, by rank, returned from the CUBESET function call in A5. For example, A6 uses the "xlextdat9 Adventure Works" connection to connect to the OLAP cube, and addresses the first member (because "ROW(A1)" resolves to "1") in the set returned in A5.

Cell C6 uses the CUBEVALUE function to return measure data identified by intersecting the mdx expression found in A6 with the mdx expression found in C5 ("CY 2003 Internet Sales for All Products in the United States"). C7:C11 use similar CUBEVALUE function calls. D6:D11 involves similar functions as well, but using "CY 2004" instead.

The power of metadata in this example is that anytime a CUBE function argument referenced another cell, and that argument requires a set or member mdx expression, the mdx metadata for that referenced cell is returned to the calling function instead of the simple string value. For example, A6 contains a CUBERANKEDMEMBER function call, whose second argument is expecting a set of members. The reference for that argument is $A$5. Instead of using the A5's string value of "Countries" (which would result in a error), A5's mdx expression metadata is used instead, which returns a set. Similarly any of the CUBEVALUE function calls rely on cell references, where those cells contain mdx metadata used to pinpoint the measure data requested. Furthermore, each of the cells referenced by C6:D11, in turn reference other cells' mdx metadata. In this way, the mdx metadata is able to propagate through the formula calculation chain.