Install and Configure SQL Server 2012 PowerPivot for SharePoint 2010 Using PowerShell

The PowerPivot feature in SQL Server 2012 provides powerful data mashup capabilities to support self-service BI scenarios. It’s based on the xVelocity in-memory analytics engine that achieves very high performance for analytic queries by leveraging columnar storage, compression, and parallel data scanning and aggregation algorithms. PowerPivot integrates with SharePoint Server 2010 to provide a reliable platform for building the managed BI collaboration environment such as avoiding proliferation of spreadmarts, ensuring data consistency and data refresh across user-generated workbooks, and providing and monitoring usage patterns. It also takes advantage of core SharePoint capabilities such as role-based security, versioning, compliance policies, and workflows.

The official MSDN documentation for the manual steps to install SQL Server 2012 PowerPivot in an existing SharePoint 2010 farm can be found here. After installation, you can configure PowerPivot either by performing the manual steps in Central Administration as documented here, or using the “PowerPivot Configuration Tool”. This article describes the PowerShell scripts needed to completely automate the installation and configuration process. In a subsequent post, we’ll discuss the “PowerPivot Configuration Tool” and some issues we encountered while trying to use this tool to validate the installation and configuration accomplished using the automated scripts described here.

1. Install SQL Server Analysis Services

The first step is to install “Analysis Services” (in-memory mode) and “Analysis Services SharePoint Integration” components on the application server(s) in SharePoint Server 2010 farm that will host Analysis Services. This is accomplished by invoking the SQL Server 2012 setup.exe in a PowerShell session as follows:

$setupPath = "<Path to SQL 2012 Bits>setup.exe"
$command = "$setupPath /q /ACTION=Install /IAcceptSQLServerLicenseTerms /ROLE=SPI_AS_ExistingFarm /INSTANCENAME=PowerPivot /ASSVCACCOUNT=<Account for AS Service> /ASSVCPASSWORD=<Password> /ASSYSADMINACCOUNTS=$farmAdminsGroupName /ErrorReporting=1 /UpdateEnabled=0
Invoke-Expression "& $command"

Note the following parameters passed to setup.exe: /Role of “SPI_AS_ExistingFarm” is equivalent to selecting “Analysis Services” and “Analysis Services SharePoint Integration” on the graphical setup screen, /InstanceName can only be “PowerPivot”, /UpdateEnabled is set to 0 so that the install does not fail on servers with no external connectivity, and /ASSysAdminAccounts specifies that the farm administrator group is also Analysis Services Administrator (you can specify multiple individual accounts using /PARAMETER="value1" "value2" "value3" format). No SQL Relational Database Engine needs to be installed. For a complete list of parameters see Install SQL Server 2012 from the Command Prompt

Once the installation is complete, the SQL log file can be examined for any errors using the following function:

function ValidatePowerPivotInstall()
{
[bool] $bReturn = $false
[string] $Constant_LogFilePath = "C:Program FilesMicrosoft SQL Server110Setup BootstrapLogSummary.txt"
if((Test-Path $Constant_LogFilePath))
{
$SetupLog = get-item "$Constant_LogFilePath"
$installationSuccess = $SetupLog | select-string -pattern "Installation completed successfully"
if ($installationSuccess -ne $null)
{
Write-Host "Installation not successful"
$bReturn = $false
}
else
{
$bReturn = $true
}
}
return $bReturn
}

a. Install OLE DB and XMLA libraries on Excel Server

If Excel Calculation Services and PowerPivot run on separate application servers in the farm, installation of the new SQL Server 2012 version of Analysis Services OLE DB provider (MSOLAP.5) is needed on app server(s) running Excel Calculation Services. The provider is included in SQL Server Setup, therefore explicit installation is only required if the Excel server is not a PowerPivot application server.

$setupPath = "<Path to SQL 2012 Bits>setup.exe"
$command = "$setupPath" /q /ACTION=Install /FEATURES=Conn /IAcceptSQLServerLicenseTerms /ERRORREPORTING=1 /UpdateEnabled=0"
Invoke-Expression "& $command"

Note that the Feature parameter of “Conn” installs connectivity components.

In addition, the new OLE DB provider must be specified as a trusted data provider in Excel Service App.

[Array] $serviceApps = Get-SPServiceApplication | Where-Object { $_.TypeName -eq “Excel Service Application”}
foreach($ecsApp in $serviceApps)
{
$provider = $ecsApp | Get-SPExcelDataProvider | where {$_.ProviderId -eq "MSOLAP.5"}
if (!$provider)
{
$ecsApp | New-SPExcelDataProvider -providerId "MSOLAP.5" -ProviderType oleDb -description "Microsoft OLE DB Provider for OLAP Services 11.0"
Write-Host "Registered MSOLAP.5 as a Trusted Data Provider with Excel Service App"
}
}

b. Install ADOMD.NET on server hosting Central Administration web site

Some Central Admin reports in the PowerPivot Management Dashboard use ADOMD.NET to access data collected on PowerPivot query processing and server health in the farm. If the farm server hosting the Central Administration site does not run Excel Services or PowerPivot, installation of ADOMD.NET client library is needed for these reports to function properly.

$setupPath = "<Path to SQL 2012 Bits>setup.exe"
$command ="$setupPath" /q /ACTION=Install /FEATURES=Conn /IAcceptSQLServerLicenseTerms /ERRORREPORTING=1 /UpdateEnabled=0”
Invoke-Expression "& $command"

2. Deploy Solutions

After Analysis Services is installed on one or more servers, the “PowerPivot Tools” directory on the server(s) (%Program Files%Microsoft SQL Server110ToolsPowerPivotToolsConfigurationToolResources) will contain two SharePoint solutions (.wsp files). The PoverPivotFarm.wsp is a farm level solution that adds the library templates (for PowerPivot Gallery and Data Feed libraries) and application pages, and the PowerPivotWebApp.wsp is a web application level solution that adds PowerPivot Web service to the Web-front end. The following scripts install these solutions in SharePoint and deploy the web application level solution to the desired web applications in the farm.

#Deploy Farm level solution
$sol = Get-SPSolution | Where-Object { $_.Name -eq "PowerPivotFarm.wsp" }
if (($sol -ne $null –and $sol.Deployed -eq $false)
{
Install-SPSolution -Identity "PowerPivotFarm.wsp" -GacDeployment –Force -Confirm:$false
}
WaitForSolutionDeployment "PowerPivotFarm.wsp" $true

#Deploy Web App level solution
$webAppUrl = (Get-SPWebApplication -IncludeCentralAdministration | Where { $_.DisplayName -eq “<web app name>”}).Url
Install-SPSolution -Identity "PowerPivotWebApp.wsp" -WebApplication $ webAppUrl -GacDeployment -Force -Confirm:$false
WaitForSolutionDeployment "PowerPivotWebApp.wsp" $true $ webAppUrl

The web application must use Windows classic mode authentication and not claims authentication to support PowerPivot. The WaitForSolutionDeployment function is borrowed from the PowerPivot Configuration Tool resources script (ConfigurePowerPivot.ps1 in the “PowerPivot Tools” directory mentioned above) and modified as follows:

# This method will be used to wait for the timer job deploying or retracting a solution
# to finish. The parameter $deploy is a bool that indicates if this is a deployment or a
# retraction
function WaitForSolutionDeployment
{
param($solutionName, $deploy, $webApplication)
$solution = Get-SPSolution $solutionName -ErrorAction:SilentlyContinue
$count = 0
while(!$solution -and $count -lt 10)
{
"PowerPivot Solution is not added to farm yet. Wait 3 seconds and check again."
Start-Sleep -s 3
($count)++
$solution = Get-SPSolution $solutionName -ErrorAction:SilentlyContinue
}
if(!$solution)
{
"PowerPivot solution does not exist in the farm"
return
}
"Found solution " + $solutionName
$activeServers = @($solution.Farm.Servers | where {$_.Status -eq "Online"})
$serversInFarm = $activeServers.Count
## Wait for the job to start
if (!$solution.JobExists)
{
"Timer job not yet running"
$count = 0;
## We will wait up to 90 seconds per server to start the job
$cyclesToWait = 30 * $serversInFarm;
while (!$solution.JobExists -and $count -lt $cyclesToWait)
{
Start-Sleep -s 3
($count)++;
}
## If after that time timer still doesn't exist, verify if it suceeded
if (!$solution.JobExists)
{
if ($deploy -xor $solution.Deployed)
{
"Timer job did not start"
# throw new Exception(Strings.ASSPIGeminiSolutionNoDeployed_Exception);
return
}
else
{
"Timer job already finished"
return
}
}
}
else
{
"Timer job already started"
}
if($deploy)
{
$deployText = "deployed"
}
else
{
$deployText = "retracted"
}
## If deploy action and solution not deployed yet or retract and solution still deployed
[bool]$status = CheckIfDeployed $solution.Name $webApplication
if (((!$solution.ContainsWebApplicationResource -and
($deploy -xor $solution.Deployed)) -or
($solution.ContainsWebApplicationResource -and
($deploy -xor ($status)))))
{
"Solution not yet " + $deployText
$count = 0
## We will wait up to 10 minutes per server
$cyclesToWait = 200 * $serversInFarm;
# We enter this cycle if solution is not yet deployed or retracted
$status = CheckIfDeployed $solution.Name $webApplication
while (((!$solution.ContainsWebApplicationResource -and
($deploy -xor $solution.Deployed)) -or
($solution.ContainsWebApplicationResource -and
($deploy -xor ($status)))) -and
$count -lt $cyclesToWait)
{
write-host "." -nonewline
Start-Sleep -s 3
($count)++
$status = CheckIfDeployed $solution.Name $webApplication
## Check every 3 minutes to see if job is aborted or failed
## Application still not deployed/retracted and job not running mean something is wrong
## We can't check geminSolution.JobStatus because it throws in the absence of a job.
if (($count % 60 -eq 0) -and ($deploy -xor $solution.Deployed) -and !$solution.JobExists)
{
"We waited " + $count + " seconds for the solution to be " + $deployText + ". However, the PowerPivot solution is not yet " + $deployText + ". Please check whether SharePoint timer job is enabled. "
break
}
}
}
else
{
"Solution already " + $deployText
}
Start-Sleep -s 15
## Check if solution wasn't successfully deployed/retracted
$status = CheckIfDeployed $solution.Name $webApplication
if (((!$solution.ContainsWebApplicationResource -and
($deploy -xor $solution.Deployed)) -or
($solution.ContainsWebApplicationResource -and
($deploy -xor ($status)))))
{
"We waited " + $count + " seconds for the solution to be " + $deployText + ". However, the PowerPivot solution is not yet " + $deployText + ". Please check whether SharePoint timer job is enabled. "
throw "Solution failed to " + $deployText + ", reason: " + ($solution.LastOperationDetails) + " at: " + ($solution.LastOperationEndTime)
}
"PowerPivot solution is successfully " + $deployText
}

function CheckIfDeployed($solutionName, $webAppName)
{
$solution = Get-SPSolution $solutionName -ErrorAction:SilentlyContinue
$webApp = Get-SPWebApplication $webAppName
foreach ($deployedWebApp in $solution.DeployedWebApplications)
{
if ($deployedWebApp.Id -eq $webApp.Id)
{
return $true
}
}
#write-host "$solutionName not deployed to $webAppName"
return $false;
}

The deployment of these solutions makes three features available in the farm, which can be installed as follows:

try
{
Install-SPFeature -path PowerPivot -force -Confirm:$false
Install-SPFeature -path PowerPivotAdmin -force -Confirm:$false
Install-SPFeature -path PowerPivotSite -force -Confirm:$false
}
catch
{
write-host "Error: $_"
}

In addition, activation of PowerPivotSite feature at the site collection level is necessary to make application pages and templates available to specific sites

Enable-SPFeature –identity "PowerPivotSite" -URL "<site collection URL>"

3. Register Engine and System Services

This step is critical in making PowerPivot functionality available in the SharePoint farm, but is missing from the list of manual steps on official MSDN documentation. Recall from the PowerPivot for SharePoint Architecture that an instance of the PowerPivot System Service exists on each server in the farm running Analysis Services in-memory engine, performing important functions (like monitoring server health, coordinating client requests for load balancing, collecting usage data, and performing automatic data refresh for PowerPivot workbooks). The PowerPivot system service works with Excel Services in SharePoint 2010 to extract the database from the Excel workbook, select an appropriate SharePoint application server running the Analysis Services service (preferably one that may already have the data loaded into memory), and then attaches the database to the Analysis Services instance.

To facilitate the above operation, all servers must have their instances of Analysis Services (“Engine” service) and PowerPivot “System” Service registered with the farm. The deployment of solutions and activation of features in the Step 2 simply registered the engine service and system service as a farm wide service, but no instances of these services have been registered in the farm yet. While creation of a PowerPivot Service Application will succeed at this point, there will be no Analysis Service instances available in the farm to serve a request. In Central Administration, in System Settings, click “Manage services on servers”, select the farm server(s) where Analysis Services was installed in Step 1 and verify that “SQL Server Analysis Services” or “SQL Server PowerPivot System Service” are not available to be started. Then run the following scripts on each of these servers to register their local instances of Engine and System service in the farm

$service = Get-PowerPivotEngineService
if ($service -eq $null)
{
Write-host "The PowerPivot Engine Parent Service is not registered in the farm: $_"
#installation error, do not proceed
}
$service = Get-PowerPivotSystemService
if ($service -eq $null)
{
Write-host "The PowerPivot System Parent Service is not registered in the farm: $_"
#installation error, do not proceed
}

try
{
$serviceinstance = Get-SPServiceInstance -Server "$ENV:COMPUTERNAME" | where { $_.TypeName -like "*Analysis Services*" } #Get-PowerPivotEngineServiceInstance
if ($serviceinstance -eq $null)
{
New-PowerPivotEngineServiceInstance -Provision:$true
Write-host "New PowerPivot Engine Service Instance created on "$ENV:COMPUTERNAME"
}
}
catch
{
Write-Host "The PowerPivot Engine Service Instance not created : $_"
}

try
{
$serviceinstance = Get-SPServiceInstance -Server "$ENV:COMPUTERNAME" | where { $_.TypeName -like "*PowerPivot*" } #Get-PowerPivotSystemServiceInstance
if ($serviceinstance -eq $null)
{
New-PowerPivotSystemServiceInstance -Provision:$true
Write-host "New PowerPivot System Service Instance created on "$ENV:COMPUTERNAME "
}
}
catch
{
Write-Host "The PowerPivot System Service Instance not created : $_"
}

After running this script, verify that in Central Administration, “SQL Server Analysis Services” and “SQL Server PowerPivot System Service” are now available to be started on the server(s) where the instances were registered, and start them (if not already started)

4. Create Service Application

To make the service available to clients, a PowerPivot service application (a shared service instance of the PowerPivot System Service) needs to be created in the farm. Important service parameters such as Connection Pool Timeout, and Unattended Data Refresh Account can be specified for the Service Application. This is accomplished by running the following script:

$serviceAppName = “<name>”
$dbServer = “<server name>”
$dbName = “<DB name>”
$saPowerPivot = New-PowerPivotServiceApplication -ServiceApplicationName $serviceAppName -DatabaseServerName $dbServer -DatabaseName $dbName -AddtoDefaultProxyGroup:$true