Report Portal

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

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.

Read more...

 

Tags: excel, performance

 

2007-2015 VidasSoft Systems Inc.