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.
Your example is not the right one to demonstrate the performance superiority of HAVING clause. Any perf difference that you might see is probably small, and is actually not caused by HAVING being better then Filter here, but simply by the virtue of NON EMPTY being at top level.The right example would use more then one axis (your COLUMNS axis is too simple here). Then, you won\’t be able to easily use NonEmpty function, since you would need to use it more then once, and repeat the other axes as filter condition etc. When NON EMPTY is used, though, it runs only ones over all axes in one scan – and then HAVING does postfiltering. Since filtering that NON EMPTY does is usually much more efficient then more complex filtering by boolean condition that HAVING/Filter provide – switching the order of operations (and they obviously commutative) provides performance benefit.Mosha