Tutorial 2: How to establish a connection to SQL Server using Excel 2007-2010.

Most people know how to use excel, but usually the data is provided to them in a file. What if the data is not provided and it is only accessible by connecting to a SQL server instance? This is common in businesses where a data warehouse is set up, and business users are forced to analyze the data by connecting to either an OLAP cube (click for more info) or a table in SQL server. I am not going to talk about OLAP cubes today, so I am just going to focus on how to use Excel with a direct SQL Server source.

A few things to consider:

1. Make sure you have access to the SQL Server. Usually, Database Administrators will limit the access of the databases to a few key people. Typically, business users will have access only to the data warehouse database for deeper analysis, so make sure you communicate with the DBA to ensure you have rights.
2. While most data warehouses are designed with the business user in mind, sometimes table names and column names are not named in an easy to understand manner. Make sure you understand what the definition of every measure and dimension is in the database.
3. Excel always needs to be refreshed to see new data, so if you establish a connection to SQL Server and then save the file, that will be data at the time you initially created the file. If you need to see current data, then you will need to refresh.

So let's get started!

First, open Excel and click on the Data tab. Now click on "From Other Sources," and click on "From SQL Server." You should see a dialog box that looks like this:

This is always the standard connection box for establishing a connection to SQL. 

Now put in the information for the server name and the authentication, and if you do not know this information, please get with your Database Administrator to find this out.

Next, it will ask for what database and table that you wish to connect to. This information should be provided to you by the Database Administrator. Once you know, go ahead and make the appropriate selections and click next. For my example, I am connecting the DataWarehouse database and am accessing the rpt_PLRV2_ALLSites table.

Now, it will ask where you would like to save the connection file. I recommend storing it in the default location it brings up: C:\Users\richecker\Documents\My Data Sources.

Once saved, you will then be prompted with the following dialog box:

If you wish to perform analysis on aggregations of the data, I would recommend using a pivot table, but if you just need to export the data, the table option is all you will need. For purposes of this tutorial, I am going to use the Pivot table option

Select Pivot table, and now it will start transferring all of the data from the server to Excel. Depending on how large the table is, this could take some time. I usually recommend using this approach on small data tables.

Now you will be presented with the usual pivot table editor and now you can start massaging the data you best see fit.

I wanted the report by Product ID, Product Code ID, and dates of how many confirmed leads we had. Therefore, I made the selections and this produced the final product:

Note: you can do the same manipulation through excel as if it were just a regular excel document where all the data was located on a worksheet. The only difference is the data is not located on a worksheet; it is located on a SQL Server instance.

**WARNING**

This data only updates when you choose to refresh. If you want the latest data available when working with this sheet, you must go to the data tab and click the refresh button:

 

Please note that in Excel 2010, PowerPivot is available as a plugin for excel, and I would recommend using this plug in if you have Excel 2010. For more information on PowerPivot and how to get started, please check out this video below:

*Pictures provided by Richard Ecker, 2010.

*Video provided by YouTube user: sqlserver