So, today is the day everybody should blog about something really stupid…
Ok, so here’s ours: Fizz Buzz in MDX.
Fizz Buzz, for those that don’t know it, is one of those “tests” interviewers love to see whether a candidate can code or not. It’s useless as pretty much everybody knows it by now, but that’s besides the point.
There are a number of examples of “how to implement Fizz Buzz in language X”. Some are quite lame, others more impressive.
So, in the spirit of April Fools, here’s our Fizz Buzz implementation, in MDX. Run it against SteelWheelsSales, or any cube that has a dimension called [Time]
.
WITH
member [Time].[0] as 0
member [Time].[1] as 1
member [Time].[2] as 2
member [Time].[3] as 3
member [Time].[4] as 4
member [Time].[5] as 5
member [Time].[6] as 6
member [Time].[7] as 7
member [Time].[8] as 8
member [Time].[9] as 9
set DIGITS as { [Time].[0], [Time].[1], [Time].[2], [Time].[3], [Time].[4],
[Time].[5], [Time].[6], [Time].[7], [Time].[8], [Time].[9] }
set NUMBERS as Generate( DIGITS, Generate( DIGITS, DIGITS, ALL), ALL)
member [Measures].[Ordinal] as NUMBERS.CurrentOrdinal
member [Measures].[Fizz] as Iif( [Measures].[Ordinal] = Round( [Measures].[Ordinal]/3 ) *3, "fizz", "")
member [Measures].[Buzz] as Iif( [Measures].[Ordinal] = Round( [Measures].[Ordinal]/5 ) *5, "buzz", "")
member [Measures].[FB] as [Measures].[Fizz] || [Measures].[Buzz]
member [Measures].[FizzBuzz] as Iif( [Measures].[FB] = "", [Measures].[Ordinal], [Measures].[FB] )
SELECT
NUMBERS on Rows,
{ [Measures].[Ordinal], [Measures].[FizzBuzz] } on Columns
FROM
[SteelWheelsSales]
Although a trivial and quite futile exercise, it does have one feature that is worth mentioning: the All
argument in the Generate
function. Generate applies each element of the first set to each element of the second set. However, without the ALL
argument, duplicates from the resulting set are removed, which results in us getting only a 10 element set.