Excel, Cube Formulas, Analysis Services, Performance, Network Latency, and Connection Strings
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.
Tags: excel, performance