MDX for Business - Ranking Customers/Cities

I was just asked to help with an MDX query to list out the top 50 Customers and provide ranks for the last 3 periods. This is a relatively simple MDX query, which someone with moderate MDX skills should be able to write. It might be interesting to you, so here it is.

with
Set Last3Months as tail([Date].[Year Month Day].[Month],3)
Member Measures.Last3MonthsHits as Sum(Last3Months,Measures.Hits)
Set Top50Cities as topcount(filter([Client Host].[Client Geography].[City],[Client Host].[Client Geography].member_caption<>"-"),50,Measures.Last3MonthsHits)
Member Measures.RankThisMonth as
rank([Client Host].[Client Geography].currentmember,order(Top50Cities,Measures.Hits,BDESC))
Member Measures.Movement as Measures.RankThisMonth-(Measures.RankThisMonth,[Date].[Year Month Day].prevmember)

Select Last3Months
*{Measures.Hits,Measures.RankThisMonth,Measures.Movement} on 0,
Top50Cities on 1
From EasternMining

It is a real query and I have executed it on my Weblogs cube (all hits and sessions from my web server). The results are formatted in Excel, which I have found interesting. Sydney (my home town) naturally is top of the list, but then comes New York and London. I don't do any business in these cities, which is why I find it interesting. Perhaps I should have offices there? Redmond is quite high. I guess my old Microsoft colleagues are still finding the real time demonstrations interesting.

Anyway, you can read the MDX and see what it is doing. Notice the elegance of the MDX query. It is very succinct, and easy to read. For those of you who know SQL, can you imagine writing this query in SQL? It wouldn't look anywhere near as elegant, nor would it perform as well.

If you would like assistance with your OLAP cube design or MDX query writing, please don't hesitate to ask.