The 4.2 billion tuples limitation

In the What Are Cubes Bad At post, I mentioned about SSAS 4.2 billion tuples limitation. The fact that moving the attributes about solves the problem really amazed me so I’ll detail it out a little bit.

When we run a complex MDX query, sometimes SSAS returns the following error message:
The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.

This error is consistent, i.e. it always happens every time we run that MDX query. It does not matter where we are running the query from, SSMS, BIDS, ProClarity, or MDX Studio, it always returns the same error message.

Oddly enough, when you reorder the attributes on rows or columns the error goes away. It seems that this error only happens on complex MDX queries, i.e. when we have more than 5 attributes on the rows or columns, and only when we use the non empty function. This error happens on both SSAS 2005 and 2008 (I don’t know if it happens on R2 or 2k).

The error occurred because the query results in more than 4.2 billion tuples and therefore the non empty function fails. For this error to occur the user must belong to a role that has dimensional security. The non empty cross join function fails, as Microsoft explained here, because the ‘optimized algorithm for non empty’ can’t handle dimension security on the measures, so SSAS uses the default algorithm.

It is very interesting to learn that when we move the attributes about, the error goes away. Let’s have a look at this MDX query:

SELECT { [Measures].[M1], [Measures].[M2], [Measures].[M3] } ON COLUMNS,
  { NONEMPTY
    ( { [Dim1].[Attr1].&[Member1], [Dim1].[Attr1].&[Member2],
        [Dim1].[Attr1].&[Member3], [Dim1].[Attr1].&[Member4] } *
      { [Dim2].[Attr1].&[Member1], [Dim2].[Attr1].&[Member2], [Dim2].[Attr1].&[Member3] } *
      { [Dim3].[Attr1].Members } *
      { [Dim4].[Attr1].Members } *
      { [Dim4].[Attr2].Members } *
      { [Dim3].[Attr2].Members } *
      { [Dim4].[Attr3].Members } *
      { [Dim5].[Attr1].Members } *
      { [Dim6].[Attr1].Members },
      { [Dim7].[Attr1].&[Member1] } *
      { [Dim5].[Attr1].&[Member1], [Dim5].[Attr1].&[Member2] } *
      { [Measures].[M1], [Measures].[M2], [Measures].[M3] }
    )
  } ON ROWS
FROM [Cube1]
WHERE ([Dim7].[Attr1].&[Member1], [Dim4].[Attr4].&[Member1])

The above query results in the 4.2 billion error message. But if we move line 9 and 10 above like this, the error goes away:

SELECT { [Measures].[M1], [Measures].[M2], [Measures].[M3] } ON COLUMNS,
  { NONEMPTY
    ( { [Dim1].[Attr1].&[Member1], [Dim1].[Attr1].&[Member2],
        [Dim1].[Attr1].&[Member3], [Dim1].[Attr1].&[Member4] } *
      { [Dim2].[Attr1].&[Member1], [Dim2].[Attr1].&[Member2], [Dim2].[Attr1].&[Member3] } *
      { [Dim3].[Attr1].Members } *
      { [Dim3].[Attr2].Members } *
      { [Dim4].[Attr1].Members } *
      { [Dim4].[Attr2].Members } *
      { [Dim4].[Attr3].Members } *
      { [Dim5].[Attr1].Members } *
      { [Dim6].[Attr1].Members },
      { [Dim7].[Attr1].&[Member1] } *
      { [Dim5].[Attr1].&[Member1], [Dim5].[Attr1].&[Member2] } *
      { [Measures].[M1], [Measures].[M2], [Measures].[M3] }
) } ON ROWS
FROM [Cube1]
WHERE ([Dim7].[Attr1].&[Member1], [Dim4].[Attr4].&[Member1])

The last MDX does not result in the 4.2 billion error message. In this last MDX query, you’ll notice that Dim3’s attributes are put together and Dim4’s attributes are put together; whereas on the first MDX query, the attributes are sandwiched. While this order of attribute might not be the one that suit the user, it is the one that SSAS likes. If we put the attribute from the same dimension together, SSAS only return the tuples that actually exist, rather than returning all possible tuples. It is because of Auto-Exists, as Mosha pointed out here, i.e. SSAS detects that the set it needs to apply Auto-Exists to, can be nicely split into parts. A backgrounder on Auto-Exists is here (by Christian Wade, look under Fact Dimension section) and here (by Teo Lachev). It is basically the MDX Exists function, but automatically put there by SSAS. A full explanation about Auto-Exists is here (look under ‘Cube Space and Auto-Exists’ section).

So, the take away is: put attributes from the same dimension next to each other.