Today’s author, Christian Stich, a Program Manager on the Excel Services team, discusses how to enable users to quickly and easily create workbooks using external data connections specified in server based data connection files.
Excel and Excel Services support importing external data, which can be specified using connections embedded in the workbook itself or in Office Data Connection (.odc) files. One of the advantages of using .odc files is that any updates to the external data connection properties only have to be done once in the .odc file itself – any workbook using the .odc file for its data connection will use the updated connection properties. Another advantage of .odc files is that the user can open an .odc file directly, which results in Excel creating a new workbook that already contains the external data connection.
Excel Services can only open .odc files that are located in trusted data connection libraries. For additional information please see White paper: Excel Services step-by-step guides and Plan external data connections for Excel Services
Creating an .odc file on SharePoint and making it easily accessible
.odc files are stored in trusted data connection libraries. It is desirable to provide users with an easy means of accessing these .odc files. This can be done by adding the data connection library to the navigation links. An alternative approach is the use of SharePoint’s “DataConnections” web part.
Let’s get started. We first open Excel and create the new data connection using the “Data Connection Wizard.” Please make sure to check the “Always attempt to use this file to refresh data” checkbox – this results in an external data connection that is defined in the .odc file. If the checkbox is not checked, then an embedded data connection would be created instead.
The next step is to upload the .odc file to SharePoint. We click on “Finish”, followed by a click on “Properties” and then in the “Definition” tab we click on “Export Connection File” in order to publish the .odc file to a trusted data connection library on the SharePoint server. The path to the connection file next to “Connection file” is updated once the connection file has been uploaded to the SharePoint server.
Note: We would require an additional step if the workbook had an existing embedded data connection that we would want to use. In this case we select the data connection, click on “Properties” and then in the “Definition” tab we click on “Export Connection File” in order to publish the .odc file to a trusted data connection library on the SharePoint server. Once the connection file has been uploaded to the SharePoint server the path to the connection file next to “Connection file” is changed from an empty path (since it was an embedded data connection) to a path pointing to the .odc file in the data connection library. Please note that the “Always use connection file” checkbox could not be checked prior to exporting the connection file. We are now able to check the “Always use connection file” checkbox. However, the .odc file on the server does not have that setting enabled yet. Therefore, we need to export the connection file one more time to SharePoint by clicking on “Export Connection File” and then “Save.”
The .odc file that specifies the external data connection is now stored in the trusted data connection library on SharePoint.
Next we edit a page on the SharePoint site. We click on “Add a Web Part”, then on “Advanced Web Part gallery and options”, and then select and add the “DataConnections” web part. We now have a web part on the page that ‘shows’ the .odc file.
Users viewing the page can simply click on the .odc file in the “DataConnections” web part. This results in the .odc file being opened in the Excel client.
The external data connection in our example points to the Adventure Works Analysis Services Cube example. Excel opens – the workbook already contains a PivotTable using the Adventure Works cube without any further work on our part. We enable external data and click on “Edit Workbook” and then proceed to customize the PivotTable using the PivotTable Field List displayed on the right. Once we have finished editing the workbook we publish it to a trusted file location on SharePoint and, if desired, add an “Excel Web Access” web part on a dashboard page that uses the Excel Workbook that we just uploaded.
The “DataConnections” web part can include multiple data connections, providing an easy way for users to locate trusted data connections on the SharePoint site and allowing them to rapidly build workbooks using those data connections. At the same time, updates to external data connections, such as migrating a cube or data base from one server to a different server, only require modifying the .odc files in the trusted data connection library. No changes to the individual workbooks are required.