Slow Query Performance - Filtering Empty Cells & Totaling

  • Article

With ProClarity and SQL Server 2005 SP2, users may experience slower than expected query results in some circumstances.  One example is when users want to filter out empty cells while using totals.  The WITH MEMBER clause used to calculate totals on the fly is treated in a certain way by SSAS 2005.  2005 It sees this query scoped, on-the-fly member and switches to a "naive" type of non empty filtering.  This type of filtering is less efficient than other methods due to the fact it has to check every cell for the presence of data.  

What are the options?

  • Educate users.
  • Optimize your cube - such as limiting the number of members, partitioning data, etc.
  • Use SQL Server SSAS 2008 - it can use intelligent nonempty filtering much more often than SSAS 2005.  For more information on the improvements, seethe section titled: "MDX Query Performance: Block Computation" at this link: https://msdn.microsoft.com/en-us/library/cc278097.aspx.
  • Change the query in the ProClarity Professional MDX editor to use NONEMPTY and execute - (remember that you will lose this NONEMPTY keyword if it is not specified in the MDX editor before every query).

A BIG thank you goes out to Steve Pontello for his help on this issue.

 -Joey