MDX is a redundant language

It is often said that SQL is a redundant language, since all but the most simple queries can be written many different ways. Well, I find MDX much more redundant than SQL. There really are thousands of ways to express the same logical query. I like this, since it means that there are lots of opportunities to tune the query by writing it in a different way. Just like SQL. As an MDX writer, I encourage you to strengthen your ability to write queries in different (redundant) ways. This will make you more powerful in query writing and give you more performance opportunities.

Here is a simple example, which involves one of the most common "brick walls" hit by new MDX writers. You want to write a query that would be the logical equivalent of

columnx IN ('Australia','New Zealand'). Here is one way to write this in MDX

with member [Client Host].[Client Geography].ANZ as
sum({[Client Host].[Client Geography].[Region].&[Asia].[AUSTRALIA],
[Client Host].[Client Geography].[Region].&[Asia].[NEW ZEALAND]})
select
{measures.hits,measures.sessions,Measures.[bytes total]} on 0,
tail([Date].[Year Month Day].[Month],12) on 1
from [EasternMining]
where [Client Host].[Client Geography].ANZ

Here is another way to write exactly the same query

select
{measures.hits,measures.sessions,Measures.[bytes total]} on 0,
tail([Date].[Year Month Day].[Month],12) on 1
from
(select {[Client Host].[Client Geography].[Region].&[Asia].[AUSTRALIA],
[Client Host].[Client Geography].[Region].&[Asia].[NEW ZEALAND]} on 0
from [EasternMining])

Of course there are many other ways of expressing this same query. I would not suggest that you should use one technique over the other. It really depends on which one is easier to read, easier to maintain, and runs faster.

For online real-time OLAP and data mining demonstrations goto http://RichardLees.com.au/Sites/Demonstrations