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: