Archive for category: Blog

I’ve come across a good best practices article about scaling out SSAS by Denny Lee and Nicholas Dritsas. It describes how to set up a load-balanced scalable querying environment for Microsoft SQL Server 2005 Analysis Services so that you can handle a large number of concurrent queries to your Analysis Services servers.

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 came across this little gem which I haven’t noticed so far. A nice summary of the 10 things you should do to optimize the UDM query performance. The best one is kept for last – scale out when you can no longer scale up. So true, if I could only convince customers to do so J