In my earlier post, I have mentioned configuring excel
services Global settings and Trusted file location. In this post we can see trusted
data providers, trusted data connection libraries, user defined function
assemblies and data model settings.
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.
To Configure trusted data connection libraries for excel
services click on Trusted Data Connection Libraries in Manage Excel Service
Applications page. Trusted data connection libraries are the data connection
libraries for trust configuration in SharePoint 2013 excel services. All the
Data connections for excel services will stored in trusted data connection
library.
To create new trusted data connection in SharePoint 2013
excel, Click on Add Trusted Data Connection Library in Excel Service
Application Trusted data Connection Libraries page.
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.
To add the User defined assemblies for the excel service,
click on User Defined Function Assemblies link in Manage Excel Services
Application page. Click on Add User-Defined Function Assembly button on the
page to add new User-Defined Assembly.
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.
Share this
