Introduction to MDX - Professional SQL Server Analysis Services 2005 with MDX - page 105

Product Line Color

Accessories Silver

Mountain Grey

Road Black

Touring Red

The concept of crafting queries to return only what you need maps directly to
MDX from SQL. In fact, they share a conditional statement that adds a whole
new level of power to restricting queries to return only desired data. It is
called the WHERE clause. After taking a look at the SQL use of WHERE you
will see how the concept applies to MDX. Here is a SQL query that uses
WHERE to return two columns for those products with a silver color from the
Product table:
SELECT ProductLine, Sales
FROM Product
WHERE Color = 'Silver'

This query returns the following:

Product Line Sales

Accessories 200.00

Road 2500.00

The same concept applies to MDX. Indeed, MDX uses both SELECT and
WHERE statements. The SELECT statement is used to identify the dimen-
sions and members a query will return and the WHERE statement limits the
result set by some criteria; the preceding example is restricted by Color = 'Sil-
ver'. Note that members are the elements that make up a dimension's hier-
archy. The Product table, when modeled as a cube, will contain two meas-
ures, Sales and Weight, and a Product dimension with the hierarchies Pro-
ductID, ProductLine, and Color. In this example the Product table is used as a
fact as well as dimension table. An MDX query against the cube that pro-
duces the same results as that of the SQL query is