See Also: Main_Page - Database Administration - Analysis Services - MDX with Analysis Services


Basic concepts help you decipher the strict MDX syntax and understand why things work as they do. Knowing which string is a tuple as opposed to a set won't make you an MDX guru; however, it will assist you when referencing documentation or when you are trying to implement a particular function. Some of the terms introduced here are also explained in the "Building MSAS Dimensions" section.



A member is a value stored in a dimension. For example, [time].[January 8th, 2009] could be a member in a time dimension. Product dimension can contain members [product].[Toyota].[camry].[2003], [product].[Nissan].[maxima].[2004], [product].[Ford].[mustang].[2005], [product].[Cadillac].[Escalade].[2005] and so on. Note that each member and dimension name is enclosed in square brackets. MDX requires square brackets if a member contains a space or is a number. Members that do not contain any spaces and aren't numbers can be represented without brackets. However, for the sake of consistency you should enclose every member in square brackets. With MSAS 2005 MDX allows you to refer to a dimension member without specifying the hierarchy where the member is found. For example, [time].[December] is a valid member; however, you should include the hierarchy name to avoid confusion and potential errors. For example, you could have [fiscal] hierarchy and [calendar] hierarchy within your time dimension. Furthermore, each year included in your time dimension is likely to have the month of December. Therefore be sure to type [time].[calendar].[2005].[December] to specify exactly which member you want to reference in your queries.



A tuple is a combination of members from one or multiple dimensions; a tuple can only contain ONE member from each dimension. A tuple that contains a member from a single dimension is synonymous to a member. For example, ([time].[January 8th, 2009]) would be a tuple even though it refers to only one dimension. A tuple that refers to multiple dimensions could be ([product].[Cadillac], [time].[January 8th, 2009]). If you attempt to reference more than one member from each dimension the tuple will become invalid; for example ([product].[Cadillac], [time].[January], [time].[February]) would be incorrect. As a rule tuples are enclosed in parenthesis; if a tuple is comprised of a single member then parenthesis aren't necessary; but, for the sake of consistency you should always use parenthesis when referring to a tuple. If you wanted to compare a cube with a spreadsheet a tuple would be equivalent of a cell in a spreadsheet file; that is why cell B30 on a spreadsheet identifies only a single cell the intersection of column B and row 30. Note however, that the intersection of column B and row 30 will occur in each sheet within your Excel file. Similarly ([product].[Cadillac], [time].[January 8th, 2009]) has intersections with other dimensions the same product could be sold in multiple stores, by different salespeople, in different cities, financed by different banks. Unless you include all cube dimensions in a tuple its definition would be incomplete. So how do we decide exactly which members to include in the tuple? If the dimension member isn't explicitly specified Analysis Services assumes the default member. Typically default member for each dimension is the [All] member, so ([product].[Cadillac], [time].[January 8th, 2009]) references sales of Cadillac on January 8th of 2009 by all salespeople in all cities regardless of the financial institution that paid for the vehicle.



A set is a collection of zero, one or multiple tuples that have the same dimensionality. All tuples enclosed in a set must reference the same dimensions in the same order. For example ([product].[Cadillac], [time].[January]) cannot be combined with ([time].[February], [product].[Ford]) because they specify dimensions in reverse order. Nor could you combine ([time].[March], [product].[Toyota]) with ([geography].[northern USA], [product].[Audi] ) because the two reference different dimensions. However, {([product].[Cadillac], [time].[January]), ([product].[Audi], [time].[December])} is a valid set. Since a set can consist of a single tuple, and a tuple can consist of a single member the following is a perfectly valid set {([time].[January 8th, 2009])}.



A set of zero tuples is an empty set. MDX requires enclosing sets in curly braces ({}). Some built-in MDX functions return sets; if this is the case you don't have to enclose the output of the function in curly braces. For the sake of consistency you should enclose all your sets in curly braces.