Skip to main content

Import SQL Server Data into a PowerPivot Workbook

Rate:  

About This Video:

Use the PowerPivot Add-in for Excel to import data from Microsoft SQL Server relational databases.

This video available with closed captions. To view closed captions, click CC on the video control bar.

Transcript

In this video we'll show you how to import data from a SQL Server relational database.

I'll start by importing data from a corporate SQL Server relational database.

The first step is to find and connect to that database. Then I'll choose which data to import.

First open the PowerPivot Window.

On the PowerPivot Ribbon, click From Database and select From SQL Server.

This launches the Table Import Wizard which guides me through setting up a connection to an external data source.

I enter the name of my server and select Use Windows Authentication.

My database is named Contoso.

I want to select from A list Of tables and views and when I click Next, the Wizard displays a list of all the source tables within my database.

I select six tables, DimChannel, DimDate, DimEntity, DimProduct, DimProductCategory, and FactSales.

I already know that I need only some of the data located in the DimProduct and DimProductCategory Tables. So I'll apply a filter to those two tables before I import them.

To do this, select the row for DimProductCategory and click Preview and Filter.

At the top of the ProductCategoryName Column, click the arrow and clear the Select All checkbox.

Scroll down the list and Select each category except MUSIC, MOVIES and AUDIO BOOKS, and GAMES and TOYS.

Click OK.

I select the row for DimProduct and click Preview and Filter.

When the Preview Selected Table window opens, all the columns in the DimProduct Table are displayed.

I clear the check boxes at the top of the columns for ClassID, ClassName and StyleID. Notice that the words Applied filters are now displayed in the DimProduct and DimProductCategory cells. If I click on one of these links, I can see a textual description of the filters that I just applied.

Now let's import the selected tables and columns. While importing the data, the Wizard displays how many rows have been fetched. When all the data has been imported, a message indicating success is displayed. Notice that I've imported nearly four million rows from my FactSales Table alone.

When I click Close, the PowerPivot Window displays. Each table has been added as a new tab in the PowerPivot Window.

I can keep this data current by doing a manual refresh. If the contents of the SQL Database change, refreshing keeps my PowerPivot data up-to-date.

The Wizard imports the table relationships along with the data. I can click on the Relationship icon for more information. Here I see that the FactSales DateKey Column is related to the DateKey Column in the DimDate Table.

I've added data from my corporate SQL Server database using the Table Import Wizard. During the install, I filtered my data to import only those tables and columns that I needed for my analysis.

The next video in the series show you how to import data from an access database.

Thank you for taking the time to view this video.

I hope you found this helpful.

 

Presented by: Michele Hart and Ed Price

 

Downloads

Video: WMV(Zip) | MP4 | WMV