Transcript Demystifing PowerPivot from the SharePoint Admin’s Perspective

DEMYSTIFYING
POWERPIVOT FROM THE
SHAREPOINT ADMIN’S
PERSPECTIVE
David Peterson – SharePoint Administrator – Trek Bicycles Corp.
[email protected] - @sprookie1 – sharepointrookie.com
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
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
AGENDA
•
•
•
•
•
•
•
What is PowerPivot
PowerPivot in SharePoint
Setting it up
Setting up the Refresh
Example Scenario
Add’l Ways to Use PowerPivot
Questions
WHO’S USED
PIVOTTABLES OR
PIVOTCHARTS?
PIVOTCHARTS
• Visualize Data
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
TAKES EXCEL
FROM…
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/nonexistent BI team
ARCHITECTURE
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 = $$$
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
$$$
ARCHITECTURE
SETTING IT UP
HARDWARE &
SOFTWARE
REQUIREMENTS
• 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
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
OPTIONS
BITS CONT.
• Prepare yourself to hit Next…a lot
– Remember the Service Account
used for the Analysis cube
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
SET
UNATTENDED
ACCOUNT
CREDENTIALS
• Unattended Account needs an AD
Account
– Central Admin > Manage
Service Applications > Secure
Store Service
ADD
CREDENTIALS
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
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
SSRS REPORT
ENOUGH
TALKING!
LET’S SEE THIS
IN ACTION
WAYS TO USE
POWERPIVOT IN
SHAREPOINT
ALERTING/
WORKFLOWS
• 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
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
POWERPIVOT VS. PERFORMANCEPOINT VS. SSRS
Source: technet.microsoft.com/en-us/library/gg537617.aspx
QUESTIONS?
CONTACT
INFO
David Peterson
Trek Bicycles Corp.
[email protected]
@sprookie1
http://www.linkedin.com/pub/davidpeterson/8/910/859