PowerPivot – The Coming Wave

1. PowerPivot – The Coming Wave Jeff Johnston Greg Galloway

2. Agenda • What is PowerPivot and why should I care? • The value of PowerPivot for the Business User • Self-service reporting with Excel • Publishing PowerPivot reports to SharePoint • What SharePoint Administrators need to know about PowerPivot • Governance • Architecture • Configuration

3. The Need for PowerPivot • Perform analysis and create reports • Analyze more than > 1 million transactions • Bring in data from several different sources • IT could do it, but not fast enough for my needs • Use a tool that is familiar and intuitive • Share my reports with others • IT’s concerns about governance & scalability

4. Ok, so what is PowerPivot? • PowerPivot for SharePoint 2010 • Publish to SharePoint • Share & collaborate with others • Centrally monitor and manage • Manage security • Discover mission-critical BI solutions • Excel Services support • PowerPivot for Excel 2010 • Free Add-in for Excel • Millions of rows of data • Discovers relationships • Flexible layout & formatting • PivotTables & Slicers • Tables & Charts

5. PowerPivot Unites Business Users and IT Provides alignment between I.T. and Business Users Empowered to create without IT dependence • Managingcompliance and resources without user interference

6. Where Does it Fit? The BI Continuum

7. Where Does it Fit? The BI Continuum Self-Service / Flexible Enterprise/Formal

8. Where Does it Fit – Analytical Tools Data Mining Monitoring PowerPivot Analytics OLAP Software-derived Insight Ad-hoc Reporting Spreadsheet Reporting Strong Hypothesis Weak

9. demo: Self-Service Reporting with PowerPivot

10. Demo Recap • PowerPivot enhances the analytical capabilities of Excel • Illustrated 3 different data sources • SQL Server tables • Reporting Services via Atom Feed • Clipboard • Multi-Dimensional (OLAP Cubes) • Files • Excel • Ability to handle 3.9 million rows • Ability to detect relationships between tables • DAX Expressions • Familiar PivotTable / PivotChart ad-hoc reporting

11. demo: Publishing and • the PowerPivot Management Dashboard

12. PowerPivot Architecture Overview Producer Excel PowerPivot Add-In Data Sources

13. PowerPivot Architecture Overview Producer SharePoint Farm Excel PowerPivot Add-In WFE Data Sources Content DBs

14. PowerPivot Architecture Overview Excel Services SharePoint Farm PowerPivot WFE App Servers Consumer Content DBs Browser

15. Responsibilities for SharePoint Admin • Setup • Capacity planning • Installing PowerPivot for SharePoint • Tune SharePoint for large files • Ongoing Support • Secure document libraries • Make sure data refresh is succeeding • Monitor usage and performance

16. PowerPivot Capacity Planning • Metrics to capture: • Total SharePoint users • Concurrent SharePoint Users • Concurrent PowerPivot Users • Number of files per user • Average and max size of files • Total size of all unique PowerPivot workbooks being used concurrently • PowerPivot is in-memory technology • Specific guidelines will be coming soon

17. http://powerpivotgeek.com/server-installation

18. SharePoint 2010 Topologies - Departmental Single Server Multiple Standalone Server SharePoint Farm PowerPivot WFE Excel Svcs SQL • Evenly distributes load across many servers with NLB in front of servers • SQL on separate server, with or without clustering • Easy to rebuild but doesn’t take into account heavier loads • Good for small setups • Not a good farm scenario

19. SharePoint 2010 Topologies - Enterprise • Licensing • SharePoint app server with PowerPivot requires these licenses: • SQL 2008 R2 Enterprise • SharePoint 2010 Enterprise SharePoint Farm PowerPivot Excel Svcs WFE PowerPivot SQL Excel Svcs SQL WFE Excel Svcs PowerPivot web app dB

20. SharePoint Upload Config for Large Files • 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 (change both Excel Services and SharePoint settings) • Average Size: ~50MB • Beware of versioning • Consider Remote Blob Storeor third party solutions like StoragePoint

21. SharePoint Upload Choices • Ways to upload: • Save As from Excel with SharePoint • Excel 2010 uses Office Upload Center to upload asynchronously under the covers • Publish to SharePoint (the wizard) • WebDAV where SharePoint looks like a file share • Make sure Windows Desktop Experience role is installed and the WebClient service is running • Upload from SharePoint web page

22. SharePoint Upload Effect of Bandwidth New: Office Upload Center which can asynchronously upload files from Excel to SharePoint

23. Troubleshooting: Reading ULS Logs %commonprogramfiles%\Microsoft Shared\Web Server Extensions\14\LOGS\

24. Key Takeaways • SQL Server PowerPivot for Excel 2010 is a data analysis add-in that delivers unmatched computational power directly within Microsoft Excel • SQL Server PowerPivot for Excel 2010 is available for download free of charge at www.powerpivot.com. • SQL Server PowerPivot for SharePoint integrates with Excel Services on the SharePoint server to enable seamless and secure sharing and collaboration on user-generated BI solutions directly in the browser • SQL Server PowerPivot for SharePoint is included in SQL Server 2008 R2

25. Resources • http://www.powerpivot.com/ • http://powerpivottwins.com/ • http://powerpivotgeek.com/ • http://powerpivotpro.com/ • http://geekswithblogs.net/darrengosbell • http://sqlblog.com/blogs/marco_russo/

26. About Artis Consulting right informationright person right time

27. Contact Information • Jeff Johnston: jjohnston@artisconsulting.com • Greg Galloway: ggalloway@artisconsulting.com