Demystifying PowerPivot from the SharePoint Admin’s Perspective

1. Demystifying PowerPivot from the SharePoint Admin’s Perspective David Peterson – SharePoint Administrator – Trek Bicycles Corp. David_Peterson@trekbikes.com - @sprookie1 – sharepointrookie.com

2. Session Evaluations • Schedule and evaluate each session you attend via our mobile app that can be used across devices at http://spsaturday.cloudapp.net • You will be able to evaluate a session 25 minutes before the scheduled end time • Evaluations are stored anonymously and your feedback is appreciated • The app will be the only method available to submit session evaluations for the event and we hope you find it intuitive and convenient

3. Housekeeping • Follow SharePoint Saturday St. Louis on Twitter @spsstlouisand hashtag #spsstl • Play “Sponsor Bingo” to register for your chance to win one of the many great giveaways at the end of the day • Schedule and evaluate each session you attend via our mobile app that can be used across devices at http://spsaturday.cloudapp.net

5. About Me • Hi, I’m David • SharePoint Administrator with 8+ years experience • Trek Bicycle Corporation • Aug ‘11-present • State Farm Insurance • Aug ‘08-Aug ‘11 • Largest SharePoint deployment ever • MS Professional Writing – Illinois State University • Married to Dawn with 2 children

6. About Trek • Trek Bicycle Corporation – Waterloo, WI • One of the leading bicycle manufacturers in the world • Sold in over 90 countries • Variety of subsidiary businesses • Ascend – POS software • Ray’s – Mountain Bike Park • Trek Travel – Travel Company • Mansion Hill Inn – Hotel • B-cycle – Bicycle Ride Sharing

7. Agenda • What is PowerPivot • PowerPivot in SharePoint • Setting it up • Setting up the Refresh • Example Scenario • Add’l Ways to Use PowerPivot • Questions

8. Who’s used PivotTables or PivotCharts?

9. PivotCharts • Visualize Data

10. What is PowerPivot • PivotTables/PivotCharts only allow you to connect to 1 datasource • PowerPivot can connect to many datasources • With the familiarity of Excel in an Add-on

11. Takes Excel from…

15. Business Case • Takes BI (Business Intelligence) and puts it in the hands of decision makers • Now your Business Analysts, Managers, Directors, anyone really, can build meaningful reports and views into org data (mundane, boring day-to-day content) • …Leaving you to build the more complex reports/views and manage the datasource(s) (fun, challenging content) • PowerPivot is your desktop solution for BI if you have an understaffed/non-existent BI team

16. Architecture

17. PowerPivot without SharePoint • Old, outdated data • Manual Refresh • Users may encounter old, stale data and are not aware of this • Each user must have access to the data source • Must install Excel and PowerPivot on everyone’s machine = $$$

18. PowerPivot with SharePoint • Takes the Processing and automates it server –side • No more manual refreshes of PowerPivot and Excel • One-time or Schedule based • Not all users need access to data source • Adds in collaboration and document management features of SharePoint • Easily create online dashboards for mass consumption • No need for Excel client-side if Office Web Apps deployed = less $$$

19. Architecture

22. Setting it up

23. Hardware & Software • What’s it gonna take? • Minimum • 64-bit dual core proc • 8GB RAM • 80GB Storage • Recommended • 4 quad-core servers • 64GB RAM • 80GB Storage or more • Bottom Line . . . the more, the better to ensure a consistent user experience

24. Laying Down the Bits • Assumptions: • SQL 2012 deployment, although SQL 2008 R2 isn’t much different • What you’ll need: • SQL 2012 Resource Disc or .iso • SharePoint SP1 • ADO.NET Data Services Update for .NET 3.5 SP1 • Existing Site Collection • Place resource Disc in server or mount .iso and initialize setup

25. Bits cont.

26. Configuration • Now for the easy part • I’m the lazy SharePoint Admin • Open the PowerPivot Configuration Tool • Couldn’t be simpler • Will register necessary components, deploy solutions, create service applications, & validate that everything is done correctly • Will even create the refresh account

27. Set Unattended Account Creds • Unattended Account needs an AD Account • Central Admin > Manage Service Applications > Secure Store Service

28. Add Credentials

29. Notes about SSAS and Unattended accounts • Group Policy • Will need to update group policy • Accounts need rights to: • Allow Log on Locally • Log on as a Service • Set in Group Policy • Run gpupdate • Kerberos • Unattended still required when Kerberos is enabled on Farm • Who else is going to update the PowerPivot? • Unattended account will need contribute rights to the site you enable the refresh on • Same for SSAS if you are going to use SharePoint list data in a PowerPivot

30. Monitoring refresh • Someone’s gotta keep watch • Plenty of reports/information in Central Admin • Important to watch resource usage • Can be automated via SSRS report (i.e. e-mailed daily) • LINK

31. SSRS Report

32. Enough Talking!Let’s see this in action.

33. Ways to Use PowerPivot in SharePoint

34. Alerting/Workflows • Users can create alerts; notified after a refresh • Create a SPD workflow to e-mail specific link(s); good for Web App links since alerts send links directly to Excel file

35. Dashboards • Web Part Pages with Office Web App links • Dictate where/how users see specific content • Excel Services Web Part • Control what sheets are seen and how they’re consumed • Excel Service REST API • Full, granular control over workbook content • “Security through obscurity” • Trade-off – much more administration

36. PowerPivot vs. PerformancePoint vs. SSRS

38. Questions?

39. Contact Info • David Peterson • Trek Bicycles Corp. • david_peterson@trekbikes.com • @sprookie1 • http://www.linkedin.com/pub/david-peterson/8/910/859

40. Thanks to Our Sponsors! Raffle Silver Gold