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.

image

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

image

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

image

The data is as shown

image

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

image

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:

image

Finally, I also added a PivotChart as shown below:

image


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



 
  Feedback:

comments

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 http://blogs.msdn.com/access/archive/2009/10/21/net-developer-blogs-about-access-2010.aspx

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions