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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
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.