Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

PowerPivot Example with SQL Server 2012

MSSQLTips author Daniel Calbimonte By:   |   Read Comments (1)   |   Related Tips: More > 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:

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


Last Update: 12/24/2012


About the author
MSSQLTips author Daniel Calbimonte
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, April 25, 2013 - 8:11:20 PM - Grace Read The Tip

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,

Grace

 




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.