Automatically building a Microsoft BI machine using PowerShell – Installing PowerPivot for SharePoint (post #12)

This post is #12 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

Ok, now that both SQL Server and SharePoint Server are installed, we just need to set up PowerPivot for SharePoint and configure it. Easy huh? Well, it turns out it is pretty difficult to get it right. Installation is not difficult (this post) but the configuration is harder (the next post). Here is how to install PowerPivot. I used MSDN for the info: http://msdn.microsoft.com/en-us/library/ee210645.aspx.

Installing PowerPivot involves mounting the SQL Server Installation Media and calling the setup with the right parameters.

Function InstallPowerPivot
{
Param(
        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        $Password
)
    Write-Log -Verbose  "Step 7: Install PowerPivot"
    #MOUNT SQL ISO
    $mountresult = Mount-DiskImage -ImagePath $global:pathToSQLISO -PassThru
    $driveLetter = ($mountresult | Get-Volume).DriveLetter
    $setupFile = $driveLetter+":\setup.exe"
    #Remove Service Account if it already existed
    Get-ADServiceAccount -Filter {Name -eq 'PP'} | Remove-ADServiceAccount
    $ppAccountName = "PP"
    $ppAccountNameFQ = $global:domainpart+"\"+$ppAccountName
    CreateServiceAccount -AccountName $ppAccountName -DisplayName "PowerPivot" -Description "Service Account for PowerPivot for SharePoint" -Path $global:path -Password $Password
    #do PP installation
    #trying with plain text pwd in call
    $process = Start-Process -NoNewWindow -Wait $setupFile -ArgumentList "/ACTION=INSTALL /IACCEPTSQLSERVERLICENSETERMS /Q /INSTANCENAME=POWERPIVOT /ERRORREPORTING=1 /SQMREPORTING=1 /ASSVCACCOUNT=$ppAccountNameFQ /ASSVCPASSWORD=$Password /ASSYSADMINACCOUNTS=$global:currentUserName /ROLE=SPI_AS_ExistingFarm"
    #SPI_AS_ExistingFarm
    
    #dismount
    Dismount-DiskImage -ImagePath $global:pathToSQLISO
    Write-Log -Verbose  "If above an error is shown please check out C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\Summary.txt"
    Write-Log -Verbose  "PowerPivot Installed"
    if ($global:DoAllTasks) {
        Set-Restart-AndResume $global:script "9"
        }
}

Next time: configuring PowerPivot.