Search code examples
ssasmdxolapcube

Visualizing the "ALL" rollup in a cube or mdx query

Let's take the following illustration which is often used (or a variant thereof) to show a cube and how it stores information for querying:

enter image description here

Here we have three dimensions, Time, Product, and Location with let's call it a COUNT Sold for the measure. I understand viewing a particular cell to get the intersection of a Product x Location x Time, but what about if the Product is "All" or the Location is "All" or the Time is "All", how would that work or be visualized?

Solution

  • When you use All member it is like you are not slicing the cube with that dimension at all. For example in SQL if you want to find count of rows for particular product, time and location, you would write where condition:

    where Product = 'Modems' and Location = 'Asia' and Time = '2001'

    If you want to see count for all values of Time column you will simply omit Time from the where condition:

    where Product = 'Modems' and Location = 'Asia'

    You basically asked for the count for Product = 'Modems', Location = 'Asia' and all rows no matter what Time value it has. This is basically what MDX will return when you ask for Product = Modem, Location = Asia and Time = All in the cube.

    If you had 4th dimension in your cube, lets say Account, in you picture you cannot visualize 4th dimension, but it is present, and in the context of your result cells, you actually defined a following tuple (if All is a default member of Account dimension), for example:

    (Measures.Count, Product.Modems, Time.2001, Location.Europe, Account.All)