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.
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
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).
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***
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
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])
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
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])
Hmm … is "Mosha" pronounced "mosh-a" (as in posh) or "m-oh-sha"?