Getting Started with the Table Analysis Tools (SQL Server Video)

My name is Jamie MacLennan and I’m a Principal Development Manager for Microsoft SQL Server and also the author of several SQL Server books.

The Table Analysis Tools are a simple and familiar way to access the power of Microsoft SQL Server Data Mining and generate friendly and actionable reports, whether you are a data mining expert or novice. Excel is not doing the mining – the data mining is performed on the server and the results are returned to your Excel worksheet.

To help you learn the tools, Microsoft provides a database and several pre-populated Excel worksheets. In order to complete these tutorials, you will also need:

• An installation of SQL Server 2008.

• A connection to a SQL Server 2008 Analysis Services (SSAS) Server

• Microsoft Excel

• The free Microsoft Excel Data Mining add-ins download.

A trial version of SQL Server 2008 Analysis Services (SSAS) and the free Excel add-ins download are available:

Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007

Microsoft SQL Server 2008

The first thing I'm going to do is open the sample Excel workbook.

1. On the Start menu, select Microsoft SQL Server 2008 DM Add-ins and click Sample Excel Data.

   For the purposes of following along with these tutorials, don’t open Excel directly. All examples in this tutorial are based on this sample workbook.

   Let’s familiarize ourselves with the Introduction worksheet. This worksheet describes the tabs and other worksheets included with your download.

2. Click Table Analysis Tools Sample and open the worksheet. It contains an entire set of data about a fictitious bike company named Adventure Works Cycles.

   This worksheet combines data about customers with sales history of purchases

   The last column, bike buyer, is an important one, and we’ll be using it often in these tutorials

   You will use this, and other sample AdventureWorks data, with the Excel add-ins tutorials.

3. Place your cursor in any table cell. Clicking inside the table changes your ribbon. Because you installed the Data Mining Add-ins, you now have a Table Tools menu heading with 2 options: Analyze and Design.

   In order to work with the data mining Table Analysis Tools, you must define your data as an Excel table. In the supplied worksheet, the data is already formatted as a table. When you begin working with your own data, you will need to format it as a table.

   Here is a worksheet I created that is not formatted as a table. Notice that Table Tools is not available. In order to use the Data Mining Table Analysis Tools, I:

   1. First, select cells

   2. Then click Format as Table

   3. Select a Style/colors

   4. Check My table has headers and click OK.

Notice that I now have the Table Tools menu heading and can begin working with the Data Mining add-ins for Excel.

Let’s take a closer look at the Table Analysis Tools. In your menu bar, select Table Tools and then Analyze. The Table Analysis Tools ribbon displays the data mining tools. Each of these will be covered in future tutorials.

Notice the Help button at the end of the ribbon. The Help button provides access to the documentation included with the add-ins as well as the Getting Started wizard and online tutorials.

Before you can start using these tools, you must connect to an Analysis Services server. The Connection button allows you to create and manage connections to an Analysis Services Server and database. This connection is required before you can run any of the Table Analysis Tools.

If you opened the sample data and completed the wizard you may already be connected to the DMAddinsDB. If so, you can skip ahead.

1. To set up a connection to Analysis Services, click on the “connection” button on the ribbon which brings up the Analysis Services connection dialog.

2. In the dialog, click the New button to create a new connection. Here you specify the name of the server to which you are connecting, and a database that will hold the models you create. You can also specify a friendly name for the connection that will be displayed in the ribbon when you are connected.

3. Type the server name DMAddinsDB.

4. Select Use Windows Authentication and click OK.

   The connection name and status display in the Connection ribbon.

Now that you can open the sample Excel data, connect to Analysis Services, and understand how to access the Table Analysis Tools, you are ready to start using the Data Mining add-ins for Excel. For additional help with the Table Analysis Tools, I recommend viewing the other Table Analysis Tool video tutorials and visiting the homepage on microsoft.com. Thank you for viewing this tutorial.