MDX Concepts - Microsoft SQL Server 2008 Analysis Services - page 140

LISTING 10.2

Dimension Members Are Projected on Two Axes

SELECT
{([Drink],[1997]),([Drink],[1998]),([Food], [1997]),([Food], [1998])} ON COLUMNS,
{[Measures].[Store Sales],[Measures].[Store Cost], [Measures].[Unit Sales]} ON ROWS
FROM [Warehouse and Sales]

If we execute this query in the MDX query editor of SSMS, we get the results arranged in a
grid, shown in Figure 10.3.

Drink
1997

Drink
1998

Food
1997

Food
1998

Store Sales 48836.2100000001

19747702346

93742.1600000003
37498.6690000001

409035.59
163270.7235

778135.800000001
311993.641900003

Store Cost
Unit Sales

24597 46954 191940 365923

FIGURE 10.3

The results of our query appear in this grid.

Default Members and the WHERE Clause

Each cell in a multidimensional space is defined by all the attributes (or attribute hierar-
chies) in the cube. Some of the attributes are specified in a SELECT clause and define the
shape of the resulting multidimensional space. But what happens when the cube has more
attributes than the ones that we projected on the axes in Figure 10.1? How can those
missing coordinates be defined?

Look back at Figures 10.1 and 10.2. Figure 10.1 represents the multidimensional space
where data is stored, and Figure 10.2 represents the multidimensional space that is the
result of the query. Now let's pick up one of the resulting cells (for example, Store Sales ,
Drink , 1997 ) and see how the system assigned coordinates that can be used to retrieve the
data from the cube.

You can see in Figure 10.4 that the cell coordinates are based on the attributes of all the
dimensions in the cube: Store , Product , Time , and Measures . The coordinates on three of
those dimensions were defined by the SELECT statement and are shaded in the figure. The
fourth dimension, Stores , was left undefined by the query.

When the cube has more attributes than the number of attributes that are projected on
the axes, the system creates an axis, called the slicer axis , which contains all the other
attributes. The slicer axis is made up of one member from each of those attributes; those
members are called default members .

In the design phase, you can choose a specific member as the default member for the
attribute, by setting the DefaultMember property of either the Dimension attribute or the
Perspective attribute. For example, you might decide that the default member of the
Currency attribute should be the U.S. dollar. If you don't specify a default member in the
model, however, the system chooses default members automatically.