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.