MDX Part-3(MDX Members and member function)

Members represent an important Concept in an understanding of MDX. A member is, simply, an item in a dimension; members compose the values of the attributes that belong to a dimension. Keep in mind that measures are themselves dimensions, and are composed of members. 


MDX contains a set of functions, known as member functions that enable us to perform operations upon any member of a dimension. Member functions return a member or a zero. 


member functions allow us to perform operations, based upon a member's relative position, either up ("above" the member upon which the function is being performed) or down ("below" the member upon which the function is being performed) a dimensional hierarchy.


Working with Members and the .Members Operator:

SELECT
 [Measures].Members ON COLUMNS,
[Department].Members ON ROWS
FROM [HR]

All members of the Measures dimension (as columns);
All members of the Department dimension (as rows).

It is important to remember that the .Members operator must be applied at a level within a dimension where hierarchy is not ambiguous. If multiple hierarchies exist within a dimension, we must apply the .Members operator at or below the level of the "split," in a manner of speaking.
 If we attempt the operation we performed above, and multiple hierarchies exist within the dimension selected , the "membership" we are requesting is not precise, and our attempt will end with an error.

.Members operator is appended to the right of the level, hierarchy, or dimension

[Product].Members

[Product].[Product Family].Members
Note:
It is important to be aware that Calculated Members will not appear in the result dataset returned by the .Members function.
The.AllMembers function exist to include Calculated Members in our result datasets.

 Example 1:Let's say that an information consumer in the Human Resources department wants to know total organizational salaries for 1997 and 1998 (the two years captured in our corporate HR cube - and therefore the full "membership" of the Year level of the Time dimension).

Type the following simple query into the Query pane:
-- MDX04-1:  Tutorial Query No. 1

SELECT

{ [Time].[Year].Members } ON COLUMNS,

{ [Measures].[Org Salary]} ON ROWS

FROM [HR]
Example 2:
SELECT
  { [Measures].[Units Shipped], [Measures].[Units Ordered] } ON COLUMNS,

[Store].Members ON ROWS

FROM [Warehouse]   -- Remember to switch to the Warehouse cube!
The Results shown below.
The result dataset contains many empty members.
Empty cells occur in MDX statements when data for the intersection of multiple dimensions does not exist.
eliminate the empties, by adding the NON EMPTY keyword as follows:
SELECT
  { [Measures].[Units Shipped], [Measures].[Units Ordered] } ON COLUMNS,

NON EMPTY [Store].Members ON ROWS

FROM [Warehouse]   -- Remember to switch to the Warehouse cube!