MDX Query

Hi,

Before I start, all the queries here have been used for my learning process and they may don't make any sense.
I've got a little bit confused about the way a specific MDX query executes.
Underestanding the following query is simple: (I used AdventureWorks sample database and DW)

WITH MEMBER MEASURES.PROFIT AS ([Measures].[Internet Sales Amount]-[Measures].[Total Product Cost])
SELECT MEASURES.PROFIT ON COLUMNS, [Customer].[Country].MEMBERS ON ROWS
FROM [Adventure Works]

for each member of country hierarchy in customer dimention it calculates the profit.
but I don't get how the following query is executed:

WITH MEMBER MEASURES.LOCATIONNAME AS [Customer].[Country].CURRENTMEMBER.NAME
SELECT MEASURES.LOCATIONNAME ON COLUMNS,
[Customer].[Country].MEMBERS ON ROWS
FROM [Adventure Works]

I mean there is nothing from the measures dimention except what we've located there (LOCATIONNAME). So does SQL Server for each row find the appropriate locationname by searching in [Customer].[Country]? what if the query was like this: (I know it's useless, just curious)

WITH MEMBER MEASURES.LOCATIONNAME AS [Customer].[Country].CURRENTMEMBER.NAME
SELECT MEASURES.LOCATIONNAME ON COLUMNS,
[Date].[Calendar].[Calendar Semester] ON rows
FROM [Adventure Works]

the [Customer].[Country].CURRENTMEMBER matches to nothing on rows. So again, does SQL Server search and find nothing and return default member of [Customer].[Country]?
How does it do that?

Thanks in advance

Welcome to the multidimensional world where things are a bit different from the relational world.

An MDX query defines a query space, a collection of cells, within a cube. A cube cell is determined by its coordinate which contains a member from every hierarchy of the cube dimensions.

For example, the query space of your second query is a set of cells which looks like:

{

( [LOCATIONNAME], [USA], ... ), // cell 1

( [LOCATIONNAME], [Canada], ... ), // cell 2

...

}

Here I only listed the attribute hierarchy members explicitly specified in your query. Since every cell is defined by taking one member from every hierarchy, the other hierarchy members in the cell coordinates are implicitly provided by the OLAP engine using the default members.

Given a query space, OLAP engine will evaluate all cell properties requested by the query. Value is one of the default cell properties returned unless your specify cell properties explicitly in your query.

After the OLAP engine parses your query, it iterates over the query space cell by cell and evaluates cell values among other cell properties. In your 2nd and 3rd queries, cell values are determined by the values of LOCATIONNAME. For each cell, LOCATIONNAME tells the OLAP engine to retrieve the name of the current member of the [Country] attribute hierarchy. Since every cell coordinate must have a member from every hierarchy, including the [Country] attribute hierarchy, the OLAP engine retrieves that member and returns its name as the cell value.

Therefore the answers to both your questions are yes.

|||Thanks for such an exponential reply.
What I understand is if I have for example 3 dimentions excluding Measures and each one of them has 2 hierarchy, each cell should contain 3*2+1=7 items of data. (3 dimentions * 2 Hierarchy + the data from Measures dimention)
Did I get it right?

I have one more question:
Could you please make it clear the difference between Hierarchy and Attribute Hierarchy?

Thanks again,
Amir|||

There are two types of hierarchies: attribute hierarchies and user defined hierarchies. Attribute hierarchies completely define the space of a cube. In your example, assume all 6 hierarchies are attribute hierarchies, each cell coordinate is defined by six attribute members + one measure member as you said.

Here is some information on attribute hierarchy.

http://msdn2.microsoft.com/en-us/library/ms174939.aspx