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. 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 About Me

3. 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 About Trek

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

5. Who’s used pivottables or pivotcharts?

6. Visualize Data Pivotcharts

7. What is powerpivot PivotTables only allow you to connect to 1 datasource PowerPivot can connect to many datasrouces With the familiarity of Excel in an Add-on

8. Takes excel from…

12. 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 Business Case

13. Architecture

14. 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 = $$$ Powerpivot withoutsharepoint

15. 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 $$$ Powerpivot withsharepoint

16. architecture

19. Setting it up

20. 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 Hardware & Software Requirements

21. 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 Laying down the bits

22. Options

23. Prepare yourself to hit Next…a lot • Remember the Service Account used for the Analysis cube Bits cont.

24. 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 configuration

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

26. Add Credentials

27. 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 Notes about SSAS and Unattended accounts

28. 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 Monitoring refresh

29. SSRS Report

30. Enough talking! Let’s see this in action

31. Ways to use powerpivot in sharepoint

32. Similar to SSRS’s subscription and alerting capabilities • 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 Alerting/workflows

33. 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 dashboards

34. PowerPivot vs. performancepointVs. ssrs

35. Source: technet.microsoft.com/en-us/library/gg537617.aspx

36. Questions?

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