"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

February 03, 2010

Power Pivot

Getting started with power pivot.
Installed office 2010 Beta
Download power pivot for Excel 2010
Reference - Link1
I am trying to implement SSRS Report I implemented in Earlier post in power pivot

SQL Server PowerPivot for Excel Features
  • Import data from Multiple sources, Directly connect to SQL Server run queries, Slide/Dice output as Graphs/Charts/Pivot tables
  • In Excel 2010 option refresh is manual currently for refreshing data
  • Consume data from Feeds/SSRS Reports (I need to check)
  • Row and column limitations of excel are more not 66K plus as I remember in 2007 versions. More details from link
 Step 1 - Under powerpivot select SQL Server

Step 2 - Provide below query
I am going to use below query

SET @FromDate = GETDATE()-100
EXEC SaleValue @FromDate,@ToDate
Step 3 - Next step is pivot the table data
Step 4 - In Pivot table -> Select 4 chart options

As you see chart1 is based on Name/Salevalue
Chart2 is based on Salevalue/City
Chart3 is based on SaleValue/Date

Step 5 - Insert few more records in DB

INSERT INTO Earnings (Name,SaleDate,SaleValue,City)

Step 6 - Under Power Pivot click Refresh and Refresh data, Also in chart you have refresh option when you right click.
PowerPivot/Excel/Sharepoint and SSRS – should they merge?
Good Power Pivot / Gemini Resources
Microsoft Virtual Lab: PowerPivot for Excel 2010 Introduction
The Great PowerPivot FAQ
PowerPivot datasheet
Introduction to time intelligent functions in PowerPivot
Introduction to Data Analysis Expressions (DAX) in Gemini
Academy Live: A Preview to PowerPivot Server Best Practices
10 things you should know when you use PowerPivot
Powerpivot Client Architecture
List of PowerPivot DAX functions with description
Code Plex Data Analysis Expressions (DAX) Sample Data

No comments: