SharePoint 2013 – Create PowerPivot Service Application

2017-12-11T16:14:05+00:00 August 4th, 2014|PowerShell, Sharepoint|

Ok, so you’re deploying PowerPivot for SharePoint 2013 and using the configuration tool? It does a great job, but I do have one niggle with it. During the Service Application creation step, you are not given the opportunity to change the database name. While this may not be a concern for some, many organisations (rightly so) have a naming convention for their SharePoint databases. Additionally, if the database backup files are located in the default path, combined with the default database name, it is entirely possible that the directory length is too long, causing your backups to fail! Consider this:

  • [drive]:Program FilesMicrosoft SQL ServerMSSQL11.[InstanceName]MSSQLBackupDefaultPowerPivotServiceApplicationDB-c6324718-66bb-4344-9ed3-4f0496feff4e

longdbname

What a mess!

So what can we do about this? Well the answer is rather nicely handed to us within the configuration tool. If you click the “Script” tab on the “Create Service Application” step, you will see the PowerShell cmdlet for precisely what we want. Obviously, you just need untick the “include this action in the task list” box and swap out the variables to suit your environment, but it couldn’t be easier. First, create the Service Application:

New-PowerPivotServiceApplication -ServiceApplicationName 'PowerPivot' -DatabaseServerName 'SharePointSQL' -DatabaseName 'SP2013_ServiceApplication_PowerPivot' -AddToDefaultProxyGroup:$true

Once you receive the “A PowerPivot service application was created successfully” confirmation, execute the next cmdlet:

Set-PowerPivotSystemService -Confirm:$false

Finally, you will receive the “PowerPivot system service instance was successfully updated” confirmation. Easy!