Excel Data Import and PivotTables
Excel is one of the most widely used front ends to many different data sources,including SQL Server. Often, however, the connection from Excel to data in the database is made by end users,DBAs, or IT professionals using low-tech comma-separated value (CSV) file transfers generated by SQL Server's bulk copy program (Bcp), SQL Server 2000 DTS, or SQL Server 2005 SQL Server Integration Services (SSIS). In many cases, the process of getting database data into Excel can be made faster and more efficient by bypassing the SQL Server file export step and instead using Excel to connect directly to the data. In fact, Excel's database connection methods are very flexible. Two of the most common ways to connect SQL Server with Excel are through direct data imports from relational tables and by using PivotTables to perform data analysis.
To import data directly from a relational table, start Excel and open the spreadsheet into which you want to import the data. Click Data on the toolbar and select Import External Data, Import Data to display the Select Data Source dialog box. You can create a new data source by following the instructions I provided in the Word Mail Merge section, or you can select an existing data source from the list of sources.
After selecting a data source, click Open. The Import Data dialog box lets you select the place within the spreadsheet to insert the imported data; the default location is $A$1 (row 1 column 1). Click OK to copy the data from the Employees table in the Northwind database to the insertion point in the spreadsheet. By default, Excel also displays the External Data toolbar, letting you easily edit the query used to retrieve the data or requery SQL Server for the most recent data changes. Unlike the tables and projects I created in Access, which were linked to SQL Server, Excel creates only a snapshot of the data. There is no live link with SQL Server, and changes you make to the data in the Excel workbook are not made to the base SQL Server tables.
Excel PivotTables are interactive tables that provide a powerful data analysis tool you can use with both relational data and business intelligence (BI) data from SQL Server Analysis Services. PivotTables let you quickly move and compare data, making it easy to identify patterns, trends, and relationships. To connect Excel 2003 to Analysis Services 2000, you can use the SQL Server Analysis Services 8.0 OLE DB provider that's included with Office 2003. To connect to Analysis Services 2005, you need to have MSXML 6.0 and SQL Server 2005 Analysis Services 9.0 OLE DB Provider installed. You can download MSXML 6.0 and Analysis Services 9.0 OLE DB Provider from www.microsoft.com/downloads.
To create an Excel PivotTable report that uses SQL Server data, start Excel and click Data, PivotTable and PivotChart Report to start the PivotTable and PivotChart Wizard. Select External data source for the type of data you want to analyze, choose PivotTable for the type of report, then click Next. Click the Get Data button to display the Choose Data Source dialog box.
Click the Database tab, then select the sql2005-northwind-emp data source you created earlier and click OK. (Although this example uses the SQL Server relational database as a data source, you can also use the OLAP Cubes tab to connect the PivotTable to Analysis Services.) In the Query Wizard - Choose Columns dialog box, scroll through the list of tables and select the Order Details table under Available tables and columns, then click the uppermost arrow pointing to the right to add all the columns from the Order Details table into the Columns in your query box. Click Next to display the Query Wizard Filter Data dialog box. Don't add any filters, but just click Next, then click Next again in the subsequent window. In the Query Wizard - Finish dialog box, select Return Data to Microsoft Office Excel and click Finish to display the PivotTable builder you see in Figure 6.
To build a sample PivotTable, drag the Order ID to the Page Field section of the PivotTable builder. Next, drag the Unit Price and Quantity columns to the Drop Data Items Here section. Finally, drag the Product ID column to the Drop Row Fields Here section. Excel populates the PivotTable as you drag and drop columns into it. You can then use the drop-down list to select specific data values and move columns to different areas to see other possible data relationships. Figure 7 shows a finished PivotTable. In addition to Excel's basic PivotTable functionality, Microsoft provides an Excel addin you can use to build Excel reports based on Analysis Services data. (For more information about this add-in, see the sidebar“Excel Add-in for Analysis Services.”)
Inclusive, Time-Saving Data Access
Access to the information in your database isn't limited to programmers. You can use familiar applications such as Word, Access, and Excel to access database information and use it in a variety of ways. SQL Server's ability to integrate with these Office 2003 applications can eliminate the need for custom programming or the need to build cumbersome multistep flat-file downloads in order to access information from a database. Taking advantage of this capability lets you get to the data you need and can save you time as well.