Some MDX Fundamentals

Members, Tupls's and Sets

A member is a dimension item. A member can be at any level within a dimension. If a dimension has user defined hierarchies, each level within those hierarchies will contain members. The member is the lowest level of reverence for a dimension item.

A tuple is the intersection of one member from each dimension/hierarchy that is participating in a query or cube context

A set is formed from multiple tuple's. In MDX you do this by enclosing all the tuple's in curly braces and separated by comma's.

The fully qualified way to reference members

[Dimension].[Hierarchy].[Level].[Member]

e.g. 

[Sales Territory].[Sales Region].[Sales Region].&[Europe]

For attribute hierarchy's, the level is generally the same as the hierarchy, as attribute hierarchy's only have the one [All Members] level. In these cases many queries typically do not state the level. However it is good practice to always fully qualify the member for better readability and clarity.

Calculated Members

When creating calculated members, keep in mind that they work in relation to the dimensions used on the axis (SELECT clause). The data on the axis is restricted by either the cube context (the FROM clause) or the slicer axis (WHERE clause).

Consider the following MDX code snippet:

WITH

MEMBER [Measures].[ _Practice Patient Count] AS

([Measures].[Patient Count],[Activity Breakdown].[Description].defaultmember)

MEMBER [Measures].[Practice Expected Rate Count] AS

SUM({([ACG].[ACG Description].[ACG Description])},[Measures].[ _Practice Patient Count] * [Measures].[CCG Count Ratios])

..............

--End of calculated members

SELECT 

NON EMPTY {

[Measures].CCGActivityCount,

.............. } ON COLUMNS,

NON EMPTY {(

[Location].[PCT Code].[PCT Code].ALLMEMBERS * 

[Location].[Locality Code].[Locality Code].ALLMEMBERS * 

[Location].[Practice Code].[Practice Code].ALLMEMBERS )} ON ROWS

FROM ( SELECT ( STRTOSET('[Activity Breakdown].[Description].&[A&E General Activity]') ) ON COLUMNS

FROM ( SELECT ( STRTOSET('[Location].[Practice Code].&[P02341]') ) ON COLUMNS 

FROM ( SELECT ( STRTOSET('[Location].[Locality Code].&[05J01]') ) ON COLUMNS 

FROM ( SELECT ( STRTOSET('[Location].[Location].[PCT].&[05J]') ) ON COLUMNS

FROM (SELECT ClosingPeriod([Calendar].[Calendar].[Calendar]) ON COLUMNS

FROM [ACG])))))

In the above example, the data is being restricted to A&E activity on the FROM clause sub select. The query result is output at the hierarchy level by Location. On the calculated members section, the [_Practice Patient Count] member is being created as a member of the default [Measures] dimension. This member creates a patient count by A&E because the data is restricted to A&E on the FROM clause. The second calculated member [Practice Expected Rate Count] works on the [_Practice Patient Count] member by breaking the patient count by ACG's, then for each ACG count, multiplying by the CCG ratio (which was created by another calculated member not shown here), then summing the individual results up together to get the expected patient count.