Microsoft Office Tutorials and References
In Depth Information
In this chapter, you learned about the various external data features of Excel Services as well as their
security and performance implications. This section shows a few common cases of accessing external
data and the recommended configuration for each case.
The common cases described here are generic cases that might not fully correspond to your situation.
Adapt these configurations to your specific needs.
Line of Business Data
In this example, assume that your organization has one or more line of business (LOB) applications such
as enterprise resource planning (ERP) or customer relationship management (CRM). You want to build a
number of reports that can be accessed through your SharePoint portal.
To do this, you set up SSO in your SharePoint farm to map the credentials of the users to the account used
to connect to the LOB application. You might need to install the relevant drivers to the ECS machine and
define them as trusted data providers.
When authoring the reports, you define the authentication settings to be SSO. On the server side, you
create a trusted location to which these workbooks will be published. You set up the trusted location
with a value of DCL and an Allow External Data setting of DCL And Embedded. You should give write
permissions to this trusted location to only a few trusted authors. If you want to allow other users to
create their own reports, you can create additional trusted locations and allow the users to publish reports
in those libraries.
In addition, you can allow access to some of these reports on your extranet, for customers or partners.
You should allow only view item rights to the users outside of your firewall, so that they will not have
access to the data connection information or the calculation model of the workbooks.
Reporting on Restricted Data
In this example, assume that the central IT department of a large organization manages an Analysis
Services database that contains restricted data that can be accessed only by certain users. In addition,
various users will get different views on the data. For example, managers will get only the data for their
The IT organization wants to add a number of reports for those managers in the organization’s
SharePoint portal. To build the solution, you define Kerberos-constrained delegation between the ECS and
the Analysis Services machine.
When authoring the reports, you define the authentication settings as Windows Authentication. You
create a trusted location to which these workbooks will be published. This trusted location should have
a value of DCL, an Allow External Data setting of DCL And Embedded, and the Stop When Refresh
on Open Fails setting turned on. You should limit the write permission for this folder to a few trusted
authors, but you can give the read permission to a larger set of users, because the database will restrict
the data access.