Uncategorized

Excel Calculation Services for Cross-Forest Deployments

While on vacation, yes working ;-), I was asked to look into an issue where Excel Calculation Services was deployed in a cross-forest environment; specifically where Excel Services is deployed on server A residing in Forest 1 and the clients requesting workbooks reside in Forest 2. Forest 2 does not trust Forest 1; however, Forest 1 trusts Forest 2. The Excel Calculation Services topology in this scenario was fairly basic where Excel Web Access, Web Services, and the Calculation Services are distributed across two network load-balanced web front-end servers and Excel Calculation Services and any UDF assemblies are hosted on a separate application server also servicing the Office SharePoint Server Search indexing service.


The Excel Calculation Services topology is supportive of a cross-forest deployment and only needed one minor adjustment. In a scenario such as that described above to allow workbooks in trusted file locations to be access across domains [in this scenario, the Windows SharePoint Services Web application is defined as a Trusted File Location and includes all children in the trust] Excel Calculation Services must be configured to allow cross domain access using the SharePoint administration tool (STSADM). To allow access across domains you will need to run the following STSADM operation on the application server hosting the Excel Calculation Services component:


stsadm.exe -o Set-EcsSecurity -Ssp <SSP name> -AllowCrossDomainAccess true


About the Components

The Excel Calculation Services component is the “engine” of Excel Calculation Services and handles loading, calculation, session management, and external data refresh for workbooks.

The Excel Web Access component is a Web part used to display and enable interaction with Microsoft Office Excel workbooks in a browser.

The Excel Web Services component is a Web service hosted in Microsoft Office SharePoint Server 2007 that provides methods that a developer can use as an API to build custom applications based on Microsoft Office Excel workbooks.

Basic Component Distribution

Standard
Uncategorized

Configuring Trusted File Locations for Excel Services

The following information is a reference guide to configuring Trusted File Locations for Excel Calculation Services in Microsoft Office SharePoint Server 2007.  Your configuration may vary due to performance and hardware considerations; you should test configuration parameters for performance and scale before application to a production environment.


 


Open your Shared Services Administration site and select Trusted file locations under Excel Services Settings.

Click Add Trusted File Location to add a new Excel workbook file location considered trustworthy. Trusted file locations can include Windows SharePoint Services Web Applications, network file shares, and web folder addresses. Excel Services will deny requests to open files that are not stored in any trusted location.

Step 1 Location

Location settings specify the address of the trusted location, type and scope of trust.

1. Address field, specify the address of the trusted location. In this example we will use the sample Web Application – http://fabrikam.

2. Location Type field, select the storage type of the trusted location, this example uses the sample Web Application – http://fabrikam and will be configured as a Windows SharePoint Services storage type.

3. Children trusted under Trusted Children to trust child libraries or directories, in this example using the http://fabrikam Web Application all documents from Document Libraries on this server will be rendered as a result of selecting Children trusted.

4. Provide a Description of the purpose of this trusted location. This is not a requirement to configure Excel Services; however, is beneficial for the management and identification of configured trusted locations hosted by the Shared Services Provider.

Step 2 Session Management

Session Management settings determine the behavior of Excel Calculation Services sessions using workbooks from the specified trusted location. Configurable settings include session timeouts and request durations.

1. Session Timeout field specify the maximum time in seconds that an Excel Calculation Services session can remain open ad inactive. This value will be dependent on the performance of your environment and server hosting Excel Calculation Services. The default session timeout is 300 seconds. The session timeout is measured from the end of reach request and the session subsequently shut down at the timeout specified. For example an inactive session of 290 seconds will be reset once activity has been resumed or shutdown if it remains inactive after 300 seconds.

2. Short Session Timeout field specify the maximum time in seconds that an Excel Web Access session can remain open and inactive. This setting is similar to the Session Timeout setting, though applies exclusively to Web Access sessions and the duration is measured from the end of the initial Open request. The default session timeout is 75 seconds.

3. Maximum Request Duration field specify the maximum duration in seconds of a single request in a session. The default request duration is 300 seconds.

Step 3 Workbook Properties

Workbook property settings specify the behavior of workbooks from the specified trusted location in Excel Calculation Services sessions. Configurable settings include the maximum size of a workbook that can be opened and the maximum chart size in that can be opened by Excel Calculation Services.

1. Maximum Workbook Size field specify the maximum size of a workbook that can be opened by Excel Calculation Services. The default maximum size value is 10MB.  In our corporate deployments in MSIT we have configured this setting to 100MB on application servers running Windows Server 2003 x64 Edition with 8GB RAM (4 proc).

2. Maximum Chart Size field specify the maximum chart size that can be opened by Excel Calculation Services. Any positive integer can be specified as a value in this field; the default maximum size value is 1MB.  In our corporate deployments in MSIT we have configured this setting to 10MB on application servers running Windows Server 2003 x64 Edition with 8GB RAM (4 proc).


Step 4 Calculation Behavior

Calculation behavior settings specify the calculation modes in Excel Calculation Services for workbooks in the trusted location. For most medium-large farms; the default settings are appropriate.

1. Volatile Function Cache Lifetime field specify the value for the maximum time in seconds that a computed value for a volatile function is cached for automatic recalculations. The default maximum volatile function cache lifetime is 300 seconds.

2. Workbook Calculation Mode field specify the calculation mode of workbooks in Excel Calculation Services. Manual, Automatic and Automatic except data tables settings override the workbook settings. The default calculation mode value is File which retains the workbook settings. The default workbook calculation mode is File.

Step 5 External Data

External data settings specify how Excel Calculation Services should handle external data connections in workbooks from the trusted location.

1. Allow External Data field specify external data processing setting. To disable external data connections select the radio button labeled None; otherwise, select the radio button(s) labeled Trusted data connection libraries only or Trusted data connections libraries and embedded to allow connections embedded in workbooks from the trusted location. The default external data connection value is None.

2. Refresh warning enabled to display a warning before refreshing external data in workbooks from the trusted location. The default warn on refresh value is enabled.

3. Stopping open enabled to stop the open operation on a file from the trusted location when the file contains Refresh on Open data connections and cannot be refreshed when opened or the user does not have an Open right to the file. This setting can be used in conjunction with Warn on Refresh. The default stop when refresh on-open fails value is enabled.

4. Automatic refresh (periodic / on-open) field specify the value for the automatic refresh duration in seconds. This setting specifies the time in seconds the system can use external query results. A value should be specified here if external data connections are allowed. The automatic refresh value is 300.

5. Manual Refresh field specify the value for the manual refresh duration in seconds. This setting specifies the time in seconds the system can use external query results. A value should be specified here if external data connections are allowed. The manual refresh value is 300.

6. Maximum Concurrent Queries Per Session field specify the value for the number of external data queries that can execute concurrently in a single session. This value can be any positive integer and has a default value of 5 concurrent queries per session.

Step 6 User-Defined Functions

1. User-defined functions allowed to permit user-defined functions to be called from workbooks from this trusted location. The default user-defined functions value is disallowed.


Click OK to commit the configuration when complete.

Standard
Uncategorized

New Excel Services Articles and Code Samples

Two (2) new articles have been published centered on Excel Services programmability with accompanying sample solutions available in the Microsoft Download Center.  The first article demonstrates how to extend List and query tables on Excel Services and includes content detailing external workbook references, SQL write-back and user-defined functions.  The second article provides a more in-depth approach to developing UDF’s for Excel Services.


Article:


Extending the Excel Services Programmability Framework


Accompanying Solution:


SharePoint Server 2007 Sample: Extending Excel Services Programmability Framework Samples


 


Article:


Developing User-Defined Functions for Excel 2007 and Excel Services


Accompanying Solution:


SharePoint 2007 Sample: User-Defined Functions for Excel 2007 and Excel Services Sample Solution

Standard