November 23, 2009

Exploring New Features of SQL Server 2008 R2 with Excel 2010

SQL Server 2008 R2 November CTP is released and can be downloaded from here .

There is a lot of new functionality available with Business Intelligence with SQL Server 2008 R2. In this article, we will use Excel 2010 as a client for SQL Server Analysis Services to explore some new features.

To get started, we first need to install SQL server 2008 R2 and later Office 2010 to get the Gemini tab. ‘Gemini’ is the official name for SQL Server PowerPivot for Excel and SharePoint.

We will discuss creating pivot table with Excel 2010 using PowerPivot (working with SQL Server 2010 November CTP). After installing Office 2010 and PowerPivot, we get the following tab in Excel 2010.


I have used the sample database for SQL Server 2008 R2 November CTP available on codeplex

After clicking on PowerPivot window, select a SQL Server database and use database AdventureWorksDW2008R2.

From Table Import Wizard, select 3 tables namely DimDate, DimSalesTerritory and FactInternetSales as follows


You can even give them friendly names as shown. You will get following the Success screen after you click Finish.


The data is as shown


You can click on Pivot table and create a complete Pivot table.


Following table shows Countries in columns, months in rows and sum of Order quantity for calendar years 2007 and 2008.

I added a vertical slicer for SalesTerritoryGroup and the result is as shown:


Finally, I also added a PivotChart as shown below:


Did you like this post?
kick it on
subscribe via rss subscribe via e-mail
print this post follow me on twitter



2 Responses to "Exploring New Features of SQL Server 2008 R2 with Excel 2010"
  1. Mao said...
    November 27, 2009 at 8:56 PM

    nice post! Gouri Shoni do you have any idea if we can use MSAccess as a client for analysis services?

  2. Gouri Sohoni said...
    November 27, 2009 at 10:42 PM

    Hi Mao,

    Please refer to this link


Copyright © 2009-2016 All Rights Reserved for by Suprotim Agarwal | Terms and Conditions