Microsoft Office Tutorials and References
In Depth Information
Chapter 5: External Data
Excel Services queries data from external data sources such as relational and OLAP databases.
This functionality is critical for a number of mainline scenarios such as reporting and business
intelligence. It is also one of the most difficult areas to configure properly.
The complexity is caused by the need to synchronize the settings across the workbooks, the Data
Connection Library (DCL), Excel Services, single sign-on (SSO), and the external data sources.
These entities are owned by different groups of people who might have different (and, in some
cases, even conflicting) goals:
The external data sources are, in many cases, managed by a different group, such as the
central IT department of the organization. Their goal is to keep these data sources
running. They might not trust a specific application such as a deployment of Excel Services
enough to give them special permissions to the data source.
The goal of the administrator of Office Server is to ensure that Excel Services has high
availability, is secure, and has high performance. To achieve that, the administrator might
oppose connecting to certain data sources or allowing some types of authentication.
Power users can author data connections and save them in a DCL for the benefit of other
users creating workbooks with external data. They will usually manage a number of
important connections to data sources, which might not satisfy the needs of all users.
The end users want to create workbooks that query data and work both in Excel and on
the server. They are usually not familiar with all the complexities of running queries to
external data sources in a server environment, and just expect their workbooks to work.
In this chapter, you learn about creating data connections on the client, publishing them to the
server, and how the server connects to external data. Then you learn about the security and
performance implications of data on the server. The chapter concludes by summarizing several
common usage patterns and server configurations for external data connectivity.