We can connect different data sources using excel services
includes SharePoint list, access DB, Windows azure etc... All the data
connections in excel will support in excel services. We can refresh the data in
excel service reports and dashboards and also supports SQL server tables and
Analysis services cubes and OLE DB data providers. We can also save the
connections to excel services trusted data connection library. It will help us
to access the data sources easily without knowing server names and DB names.
Excel service workbooks uses in a trusted data library to easy access the
current information.
We can use Excel to create and share connections with
database. When uploading a data connection to excel services trusted data
connection library, data connections will be available to use data models,
reports and score cards. Depending on the data source, data will be refreshed easily in excel service workbooks.
To create a SQL server tabular data connection by using
excel, we have to follow the steps below,
In Excel Data tab, click on From Other Sources in Get
External Data group and we have to select SQL Server.
In Connect to Database Server page, Server name box, we have
to specify the SQL Server name to get the SQL Server data. Here Iam entering as
“.” to connect the local. We have to specify the Log on credentials in the
credentials section. To use Windows Authentication, we have to select Use
Windows Authentication or to choose SQL Server authentication Select SQL Server
Authentication and have to enter the credentials. Click on Next.
“In the Select the database that contains the data that you
want” list, we have to select the database that we want to use, In the Select Database
and table page, To create new connection uses single table, we have to select
Connect to specific table and we need to select the table to use. By selecting
Enable selection of multiple tables, we can select multiple tables.
On Save Data Connection and File and Finish page, we have to
enter File Name, Description, Friendly Name, and Search Keywords boxes. In the
Authentication settings, we can choose the type of authentication. Click on
finish.
On Import Data page, Click on Only Create Connection and
Click on OK.
To Upload Data connections to SharePoint,
Navigate to BI center site and Open Data Connections
Library.Click on New Item and click on Browse to choose the file.
If we save the data connections in Default location, in
default location, click on libraries, Documents and navigate to My Data
Sources.
Select the ODC file
that we wanted to upload and click on Open.
In the Add a document dialog box we
have to select Content Type as Office Data Connection File. Need to provide
details for Name, Title and Description and Keywords, click on Save.
New data
connection will be added as shown below.
