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