PowerPivot Example with SQL Server 2012

By:   |   Comments (1)   |   Related: > Microsoft Excel Integration


Sometimes we need to integrate different data sources to generate reports in SQL Server. We could use SSIS to integrate the data and use SSAS to generate reports. However, is there an easy way to generate some reports of data with millions of rows and integrating different data sources?  Check out this tip to learn more.


Yes - The answer is PowerPivot.  PowerPivot is an add-in for Microsoft Excel 2010 that allows you to import millions of rows of data from multiple data sources into a single Excel workbook.  With PowerPivot you can:

  • Create relationships between heterogeneous data
  • Create calculated columns and measures using formulas
  • PivotTables
  • PivotCharts

By building one or more of these items you can further analyze the data so that you can make appropriate business decisions.

But why Excel? Because everybody loves Excel and everybody knows how to use it. Learning the PowerPivot is then a straightforward task. You can import data from SQL Server, Oracle or other databases.  PowerPivot for Excel 2010 is used for data analysis, we can also transform large amounts of data into meaningful information really fast.  So let's dive in.

PowerPivot Requirements

To begin testing the PowerPivot we will need the following items:

Learning PowerPivot

Start by opening ‘Excel 2010'. A new PowerPivot Menu is added when you install PowerPivot as shown below:

PowerPivot integration in Excel

The steps to connect to the SQL Server database from 'Excel 2010' are the following:

1.- Click the Power Pivot Menu Option
2.- Click 'From Database' Icon
3.- Select the SQL Server instance
4.- Enter the Database name
5.- Press the 'Next' button

Table Import Wizard in PowerPivot

On the next screen, select the list of tables and views of data to import. You can select multiple tables or views or write a specific query.

You can select multiple tables or views or write a specific query in PowerPivot

In our example, select the table 'Sales.OrderDetail' table and click the 'Finish' button.

Select the sales.orderdetail table

Now you have the SQL Server data in Excel. It is similar to the Pivot Tables, but faster.

SQL Server data in Excel with PowerPivot

Build a PivotChart in Excel with PowerPivot

Below are the steps to generated a PivotChart in Excel with PowerPivot:

1.- Select the ‘Line Total' column in Excel.
2.- Click the ‘PivotTable' icon.
3.- Select the ‘Pivot Chart' option.

Build a PivotChart with PowerPivot

Select a 'New Worksheet' to display the chart and press OK.

Select a New Worksheet to display the chart in PowerPivot

You can choose which column to graph from the PowerPivot Field List as shown below.

Choose which column to graph in PowerPivot

Building a PivotTable in PowerPivot

Now let's add more tables in PowerPivot by following the steps from above with the Table Import Wizard.  Let's add a new table to Excel. In this example we are adding the Production.Product table.

Add more tables in PowerPivot

Now select the 'Product' table and the 'ProductID' column to create the relationship between SalesOrderDetail and Products tables.

1.- Select the column ProductID.
2.- Click the ‘PivotTable' icon.
3.- Select ‘PivotTable' from the menu.

Select the Product table and the ProductID column to build a relationship

Now we have the 'Product' and 'SalesOrderDetail' tables to create queries from both tables as shown below.

Now we have the Product and SalesOrderDetail tables in PowerPivot
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, April 25, 2013 - 8:11:20 PM - Grace Back To Top (23566)

Hi Danial,

   PowerPivot Example with SQL Server 2012 is nice article and helpfull, I have one doubt on PowerPivot refresh you may help me

I have created PowerPivot data model and In DATA tab connection properties I selected Refresh for every and given 15 mins

but data is not refreshing in PowerPivot data model, can you please let me know If I am missing anything to schedule data refresh for Power Pivot data model


Thank You,



get free sql tips
agree to terms