Microsoft Office Tutorials and References
In Depth Information
Supported Data Objects
Excel Services supports the use of PivotTables or OLAP formulas for connecting to external data. This
version does not support any other data objects, including the following:
PivotTables based on data ranges from other workbooks
PivotTables that have server-based page fields
Links to other workbooks
Dynamic Data Exchange (DDE) links
To be able to open workbooks on the server, replace the listed features with equivalent supported features,
such as PivotTables, OLAP formulas, or equivalent UDF assemblies.
Chapter 4 provided information about supported and unsupported features in Excel Services.
Using UDFs to Query External Data
One way to query additional data sources that are not supported natively by Excel Services is to write your
own UDF. The UDF can take in parameters, perform the query, and return a value or a range of values.
When writing this type of UDF, design for security and performance. Excel Services does not provide any
tools to help with these tasks — you, as the writer of the UDF, must handle the external data
authentication and optimize the performance by other means, such as caching.
When allowing a UDF to run on the server, the administrator must verify that the UDF developer has
written it in such a way that it will not undermine the overall security and performance of the server,
especially with UDFs that connect to external data.
Implementing a server solution that accesses data from a source external to that solution in a secure way
is a challenging task. In today’s world of increased security risks, protecting the data is critical.
In this section, you learn about the security threats related to accessing external data. You also explore
the various solutions that Excel Services provides to support securing the data access. There is no silver
bullet to solve this complex problem, but you can combine a number of tools and techniques to achieve
the results that are best for your unique situation.