Download to read offline
Uploaded byDenny Lee
SQLCAT: A Preview to PowerPivot Server Best Practices
The document discusses SQL Server Customer Advisory Team (SQLCAT) and their work on the largest and most complex SQL Server projects worldwide. It also discusses SQLCAT's sharing of technical content and driving of product requirements back into SQL Server based on customer needs. The document promotes an upcoming SQL Server Clinic where experts will be available to answer questions about architecting and designing future applications.
More Related Content
Similar to SQLCAT: A Preview to PowerPivot Server Best Practices
![Ensuring compliance of patient data with big data and bi [bdii 301-m] - (4078)](https://cdn.slidesharecdn.com/ss_thumbnails/ensuringcomplianceofpatientdatawithbigdataandbibdii-301-m-4078-130430231240-phpapp01-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![UiPath Automation Suite Installation (Hands-On) [2/3]](https://cdn.slidesharecdn.com/ss_thumbnails/automationsuitecommunitysession2-251015095633-a6d862f1-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
SQLCAT: A Preview to PowerPivot Server Best Practices
- 1. SQLCAT: A Previewto PowerPivot Server Best Practices Denny Lee, Dave Wickert Microsoft Corporation
- 2. SQL Server CustomerAdvisory Team (SQLCAT) • Works on the largest, most complex SQL Server 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 transactions / 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. SQL Server DesignWin 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. SQLCAT and SQLCSS 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!
- 5. PASS PowerPivot Sweepstakes 1)Become a follower of www.twitter.com/powerpivot 2) Re-tweet our message: “Want to learn more, go to www.powerpivot.com and sign up for CTP #powerpivot” 3) You are entered to win an XBOX 360 4) We will randomly select a follower who re-tweeted this and announce the winner 5) The results will be posted on Monday, November 9th at www.twitter.com/powerpivot *No Purchase Necessary. Open only to residents 18+ of the 50 US (includes D of C) or Canada (excluding Quebec). Game ends 11/5/09. For full Official Rules, visit www.powerpivot.com/contest
- 6. Agenda • PowerPivot Infrastructure •Capacity Planning • Configuration • Installation • Upload Configuration • Troubleshooting
- 7. Business User Experience MicrosoftBusiness Intelligence Get more out of products you already own Data Infrastructure and BI Platform Analysis Services Reporting Services Integration Services Master Data Services Data Mining Data Warehousing Business Collaboration Platform Dashboards & Scorecards Excel Services Web based forms & workflow Collaboration Search Content Management LOB data integration Familiar User Experience Self-Service access & insight Data exploration & analysis Predictive analysis Data visualization Contextual visualizationBusiness Collaboration Platform Data Infrastructure & BI Platform
- 8. PowerPivot Infrastructure Client andServer Infrastructure PowerPivot Infrastructure Capacity Planning Topologies Installation Upload Configuration Troubleshooting Agenda
- 9. PowerPivot Infrastructure Overview SharePointFarm WFE App Servers Content dBs NLB Excel, RB, PerfPoint Power User Data Sources Excel Services PowerPivot Service AS Engine Browser Standard User PowerPivot Add-In
- 10. PowerPivot Client Excel ClientAdd-In PowerPivot Infrastructure PowerPivot Client Capacity Planning Topologies Installation Upload Configuration Troubleshooting PowerPivot Architecture Agenda
- 11. PowerPivot Infrastructure: Excel SharePointFarm WFE App Servers Content dBs NLB Excel Services PowerPivot Service AS Engine Browser Standard User Excel, RB, PerfPoint Power User Data Sources • Use of AS Engine: in-memory, column-based store w/ VertiPaq compression • Once data is imported, all calculations are performed on client • Excel now has it’s own local in-process SSAS engine • Added Excel power functions for PowerPivot called DAX (Data Access eXpressions) • Use of new compression algorithm to significantly compress the data ~ 10:1 • Added slicer functionality: not just for UI but for smoother SharePoint integration PowerPivot Add-In All in the workbook!!
- 12. PowerPivot Infrastructure Client Observations •For few instances VertiPaq may result in more storage than MOLAP • MOLAP can compress fact rows with same FK into a single row where VertiPaq maintains each row separately • How do you see the VertiPaq internal storage… Let’s show you the trick!
- 13. PowerPivot Server SharePoint Integration PowerPivotInfrastructure PowerPivot Server Capacity Planning Topologies Installation Upload Configuration Troubleshooting Agenda
- 14. Excel, RB, PerfPoint Power User DataSources Browser Standard User SharePoint Farm WFE App Servers Content dBs NLB PowerPivot SharePoint Integration: ECS Viewing Excel Web Access Excel Services PowerPivot Service AS Engine
- 15. Excel, RB, PerfPoint Power User DataSources Browser Standard User SharePoint Farm WFE App Servers Content dBs NLB PowerPivot SharePoint Integration: Server Action Excel Web Access Excel Services PowerPivot Service AS Engine
- 16. Capacity Planning PowerPivot BestPractices PowerPivot Infrastructure Capacity Planning Topologies Installation Upload Configuration Troubleshooting Agenda
- 17. Capacity Planning: Basic observations •VertiPaq mode is an in-memory system • Any currently ‘in-use’ database must be in memory (add’em up) • Need some buffer (~10-20%) for the auto- detection to work effectively • To reduce impact on content database, PowerPivot keeps a ‘cache’ of detached databases in the SSAS Backup folder
- 18. 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
- 19. Capacity Planning Scale OutContent Database SharePoint Farm WFE App Servers Content dBs • Excel workbooks (or any file) stored as blobs in content database • Another option is to use Remote Blob Store to store in FILESTREAM or connected network storage (e.g. EMC Centera) • Preliminary results note low overhead (2-3%) and able to better handle higher concurrency scenarios
- 20. Topologies PowerPivot Best Practices PowerPivotInfrastructure Capacity Planning Topologies Installation Upload Configuration Troubleshooting Agenda
- 21. http://go.microsoft.com/fwlink/?LinkID=167089 SharePoint Server 2010Topologies Medium Server Farm Topology
- 22. SharePoint Server 2010Topologies PowerPivot Departmental Topology WFE Power Pivot Excel Svcs SQL • Good for small setups • Not a good farm scenario • Should work fine in Kerberos or Claims scenarios Single Server Multiple Standalone Server • Evenly distributes load across many servers* • Easy to rebuild but doesn’t take into account heavier loads • Should work in Claims and Kerberos SharePoint Farm
- 23. SharePoint Server 2010Topologies Enterprise Topology (1) SharePoint Farm WFE Power Pivot Excel Svcs SQL Power Pivot Power Pivot WFE Excel Svcs web app dB Application Servers are PowerPivot Only • Scenario is good if PowerPivot servers have the most amount of load • Not as common because most calculations in PowerPivot are not overly complex • May be helpful for Report Builder and Performance Point scenarios • Should work in Claims and Kerberos scenarios
- 24. SharePoint Server 2010Topologies Enterprise Topology (2) SharePoint Farm WFE SQL Power Pivot WFE web app dB Application Servers are PowerPivot and Excel Services • Scenario is good if WFE servers have the most amount of load • May be a good idea due to high amount of chatter between Excel Services and PowerPivot • Should work in Claims and Kerberos scenarios Excel Svcs Power Pivot Excel Svcs Power Pivot Excel Svcs
- 25. SharePoint Server Topology Multi-hopscenario? SharePoint Farm SQL WFE web app dB Multi-Hop • By definition, going from the client to Excel Services to PowerPivot server is multi-hop But • Excel Services and PowerPivot Services are service applications • PowerPivot System Service recreates the identity token Background With SharePoint 2010, we have Claims token as part of Geneva framework Excel Svcs PP Service Client AS Engine
- 26. SharePoint Server Topology ClaimsData Source Scenario SharePoint Farm SQL WFE web app dB • PowerPivot System Service Login • AS Engine Service Impersonates SSS credentials • Connects to Data Source 1 via impersonation Excel Svcs PP Service Client AS Engine Data Source 1
- 27. SharePoint Server Topology KerberosData Source Scenario SharePoint Farm SQL WFE web app dB • Still need Kerberos in scenarios like above. • Constrained Delegation by machine (less secure) or user account (more secure) • This is defined by settings on Data Source 2 Excel Svcs PP Service Client AS Engine Data Source 1 Data Source 2 Linked To Kerberos
- 28. Installation PowerPivot Best Practices PowerPivotInfrastructure Capacity Planning Topologies Installation Upload Configuration Troubleshooting Agenda
- 29. Installation Best Practices •Read the manuals!!! • 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
- 30. Installation Best Practices Gotchas! •“New Farm” vs. “Existing Farm” • Require Domain Accounts • Upgrade SharePoint 2007 to SharePoint 2010 is very complex • Cannot upgrade from Gemini Server • CTP2 -> CTP3: Cannot do • CTP3 -> RTM: ??
- 31. Upload Configuration PowerPivot BestPractices PowerPivot Infrastructure Capacity Planning Topologies Installation Upload Configuration Troubleshooting Agenda
- 32. Upload Configuration Overview • SharePointis 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 • New: Office Sync Center which can asynchronously upload files from Excel to SharePoint
- 33. Upload Configuration SharePoint LargeChunkFileSize LargeChunkFileSizeparameter little to no effect on upload performance
- 34. Upload Configuration Effect ofBandwidth (100Mbps vs. 1Gbps)
- 35. Upload Configuration Effect ofBandwidth (Various) 0:19:56 2:29:27 3:41:03 0:53:16 0:00:00 0:28:48 0:57:36 1:26:24 1:55:12 2:24:00 2:52:48 3:21:36 3:50:24 4:19:12 Corp (100) T1 (1.54) Comcast (1.00) OC3 (155.52) Bandwidth Category Average Upload Duration for 1.3GB to SharePoint
- 36. Upload Configuration SharePoint Uploadvs. File Copy 0:00:01 0:00:01 0:00:02 0:00:04 0:00:07 0:00:12 0:00:16 0:00:37 0:00:59 0:00:09 0:00:16 0:00:28 0:00:43 0:01:11 0:02:12 0:02:57 0:04:32 0:06:55 0:00:00 0:01:26 0:02:53 0:04:19 0:05:46 0:07:12 0:08:38 25 43 75 128 223 388 536 822 1240 File Size (MB) WebDav vs. FileCopy Speed by File Size (1Gb) FileCopy WebDav
- 37. Troubleshooting PowerPivot Best Practices PowerPivotInfrastructure Capacity Planning Topologies Installation Upload Configuration Troubleshooting Agenda • Reading ULS Logs • SQL Profiler Trace
- 38. Troubleshooting Reading ULS Logs %commonprogramfiles%MicrosoftSharedWeb Server Extensions14LOGS
- 39.
- 40. Complete the EvaluationForm & Win! • You could win a Dell Mini Netbook – every day – just for handing in your completed form! Each session form is another chance to win! Pick up your Evaluation Form: • Within each presentation room • At the PASS Booth near registration area Drop off your completed Form: • Near the exit of each presentation room • At the PASS Booth near registration area Sponsored by Dell
- 41. Thank you for attendingthis session and the 2009 PASS Summit in Seattle
- 42. Visit the Microsoft TechnicalLearning Center Located in the Expo Hall Microsoft Ask the Experts Lounge Microsoft Chalk Talk Theater Presentations Microsoft Partner Village
- 43. SharePoint Farm PowerPivot forExcel Architecture EXCEL MSOLAP ADOMD.NETAMO PowerPivot Addin PowerPivot In-Proc Engine SSAS Server Web Data Pump SSAS Server
- 44. WFE EWA EWS ECS Other OLEDB Providers. . . App Server PSS ASPWS H T T P PowerPivot OLEDB Transports SharePointFarm Other PowerPivot- enabled SP Farm(s) WFE Excel Desktop Application Internet Explorer SSAS Server SQL Server Instance Config Db Content Db(s) PowerPivot Db(s) T C P C H A N N E L PowerPivot for SharePoint Architecture PowerPivot OLEDB Provider
- 45. PowerPivot AS EngineArchitecture Customer Insider Session – Microsoft NDA Only Formula Engine XMLA & MDX VertiPaq SE Parsing and query preparation Block computation On-disk files loaded Into memory on database open
- 46. • Optimized forin-memory operation; no paging (But we do have on-disk files for transactional integrity) • Column-oriented; high compression • Expressions evaluated natively by storage engine (where possible) • No aggregates – scan speed is sufficient for self-service needs Customer Insider Session – Microsoft NDA Only VertiPaq Storage Engine
Editor's Notes
- #8 Point of the Slide: To show a bit more detail on how we think of the BI architecture in 3 main layers. Flow of the Slide: A trusted BI platform is critical for a business intelligence solution to work today. If we’re going to achieve the promise of BI, we need to have the confidence and trust in the data, we need to know where it came from, and that it is both timely and reliable for us to use to make a decision. That’s where the power of an integrated BI platform like SQL Server comes into play. Once you have the data ready to use, the middle tier comes into play, where business users actually interact with the data and turn it into something that is useful to them to make the right decision. The numbers that we pull from other systems are just that—we need applications and content to turn them into actionable items—and that’s where the middle tier comes into play. Finally, we need the right tools and applications to ensure that we can use that data in the way we want to in order to make our decisions. Applications and tools that range from personal, to team, to organizational and corporate tools, all with a familiar look and feel, all integrated and working with my Operating system, email, Internet search function. And this is what the power of integration through Microsoft Office brings you. From the Office productivity tools like Excel, through to the team and collaboration tools of SharePoint. By integrating BI seamlessly into a broader business productivity suite that includes search, collaboration, unified communications, and content management, we offer the end user a much richer experience. Soundbite: With other BI solutions, you need 3-4-6-10 different products, footprints, and integration points. In our BI solution, we look three main layers of data and information that you need—all within the Office and SQL Server families: The data infrastructure and BI platform; the middle tier, where the business user comes into play, and the individual layer—where you and I use the data to make decisions.
- #9 Purpose: It is important to understand the underyling infrastructure to support PowerPivot client and server to understand why we are suggesting these best practices
- #11 Show database within Excel workbook
- #14 Show demo of central administration and usage collection to provide context of 12, 13
- #18 2nd bullet no limit on the size of the on-disk space taken Possible / Make it discussion Client Producer (Windows 7, dual core, 4GB RAM) Allows Windows Desktop Experience for Upload More memory and CPU to process data Consumer: Regular PC (browser-based) Server (64GB RAM, 64-bit, multi-core) SharePoint 2010 requires 64-bit Allows for multiple configurations (more later)
- #23 Introduce concept of Claims authentication here *Provided NLB in front of servers
- #25 Another option is to have separate servers for all components but most likely not optimal use of resources
- #30 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 Exponential difference for different bandwidth for WebDav upload
- #36 Using Cobalt protocol (TBD), see how much slower it is by having so much slower bandwidth
- #37 Comparison of FileCopy vs. WebDAV – for remote systems, perhaps want to filecopy it to a local box and then upload if speed is concern. Otherwise, use Office Sync Manager to do the upload async – though there can be issues with this (when the file was uploaded, etc.)
- #39 Thanks to John Sirmon for these screenshots
- #40 Thanks to John Sirmon for these screenshots
- #47 (optimized for self-service workload: may not apply in the future as we adapt vertipaq to other scenarios) (storage engine understands some expressions such as arithmetic, scalar functions in DAX; others such as multi-dimensionality and statistical functions (distinct count, sum of squares, stddev) are done by formula engine) (split between formula engine and storage engine is V1 – general guiding principal is that we should push down as much of the evaluation as possible so we are as close to the data – will likely change over time) (no aggregates means we can easily accept structural changes, e.g. add/remove attributes) VertiPaq – DAX expressions,