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.