Tag Archive for: Analysis Services

If you have a cube with large dimensions you may have come across the following error:

The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.

The culprit is the NON EMPY (or NonEmpty), as mentioned in the Chris Webb’s blog and Mosha Passumansky’s blog. It turns out that NonEmpty simply gives up when the number of tupples in the cross-join members exceeds 4GB. The problem is all Microsoft SSAS clients (Excel, Reporting Services, Report Builder, etc.) are blissfully unaware of the trap to come and would happily cross-join dimension members as you add more dimensions to the report. Take for example the following (simplified) query produced by the Report Designer when the report shows the customer name and its accounts:

SELECT NON EMPTY { [Measures].[Sales] } ON COLUMNS,
NONEMPTY { ([Customer].[Customer].[Customer].ALLMEMBERS *
[Account].[Customer Account Number].[Customer Account Number].ALLMEMBERS)} ON ROWS
FROM <some cube>
WHERE ([Date].[Date].&[20070712])

In my case, the server returned the 4GB error with about 150,000 customers and 200,000 customer accounts. I can think of two approaches to avoid this error:

1. Use Autoexists – Consider placing the frequently-queried large attribute hierarchies in the same dimension. Not only this may avoid the error but also it may give you better performance since the server knows how to cross-join attribute hierarchies within the same dimension efficiently. Let’s say you can add the Customer attribute to the Account dimension so it exists both in the Customer and Account dimensions. This query executes successfully:

   SELECT NON EMPTY { [Measures].[Sales] } ON COLUMNS,
   NONEMPTY { ([Account].[Customer].[Customer].ALLMEMBERS *
   [Account].[Customer Account Number].[Customer Account Number].ALLMEMBERS)} ON ROWS
   FROM <some cube>
   WHERE ([Date].[Date].&[20070712])
   

2. Reduce large sets with Exists() before NonEmpty()

   SELECT NON EMPTY { [Measures].[Sales] } ON COLUMNS,
   NONEMPTY { ([Customer].[Customer].[Customer].ALLMEMBERS *
   Exists([Account].[Customer Account Number].[Customer Account Number].ALLMEMBERS, [Date][Date].CurrentMembers, “<Measure Group Name>”)} ON ROWS
   FROM <some cube>
   WHERE ([Date].[Date].&[20070712])
   

In this case, the Exists function returns only the customer accounts that have data in the measure group for the date requested. The net result is that you pass a much smaller set of accounts to the NonEmpty function.

I had an interesting issue this week. I was working on a small test cube with financial data. The lowest grain in the measure group was the Account dimension which represents a customer bank account. I had to implement rolling 12 calculations on semi-additive measures (e.g. Avg Account Balance) by weighting the balances at the account level: