Fizz buzz

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.

Back to blog

Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like

  • Graph Databases: Loading Data with Neo4j

    Graph databases are becoming more popular as a way of storing and analysing large connected datasets. Neo4j is a...

    Read more
  • Date Dimension Revisited: Bank Holidays

    Everyone familiar with data warehousing knows about the date dimension. It’s one of the first steps in the creation...

    Read more
  • Setup Pentaho BA Server to use SSL Certificates

    SSL Certificate Java Truststore Setup SSL, or Secure Socket Layer, is a technology which allows web browsers and web...

    Read more