PowerPivot Example with SQL Server 2012

By: Daniel Calbimonte   |   Updated: 2012-12-24   |   Comments (1)   |   Related: > Microsoft Excel Integration

Problem

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.

Solution

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:

• Excel 2010
• PowerPivot
• SQL Server 2012
• .Net Framework 4.5
• The AdventureWorks Database

Learning PowerPivot

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

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

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.

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

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

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.

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

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

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.

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.

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

Next Steps

• PowerPivot is an extremely flexible tool and a really easy to learn. You can create reports from multiple sources with millions of rows in a very rapid manner
• For more information, refer to these links:
  • Microsoft Excel Integration Tips
  • Introduction to PowerPivot for Excel and SQL Server

About the author

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