Contributor: Josh Caplan
Reviewers: Marius Dumitru, John Sirmon, Nick Medveditskov
Microsoft, as a worldwide company, has users spread all over the globe that use a custom Microsoft® Office 2007 Excel solution to perform analysis of business data that is stored in Microsoft SQL Server® 2008 Analysis Services cubes that are physically located in Redmond, Washington, United States. To facilitate the analysis of this business data, the Microsoft IT department has developed approximately 25 Excel reports, each of which contains approximately 5,000 cube formulas.
We began by analyzing the Excel reports executed against Analysis Services, comparing the performance characteristics of these reports over different types of network connections. In performing this analysis, we used SQL Server Profiler and Performance Monitor to begin to understand the performance differences over different types of connections and to identify the source of the differences in performance. The first thing that we discovered is what you would probably expect – namely that the performance degradation over slower network connections was directly related to the amount of network traffic transmitted between Excel and the Analysis Services cube. As we dug deeper, however, our analysis became more interesting. We discovered the following:
We changed the value of the MdxMissingMemberMode XMLA property in the connection string to ignore missing members in MDX queries. For more information about this property, see Supported XMLA Properties (http://msdn.microsoft.com/en-us/library/ms186627.aspx) on MSDN. With Excel, changing this setting avoids the resetting of the connection to Analysis Services within a report each time a cube formula receives an error from Analysis Services due to restricted security access to a cube member referenced on a report.
The following graphs show the performance improvement associated with each of these settings as well as with all three of these settings combined in four scenarios:
The following graph displays the performance of a standard MSIT report by a typical user (restricted access to only a slice of the cube) executed in my office in Redmond, Washington, United States over a wired network connection to the Analysis Services cube. This report took 27 seconds to execute using the default settings and 11 seconds with all three of the amended settings discussed in this note. For this type of report, the use of these settings increased performance by approximately 250 percent. Notice that the both the elimination of formatting and the ignoring of missing members had a significant impact on performance in our environment, with the change in protocol encoding format showing no impact on performance.
Through the use of the three connection settings discussed in this technical note, we were able to increase the performance of complex Excel reports containing large numbers of cube formulas when querying Analysis Services. The performance increase ranged from 250 percent to 650 percent depending on the performance characteristics of the underlying network connections. While these reports still execute more slowly over slow connections, their performance is much more acceptable to remote users than before these settings were incorporated into our MSIT report environment.
Pingback from Twitter Trackbacks for Excel, Cube Formulas, Analysis Services, Performance, Network Latency, and Connection Strings - Technical Notes [sqlcat.com] on Topsy.com
Hi, thanks for this article. I have one question I'm doubting about and would like your input.
It seems that in this case, setting the Missing Member Errors to ignore resulted in the largest performance improvement where the network bottleneck is greatest.
Having read the important notice on the effect of the pivot table, could it be true that setting this property to ignore would have the benefits but none of the downsides if the following conditions are met?
1) Each time a pivottable/report is opened, it is constructed programatically by the object model and thus has no data is stored locally on the end user side.
2) There is no chance that while using this dynamically created report, the server dimension members change.
We had seen scenarios where Excel Sheet with Pivot table takes less time locally but over Wan it takes