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

 

The StrTo functions and CONSTRAINED

If you’ve been using Reporting Services 2005 to generate MDX you’ll have probably noticed that the StrTo<Something> family of functions (StrToSet, StrToMember, StrToTuple, StrToValue) have gained an extra, optional parameter – the CONSTRAINED flag. It’s not mentioned in Books Online and since someone has been asking about it on the newsgroup I thought it would be worth a quick post explaining what it does.
 
Basically it’s a security feature for middle-tier scenarios to stop MDX injection attacks. Running parameterised MDX queries in RS is probably going to be the most common example of when you’d want to use it, but I guess anywhere where you are generating dynamic MDX in code you’re running the risk of an evil user trying to pass you a stored procedure call (or something worse) instead of the member name or key you were expecting. By putting the CONSTRAINED flag in your query you automatically restrict what the StrTo<Something> functions can accept as their first parameter:
  • StrToMember can only take a string containing a member name
  • StrToSet can only take an explicitly defined set, ie a list of member names or tuples in curly brackets
  • StrToTuple can only take an explicitly defined tuple, ie a list of member names in brackets
  • StrToValue can only take a constant value
What’s not allowed with CONSTRAINED is an MDX expression which evaluates to either a Member, Set, Tuple or Value. Here’s an Adventure Works query which illustrates this:
 

WITH

MEMBER MEASURES.STRTOSET_TEST1 AS COUNT(STRTOSET("{[Measures].[Internet Order Quantity]}"))

MEMBER

MEASURES.STRTOSET_TEST2 AS COUNT(STRTOSET("MEASURES.MEMBERS"))

MEMBER

MEASURES.STRTOSET_TEST3 AS COUNT(STRTOSET("{[Measures].[Internet Order Quantity]}", CONSTRAINED))

MEMBER

MEASURES.STRTOSET_TEST4 AS COUNT(STRTOSET("MEASURES.MEMBERS", CONSTRAINED))

SELECT

{MEASURES.STRTOSET_TEST1, MEASURES.STRTOSET_TEST2, MEASURES.STRTOSET_TEST3, MEASURES.STRTOSET_TEST4} ON 0

FROM

[ADVENTURE WORKS]

 
The final calculated member here produces an error because "MEASURES.MEMBERS" is an expression which evaluates to a set and is not an explicitly defined set like the ones in the first or third calculated members.
 
UPDATE: Jon makes a good point in the comments…

Paper on Data Mining Reports

There’s a good paper on how to create Reporting Services reports which use Analysis Services data mining available here:
It’s taken from Teo Lachev’s book, Applied Microsoft Analysis Services 2005, which is on my list of must-buys (other sample chapters are available here: http://www.prologika.com/Books/0976635305/Book.aspx and it looks like it’s pretty good in terms of content).

Impressions of Pentaho

Not Microsoft BI-related, but I guess we all know we need to keep an eye on what’s happening in the open source BI space. To that end I thought I’d link to the Bayon Technologies blog (usually covering Oracle BI) where there’s a good, ongoing series of posts on Pentaho. See:
…for the story so far.
 

Data Mining Amazon Wish Lists

I’ve seen this article linked to by several people today and thought it was worth posting up myself:
Not true data mining as BI folks understand it, but once you’ve got the data just think what you could do with it using AS2005 data mining (something similar to what Amazon are already doing with it, I guess). By the way, if anyone wants to buy me a present…

NON EMPTY limitations

I was putting together a ridiculously large query for my previous post to try to test performance of the HAVING clause, and tried to run the following on Adventure Works:
 
SELECT [Measures].[Internet Order Quantity] ON 0,
NON EMPTY
[Customer].[Customer].MEMBERS
*
[Date].[Date].MEMBERS
*
[Product].[Product].MEMBERS
*
[Geography].[City].MEMBERS
ON 1
FROM [Adventure Works]
 
To my surprise, I got the following error message:
 

Executing the query …

The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.

Execution complete

 
Obviously some kind of internal limit here to do with NON EMPTY: the query runs if you remove it. Now 4,294,967,296 tuples is a lot of tuples but imagine that you had a million customers and ten thousand products, and you wanted to find out which customers had bought which products – you’d want to find the non empty combinations in a set of tuples which is still larger than that limit. OK in any normal cube this would probably bring back far too many rows to be a useful query, but what if you had a very specific slice in the WHERE clause (eg adding WHERE([Ship Date].[Date].&[1])  onto the end of the above query still gives the same message) so that you’d only expect to return a very small number of rows in your results? There are certainly going to be ways to work around this if you are writing your own MDX, but I wonder if any of the commercially available client tools which use NON EMPTY know to do this yet? Hmm…
 
 

The HAVING clause

The fact that there’s now a HAVING clause at all in AS2005 MDX doesn’t seem to be publicly documented anywhere, as far as I know; it’s one of those hidden features we found out about while researching ‘MDX Solutions’. While George has a full write-up on how it behaves in the book (coming soon to a store near you) here’s a quick overview of what it does…
 
Basically, it allows you to filter the contents of an axis without having to use the FILTER function. So, for example, the following Adventure Works query only returns the tuples on the ROWS axis which have Internet Order Quantity greater than 1000:
 

SELECT [Measures].[Internet Order Quantity] ON 0,
NON EMPTY
[Date].[Date].MEMBERS
*
[Product].[Subcategory].MEMBERS
*
[Geography].[Country].MEMBERS
*
[Customer].[Gender].MEMBERS
HAVING
[Measures].[Internet Order Quantity]>1000
ON 1
FROM
[Adventure Works]

 
Of course this isn’t anything you couldn’t do before in AS2K, so what’s the point of it? Well, first of all, if you know how you’d do this using a FILTER then you’ve probably already realised that using the HAVING clause makes your MDX much easier to read; as someone who in the past wrote the MDX generation component for a client tool, I can also say that it will also make generating MDX in code easier.
 
There also seem to be some performance benefits to using it too. Since I was told the HAVING condition is applied after the NON EMPTY, I rewrote the above query to use a combination of NONEMPTY and FILTER as follows:
 

SELECT

[Measures].[Internet Order Quantity] ON 0,
FILTER(
NONEMPTY(
[Date].[Date].MEMBERS
*
[Product].[Subcategory].MEMBERS
*
[Geography].[Country].MEMBERS
*
[Customer].[Gender].MEMBERS
,[Measures].[Internet Order Quantity])
,[Measures].[Internet Order Quantity]>1000)
ON 1
FROM
[Adventure Works]

 
I was expecting this query to perform exactly the same as the first one; however the version which uses HAVING seems to consistently perform better on my machine, although only slightly so. I’d be interested to know whether this is reproducible and why this is. 

Netscan

Netscan is a site I have a look at every few months, and since I’ve just noticed it’s had an upgrade (although this could have happened a while ago) I thought it would be worth a blog entry:
 
As you can see, it’s a very good example of a web-based dashboard providing information/stats on newsgroup postings. The one new feature I was particularly impressed by was the graphical representation of the life of a newsgroup thread – expand one of the nodes in the ‘Thread Tracker’ section to see this; in my opinion this is exactly the kind of creative use of graphics which helps people understand data much more efficiently than a dry text-based approach. I wonder what technology they’ve used for it all? I would guess the UI is custom-coded, but I’m sure you can do something very similar with RS2005 and the rest of the SQL BI suite.
 
It’s also interesting from another point of view in that for a change the data is something that has relevance to me, as opposed to being some customer’s sales figures or accounts data. I’m there listed at #5 on the ‘Author Tracker’ rankings (using my onlyforpostingtonewsgroups hotmail account) for the whole of 2005, although if you were to rank on number of replies instead of days active I wouldn’t do quite as well; Deepak comes in at #1 on any measure though and you can understand why he’s the MVP given the amount of work he must put in. Also compare the data for Quarter 3 and Quarter 4 2005: in the ‘Report Card’ section for Q3 you can see that most measures were down lots compared to the same period 2004, whereas in Q4 the measures were way up. I guess that’s because AS2K is a mature product so the people working with it have less need to ask questions, or perhaps all Microsoft BI projects were put on hold for SQL2005; certainly I’d say that the increase in the Q4 figures must be due to the release of SQL2005 and people having to come to grips with the new functionality.

Blog Birthday

It’s my blog’s first birthday today! Seems like I’ve been writing it forever… Anyway, I’m quite pleased with how the first year of blogging has gone – I’ve managed to keep my resolution and posted something just about every week, and hopefully it’s been interesting and useful. I’ve learnt a lot too, made a lot of new friends and even managed to become the top-ranked "Chris Webb" on Google (for the moment)!
 
Next year will see a lot of changes for me professionally and with a bit of luck they will leave me more time for blogging as well as provide me with some new Microsoft-BI-related topics to blog about. As always, I welcome any kind of feedback/suggestions/tip-offs for news, either in public via comments or by email (check my profile for how to contact me).
 
Happy New Year!