Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

MDX Limericks

with 8 comments

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 http://en.wikipedia.org/wiki/Limerick_(poetry 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:

WITH MEMBER DATE DAY NAME TODAY,
AS ONE PLUS DATE DAY NAME THURSDAY,
SELECT AVERAGE RATE,
ON ZERO, DATE DATE,
ON ONE FROM DIRECT SALES WHERE MAY.

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.

 

Written by Chris Webb

January 19, 2006 at 2:29 pm

Posted in Off-topic

8 Responses

Subscribe to comments with RSS.

  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

    Unknown

    January 19, 2006 at 11:41 pm

  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).

    Mosha

    January 20, 2006 at 7:57 am

  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***

    Unknown

    January 20, 2006 at 9:12 am

  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

    Jon

    January 20, 2006 at 10:36 am

  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])

    Chris

    January 20, 2006 at 11:00 am

  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

    Jon

    January 20, 2006 at 11:16 am

  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])

    Chris

    January 20, 2006 at 11:43 am

  8. Hmm … is "Mosha" pronounced "mosh-a" (as in posh) or "m-oh-sha"?

    Unknown

    January 20, 2006 at 11:48 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,299 other followers