KEMBAR78
Deploying and Managing PowerPivot for SharePoint | PPTX
Deploying and Managing
PowerPivot for SharePoint
An Analysis Services and SQLCAT presentation
Dave Wickert, Denny Lee
Microsoft
Session Code • Session Title
SQL Server Customer Advisory Team (SQLCAT)
• Works on the largest, most complex SQL projects worldwide
• MySpace - 4.4 million concurrent users at peak time, 8 billion friend
relationships, 34 billion e-mails, 1 PetaByte store, scale-out using SSB
and SOA http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000004532
• Bwin – Most popular European online gaming site – 30000 database
trans / second, motto: “Failure is not an option”; 100 TB total storage
http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000004138
http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000001470
• Korea Telecom - Largest telco in Korea serves 26 million customers;
3 TB Data Warehouse
http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000001993
• Drives product requirements back into SQL Server from our
customers and ISVs
• Shares deep technical content with SQL Server community
• SQLCAT.com & http://blogs.msdn.com/mssqlisv
Session Code • Session Title
SQL Server Design Win Program
• Target the most challenging and innovative SQL Server
applications
• 10+ TB DW, 3k/tran/s OLTP, Large 500GB+ Cubes, Competitive
migrations, Complex deployments, Server Consolidation (1000+)
• Invest in large scale, referenceable SQL Server projects
across the world
• Provide SQLCAT technical & project experience
• Conduct architecture and design reviews covering performance,
operation, scalability and availability
• Offer use of HW lab in Redmond with direct access to SQL Server
development team
• Work with Marketing Team Developing PR
Session Code • Session Title
Upgrade Reliability Assurance
Regular status updates within one month of completing each
playback test cycle
Complimentary testing for qualified customer against every new
version of SQL Server
Opportunity to request improvements directly from the product
group for potential upgrade issues
Preparing for a smooth transition to the next version of SQL Server ...
Two-way NDA and Participation Agreement
to ensure security and privacy
For more information, contact sqlura@microsoft.com
Session Code • Session Title
SQLCAT and SQL CSS Invite You…
ROOM 611
• To the SQL Server Clinic where the most experienced SQL Server experts in the
world will be waiting to talk with you.
• Bring your toughest Questions / Challenges to the experts who have seen it all
• Architect and Design your future applications with experts who have done it before
with some of the largest, most complex systems in the world
• Or just stop in to say hello!
Session Code • Session Title
Agenda
Focus is PowerPivot for SharePoint
• Implementations
• PowerPivot Infrastructure
• Capacity Planning
• Upload Configuration
• Troubleshooting
Implementations
How are we going to build this?
Session Code • Session Title
Implementations
Installation
Read the manuals!!!
• New end-to-end install guides will be on sqlcat.com shortly
There are a lot of steps and a lot components integrated together:
• PowerPivot System Service, AS Engine, SharePoint, Excel Services,
Reporting Services, Excel Client, Office Workspace, IIS, Windows, etc.)
Examples:
• To change password for SharePoint Farm takes seven separate steps in the
farm, services, and IIS.
• Multiple steps to handle larger file sizes
Quick Tips
• Requires domain accounts
• Upgrade is tricky! May want to build out new server vs. upgrading existing
Implementations
PowerPivot “New Server”
PowerPivot
“New Server”
Implementations
Move SQL databases out
SharePoint
Databases
SharePoint
Web/App Server
Implementations
Scale-out
SharePoint
Databases
SharePoint
Web/App Server
SharePoint
Web/App Server
SharePoint
Web/App Server
SharePoint
Web/App Server
LB
Implementations
Dedicated servers w/
high availability
SharePoint
Databases
SharePoint
Web Server
SharePoint
Web Server
SharePoint
App Server
SharePoint
App Server
SharePoint
App Server
LB
. . .
. . .
Implementations
Capacity planning (long term)
“New Server”
(1)
‘alias’ the SharePoint RDBMS physically moves the database,
but the SharePoint configuration remains intactSingle
Server
(2)
‘migrate’ the SharePoint RDBMS so that the SharePoint
configuration sees the database server in its true
location
Single
Server
(3)
Single
Server
Single
Server
Multiple
Servers
Scale-out the farm by adding NLB servers;
all servers providing all services(4)
Specialize the farm; add app servers which are tied
to various services (size, scale and configure)
WFE App
Server
(5)
App
Server
App
Server
http://bit.ly/cZOHe0
Session Code • Session Title
Implementations
Delegation, Claims, Active Directory
• Issue: Clicking on slicers on a PowerPivot workbook in SharePoint only
worked as a domain administrator
• Tried: The data connection uses Windows Authentication and user
credentials could not be delegated
• Solution: Occurs when AD environment upgraded from NT 4.0. Ensure users
are:
• Members of the “Pre-Windows 2000” NT group (i.e. have Pre-Windows 2000
Compatible Access”
• Part of the “Authenticated Users” group and have “Read Permissions”
• References
• Delegation, Claims, Active Directory…Oh My!…Aw Crap!
• The data connection uses Windows Authentication and user credentials could not
be delegated
• Testing the Claims To Windows Token Service for different identities
• Delegation, Claims, Active Directory….Again?! Frak!
PowerPivot Infrastructure
a peek inside
PowerPivot Infrastructure
PowerPivot for Excel
The database is IN the workbook
Excel
PowerPivot Add-In
MSOLAP
AMO ADOMD.NET
VertiPaq Engine
PowerPivot for Excel
Architecture
Excel Client
PivotTable MSOLAP
In-Proc
TCP
HTTP
VertiPaq
PowerPivot
Add-In
AMO
ADOMD.NET
A peek into the VertiPaq Internal Storage
Understanding why an Excel PowerPivot workbook is so large
Data Sources
PowerPivot for SharePoint
Server, Services, and Components
WFE
Excel Web Access
App
Excel Calculation
Services
Content
SQL
Excel Web Service
SharePoint Farm
MSOLAP
TCP
HTTP
Channel
PowerPivot Web
Service
Excel Calc Services
PowerPivot System
Service
Analysis Services in
VertiPaq Mode
PowerPivot Service
Application Proxy
Client
Excel Services
Application Proxy
Excel Services
PowerPivot Services
c2wts
Data Sources
PowerPivot for SharePoint
Rendering the PowerPivot Workbook
WFE
Excel Web Access
App
Excel Calculation
Services
Content
SQL
Excel Web Service
SharePoint Farm
MSOLAP
TCP
HTTP
Channel
PowerPivot Web
Service
Excel Calc Services
PowerPivot System
Service
Analysis Services in
VertiPaq Mode
PowerPivot Service
Application Proxy
Client
Excel Services
Application Proxy
c2wts
Data Sources
PowerPivot for SharePoint
Rendering the PowerPivot Workbook
WFE
Excel Web Access
App
Excel Calculation
Services
Content
SQL
Excel Web Service
SharePoint Farm
MSOLAP
TCP
HTTP
Channel
PowerPivot Web
Service
Excel Calc Services
PowerPivot System
Service
Analysis Services in
VertiPaq Mode
PowerPivot Service
Application Proxy
Client
Excel Services
Application Proxy
c2wts
c:Program Files...OLAPBackup
Data Sources
PowerPivot for SharePoint
PowerPivot does NOT require Kerberos
WFE
Excel Web Access
App
Excel Calculation
Services
Content
SQL
Excel Web Service
SharePoint Farm
MSOLAP
TCP
HTTP
Channel
PowerPivot Web
Service
Excel Calc Services
PowerPivot System
Service
Analysis Services in
VertiPaq Mode
PowerPivot Service
Application Proxy
Client
Excel Services
Application Proxy
c2wts
Data Sources
PowerPivot for SharePoint
Server Action
WFE
Excel Web Access
App
Excel Calculation
Services
Content
SQL
Excel Web Service
SharePoint Farm
MSOLAP
TCP
HTTP
Channel
PowerPivot Web
Service
Excel Calc Services
PowerPivot System
Service
Analysis Services in
VertiPaq Mode
PowerPivot Service
Application Proxy
Client
Excel Services
Application Proxy
c2wts
PowerPivot for SharePoint
Server Action > c2wts
Data Sources
PowerPivot for SharePoint
Server Action > c2wts
WFE
Excel Web Access
App
Excel Calculation
Services
Content
SQL
Excel Web Service
SharePoint Farm
MSOLAP
TCP
HTTP
Channel
PowerPivot Web
Service
Excel Calc Services
PowerPivot System
Service
Analysis Services in
VertiPaq Mode
PowerPivot Service
Application Proxy
Client
Excel Services
Application Proxy
c2wts
Data Sources
PowerPivot for SharePoint
Server Action
WFE
Excel Web Access
App
Excel Calculation
Services
Content
SQL
Excel Web Service
SharePoint Farm
MSOLAP
TCP
HTTP
Channel
PowerPivot Web
Service
Excel Calc Services
PowerPivot System
Service
Analysis Services in
VertiPaq Mode
PowerPivot Service
Application Proxy
Client
Excel Services
Application Proxy
c2wts
c:Program Files...OLAPBackup
ImageLoad
PowerPivot for SharePoint
Load Balancing
App1
Excel Calculation
Services
SharePoint Farm
MSOLAP
TCP
HTTP
Channel
PowerPivot System
Service
Analysis Services in
VertiPaq Mode
PowerPivot Service
Application Proxy
c2wts
App2
Excel Calculation
Services
MSOLAP
TCP
HTTP
Channel
PowerPivot System
Service
Analysis Services in
VertiPaq Mode
PowerPivot Service
Application Proxy
• By default, set to round-
robin health allocation
• Testing notes that BP to
set to health-based
allocation
• Uses more resources
than round-robin
• BUT, pro-actively evicts
unnecessary VertiPaq
dBs when under
memory pressure
Capacity Planning
A preview on how to plan for PowerPivot for
SharePoint
Session Code • Session Title
Capacity Planning
Provisioning
Metric Determination
Total # of Users • Ascertain maintenance costs for user-base
• Storage Requirements = Avg # of files x Avg Size of files x
Total # of Users
Total # of Concurrent Users Help determine number of SharePoint WFEs
Total # of PowerPivot Users Help determine number of SharePoint Application Servers
(hosting Excel Services, AS Engine, PowerPivot System
Service)
# of Files; Avg/Max Size of Files Necessary to determine storage requirements to host
SharePoint data (PowerPivot and non-PowerPivot).
Data Refresh Options • Security and driver issues – users will have access to
data sources that PowerPivot may not have
• Frequency and # of files dictate how much resources
used to process/open/store these files
Session Code • Session Title
Capacity Planning
e2e Perf Test Preview > Scenario
• We are currently running end-to-end capacity testing; will be released
as part of PowerPivot Best Practices Guide
• Test Scenario
• Starting w/ 25 workbooks; range of workbooks from 3MB-400MB
• Dell R905 Windows Server 2008R2 64-bit, 64GB, 16 cores, local 15K disks
RAID 10
• ECS/PowerPivot Same Server vs. Separate Servers
• >1.5M queries executed in our tests for each run
• Observations
• SSAS queries small % of browser RT (e.g. 200-500ms for 30s RT)
• A lot of queries from ECS to Analysis Services in VertiPaq mode (by way of
OLEDB and PSS), e.g. 196-500 calls for small workbooks (factor is number of
parts and slicers)
• Size of workbooks small so memory not an issue (e.g. 10GB to SSAS);
network, disk counters minimal
Session Code • Session Title
Capacity Planning
Impact of slicers, charts, tables, worksheets
~20
~530
Data Sources
Capacity Planning
e2e Perf Test Preview > Context
WFE
Excel Web Access
App
Excel Calculation
Services
Content
SQL
Excel Web Service
SharePoint Farm
MSOLAP
TCP
HTTP
Channel
PowerPivot Web
Service
Excel Calc Services
PowerPivot System
Service
Analysis Services in
VertiPaq Mode
PowerPivot Service
Application Proxy
Client
Excel Services
Application Proxy
c2wts
0:00
0:30
0:00.4
Capacity Planning
e2e Perf Test Preview > Server Load
0
2
4
6
8
10
12
0
10
20
30
40
50
60
70
10 20 30 40 50 60 70 80 90 100110120130140150160
ResponseTime(s)
CPU%
ECS/PowerPivot on Same Server
1. Avg ECS CPU
1. Avg AS CPU
1. Avg RT
0
2
4
6
8
10
12
0
10
20
30
40
50
60
70
10 20 30 40 50 60 70 80 90 100110120130140150160
ResponseTime(s)
CPU%
ECS/PowerPivot on Separate Server
2. Avg ECS
2. Avg AS CPU
2. Avg RT
Session Code • Session Title
Capacity Planning
e2e Perf Test Preview > Observations
• DRAFT – we are NOT done yet
• Will publish “PowerPivot Best Practices” and/or “PowerPivot
Capacity Planning” guide(s) once tests are completed on
sqlcat.com
• Observations … NOT final conclusions
• CPU hits inflection point, does not max out, RTs increase more so
after inflection (processor, memory, disk, network are NOT
bottlenecks)
• RT 2s vs. 10s (separate / same) much > linear perf
• Leaning towards optimal design to have separate ECS : PowerPivot
servers (2:1 ratio)
• Separate results also confirm that performance / scalability relate to
the HTTP POSTs
• Still determining what WCF / ASP.NET / IIS tweaks we can use to
better handle the traffic (thus able to push more through single box
and multibox
Capacity Planning
Content database > RBS
SharePoint
Excel Services
PowerPivot
Web Services
SharePoint dBs
SQL
RBS > Filestream
10K
http://bit.ly/9BW9Wx
Dedicated RBS
Capacity Planning
Content dB > RBS Server
SharePoint
Excel Services
PowerPivot
Web Services
SharePoint dBs
SQL RBS > Filestream
Capacity Planning
Content dB > RBS Server Concurrency
266
463
1119
186
261
1028
192
281
579
0
200
400
600
800
1000
1200
1 Client 2 Clients 5 Clients
Upload Duration (ms) by
Number of Concurrent Clients
Content DB
RBS on SharePoint DB Server
RBS on Dedicated Server
Upload Configuration
Uploading a PowerPivot workbook to
SharePoint takes on new meaning…
Session Code • Session Title
Upload Configuration
Quick Notes
SharePoint is optimized for download, hence our
concerns for PowerPivot scenarios were for upload
Important because IT (SharePoint) Administrators
are now dealing with larger files
• Default SharePoint Scenarios
• Max Size: 50Mb; Average Size: 20Mb
• PowerPivot SharePoint Scenarios
• Max Size: 2000Mb (configure Excel Services and SharePoint);
Average Size: ~50Mb
• Install instructions provide how to configure this
Upload Configuration
High Latency / Low Bandwidth
• Saving from Excel uses
Cobalt protocol
• Saves file asynchronously,
i.e. user can continue to
edit file while file is
uploaded
• File is uploaded in 4K
chunks
• Slower than WebDAV (next
page) but more suitable for
high latency / low
bandwidth scenarios
Session Code • Session Title
Upload Configuration
Using WAN Accelerators
0
100
200
300
400
500
600
700
800
900
1000
1 2 3
ExecutionTimeinSeconds
Run
Document Upload Execution Time
0
500
1000
1500
2000
2500
1 2 3 4 5 6 7
ExecutionTimeinSeconds
Run
Document Upload Execution Time
1.6 GB
1.8 GB
2 GB
Upload Configuration
Low Latency / High Bandwidth
• Saving from SharePoint UI
ultimately uses WebDAV
• Saves file synchronously but
does it pretty fast
• Think IIS HTTP posts –
optimized for corporate
environments
Troubleshooting
Diagnose
Late breaking bugs!
Troubleshooting
Reading ULS Logs
%commonprogramfiles%Microsoft SharedWeb Server Extensions14LOGS
Troubleshooting
SQL Profiler
Troubleshooting
Resources
• SQL Server PowerPivot for SharePoint Newsgroup
• Personal / MVP blogs like:
• powerpivotgeek.com
• dennyglee.com
• powerpivotpro.com
• powerpivot-info.com
• powerpivotblog.nl
• Twitter > #PowerPivot
• Professional Microsoft PowerPivot for Excel and
SharePoint > Chapter 9*
Troubleshooting
Late breaking bugs . . .
Client: Never edit database and rely on the Excel dirty
workbook check – always manually SAVE, then exit
Troubleshooting
Late breaking bugs . . .
Server: Doing a SQL Server 2008 R2 install prior to installing
PowerPivot for SharePoint
Troubleshooting
Late breaking bugs . . .
Issue: If running on a fast multi-core server (e.g. we’ve seen
this on a dual 6-way server with 96GB memory)
A “New Server” installation may fail to configure SharePoint
Secure Store. No error is returned; it just isn’t there . . .
Solution: Follow the “Existing farm” post-install sets to
configure a Secure Store service application, generate a master
key and create any desired accounts.
Troubleshooting
Late breaking bugs . . .
Issue: Snapshots fail to be taken (red “X”) in lower right-hand corner.
Many issues appear to be in play that can cause this problem:
• Make sure Excel Services is working. Without it, you don’t get
snapshots.
• You may have used the Blank site template. It has the PowerPivot
feature disabled – make sure the site collection feature is turned
on
• The setting for how to view an item may have been changed from
“In-Browser” to “in-application”. Check the document library
advanced settings.
• If all else fails, login interactively with the web application’s app
pool service account – can you view the content in Excel Services
using that account?
Troubleshooting
Late breaking bugs . . .
Issue: Databases are not being deleted from your local AS
instance, ULS is reporting “Type mismatch” failures.
Fixed in CU4, but you can cause it by-hand if you delete
databases using SSMS
Workaround:
• Go ahead and use delete if you wish – most databases will
delete OK
• If you are unable to delete (and you get the “Type mismatch”
error), then issue a detach instead and clean up the data
folder by-hand
Troubleshooting
Late breaking bugs . . .
Where we need your help to get a repro:
• If you do an installation and you find that the
PowerPivot timer jobs are missing
• If you are trying to modify a SP master page to
customize a Gallery, you may find that your changes
are not being applied.
If you run into any of these, drop me a line directly
(dwickert@microsoft.com)
Session Code • Session Title
Complete the Evaluation Form to Win!
Win a Dell Mini Netbook – every day – just for handing in
your completed form. Each session evaluation form
represents a chance to win.
Pick up your evaluation form:
• In each presentation room
• At the PASS Booth near registration
Drop off your completed form:
• Near the exit of each presentation room
• At the PASS Booth near registration
Sponsored by Dell
Thank you
for attending this session and the
2010 PASS Summit in Seattle

Deploying and Managing PowerPivot for SharePoint

  • 1.
    Deploying and Managing PowerPivotfor SharePoint An Analysis Services and SQLCAT presentation Dave Wickert, Denny Lee Microsoft
  • 2.
    Session Code •Session Title SQL Server Customer Advisory Team (SQLCAT) • Works on the largest, most complex SQL projects worldwide • MySpace - 4.4 million concurrent users at peak time, 8 billion friend relationships, 34 billion e-mails, 1 PetaByte store, scale-out using SSB and SOA http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000004532 • Bwin – Most popular European online gaming site – 30000 database trans / second, motto: “Failure is not an option”; 100 TB total storage http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000004138 http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000001470 • Korea Telecom - Largest telco in Korea serves 26 million customers; 3 TB Data Warehouse http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000001993 • Drives product requirements back into SQL Server from our customers and ISVs • Shares deep technical content with SQL Server community • SQLCAT.com & http://blogs.msdn.com/mssqlisv
  • 3.
    Session Code •Session Title SQL Server Design Win Program • Target the most challenging and innovative SQL Server applications • 10+ TB DW, 3k/tran/s OLTP, Large 500GB+ Cubes, Competitive migrations, Complex deployments, Server Consolidation (1000+) • Invest in large scale, referenceable SQL Server projects across the world • Provide SQLCAT technical & project experience • Conduct architecture and design reviews covering performance, operation, scalability and availability • Offer use of HW lab in Redmond with direct access to SQL Server development team • Work with Marketing Team Developing PR
  • 4.
    Session Code •Session Title Upgrade Reliability Assurance Regular status updates within one month of completing each playback test cycle Complimentary testing for qualified customer against every new version of SQL Server Opportunity to request improvements directly from the product group for potential upgrade issues Preparing for a smooth transition to the next version of SQL Server ... Two-way NDA and Participation Agreement to ensure security and privacy For more information, contact sqlura@microsoft.com
  • 5.
    Session Code •Session Title SQLCAT and SQL CSS Invite You… ROOM 611 • To the SQL Server Clinic where the most experienced SQL Server experts in the world will be waiting to talk with you. • Bring your toughest Questions / Challenges to the experts who have seen it all • Architect and Design your future applications with experts who have done it before with some of the largest, most complex systems in the world • Or just stop in to say hello!
  • 6.
    Session Code •Session Title Agenda Focus is PowerPivot for SharePoint • Implementations • PowerPivot Infrastructure • Capacity Planning • Upload Configuration • Troubleshooting
  • 7.
    Implementations How are wegoing to build this?
  • 8.
    Session Code •Session Title Implementations Installation Read the manuals!!! • New end-to-end install guides will be on sqlcat.com shortly There are a lot of steps and a lot components integrated together: • PowerPivot System Service, AS Engine, SharePoint, Excel Services, Reporting Services, Excel Client, Office Workspace, IIS, Windows, etc.) Examples: • To change password for SharePoint Farm takes seven separate steps in the farm, services, and IIS. • Multiple steps to handle larger file sizes Quick Tips • Requires domain accounts • Upgrade is tricky! May want to build out new server vs. upgrading existing
  • 9.
  • 10.
    Implementations Move SQL databasesout SharePoint Databases SharePoint Web/App Server
  • 11.
  • 12.
    Implementations Dedicated servers w/ highavailability SharePoint Databases SharePoint Web Server SharePoint Web Server SharePoint App Server SharePoint App Server SharePoint App Server LB . . . . . .
  • 13.
    Implementations Capacity planning (longterm) “New Server” (1) ‘alias’ the SharePoint RDBMS physically moves the database, but the SharePoint configuration remains intactSingle Server (2) ‘migrate’ the SharePoint RDBMS so that the SharePoint configuration sees the database server in its true location Single Server (3) Single Server Single Server Multiple Servers Scale-out the farm by adding NLB servers; all servers providing all services(4) Specialize the farm; add app servers which are tied to various services (size, scale and configure) WFE App Server (5) App Server App Server http://bit.ly/cZOHe0
  • 14.
    Session Code •Session Title Implementations Delegation, Claims, Active Directory • Issue: Clicking on slicers on a PowerPivot workbook in SharePoint only worked as a domain administrator • Tried: The data connection uses Windows Authentication and user credentials could not be delegated • Solution: Occurs when AD environment upgraded from NT 4.0. Ensure users are: • Members of the “Pre-Windows 2000” NT group (i.e. have Pre-Windows 2000 Compatible Access” • Part of the “Authenticated Users” group and have “Read Permissions” • References • Delegation, Claims, Active Directory…Oh My!…Aw Crap! • The data connection uses Windows Authentication and user credentials could not be delegated • Testing the Claims To Windows Token Service for different identities • Delegation, Claims, Active Directory….Again?! Frak!
  • 15.
  • 16.
  • 17.
    PowerPivot for Excel Thedatabase is IN the workbook Excel PowerPivot Add-In MSOLAP AMO ADOMD.NET VertiPaq Engine
  • 18.
    PowerPivot for Excel Architecture ExcelClient PivotTable MSOLAP In-Proc TCP HTTP VertiPaq PowerPivot Add-In AMO ADOMD.NET
  • 19.
    A peek intothe VertiPaq Internal Storage Understanding why an Excel PowerPivot workbook is so large
  • 20.
    Data Sources PowerPivot forSharePoint Server, Services, and Components WFE Excel Web Access App Excel Calculation Services Content SQL Excel Web Service SharePoint Farm MSOLAP TCP HTTP Channel PowerPivot Web Service Excel Calc Services PowerPivot System Service Analysis Services in VertiPaq Mode PowerPivot Service Application Proxy Client Excel Services Application Proxy Excel Services PowerPivot Services c2wts
  • 21.
    Data Sources PowerPivot forSharePoint Rendering the PowerPivot Workbook WFE Excel Web Access App Excel Calculation Services Content SQL Excel Web Service SharePoint Farm MSOLAP TCP HTTP Channel PowerPivot Web Service Excel Calc Services PowerPivot System Service Analysis Services in VertiPaq Mode PowerPivot Service Application Proxy Client Excel Services Application Proxy c2wts
  • 22.
    Data Sources PowerPivot forSharePoint Rendering the PowerPivot Workbook WFE Excel Web Access App Excel Calculation Services Content SQL Excel Web Service SharePoint Farm MSOLAP TCP HTTP Channel PowerPivot Web Service Excel Calc Services PowerPivot System Service Analysis Services in VertiPaq Mode PowerPivot Service Application Proxy Client Excel Services Application Proxy c2wts c:Program Files...OLAPBackup
  • 23.
    Data Sources PowerPivot forSharePoint PowerPivot does NOT require Kerberos WFE Excel Web Access App Excel Calculation Services Content SQL Excel Web Service SharePoint Farm MSOLAP TCP HTTP Channel PowerPivot Web Service Excel Calc Services PowerPivot System Service Analysis Services in VertiPaq Mode PowerPivot Service Application Proxy Client Excel Services Application Proxy c2wts
  • 24.
    Data Sources PowerPivot forSharePoint Server Action WFE Excel Web Access App Excel Calculation Services Content SQL Excel Web Service SharePoint Farm MSOLAP TCP HTTP Channel PowerPivot Web Service Excel Calc Services PowerPivot System Service Analysis Services in VertiPaq Mode PowerPivot Service Application Proxy Client Excel Services Application Proxy c2wts
  • 25.
  • 26.
    Data Sources PowerPivot forSharePoint Server Action > c2wts WFE Excel Web Access App Excel Calculation Services Content SQL Excel Web Service SharePoint Farm MSOLAP TCP HTTP Channel PowerPivot Web Service Excel Calc Services PowerPivot System Service Analysis Services in VertiPaq Mode PowerPivot Service Application Proxy Client Excel Services Application Proxy c2wts
  • 27.
    Data Sources PowerPivot forSharePoint Server Action WFE Excel Web Access App Excel Calculation Services Content SQL Excel Web Service SharePoint Farm MSOLAP TCP HTTP Channel PowerPivot Web Service Excel Calc Services PowerPivot System Service Analysis Services in VertiPaq Mode PowerPivot Service Application Proxy Client Excel Services Application Proxy c2wts c:Program Files...OLAPBackup ImageLoad
  • 28.
    PowerPivot for SharePoint LoadBalancing App1 Excel Calculation Services SharePoint Farm MSOLAP TCP HTTP Channel PowerPivot System Service Analysis Services in VertiPaq Mode PowerPivot Service Application Proxy c2wts App2 Excel Calculation Services MSOLAP TCP HTTP Channel PowerPivot System Service Analysis Services in VertiPaq Mode PowerPivot Service Application Proxy • By default, set to round- robin health allocation • Testing notes that BP to set to health-based allocation • Uses more resources than round-robin • BUT, pro-actively evicts unnecessary VertiPaq dBs when under memory pressure
  • 29.
    Capacity Planning A previewon how to plan for PowerPivot for SharePoint
  • 30.
    Session Code •Session Title Capacity Planning Provisioning Metric Determination Total # of Users • Ascertain maintenance costs for user-base • Storage Requirements = Avg # of files x Avg Size of files x Total # of Users Total # of Concurrent Users Help determine number of SharePoint WFEs Total # of PowerPivot Users Help determine number of SharePoint Application Servers (hosting Excel Services, AS Engine, PowerPivot System Service) # of Files; Avg/Max Size of Files Necessary to determine storage requirements to host SharePoint data (PowerPivot and non-PowerPivot). Data Refresh Options • Security and driver issues – users will have access to data sources that PowerPivot may not have • Frequency and # of files dictate how much resources used to process/open/store these files
  • 31.
    Session Code •Session Title Capacity Planning e2e Perf Test Preview > Scenario • We are currently running end-to-end capacity testing; will be released as part of PowerPivot Best Practices Guide • Test Scenario • Starting w/ 25 workbooks; range of workbooks from 3MB-400MB • Dell R905 Windows Server 2008R2 64-bit, 64GB, 16 cores, local 15K disks RAID 10 • ECS/PowerPivot Same Server vs. Separate Servers • >1.5M queries executed in our tests for each run • Observations • SSAS queries small % of browser RT (e.g. 200-500ms for 30s RT) • A lot of queries from ECS to Analysis Services in VertiPaq mode (by way of OLEDB and PSS), e.g. 196-500 calls for small workbooks (factor is number of parts and slicers) • Size of workbooks small so memory not an issue (e.g. 10GB to SSAS); network, disk counters minimal
  • 32.
    Session Code •Session Title Capacity Planning Impact of slicers, charts, tables, worksheets ~20 ~530
  • 33.
    Data Sources Capacity Planning e2ePerf Test Preview > Context WFE Excel Web Access App Excel Calculation Services Content SQL Excel Web Service SharePoint Farm MSOLAP TCP HTTP Channel PowerPivot Web Service Excel Calc Services PowerPivot System Service Analysis Services in VertiPaq Mode PowerPivot Service Application Proxy Client Excel Services Application Proxy c2wts 0:00 0:30 0:00.4
  • 34.
    Capacity Planning e2e PerfTest Preview > Server Load 0 2 4 6 8 10 12 0 10 20 30 40 50 60 70 10 20 30 40 50 60 70 80 90 100110120130140150160 ResponseTime(s) CPU% ECS/PowerPivot on Same Server 1. Avg ECS CPU 1. Avg AS CPU 1. Avg RT 0 2 4 6 8 10 12 0 10 20 30 40 50 60 70 10 20 30 40 50 60 70 80 90 100110120130140150160 ResponseTime(s) CPU% ECS/PowerPivot on Separate Server 2. Avg ECS 2. Avg AS CPU 2. Avg RT
  • 35.
    Session Code •Session Title Capacity Planning e2e Perf Test Preview > Observations • DRAFT – we are NOT done yet • Will publish “PowerPivot Best Practices” and/or “PowerPivot Capacity Planning” guide(s) once tests are completed on sqlcat.com • Observations … NOT final conclusions • CPU hits inflection point, does not max out, RTs increase more so after inflection (processor, memory, disk, network are NOT bottlenecks) • RT 2s vs. 10s (separate / same) much > linear perf • Leaning towards optimal design to have separate ECS : PowerPivot servers (2:1 ratio) • Separate results also confirm that performance / scalability relate to the HTTP POSTs • Still determining what WCF / ASP.NET / IIS tweaks we can use to better handle the traffic (thus able to push more through single box and multibox
  • 36.
    Capacity Planning Content database> RBS SharePoint Excel Services PowerPivot Web Services SharePoint dBs SQL RBS > Filestream 10K http://bit.ly/9BW9Wx
  • 37.
    Dedicated RBS Capacity Planning ContentdB > RBS Server SharePoint Excel Services PowerPivot Web Services SharePoint dBs SQL RBS > Filestream
  • 38.
    Capacity Planning Content dB> RBS Server Concurrency 266 463 1119 186 261 1028 192 281 579 0 200 400 600 800 1000 1200 1 Client 2 Clients 5 Clients Upload Duration (ms) by Number of Concurrent Clients Content DB RBS on SharePoint DB Server RBS on Dedicated Server
  • 39.
    Upload Configuration Uploading aPowerPivot workbook to SharePoint takes on new meaning…
  • 40.
    Session Code •Session Title Upload Configuration Quick Notes SharePoint is optimized for download, hence our concerns for PowerPivot scenarios were for upload Important because IT (SharePoint) Administrators are now dealing with larger files • Default SharePoint Scenarios • Max Size: 50Mb; Average Size: 20Mb • PowerPivot SharePoint Scenarios • Max Size: 2000Mb (configure Excel Services and SharePoint); Average Size: ~50Mb • Install instructions provide how to configure this
  • 41.
    Upload Configuration High Latency/ Low Bandwidth • Saving from Excel uses Cobalt protocol • Saves file asynchronously, i.e. user can continue to edit file while file is uploaded • File is uploaded in 4K chunks • Slower than WebDAV (next page) but more suitable for high latency / low bandwidth scenarios
  • 42.
    Session Code •Session Title Upload Configuration Using WAN Accelerators 0 100 200 300 400 500 600 700 800 900 1000 1 2 3 ExecutionTimeinSeconds Run Document Upload Execution Time 0 500 1000 1500 2000 2500 1 2 3 4 5 6 7 ExecutionTimeinSeconds Run Document Upload Execution Time 1.6 GB 1.8 GB 2 GB
  • 43.
    Upload Configuration Low Latency/ High Bandwidth • Saving from SharePoint UI ultimately uses WebDAV • Saves file synchronously but does it pretty fast • Think IIS HTTP posts – optimized for corporate environments
  • 44.
  • 45.
  • 46.
  • 47.
    Troubleshooting Resources • SQL ServerPowerPivot for SharePoint Newsgroup • Personal / MVP blogs like: • powerpivotgeek.com • dennyglee.com • powerpivotpro.com • powerpivot-info.com • powerpivotblog.nl • Twitter > #PowerPivot • Professional Microsoft PowerPivot for Excel and SharePoint > Chapter 9*
  • 48.
    Troubleshooting Late breaking bugs. . . Client: Never edit database and rely on the Excel dirty workbook check – always manually SAVE, then exit
  • 49.
    Troubleshooting Late breaking bugs. . . Server: Doing a SQL Server 2008 R2 install prior to installing PowerPivot for SharePoint
  • 50.
    Troubleshooting Late breaking bugs. . . Issue: If running on a fast multi-core server (e.g. we’ve seen this on a dual 6-way server with 96GB memory) A “New Server” installation may fail to configure SharePoint Secure Store. No error is returned; it just isn’t there . . . Solution: Follow the “Existing farm” post-install sets to configure a Secure Store service application, generate a master key and create any desired accounts.
  • 51.
    Troubleshooting Late breaking bugs. . . Issue: Snapshots fail to be taken (red “X”) in lower right-hand corner. Many issues appear to be in play that can cause this problem: • Make sure Excel Services is working. Without it, you don’t get snapshots. • You may have used the Blank site template. It has the PowerPivot feature disabled – make sure the site collection feature is turned on • The setting for how to view an item may have been changed from “In-Browser” to “in-application”. Check the document library advanced settings. • If all else fails, login interactively with the web application’s app pool service account – can you view the content in Excel Services using that account?
  • 52.
    Troubleshooting Late breaking bugs. . . Issue: Databases are not being deleted from your local AS instance, ULS is reporting “Type mismatch” failures. Fixed in CU4, but you can cause it by-hand if you delete databases using SSMS Workaround: • Go ahead and use delete if you wish – most databases will delete OK • If you are unable to delete (and you get the “Type mismatch” error), then issue a detach instead and clean up the data folder by-hand
  • 53.
    Troubleshooting Late breaking bugs. . . Where we need your help to get a repro: • If you do an installation and you find that the PowerPivot timer jobs are missing • If you are trying to modify a SP master page to customize a Gallery, you may find that your changes are not being applied. If you run into any of these, drop me a line directly (dwickert@microsoft.com)
  • 54.
    Session Code •Session Title Complete the Evaluation Form to Win! Win a Dell Mini Netbook – every day – just for handing in your completed form. Each session evaluation form represents a chance to win. Pick up your evaluation form: • In each presentation room • At the PASS Booth near registration Drop off your completed form: • Near the exit of each presentation room • At the PASS Booth near registration Sponsored by Dell
  • 55.
    Thank you for attendingthis session and the 2010 PASS Summit in Seattle

Editor's Notes

  • #5 By joining the program, customers gain: A contact in the Playback team who completes the legal paperwork and on-boarding process. Free performance testing of customer playback data and workloads against every new public release of SQL Server and regular status updates within one month of completing each test cycle. Confidence that Microsoft tested the customer’s workloads in a secure and carefully controlled environment and encountered no issues that would break the customer solutions. An opportunity to request improvements directly from the product group for known upgrade issues that could affect the customer’s business solutions. For more information or participation in the program, contact sqlplay@microsoft.com
  • #9 FYI – the steps are:  Change the service account passwords from the services.msc (SQL Server, Analysis Services, Reporting Services, etc.)  The lab account pwd has changed so I was not able to connect to the SharePoint Central Admin.  Therefore, followed steps 1 – 5 at http://support.microsoft.com/kb/934838  Now that I can connect to SharePoint Central Admin, needed to configure the Managed accounts 1.            In the Central Admin – Security – Configure Managed Accounts 2.            Edit the account you want to change the password for. 3.            In section for Credential Management, select “Change password now” and  select “Use Existing password” and type in the new password.  All services are up and running but the application pools need to be reset within IIS o IIS Manager -> Application pools, right-click on the “SharePoint Web Services System” app pool -> advanced settings -> set account. Then start the pool. If you are on a newer build the app pool name is a guid.   Ensure to change all of the app pools running under the changed account.
  • #35 Response Times: For 160 users we’re talking about 10s vs. <2s – pretty significant here, eh?! Inflection point: same server is about 40 users while Separate servers is about 110 users CPU utilization: Single server maxes <30% for ECS and ~20% for AS while Separate servers we see ECS at 60% while AS at 30%
  • #46 Thanks to John Sirmon for these screenshots
  • #47 Thanks to John Sirmon for these screenshots