Advanced MDX - Microsoft SQL Server 2008 Analysis Services - page 170

In Analysis Services 2000, you can't write a query with a single tuple where a set was
expected. You have to explicitly convert the tuple to a set:
SELECT {[Promotion].[Media Type].[Bulk Mail]} ON COLUMNS FROM [Warehouse and Sales]
This limitation is lifted in Analysis Services 2005, which supports implicit type conversion.
You can rewrite the preceding query to pass a member to the axis definition expression:
SELECT [Promotion].[Media Type].[Bulk Mail] ON COLUMNS FROM [Warehouse and Sales]
Of course, this particular improvement might not seem like a big deal, but this simplifica-
tion lifts a burden from the MDX programmer. Now you don't have to remember to
enclose an expression in curly braces to convert a member to a set, nor do you have to
enclose an expression in parentheses to convert a member to a tuple.
Not all types of objects can be converted to the objects of another type. There are special
rules for type conversion. Figure 11.14 shows what types of objects can be converted to
which other types of objects and the rules that govern those conversions. In the diagram,
a circle denotes a type and an arrow denotes a conversion rule.
Most of the type conversion rules can be deduced using a common sense. For example, a
tuple can always be converted to a set that contains that tuple; analogously, a member can
be converted to a tuple that contains the member.
However, some conversion rules, such as those that were invented in an effort to bring
the syntax of MDX and SQL closer together, are a bit more complicated. The conversion
of a level to a set allows you to write MDX queries in a way that mimics column specifica-
tions in SQL. For example, if you needed to use an SQL query to list all the stores, you
would write the following:
SELECT store_name FROM store
In MDX, the same query would look like this:
SELECT [Store].[Store].[Store].Members ON COLUMNS FROM [$Store]
The type conversion rule allows you to skip .Members so that it looks like you put only an
attribute name on the axis specification. Now you can write an MDX query that looks
more like the SQL query:
SELECT [Store].[Store].[Store] ON COLUMNS FROM [$Store]
Strong Relationships
In the “Query Execution Context” section in the preceding chapter, you can find a discus-
sion about the logic used to generate the current coordinate. The current coordinate is a
tuple that consists of members corresponding to each attribute in the cube. The current
coordinate is generated in the following process starting with the default members for