This project has moved and is read-only. For the latest updates, please go here.

Poor response time with calculations

Topics: Resolved
Feb 6, 2010 at 2:45 PM

I am trying to put simple calculations into my pivot table where the source is a fairly large sql server 2005 Analysis Services Cube.  I can add several fields to my Row Labels and response time is very good as long as I am using the measurements from the cube.  I can add a calculated field using the OLAP Pivot Table Extensions and if I have just one row label and response is ok (maybe 10 seconds).  But when I add a second row label with a calculated field, response time is very bad 2-3 minutes.  If I add a third row label, you can forget it.  I tried to add these calculations to the cube itself which works fine if the calculations are just adding/subtracting fields, but if I have a percentage, the cube adds the percentages together based on the criteria in my pivot table, so they are not correct.  Any ideas on how to make the response time better when using OLAP Pivot Table extensions OR how to make percentages work accurately in a sql server analysis cube?

Feb 6, 2010 at 3:42 PM

You need to have your Analysis Services administrator upgrade that server to Analysis Services 2008. Or you need to specify a NON_EMPTY_BEHAVIOR if you stay with Analysis Services 2005.

See the note in this page about NON_EMPTY_BEHAVIOR:
http://olappivottableextend.codeplex.com/wikipage?title=Calculations%20Help&referringTitle=Home

It's a performance hint that can be abused and can lead to incorrect results if specified incorrectly. But it will help performance.

Your Analysis Services administrator may be able to help you figure out the proper NON_EMPTY_BEHAVIOR for your calculations.

Marked as answer by furmangg on 6/10/2014 at 3:40 PM