MDX Limericks

Since last summer’s ‘OLAP Jokes’ entry was the most popular (in terms of links to it) entry I’ve made on this blog so far, I’ve been wracking my brains for a suitable follow-up. During my lunchtime browsing today I came across this posting on a certain Phil Factor’s blog:
…and thought that anything those SQL guys can do, us MDX-ers can do better. So I’d like to announce an MDX limericks competition. No prizes apart from the admiration and respect of the entire Analysis Services community, I’m afraid, but who needs prizes when faced with a noble challenge such as this?

Here are the rules:

  • Each entry must be an MDX query executable against any cube/perspective in the Adventure Works database, and must take the form of a limerick (see for details)
  • The query must return at least one cell which contains a value other than null
  • Entries should be posted as comments to this blog entry
  • Extra points will be awarded for wit and creativity

It’s actually pretty difficult. Here’s my first attempt:

WITH MEMBER [Date].[Day Name].[TODAY]
AS 1 + [Date].[Day Name].[THURSDAY]
SELECT [Average Rate]
ON 0, [Date].[Date]
ON 1 FROM [Direct Sales] WHERE([MAY])

As an aside, this brings up the vexed question of how you should pronounce MDX. In my limerick I’m not pronouncing the punctuation so it reads:


However I’m prepared to be flexible on this point.

So come on all of you (and especially you Jon, as I know you can never resist something like this) and get composing!


UPDATE: I’ve decided two rule changes, to make things easier. First I’ll allow queries from Foodmart 2000 for those of you on AS2K; second I’ll allow statements other than SELECT statements (eg CREATE SET, UPDATE CUBE etc) so long as they execute without errors.


8 thoughts on “MDX Limericks

  1. Love the idea Chris. *Hate* the fact that I could concentrate on nothing else until I created one!My contribution…SELECT [Measures].[Average Rate] on 0,{[Customer].[State-Province].[Ohio],[State-Province].[New South Wales]}ON 1 FROM [Direct Sales]WHERE [Customer].[Javier Navarro]SELECT MEASURES AVERAGE RATE ON ZEROCUSTOMER STATE-PROVINCE OHIOSTATE-PROVINCE NEW SOUTH WALESON ONE FROM DIRECT SALESWHERE CUSTOMER JAVIER NAVARRO

  2. You are right – it is difficult to do. I guess MDX lends itself to few tricks – since MDX Missing Members Mode is Ignore by default, you can always put anything you want inside [ ] – and pretend that it is a member name that doesn\’t exist, so there will be no error. But this would defy the spirit. I tried hard to come with something interesting, but the rules pretty much limit you to SELECT statement (in order to return cells). And trying to fit it into AABBA rhyme makes it almost impossible. Anyway – here is my version:with set [of looks] as (low, locks)select female having (white,socks)on 0, (cash, [start date], review, style, size, weight)on rows from [Adventure Works]It can be pronounced asWith set of looks as low locksSelect female having white socks On zero cash – start date Review style, size, weightOn rows from – adventure works !(P.S. Originally, I planned yellow socks – but Adventure Words cube has data only for females who bought white socks, so I was ending up with no cells, since HAVING clause was eliminating them).

  3. I\’m rubbish at MDX, so here\’s a normal limmerick about you instead:There was a young man named Chris,Whose penchant for booze was remiss,When he drank to much beer,You\’ll be sad to hear,He would go home smelling of p***

  4. Ok, ok, here\’s one (albeit not especially original or interesting):WITH SET [My Items For Sale]AS ASCENDANTS([Customer].[Male]).Item(1) * {[Product].Locks, [All Products].[Socks]}SELECT FROM [Direct Sales]Pronounced as:"With set my items for sale as ascendants customer male item one product locks all products sock select from direct sales"I agree with Mosha – drop the restriction about returning cells (perhaps allow any query that executes without error and perhaps also even permit MDX scripts that parse correctly), to allow more scope for imagination!Jon

  5. I\’m encouraged by the entries so far, keep them coming! Here\’s a handy hint for you: you can use the \’filter members\’ functionality in SQL Management Studio to search for members which end with a certain sequence of letters, which is useful for finding rhymes on large hierarchies like [Customer].[Customer].My latest effort:SELECT {[Internet Sales Amount],UNION({[Average Rate]} , {[Order Count]})}ON 0 FROM [Finance]WHERE ([Frederick Vance],[HL Mountain Rear Wheel], [No Discount])

  6. Ok, to be a bit different, here\’s one using an MDX scriptSCOPE (*, [Date].[Day of Month].[8]); this = ([Customer].[City].[Southgate], [Date].[Day of Month].[3], [Customer].[Don Lee]);END SCOPE; FREEZE; CALCULATE; Pronounced as:"Scope star date day of month eight this customer city southgate date day of month three customer don lee end scope freeze calculate"Jon

  7. What about a Microsoft themed one?SELECT {[Cristian K. Petculescu],[Thierry B. D\’Hers], [Shu K. Ito]}ON COLUMNS, {[Order Count],[Internet Tax Amount]} ON 1 FROM [Direct Sales] WHERE([2])

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.