Trusted data providers works as data providers for excel
services to access the data. A data provider is a protocol that combined with
database type for accessing data. By default excel services will not access
data without trusted data providers. Excel services will contain the data
providers as entries. We have to add additional data providers as per our
requirement.

By clicking on the trusted data providers link in Manage
Excel Service Application Page, we will navigate to Excel Services Application
Trusted Data Providers page. We can see a list of trusted data providers from excel
service. To add new data provider, click on “Add Trusted Data Provider” link on
the top of the page.

On the Excel Service Application Add Trusted Data Provider
page, Provider section, we need to add the provider ID in Provider ID text box.
In the Provider Type, we have to select the one of the following options, OLE
DB, ODBC, ODBC DSN. OLE DB option will access the data by using Object Linking
and Embedding. By selecting ODBC option, data will be accessed using Open
Database Connectivity. By selecting the ODBC DSN, data will be accessed using
Open Database Connectivity with Data Source Name.

We will navigate to Excel Service Application Add Trusted
Data Connection Library page. In the Location section, we need to enter the
document library URL that permit to access the excel service application. We
have to add the description of the trusted data connection in the description
text box.

In the Excel Service Application Add User-Defined Function
Assembly page, Assembly details section, enter the Assembly strong name or path
of the assembly contains the user-defined function. Depending on the Assembly
type you have entered, select the option Global Assembly Cache or File path. Check
Assembly enabled check box to allow the user-defined function assembly to be
located and used by Excel calculation service. Unchecking this option, disables
the option assembly will not be added for user defined function.

In SharePoint 2013 Excel Services, We can specify the SQL server instances for Analysis services to process the data models. This will provides the backend services for Excel service to load query and refresh the data model. To add the instance of the SSAS, we must have to install the Power Pivot for SharePoint mode. We can add new data model in SharePoint excel service in Excel Services Application Data Model Settings page. By clicking on the Add Server button, we will navigate to Excel Services Application Add Server page to add new data model.

In the Excel Services Application Add Server page, Server section, we have to enter the instance of SQL Server 2012 SP1 Analysis Services (SSAS) Server Name to add the new data model. Description is optional that helps to describe the data model setting.

We can deploy the excel services in three scenarios,
Enterprise, Medium or small department and custom. While configuring the
enterprise scenario we have to check that, don’t configure support for User
defined functions, do not enable workbooks to use embedded data connections or
direct access of external data. Need to limit the usage of data connection
libraries from external Data source access. For small department deployment, we
have to enable the trust for all the locations using the department members.
Need to enable the trust children option for all the trusted sites and
directories. If any chance to get problems from a file location, just restrict
the access. In custom deployment, enable the excel services to open the large
work books, we need to configure the long session time-out and large data
caches that there is change to delay in the connection. Here we don’t enable
the trusted child for trusted location and need to create a single trusted
location.

In my previous post, I have explained how to create excel
service. In this post we can see how to Manage the excel service application.
By navigating to manage excel service application page, we can see six links,
allows us to manage the service application.

By clicking on the Global Settings link, we will be
redirected in to Excel Service Application settings page. This includes
settings for Security, Load Balancing, External Data, Memory utilization and
Workbook cache.

Security settings will gives the options for excel service
application authentication, communication and service settings. File Access
method for excel services will have two options Impersonation and Process
account. Impersonation enables a thread to run in a security context that owns
the thread. We need to select the Impersonation, when workbooks are stored in
http locations. Process account option need to be used when excel services
opens the work books from HTTP sites. In the Connection Encryption section, we
need to use Internet Protocol Security or Secured Sockets Layers (SSL) to
encrypt the data transmission. There no need of connection encryption by
default. But if we choose the require connection encryption option, excel
service application server enable data transmission between the client computer
and front-end web servers.

Load Balancing settings allows us to maintain the sessions
across the excel calculation

services process.

Session Management settings allow us to set maximum number
of sessions allowed per user.

Memory Utilization settings will gives the options to
allocate the memory on the excel services.

By using Workbook cache settings, we can set the caching
workbook files on the disk and in memory.

Through external data settings, we can handle the external
data connections in excel calculation services.

“Trusted File Locations” in Excel services, trusts the
entire SharePoint farm. In SharePoint, by default we have a trusted file
location (http://) enables any file to be loaded from SharePoint server farm or
stand-alone deployment. By trusting the whole SharePoint farm enables the
easier setup for the administrators. For security purpose we can create new
trusted file locations. Trusted file locations may be SharePoint sites or http
sites that running the excel services that allows accessing workbooks.To add the new trusted location, Click on the “Trusted
Locations” link, we can see Excel Services Application Trusted File Location
page. Click on “Add Trusted File Location” link on the top of the page.

In the Address text box, we need to specify the location of
the excel documents that excel services trust. We have to specify the type of
the address in the location type like SharePoint or network file share or HTTP.
If we want to trust the all children for that location, check Children
Trusted in the Trust Children section.

In the Session Management settings section Session Timeout
field specifies the maximum time that excel calculation service open/active. We
can measure the session timeout that request time to reach start to end. The
default session tome out is 300 sec. Short session timeout specifies the
maximum time that excel web access service open/active. This is similar to session
time-out setting. Default value is 75 sec. Maximum request duration filed
specifies the maximum duration of a single request in a session.

Workbook property settings specify the behavior of the
workbooks from specified trusted location in excel calculation services. Here
Maximum Workbook size specifies the maximum size of the workbook that can be
opened from Excel calculation service. Default value is 10 MB. Maximum chart
size specifies the maximum size of the chart that can be opened by excel
calculation service. Default value is 1 MB.

External data services specifies how excel calculation
services should handle external data connections in workbooks from trusted
locations. Allow external data field specifies the external data processing. To
disable the external data connection, we have to select radio button None.
Trusted data connection libraries or trusted data connections libraries and
embedded will allow the connections embedded with trusted location workbooks.
Refresh warning enabled will display the warning message before refreshing the
external data in workbooks from trusted locations. Default is enabled. Stopping
open enabled to stop the open operation on a file from trusted location when
the file contains the refresh. Default value is enabled. Automatic refresh
(periodic/on-open) specifies automatic refresh duration period. Default value is
300. Manual Refresh field specifies the manual refresh duration. Default value
is 300. Maximum Concurrent Queries per Session specifies the value for the
number of eastern queries can execute in a session concurrently. Default value
is 5.

Follow us in facebook

About Me

Having good experience in SharePoint 2007/2010 and .net applications. Currently, working on SharePoint 2013 and training people on SharePoint. Microsoft certified technology specialist, Microsoft Certified Professional developer in SharePoint 2010.