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 

PowerPivot for Microsoft® Excel X86 Executable (32 bit)
PowerPivot for Microsoft® Excel X64 Executable (64 bit)

After installing the Add-in you should see the extra Excel tab “PowerPivot”, in the screen dumps second tab on the right:

image

 

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

image

Home > From Database > From SQL Server

image

Enter servername and databasename (AdventureWorksDW2008R2)

image

Select from a list of tables and views to choose the data to import

image

Select fact table “FactInternetSales” > Select Related Tables

image

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.

image

During the import the PowerPivot add-in automatically detected relationships

Design > Manage Relationships

image

Add, edit or delete relationships 

image 

PivotTable > Chart and Table (Vertical)

image

Select “Existing Worksheet”

 

image

This will add a chart and a PowerPivot table to sheet1. It will also create a “data sheet“

Rename Excel sheet tabs

image

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”.

image

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…

image

Drag and drop the WeekNumberOfYear to the “Slicers Horizontal”

image

Result: you’re first PowerPivot chart!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.