HEAVY METAL POWERPIVOT

Jason Himmelstein SharePoint Practice Director, Sentri, Inc

jhimmelstein@sentri.com @sharepointlhorn

About me

• Jason Himmelstein, MCITP, MCTS

• SharePoint Practice Director, Sentri Inc. • Microsoft vTSP

● virtual Technology Solutions Professional

• SharePoint Foundation Logger (http://spflogger.codeplex.com) • Web: www.sentri.com • Blog: www.sharepointlonghorn.com • Twitter: @sharepointlhorn • LinkedIn: www.linkedin.com/in/jasonhimmelstein

About Sentri

Microsoft 2010/2011 Northeast

Microsoft 2011 Northeast VOICE

Israel - Development & Engineering

Microsoft Solutions & Consulting Services

∞ Microsoft Cloud Services

∞ Call Center Solutions

∞ Managed Services

Core Interactive Components

SharePoint Exchange

Lync

Intranet/Extranet Workflow/Forms Document Management Content Management Search Business Intelligence Collaboration Protection

E-Mail Collaboration Unified Messaging Shared Calendars Managed Folders Mobile Protection

Collaboration Presence Protection

VoIP Chat Video Conferencing

Structured Process Management

CRM ERP

Communications Platform

Lync PBX

Exchange 2010

Platform

SharePoint Server

Presentation

Extranets Intranets Dashboards Analytic Views Scorecards Workflow

Remote

Access Virtualization Integration

Services SQL Server Monitoring Security

CIM/Contact Center

Lync

Microsoft Integrated Business Technology

In the Cloud On-Premises

Overall Microsoft Strategy

Platform

Cloud

Unified Communication and

Collaboration

CRM

Typical Microsoft Technology Silos

Unified Communications

Exchange

Unified Messaging

Enterprise Voice

Dynamics CRM

Sales

Marketing

Customer Service

Managed Services

Help Desk

Tier II

Tier III

The Sentri Solution Offering

Desktop

Windows 7

App-V

SCCM

Server Platform System

Management

Active Directory

Cloud Strategy

Data Center

Portals and Collaboration

Portals

Workflow

Search

Virtualization

Server

SCVMM

High Availability

HEAVY METAL POWERPIVOT

• Architecture

• Installation

• In Action Examples

• Denali

Architecture

SQL

Oracle

DB2

Azure

SharePoint

PowerPivot UDR

XML

Text

RSS

SSRS

SSAS

Informix

Sybase

Terra data Access

INSTALLING POWERPIVOT

• New 2010 farm

• Pre-existing 2010 farm

PRE-EXISTING 2010 FARM

• App Server

• SharePoint Admin Service account

• MS Data Access Engine 2010 Redist

• SQL Server 2008 R2 Setup in Admin mode

PRE-EXISTING 2010 FARM

• Add Features

• New Installation NOT Add Features

• SQL Server PowerPivot for SharePoint

• HACK IT!

PRE-EXISTING 2010 FARM

PRE-EXISTING FARM – Hack Part

• ConfigurationFile.ini

• FARMADMINPORT

• Microsoft.AnalysisServices.SharePoint.Integration.dll

• http://bit.ly/PowerPivotDLL

• C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64

DEMO

• Some cool examples of PowerPivot in action.

HEAVY METAL POWERPIVOT

New SQL Server 2012

Was Project Crescent Now Power View

Was Project Juneau SQL Server Data Tools

What’s coming in SQL Server 2012 PowerPivot

• Builds upon V1 release in SQL Server 2008 R2

• New administrative capabilities

● Lots of bug fixing, failover capabilities and scalability

enhancements

● Default allocation algorithm changed to health-based rather than

round-robin (means we are more responsive to low memory

situations

● More administrative controls:

• Manage the amount of database caching done on the app

servers

• database caching cleanup (based on inactivity)

• Disable data refresh jobs if they are constantly failing, or if

they succeed but are not being viewed

SQL Server Denali PowerPivot

SQL Server Denali PowerPivot

(cont.)

• A new setup experience

• Rather than installing and configuring SharePoint directly, setup now

just lays down the bits and there is a new standalone configuration

wizard

• Capabilities:

● Wizard has both a one-click “GO” option as well as a

step-by-step experience

● Existing farm steps integrated with the wizard

● Individual steps can be scripted

● History is directly integrated into the tool

● Able to uninstall, upgrade, repair and patch separately

PowerPivot v2 Setup and Config...

The Code Behind the Build (copied from the UI)

# Open PowerShell library from: C:\Program Files\Microsoft SQL Server\110\Tools\PowerPivotTools\ConfigurationTool\Resources\ConfigurePowerPivot.ps1

Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\110\Tools\PowerPivotTools\ConfigurationTool\Resources\PowerPivotFarm.wsp'

Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\110\Tools\PowerPivotTools\ConfigurationTool\Resources\PowerPivotWebApp.wsp'

DeployFarmSolution

DeployWebAppSolutionToCentralAdmin

Install-SPFeature -path PowerPivot

Install-SPFeature -path PowerPivotAdmin

Install-SPFeature -path PowerPivotSite

SetEngineServiceCredentials 'spflogger\b_powerpivotv2' $password; New-PowerPivotEngineServiceInstance -Provision:$true

New-PowerPivotSystemServiceInstance -Provision:$true

New-PowerPivotServiceApplication -ServiceApplicationName 'PowerPivot Service Application' -DatabaseServerName 'SQL2012' -DatabaseName

'PowerPivotServiceApplicationDB' -AddToDefaultProxyGroup:$true;Set-PowerPivotSystemService -WorkbookUpgradeOnDataRefresh:$True -

Confirm:$false

DeployWebAppSolution 'http://sp2010sp1/' 2047

EnableSiteFeatures 'http://sp2010sp1' $true

StartService "Microsoft.SharePoint.Administration.Claims.SPWindowsTokenServiceInstance"

UpdateSecureStoreMasterKey 'Secure Store Service Proxy' '********'

CreateUnattendedAccountForDataRefresh 'http://sp2010sp1' 'PowerPivotUnattendedAccount' 'PowerPivot Unattended Account for Data Refresh'

'SPFLOGGER\b_spAdmin' $password

AddMSOLAP5AsECSTrustedProvider 'Excel Services Application'

SQL Server Denali PowerPivot

(cont.)

• Important note:

SharePoint SP1 is required for CTP3, RC0 (and RTM)

• SQL Setup will issue a warning if SharePoint SP1 has

not been installed when the PowerPivot bits are laid

down. This gives you some time to upgrade to SP SP1.

• However, if you still haven’t upgraded to SP1, by the

time you get to run the PowerPivot configuration wizard,

we will throw a fatal error and you will be blocked from

configuring your SharePoint farm until you upgrade to

SP1.

Gotchas! The multi-server hiccup

CTP3 comes with TCP\IP disabled

Until you enable this, no off server

communication will happen to SQL

Gotchas! The single-server hiccup

Can’t log in to SharePoint locally?

Check your Loopback settings!

Special announcements

• New book coming for SharePoint and SQL

Server 2012 Business Intelligence:

Handy Reference Information • Useful Links

• http://www.sharepointlonghorn.com – Jase’s blog – @sharepointlhorn – jase@sharepointlonghorn.com – http://aurl.to/v - THE Authoratative SharePoint Versions and Downloads list

• http://www.cjvandyk.com/blog - Corne’s blog – @cjvandyk – c@crayveon.com

• Microsoft References ● Microsoft Full Up BI Demo Machine

• http://www.mssalesdemos.com

• Download links • http://powerpivotgeek.com

– Dave Wickert & Lee Graber (Microsoft Product Team Members) » Best exclusively PowerPivot blog around

• http://spflogger.codeplex.com – Its not PowerPivot, but its wicked cool so check it out!