Automatically building a Microsoft BI machine using PowerShell – Configuring PowerPivot (post #13)
This post is #13 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.
In this series so far:
Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service
Post #4 –Preparation: logging infrastructure
Post #5 – Master script
Post #6 – Disabling Internet Explorer Enhanced Security Configuration
Post #7 – Active Directory setup
Post #8 – Configuring Password policy
Post #9 – Installing System Center Endpoint Protection
Post #10 – Installing SQL Server
Post #11 – Installing SharePoint Server
Post #12 – Installing PowerPivot for SharePoint
Now that PowerPivot for SharePoint has been installed, we need to configure it. I split the configuration into two parts since we need a reboot in between and used MSDN for reference: http://msdn.microsoft.com/en-us/library/hh230903.aspx.
Step A: configuring SharePoint and deploying PowerPivot features
In Post #11 we talked about installing SharePoint, but the actual SharePoint provisioning was not done then. We will do it here in one go with installing PowerPivot features.
Function ConfigurePowerPivot { Param( [Parameter(Mandatory=$true,HelpMessage="Passphrase required")] [ValidateNotNullOrEmpty()] $passphrase, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] $Password ) Write-Host "Step 8: Configure PowerPivot" try { #Load Configure PowerPivot ps1 $scriptPath = Split-Path -parent $global:script . ('C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\ConfigurePowerPivot.ps1') #Create a user for SharePoint DB connection #if required, remove the ad user Get-ADUser -Filter {Identity -eq '$global:spAccount'} | Remove-ADUser CreateServiceAccount -AccountName $global:spAccount -DisplayName "SharePoint Farm account" -Description "Account for SharePoint Farm" -Path $global:path -Password $Password $spAccountFQ = $global:domainpart+"\"+$global:spAccount $pwd = convertto-securestring $Password -asplaintext -force & "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\BIN\psconfig.exe" -cmd configdb -create -server $global:HostName -database 'SharePoint_Config' -user $spAccountFQ -password $Password -passphrase $passphrase -admincontentdatabase 'SharePoint_Admin' -cmd helpcollections -installall -cmd secureresources -cmd services -install -cmd installfeatures -cmd adminvs -provision -port 2000 -windowsauthprovider onlyusentlm -cmd applicationcontent -install -cmd quiet Add-PSSnapin Microsoft.SharePoint.PowerShell Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\powerpivotfarmsolution.wsp' Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\PowerPivotFarm14Solution.wsp' Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\powerpivotwebapplicationsolution.wsp' DeployFarmSolution $false DeployWebAppSolutionToCentralAdmin $false Install-SPFeature -path PowerPivotFarm -Force Install-SPFeature -path PowerPivotFarm -Force -CompatibilityLevel 14 Install-SPFeature -path PowerPivotCA -Force InstallSiteCollectionFeatures Write-Host "PowerPivot Part 1 Configured. Computer needs to be restarted before PowerPivot configuration can continue." -ForegroundColor Green if ($global:DoAllTasks) { Set-Restart-AndResume $global:script "9" } } catch { Write-Host "Failed to configure PowerPivot. Error: $_.Exception.Message" -ForegroundColor Red } }
Step B: updating farm credentials and starting service applications
After the PowerPivot features have been deployed we need to configure Service Applications to get PowerPivot to work.
Function ConfigurePowerPivotPart2 { Param( [Parameter(Mandatory=$true,HelpMessage="Passphrase required")] [ValidateNotNullOrEmpty()] $passphrase, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] $Password ) try { #Load Configure PowerPivot ps1 $scriptPath = Split-Path -parent $global:script . ('C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\ConfigurePowerPivot.ps1') Add-PSSnapin Microsoft.SharePoint.PowerShell Write-Host "DEBUG: updating Farm Credentials" $spAccountFQ = $global:domainpart+"\"+$global:spAccount stsadm.exe -o updatefarmcredentials -userlogin $spAccountFQ -password $Password Write-Host "DEBUG: New-PowerpivotSystemServiceInstance" New-PowerPivotSystemServiceInstance -Provision:$true Write-Host "DEBUG: New-PowerPivotServiceApplication" New-PowerPivotServiceApplication -ServiceApplicationName 'PowerPivot Service Application' -DatabaseServerName $global:HostName -DatabaseName 'PowerPivotServiceApplication' -AddToDefaultProxyGroup:$true Write-Host "DEBUG: Set-PowerPivotSystemService" Set-PowerPivotSystemService -Confirm:$false Write-Host "DEBUG: Creating user DefAppPool" $appAccountName = "DefAppPool" $appAccountNameFQ = $global:domainpart+"\"+$appAccountName CreateServiceAccount -AccountName $appAccountName -DisplayName "Default Application Pool" -Description "Service Account for Default Application Pool" -Path $global:path -Password $Password Write-Host "DEBUG: CreateWebApplication" CreateWebApplication 'SharePoint - 80' $global:HostName 'Default Application Pool' $appAccountNameFQ $pwd $global:HostName 'DefaultWebApplication' Write-Host "DEBUG: DeployWebAppSolution" DeployWebAppSolution $global:httpHostName 2047 $false Write-Host "DEBUG: New-SPSite" New-SPSite -Url $global:httpHostName -OwnerEmail 'me@example.com' -OwnerAlias $global:currentUserName -Template 'PowerPivot#0' -Name 'PowerPivot Site' Write-Host "DEBUG: EnableSiteFeatures" EnableSiteFeatures $global:httpHostName $true Write-Host "DEBUG: StartService SPWindowsTokenServiceInstance" StartService "Microsoft.SharePoint.Administration.Claims.SPWindowsTokenServiceInstance" Write-Host "DEBUG: StartSecureStoreService" StartSecureStoreService Write-Host "DEBUG: CreateSecureStoreApplicationService" CreateSecureStoreApplicationService $global:HostName 'Secure Store Service' Write-Host "DEBUG: CreateSecureStoreApplicationServiceProxy" CreateSecureStoreApplicationServiceProxy 'Secure Store Service' 'Secure Store Proxy' Write-Host "DEBUG: UpdateSecureStoreMasterKey" UpdateSecureStoreMasterKey 'Secure Store Proxy' $passphrase Write-Host "DEBUG: CreateUnattendedAccountForDataRefresh" CreateUnattendedAccountForDataRefresh $global:httpHostName 'PowerPivotUnattendedAccount' 'PowerPivot Unattended Account for Data Refresh' $spAccountFQ $pwd Write-Host "DEBUG: StartService ExcelServerWebServiceInstance" StartService "Microsoft.Office.Excel.Server.MossHost.ExcelServerWebServiceInstance" Write-Host "DEBUG: New-SPExcelServiceApplication" New-SPExcelServiceApplication -name 'ExcelServiceApp1' -Default -ApplicationPool 'SharePoint Web Services System' | Get-SPExcelServiceApplication | Set-SPExcelServiceApplication | iisreset Set-SPExcelFileLocation -ExternalDataAllowed 2 -WorkbookSizeMax 200 -WarnOnDataRefresh:$false -ExcelServiceApplication 'ExcelServiceApp1' -identity 'http://' Write-Host "DEBUG: AddExcelBIServer" AddExcelBIServer Write-Host "DEBUG: SetECSUsageTracker" SetECSUsageTracker 'ExcelServiceApp1' Write-Host "PowerPivot Configured" -ForegroundColor Green if ($global:DoAllTasks) { Set-Restart-AndResume $global:script "10" } } catch { Write-Host "Failed to configure PowerPivot. Error: $_.Exception.Message" -ForegroundColor Red } }
Now we have seen all the steps required to build a Microsoft BI demo machine! The next post will serve as a wrap up and present a download for the full script.
Share this:
- Click to share on LinkedIn (Opens in new window)
- Click to share on Facebook (Opens in new window)
- Click to share on Twitter (Opens in new window)
- Click to share on Skype (Opens in new window)
- Click to share on WhatsApp (Opens in new window)
- Click to share on Pocket (Opens in new window)
- Click to share on Tumblr (Opens in new window)
- Click to share on Pinterest (Opens in new window)
- Click to share on Telegram (Opens in new window)
- Click to share on Reddit (Opens in new window)
- Click to print (Opens in new window)
- Click to email this to a friend (Opens in new window)
Related
You May Also Like

Working with aggregations in Power BI Desktop
April 23, 2020
Azure SQL firewall settings for Power BI refresh
April 28, 2020