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.
Next let's start Import data from SQL Server wizard by select Home Tab, Clicking From Database and selecting 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
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.
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.
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.
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.
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.