PowerPivot import data from SQL Server 2012

In this step by step PowerPivot Tutorial I will show you how to start creating new PowerPivot model by first important data from SQL Server 2012. For the purpose of this tutorial I will use PowerPivot in Excel 2013.

Other Versions of Excel:

  • PowePivot Excel 2013 (v3)
    • This blog post has been written using PowerPivt in Excel 2013
  • PowerPivot Excel 2010 (v2) - SQL Server 2012
    • The examples we used should work in Excel 2010 with PowerPivot SQL Server 2012 (v2)
  • PowerPivot Excel 2010 (v1) - SQL Server 2008 R2
    • The xamples we used should work in Excel 2010 with PowerPivot SQL Server 2008 R2 (v1)

Who is it for?

  • People who would like to learn basics of creating PowerPivot Models

Prior knowledge. You should have PowerPivot installed and enabled for more information visit:

 

Let's start from opening Blank workbook and opening PowerPivot window by clicking PowerPivot Tab (avialable only if Add-in is enabled) and clicking manage button.

Open PowerPivot

 

Next let's start Import data from SQL Server wizard by select Home Tab, Clicking From Database and selecting From SQL Server

Import data from SQL Server

We get Table Import Wizard. In my case in server name I put . (dot) which is the same as localhost, go with Windows Authentication and select my database. I click Next to continue

Table Import Wizard

Next I choose how to import the data and I go with "Select from a list of tables and views" which is the most common option used.

Choose how to import the data

In next step I select tables and views I want to include in the PowerPivot mode by ticking the check box in first column.

I have also renamed FriendlyName to something more Friendly (You can do that in PowerPivot Window as well) and click Finish.

Important: Friendly Name is what users will see when they use PowerPivot model. So make sure you set it to something that is understandable by users.

 

Select tables and views

By Clicking Finish PowerPivot has started the import process and data is now stored in Excel in "PowerPivot" area.

Notice that I imported more than 1million rows in visits and my Excel file is just 34Mb! which is quite an impressive data compression.

Importing data into PowerPivot

After the import is complete we can see in PowerPivot Window that we have 5 new "sheets" with data from our SQL Server tables and views.

 

PowerPivot Model

 

Now that we have data it is time to combine it together by creating relationships.

You can visit our next tutorial and find out how to using PowerPivot create relationships

 

I hope this tutorial will help to import data into your PowerPivot model.

 

Take care

Emil
SHARE:

Name: Mike Hills
Comment:

Hi

Ive just discovered PowerPivot for Excel 2013 and your excellent giudes thank you.

I am blown away by the power (no pun inteneded) of this new tool.

Question -when i add a column to a view that has been imported to PowerPivot - is three a way to include that new column without deleting view and reimporting.
Date Posted: 04/02/2013 12:31:00 PM




Disclaimer: While every caution has been taken to provide our readers with most accurate information and honest analysis, please use your discretion before taking any decisions based on the information in this blog. Author will not compensate you in any way whatsoever if you ever happen to suffer a loss/inconvenience/damage because of/while making use of information in this blog.