Roger Noble PowerPivot and PivotViewer in SharePoint

Roger Noble
BI Consultant
• Roger Noble
• BI Consultant with LobsterPot Solutions specialising in SQL Server
and SharePoint
• Certifications
• MCITP: SQL Server BI 2008
• MCTS: SharePoint Configuration and Development 2007
• MCPD: Web Developer
• Web:
• Twitter: @Roger_Noble
What does this have to do with SharePoint?
Installation overview
Solution Architechture
Creating a Pivot collection
• Interact with massive amounts* of data
• Very visual
• Able to get hands on with your data
* definitions of massive may vary
Extension for Excel
Self service BI platform
Able to analyse and manipulate millions of records
Basis for next version of Analysis Services
• Solution is composed of 4 parts
• PowerPivot
• Reporting Services
• SharePoint 2010
• PivotViewer feature – web parts and list
• Analysis Services integration with SharePoint
• Data feed library and PowerPivot gallery
• PowerPivot
• Reports
• PivotViewer solution
• Web Parts
• List
• Requires SharePoint 2010 Enterprise Edition
• Excel Services must be enabled
• Must have been installed as a server farm – not stand alone
• Either in a new or existing farm (requires SQL Server 2008 R2)
• Run SQL Server 2008 R2 install and select SQL Server
PowerPivot for SharePoint
• PowerPivot is managed in Central Admin
• Install PivotViewer solution and activate feature
• Installs 2 web parts
• BI Report Crawler
• BI Data
• Installs a list called BI Collection Administration
• Import data in to PowerPivot
• Create a parameterised report
• Only one parameter supported 
• Collections are made up of Items
• Items are made up of Facets
• Create a new list item
• Specify MDX query – SSMS can help
• PivotViewer is a highly interactive way to explore your data
PivotViewer for SSRS CTP2
Install PowerPivot into an existing SharePoint 2010 farm
Install PivotViewer (via Cristian Petculescu BI principal architect)
LobsterPot Solutions example Pivot collections

similar documents