1 / 37

Demystifying PowerPivot from the SharePoint Admin’s Perspective

In this session, David Peterson, a seasoned SharePoint Administrator at Trek Bicycles, delves into PowerPivot from a SharePoint perspective. With over eight years of experience, he explains how PowerPivot can enhance business intelligence by connecting multiple data sources and simplifying report generation. Learn about setting up PowerPivot, automating refresh processes, and leveraging SharePoint’s collaborative features. This guide is essential for administrators aiming to optimize data management and deliver insightful reports easily.

channary
Télécharger la présentation

Demystifying PowerPivot from the SharePoint Admin’s Perspective

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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…

  9. 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

  10. Architecture

  11. 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

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

  13. architecture

  14. Setting it up

  15. 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

  16. 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

  17. Options

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

  19. 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

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

  21. Add Credentials

  22. 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

  23. 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

  24. SSRS Report

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

  26. Ways to use powerpivot in sharepoint

  27. 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

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

  29. PowerPivot vs. performancepointVs. ssrs

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

  31. Questions?

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

More Related