powerpivot the coming wave prepared for n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
PowerPivot – The Coming Wave prepared for PowerPoint Presentation
Download Presentation
PowerPivot – The Coming Wave prepared for

Loading in 2 Seconds...

play fullscreen
1 / 26

PowerPivot – The Coming Wave prepared for - PowerPoint PPT Presentation


  • 128 Views
  • Uploaded on

PowerPivot – The Coming Wave prepared for. April 6, 2010. Prepared by. Jeff Johnston Greg Galloway. Agenda. What is PowerPivot and why should I care? The value of PowerPivot for the Business User Self-service reporting with Excel Publishing PowerPivot reports to SharePoint

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'PowerPivot – The Coming Wave prepared for' - josef


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
powerpivot the coming wave prepared for

PowerPivot – The Coming Waveprepared for

April 6, 2010

Prepared by

Jeff Johnston

Greg Galloway

slide2

Agenda

  • What is PowerPivot and why should I care?
  • The value of PowerPivot for the Business User
    • Self-service reporting with Excel
    • Publishing PowerPivot reports to SharePoint
  • What SharePoint Administrators need to know about PowerPivot
    • Governance
    • Architecture
    • Configuration
slide3

The Need for PowerPivot

  • Perform analysis and create reports
  • Analyze more than > 1 million transactions
  • Bring in data from several different sources
  • IT could do it, but not fast enough for my needs
  • Use a tool that is familiar and intuitive
  • Share my reports with others
  • IT’s concerns about governance & scalability
slide4

Ok, so what is PowerPivot?

  • PowerPivot for SharePoint 2010
  • Publish to SharePoint
  • Share & collaborate with others
  • Centrally monitor and manage
  • Manage security
  • Discover mission-critical BI solutions
  • Excel Services support
  • PowerPivot for Excel 2010
  • Free Add-in for Excel
  • Millions of rows of data
  • Discovers relationships
  • Flexible layout & formatting
  • PivotTables & Slicers
  • Tables & Charts
slide5

PowerPivot Unites Business Users and IT

Provides alignment between

I.T. and Business Users

Empowered to create without IT dependence

  • Managingcompliance and resources without user interference
slide6

Where Does it Fit?

The BI Continuum

slide7

Where Does it Fit?

The BI Continuum

Self-Service / Flexible Enterprise/Formal

slide8

Where Does it Fit – Analytical Tools

Data Mining

Monitoring

PowerPivot

Analytics

OLAP

Software-derived

Insight

Ad-hoc Reporting

Spreadsheet

Reporting

Strong

Hypothesis

Weak

slide10

Demo Recap

  • PowerPivot enhances the analytical capabilities of Excel
  • Illustrated 3 different data sources
    • SQL Server tables
    • Reporting Services via Atom Feed
    • Clipboard
    • Multi-Dimensional (OLAP Cubes)
    • Files
    • Excel
  • Ability to handle 3.9 million rows
  • Ability to detect relationships between tables
  • DAX Expressions
  • Familiar PivotTable / PivotChart ad-hoc reporting
slide11

demo: Publishing and

  • the PowerPivot Management Dashboard
slide12

PowerPivot Architecture Overview

Producer

Excel

PowerPivot Add-In

Data Sources

slide13

PowerPivot Architecture Overview

Producer

SharePoint Farm

Excel

PowerPivot Add-In

WFE

Data Sources

Content DBs

slide14

PowerPivot Architecture Overview

Excel Services

SharePoint Farm

PowerPivot

WFE

App Servers

Consumer

Content DBs

Browser

slide15

Responsibilities for SharePoint Admin

  • Setup
    • Capacity planning
    • Installing PowerPivot for SharePoint
    • Tune SharePoint for large files
  • Ongoing Support
    • Secure document libraries
    • Make sure data refresh is succeeding
    • Monitor usage and performance
slide16

PowerPivot Capacity Planning

  • Metrics to capture:
    • Total SharePoint users
    • Concurrent SharePoint Users
    • Concurrent PowerPivot Users
    • Number of files per user
    • Average and max size of files
    • Total size of all unique PowerPivot workbooks being used concurrently
  • PowerPivot is in-memory technology
  • Specific guidelines will be coming soon
slide18

SharePoint 2010 Topologies - Departmental

Single Server

Multiple Standalone Server

SharePoint Farm

PowerPivot

WFE

Excel Svcs

SQL

  • Evenly distributes load across many servers with NLB in front of servers
  • SQL on separate server, with or without clustering
  • Easy to rebuild but doesn’t take into account heavier loads
  • Good for small setups
  • Not a good farm scenario
slide19

SharePoint 2010 Topologies - Enterprise

  • Licensing
  • SharePoint app server with PowerPivot requires these licenses:
    • SQL 2008 R2 Enterprise
    • SharePoint 2010 Enterprise

SharePoint Farm

PowerPivot

Excel Svcs

WFE

PowerPivot

SQL

Excel Svcs

SQL

WFE

Excel Svcs

PowerPivot

web

app

dB

slide20

SharePoint Upload Config for Large Files

  • Important because IT (SharePoint) Administrators are now dealing with larger files
    • Default SharePoint Scenarios
      • Max Size: 50MB
      • Average Size: 20MB
    • PowerPivot SharePoint Scenarios
      • Max Size: 2000MB (change both Excel Services and SharePoint settings)
      • Average Size: ~50MB
      • Beware of versioning
    • Consider Remote Blob Storeor third party solutions like StoragePoint
slide21

SharePoint Upload Choices

  • Ways to upload:
    • Save As from Excel with SharePoint
      • Excel 2010 uses Office Upload Center to upload asynchronously under the covers
    • Publish to SharePoint (the wizard)
    • WebDAV where SharePoint looks like a file share
      • Make sure Windows Desktop Experience role is installed and the WebClient service is running
    • Upload from SharePoint web page
slide22

SharePoint Upload Effect of Bandwidth

New: Office Upload Center which can asynchronously upload files from Excel to SharePoint

slide23

Troubleshooting: Reading ULS Logs

%commonprogramfiles%\Microsoft Shared\Web Server Extensions\14\LOGS\

slide24

Key Takeaways

  • SQL Server PowerPivot for Excel 2010 is a data analysis add-in that delivers unmatched computational power directly within Microsoft Excel
  • SQL Server PowerPivot for Excel 2010 is available for download free of charge at www.powerpivot.com.
  • SQL Server PowerPivot for SharePoint integrates with Excel Services on the SharePoint server to enable seamless and secure sharing and collaboration on user-generated BI solutions directly in the browser
  • SQL Server PowerPivot for SharePoint is included in SQL Server 2008 R2
slide25

Resources

  • http://www.powerpivot.com/
  • http://powerpivottwins.com/
  • http://powerpivotgeek.com/
  • http://powerpivotpro.com/
  • http://geekswithblogs.net/darrengosbell
  • http://sqlblog.com/blogs/marco_russo/
slide26

About Artis Consulting

right informationright person

right time