Accessing Data from Other Sources

Data Connection Libraries are SharePoint libraries that are similar to document libraries and are used as shared repositories for Office Data Connection (.odc) files. The .odc files were introduced in Excel Services in Microsoft Office XP as a means of storing the connection information needed by Excel to access back-end databases. Users create .odc files when they connect to an external data source, such as a SQL Server database. These files are stored, by default, in the My Data Sources folder on the user's local drive. This arrangement prevents them from being easily shared with other users.

By creating a Data Connection Library and populating it with the standard .odc files that your users are most likely to need access to, you can simplify the challenge of reusing these data connections. Your users can then browse to the library when creating an external connection and select from the available .odc files. The next time a change occurs on the network that affects the connection, such as a change in the name of a database view, the modification can be made one time in the .odc file on the server and all user connections will automatically receive the update the next time they refresh the connection.

You can create a Data Connection Library in any Web site that users will have permissions to access. To add an .odc file, upload a predefined file from the My Data Sources folder on your local drive, or direct other users to do so. To use the shared data connections in Excel 2007, click the Existing Connections button under the Data menu and click Browse For More to open the Browse dialog box. Type the address to the Data Connection Library on the server, and select the .odc file to use. You will then be offered the choice of displaying the data as a Table, Pivot Table Report, or Pivot Chart and Pivot Table Report. The Data Connection Library feature of SharePoint Server 2007 can be used independently of Excel Services. In this way, users can take advantage of shared connection settings without publishing their workbook to a SharePoint document library.

Data Connection Library

You can make it even easier for users to connect to a Data Connection Library by pushing the library listing to users through the Published Links To Office Client feature of SharePoint Server 2007. You can find this command under the User Profiles And My Sites section on the Shared Services Provider administration page. Add a new link and provide the URL to the Data Connection Library along with a description that users can recognize.

For security reasons, Excel Services does not automatically trust .odc files stored in all Data Connection Libraries. Excel Services allows workbooks to use only connections that are stored in Trusted Data Connection Libraries. To identify a Data Connection Library as trusted, follow these steps:

1. Open the Shared Services Provider page.

2. Under Excel Services Management, click Trusted Data Connection Libraries.

3. Click Add Trusted Data Connection Library.

4. Type in the URL address of the Data Connection Library you want to add and, optionally, add a description, as shown in Figure 20-13, and then click OK.

Figure 20-13: Adding a Trusted Data Connection Library

Important 

Excel Services does not support viewing spreadsheets in the Web browser if they have connections to standard database tables (referred to as query tables) or pivot tables. Only connections to SQL Analysis Services cubes are supported.

Trusted Data Providers

Excel Services restricts access to external database providers used in workbooks that it processes. Only database providers that it is explicitly configured to trust can be accessed from data connections in the workbook. If a connection attempts to access a nontrusted data provider, Excel Services will not load the workbook. To add a data provider as a Trusted Data Provider, follow these steps:

1. Open the Shared Services Provider page.

2. Under Excel Services Management, click Trusted Data Providers.

3. Click Add Trusted Data Provider.

4. Type in the identifier for the data provider you want to add.

5. Select one of the following provider types:

   q	OLE DB Use for most Microsoft and many third-party drivers that have been written to use the current OLE DB interface.
   q	ODBC Use for drivers that confirm to the Open Database Connectivity standard used by most database manufacturers.
   q	ODBC DSN Use to identify a reference to a Data Source Name on the local server that contains connection string information for an ODBC driver.

6. Click OK.

User-Defined Function Assembly

For complex server-side calculations or to call scalable middle-tier code libraries, Excel Services supports custom user-defined functions . Only user-defined functions written as managed code in the Microsoft .NET Framework version 2.0 assemblies are supported directly by Excel Services. This architecture provides Excel Services with the security and stability advantages of the .NET Framework common language runtime, and it allows the deployed code to be subject to .NET Framework code access security policies. Userdefined functions written for earlier versions of Excel Services and those written to run in the Excel 2007 client cannot be used by Excel Services unless they are called from within a .NET Framework 2.0 library, or wrapped, so that they are not exposed directly to SharePoint Server 2007.

For user-defined functions to be loaded and run by Excel Services, you have to register the assembly as a Trusted Assembly on the Excel Services server. When installing userdefined functions, you can place them either in a local directory, in a network share, or in the Microsoft .NET Framework global assembly cache (GAC). Any directory can be used for storing a local copy of the assembly as long as the Excel Services process account has permissions to access the folder. If a local path is used, the file must be placed in the same directory on every Excel Services server and any updates to the assemblies need to be copied to every server. Using a network share allows for one common code base to be used across all servers, but doing so might lead to errors if the network connection to the share fails. Registering the assembly in the GAC allows you to keep all versions of the assemblies in one place and facilitates applying code access security policies. Once the file is installed on the server, use the following steps to register it with Excel Services:

1. Open the Shared Services Provider page.

2. Under Excel Services Management, click User-Defined Function Assemblies.

3. Click Add User-Defined Function Assembly.

4. Under the Assembly field, enter the unique identifier for the UDF file.

   For a local file, type the full path-for example:

    D:\ExcelUDFs\CustomFunctions.dll or \\ExcelServer\ExcelUDFs\CustomFunctions.dll 

   For an assembly in the GAC, type the Strong Name-for example:

    CustomFunctions, Version=1.1.0.0, Culture=Neutral, PublicKeyToken=d7012336c6 ae8fd27 

5. Select the Assembly Location option that corresponds with the path-either GAC or Local File-and then click OK.