An overview of PowerPivot for Excel 2010
Part two of a six-part series of blogs

PowerPivot is a superb addition to Excel: it allows you to create pivot tables based on multiple tables taken from a variety of data sources, and its DAX language lets you report any statistic you want. This overview summarises how to get started with PowerPivot, and what it does.

  1. Getting started with PowerPivot for Excel 2010
  2. Stage 1 - Importing the tables we need (this blog)
  3. Stage 2 - Creating a data model
  4. Stage 3 - Creating a Pivot Table
  5. Stage 4 - creating measures using DAX
  6. Stage 5 - KPIs in PowerPivot for Excel

Posted by Andy Brown on 31 January 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Stage 1 - Importing the tables we need

What we're trying to do is to turn two tables (one in Word, one in SQL Server) into a single pivot table field list:

Simple list of pivot table fields

What we're trying to achieve!

 

To do this we need to connect to the tables, create a relationship between them, rename some columns and hide others and create some calculated columns.

Connecting to our SQL Server table

The first thing we need to do is to link to our table called tblFilms, which I've assumed is in a SQL Server database called Movies.  First choose to link to SQL Server:

Link to SQL Server

Choose to get data from SQL Server.

 

You can now follow the numbered steps below to connect to a database:

Connecting to database

The numbered steps to follow are shown below.

Here are the numbered steps above:

  1. Give your connection a sensible name (it's usually likely that you'll use it to connect to other tables).
  2. Type in or choose the name of your server.
  3. Choose whether you're going to use your Windows logon as authentication (as here), or type in a SQL Server database name and password.
  4. Choose which database you want to connect to.
  5. Click to go to the next stage of the wizard.

You will then nearly always ask to select the tables you want to import:

Choose tables to import

You'll nearly always choose the first (default) option in this dialog box.

 

You can now choose which table or tables you want to import:

Choosing films table to import

You can tick the tables you want to import, and also rename them at this point.

At this point you would normally click on the Preview & Filter button to select the columns you want to import.  If you have millions of rows of data, you can save time, memory and disk space by importing only the rows - and columns - that you need to use.

You should now have a table giving the columns you need:

Table of films imported

We need the DirectorId field because we'll use this to link to the other table, imported from Word.

 

The next stage will be to tidy up the data so that the table and field names are sensible, and only the ones of interest for a pivot table builder are visible.  We'll do that in the next stage of this blog.

Importing our Word table

For the SQL Server table above, we've created a connection (you can refresh the data at any time), but for Word we'll just copy and paste it in.  First select the table of directors and copy it:

Word table of directors - copying

Select the table of data in Word and copy it to the clipboard.

 

Now paste it into PowerPivot:

PowerPivot paste icon

Click on this Paste icon to paste the data into PowerPivot.

 

How good is this?  You can now give your new table a name (I've called mine Director):

Pasting data into PowerPivot

PowerPivot shows a preview of the data.

 

You can now see the imported data:

Imported list of directors

The table of directors pasted into PowerPivot.

 

Linking the two tables with a relationship

You should now have the two tables in PowerPivot:

Tables in PowerPivot

The two tables imported into PowerPivot - there's nothing to say that one came from SQL Server and one from Word now.

 

To link them together, first switch to Diagram View:

Diagram View icon

If you can't see this, you may not have the latest version of PowerPivot installed.  At the time of writing this is called SQL Server 2012 PowerPivot Addin for Excel and is version number 11.0.1750.32.

 

You can now drag the common field (DirectorId) from one table to the other:

Relationship between two tables

Click and drag to create the relationship shown: each film's DirectorId column matches the director's Director id column value.

 

You've now imported all of your data, and should save your Excel workbook before continuing.  We'll now create a tidy data model.

This blog has 0 threads Add post