Author: Carl Rabeler

Contributor: Josh Caplan

Reviewers: Marius Dumitru, John Sirmon, Nick Medveditskov

This technical note discusses the usage of three connection string settings to improve the performance of Excel reports over slow network connections against Analysis Services cubes.

Problem Overview

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.

These reports are used by a wide range of users, with the data returned to these reports restricted based on the Windows® user account of the person executing the report. Analysis Services dynamic dimension security is used to ensure that the data returned for each report from the Analysis Services cubes is only the data that they have permission to view. The reports perform very well for users located in Redmond, Washington, United States and who are directly connected to the Microsoft corporate network here in Redmond. For quite some time, users located in other parts of the world have experienced a significant degradation in performance correlated with the performance characteristics of the network connection to Analysis Services cubes in Redmond. This significant degradation is also experienced by users working from home here in the greater Seattle area in Washington, United States, and connecting to the corporate network via RAS. The SQL CAT team worked with the Microsoft IT department to understand the reasons for this performance degradation and develop a solution that significantly reduces this performance degradation for all remote users.

Problem Analysis

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:

Security restrictions did not result in faster-running reports, even though these restrictions significantly reduced the amount of data available to the report. Excel reports executed by users that were restricted via Analysis Services security to a small slice of the cube did not run the same report significantly faster than users with little or no restrictions on the slice of the cube that they could see. To understand why this was true, we analyzed the execution of a single report by these different types of users :

When we analyzed report execution by using Performance Monitor, we saw that the amount of data exchanged between Excel and Analysis Services was not proportionate to the slice of the cube that a user had permission to view. Specifically, when the Excel report was run by a user that was restricted via Analysis Services dynamic security to a small slice of the cube, the amount of data exchanged between Excel and Analysis Services was not proportionally less than when the same report was run by a user with no restrictions.

When we analyzed report execution by using SQL Server Profiler, we saw that if the Excel report was run by a user that was restricted via Analysis Services dynamic security to only a slice of the cube, and if a cube formula in the report attempted to access data outside that slice, Analysis Services threw an error. After the error occurred, Excel closed the connection to Analysis Services and opened a new connection to continue resolving the remaining cube formulas in the report. Each time a new connection was opened, a new set of Discover requests were sent to Analysis Services and the results of these requests sent to Excel. In Excel 2007, with the Microsoft Analysis Services OLE DB Provider for Microsoft SQL Server 2008, 11 Discover requests were executed (synchronously) for each new connection. As a result, the smaller the slice of the cube to which a user had permission to view data, the greater the number of errors and, consequently, the greater the number of new connections opened and associated Discover requests issued to resolve the cube formulas in the report. The slower the connection, the longer the total time to resolve the cube formulas in the report.

Important: In our lab, we investigated the performance implications of upgrading to SQL Server 2008 R2 Analysis Services and Office 2010 Excel. We discovered that such an upgrade did not improve performance over slow networks. Indeed, the amount of data on the wire actually increased slightly for each new connection, resulting in a slight decrease in performance – particularly for the most restricted users. The reason for this additional data on the wire is that the Microsoft Analysis Services OLE DB Provider for Microsoft SQL Server 2008 R2 issues two new Discover requests for each new Analysis Services connection from Excel (increasing from 11 to 13 Discover requests per connection). One of these new requests enables the use of a more optimized axis format for cellsets and the other detects whether Excel is connecting to SQL Server PowerPivot for SharePoint or SQL Server 2008 R2 Analysis Services.

Analysis Services was returning both unformatted and formatted cell values in the result set returned in response to the MDX query issued by Excel for each cube formula. By default, Analysis Services returns both the formatted and unformatted value for each cell. Additional server-side formatting is also supported, including font style, fill color, and text color. However, returning the formatted value places more data on the wire than is actually required, because this formatting can also be done in Excel. For more information, see Using Cell Properties (http://msdn.microsoft.com/en-us/library/ms145573(SQL.90).aspx) in SQL Server 2005 Books Online.

Analysis Services uses a proprietary binary encoding method to return the XML data to Excel rather than using UTF-8 XML encoding. In general, this implementation saves CPU time, space, and memory on the server. However, because this implementation stores all strings in Unicode, strings on the wire require twice as much space as UTF-8 XML encoding does. The greater the number of strings involved in the exchange of data between Excel and Analysis Services, the greater the performance impact of this default encoding method. The Discover requests for each new connection are strings. For more information about these encoding methods, see Mosha Pasumansky’s blog post Analysis Services 2005 protocol - XMLA over TCP/IP (http://sqlblog.com/blogs/mosha/archive/2005/12/02/analysis-services-2005-protocol-xmla-over-tcp-ip.aspx) and protocol format connection string information Microsoft OLE DB Provider for SQL Server Analysis Services Connection String (http://msdn.microsoft.com/en-us/library/ee209205.aspx) on MSDN.

Solution

To reduce the amount of traffic on the wire for these Excel reports, our solution is to use connection string properties to modify the default behavior of Excel and Analysis Services.

Connection String Settings

We changed the following connection string settings for each of these reports for all users.

Missing Member Errors

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.

Note: Excel sets this property to "Error" by default, which overrides this setting if set as a standard connection string setting. To override an Analysis Services connection property that is set by Excel, you must specify the setting as an extended property (Extended Properties="MDXMissingMemberMode=Ignore"). The extended properties are applied after the Excel properties have been set using the OLE DB APIs – so extended property settings override what Excel sets.

Important: You should avoid changing the default value for this setting if you use PivotTables in Excel, because Excel needs to be notified when members vanish from server dimensions so that it can refresh PivotTables appropriately (that is, so that Excel can remove those members from the pivot views as well, even though this isn't always obvious and immediately visible to the end users). Also, if you change the default, invalid cube sets no longer throw an error in Analysis Services. So you must ensure that your cube sets are actually valid.

Formatting of Result Sets

We disabled formatting of result sets returned to Excel from Analysis Services and now perform all formatting in Excel. For information about how to enable or disable OLAP server formatting in an Excel PivotTable report, see Enable or Disable OLAP server formatting (http://office.microsoft.com/en-us/excel-help/design-the-layout-and-format-of-a-pivottable-report-HP010168032.aspx#BMinclude_olap_server_formatting) in Microsoft Office documentation online.

Encoding of Result Sets

We changed the encoding method for communication between Excel from Analysis Services from binary to UTF-8 ("Protocol Format=XML") by modifying the connection string used by Excel to connect to Analysis Services.

Performance Improvements

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:

Wired connection on the Redmond campus
Wireless connection on the Redmond campus
RAS connection from home in Redmond to the Redmond campus
Wired connection from Dublin, Ireland to the Redmond campus

Wired-Redmond

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.

Report Performance in Redmond, Washington, United States over wired connection (in minutes)

Wireless-Redmond

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 wireless network connection to the Analysis Services cube. This report took 39 seconds to execute using the default settings and 13 seconds with all three amended settings. For this type of report, the use of these settings increased performance by approximately 300 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 a slight degradation in performance.

Report Performance in Redmond, Washington, United States over wireless connection (in minutes)

RAS-Redmond

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 home in Redmond, Washington, United States over a RAS connection to the Analysis Services cube in Redmond, Washington, United States. This report took 7 minutes and 35 seconds to execute using the default settings and 1 minute and 10 seconds with all three amended settings. For this type of report, the use of these settings increased performance by approximately 650 percent. Notice that the ignoring of missing members had the most significant impact on performance in our environment, with the change in protocol encoding format and the elimination of formatting showing significant performance benefits.

Report Performance over RAS from home to Redmond, Washington, United States (in minutes)

Dublin-Redmond

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 Dublin, Ireland over a wired connection to the Analysis Services cube in Redmond, Washington, United States. This report took 8 minutes and 34 seconds to execute using the default settings and 1 minute and 25 seconds with all three amended settings. For this type of report, the use of these settings increased performance by approximately 600 percent. Notice that the ignoring of missing members had the most significant impact on performance in our environment, with the elimination of formatting showing significant performance benefits. The change in protocol encoding format showed only a modest impact on performance.

Report Performance Dublin, Ireland to Redmond, Washington, United States (in minutes)

Summary

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.

Note: For future investigation, we plan to investigate utilizing Excel Services to further improve the performance for our remote users.

Technical Notes

Excel, Cube Formulas, Analysis Services, Performance, Network Latency, and Connection Strings