If you want to get started with PowerPivot, you will need to install Microsoft Office 2010 and a separate Excel Add-in: PowerPivot for Microsoft Excel
After installing the Add-in you should see the extra Excel tab “PowerPivot”, in the screen dumps second tab on the right:
Microsoft SQL Server 2008 R2 AdventureWorks sample databases
If you want to use the Microsoft SQL Server 2008 R2 AdventureWorks sample databases as source for you’re PowerPivot reports, you will need to install the sample database on you’re database server. The Microsoft SQL Server 2008 R2 AdventureWorks sample databases can be found here: http://msftdbprodsamples.codeplex.com/
Before installing the sample database, make sure you’re Microsoft SQL Server 2008 R2 instance has FILESTREAM enabled and is running the SQL Full-text Filter Daemon Launcher service. To enable both feature, follow the steps on: http://msftdbprodsamples.codeplex.com/wikipage?title=Database%20Prerequisites%20for%20SQL%20Server%202008R2
PowerPivot tab > PowerPivot Window
Home > From Database > From SQL Server
Enter servername and databasename (AdventureWorksDW2008R2)
Select from a list of tables and views to choose the data to import
Select fact table “FactInternetSales” > Select Related Tables
On my virtual machine the 80.000 rows where imported in 10 seconds.
You can set friendly names for tables to show in the user interface.
During the import the PowerPivot add-in automatically detected relationships
Design > Manage Relationships
Add, edit or delete relationships
PivotTable > Chart and Table (Vertical)
Select “Existing Worksheet”
This will add a chart and a PowerPivot table to sheet1. It will also create a “data sheet“
Rename Excel sheet tabs
Click on the chart or the pivottable to show the “Field List”, if the “Field List” does not show click PowerPivot tab and click “Field List”.
Drag and drop the EnglishProductname to the “Axis Fields”
Drag an drop the SalesAmount to the Values (default this will calculate the sum of the SalesAmount, but you can change this to Count, Min, Max or Average, by right clicking the Measure SalesAmount and choose Edit Measures…
Drag and drop the WeekNumberOfYear to the “Slicers Horizontal”
Result: you’re first PowerPivot chart!!!