Microsoft Office Tutorials and References
In Depth Information
Having a separate setting for manual refresh allows for better granularity. For example, an
administrator might have a relatively high automatic cache lifetime to improve the server performance for most
cases, but will have a lower manual cache lifetime to provide more up-to-date data for users who need
it, and manually refresh. Alternatively, an administrator can set the manual refresh cache lifetime setting
to a high value to discourage manual refreshing and to keep the automatic refresh schedule prescribed
by the author.
SQL Server Analysis Services
Excel Services shares query results between users only if they are using the same credentials to connect
to data. In the case of Windows authentication, each user has his or her credentials delegated to the
database, resulting in no sharing and a significant performance impact.
Many databases have a role model that maps multiple users to the same security roles, so it would be
safe (from the security point of view) to share query results between different users who are mapped
to the same database roles. Excel Services has an optimization specific to SQL Server Analysis Services
9.0. When the ECS uses Kerberos to connect to Analysis Services 9.0 (or later versions), it will first
verify that the Analysis Services security roles of the current user are the same as those of any of the
cached results. If they are, it will reuse the cached results, resulting in improved performance with no
To improve performance, the ECS keeps a pool of connections. It reuses those connections if their settings
match, rather than creating a new external data connection for every request.
The Connection Lifetime setting limits the maximum amount of time that a connection can be kept in
the pool. For security reasons, the connection is closed after this period of time. The default value for
this setting is 30 minutes.
Parallel and Asynchronous Queries
To improve the end-user response time and to maximize the resource utilization, the ECS executes
multiple queries in parallel. For example, if a workbook has multiple query tables and the user refreshes all of
them, the server uses several threads to execute the queries.
The Maximum Concurrent Queries Per Session setting determines the maximum number of threads that
will be used. The default value for this setting is five parallel queries. You can change this setting
separately for each trusted location. Although increasing this setting might result in better response time for
an end user, it will degrade the performance of the whole machine. If there is one trusted location that
has a few critical workbooks with multiple queries that should get priority, you can increase this setting
for that trusted location.
Excel 2007 issues queries for OLAP formulas asynchronously. It can run several of these types of queries
in parallel, one for each data connection in the workbook.