Monthly Archives: January 2006
ascmd tool beta
MDX Limericks
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
- 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
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 0FROM
[ADVENTURE WORKS]
Paper on Data Mining Reports
Impressions of Pentaho
Cluster Migration
Data Mining Amazon Wish Lists
NON EMPTY limitations
NON EMPTY
[Customer].[Customer].MEMBERS
*
[Date].[Date].MEMBERS
*
[Product].[Product].MEMBERS
*
[Geography].[City].MEMBERS
ON 1
FROM [Adventure Works]
Executing the query …
The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.
Execution complete
The HAVING clause
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]
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]