Practice
We can reinforce our understanding of how the .Members
function operates by constructing expressions that call it into action. We
will use the MDX Sample Application (see the second article in our series, Structure
of the MDX Data Model, for more information about the
Sample Application) to construct and execute our expressions, and to see the
results we obtain.
1.
Start the MDX
Sample Application.
2.
Clear the top
area (the Query pane) of any queries or remnants that might appear.
3.
Ensure that FoodMart
2000 is selected as the database name in the DB box of the toolbar.
4.
Select the HR
cube in the Cube drop-down list box.
The MDX
Sample Application window should resemble that shown below, complete with
the information from the HR cube displaying in the Metadata tree
(in the left section of the Metadata pane, between the Query pane
at the top of the application window, and the Results pane, at the
bottom.).
Illustration 2: The MDX Sample Application Window
(Compressed)
We will begin with a simple illustration that involves the .Member
operator: 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).
5.
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]
6.
Click Query
on the top menu, and then select Run.
We see
the results below, which appear in the Results pane as soon as Analysis
Services fills the cells that it determines to be specified by the query.
Illustration 3: The Query Result Dataset
Note:
For an explanation of the comments line, which appears atop the query, together
with a general discussion regarding attributes of the basic MDX query
structure, see the first
article of the series. For a
discussion of the operators involved, here and going forward, see the first
three articles of the series.
The
query delivers the results that we requested.
7.
If it is
desirable, save the query by selecting File ` Save As, and call the file
something meaningful.
Now
let's delve a bit deeper, and undertake an exercise to illustrate the use of
the .Members function in varying levels of the same hierarchy.
8.
Type the
following query into the Query
pane:
-- MDX04-2: Tutorial Query No. 2
SELECT
{ [Measures].[Units Shipped], [Measures].[Units Ordered] } ON COLUMNS,
[Store].Members ON ROWS
FROM [Warehouse] -- Remember to switch to the Warehouse cube!
9.
Click Query
on the top menu, and then select Run.
The Results
pane appears as partially shown below.
Illustration 4: The Query Results
The result dataset contains many empty members, intermingled
with populated members, as we can see by scrolling down. Empty cells occur in
MDX statements when data for the intersection of multiple dimensions (in
our example, the intersection of the Units Shipped and Units Ordered
measures, and the Store dimension) does not exist. To make our points
with the .Members operator a bit clearer to see, let's issue instructions in
our query to eliminate the empties, by adding the NON EMPTY keyword as
follows:
10.
Type in the NON
EMPTY keyword just before the following line of the query above:
[Store].Members ON ROWS
The Query pane of the Sample Application
appears as shown in Illustration 5.
Illustration 5: The Query Pane with the Added NON EMPTY Keyword
» 