All About PowerPivot for SharePoint 2016

The purpose of this post is to aggregate helpful links for SharePoint 2016 PowerPivot, to make it easier for anyone to understand the service use/deployment and configurations. Please add your comments with other helpful links and I will update the post as needed.

Overview:

Microsoft SQL Server PowerPivot for SharePoint extends SharePoint 2016 and Office Online Services to add server-side processing, collaboration, and document management support for the PowerPivot workbooks that you publish to SharePoint.
PowerPivot workbooks are Excel workbook (.xslx) files that contain large, related datasets that you build in a separate PowerPivot window in Excel, and use with PivotTables and PivotCharts in a worksheet.

Prerequisites:

• SharePoint Server enterprise edition is required for Power Pivot for SharePoint. You can also use the evaluation enterprise edition.
• The Power Pivot instance name must be available. The instance name must be POWERPIVOT.
• Office Online Server (Need steps on how to deploy and configure, press here and here).

Deployment:

1- Install SQL Server Analysis Services in PowerPivot Mode):
        https://msdn.microsoft.com/en-us/library/jj219067.aspx (White Paper)
2- Install PowerPivot for SharePoint add-in:
        https://msdn.microsoft.com/en-us/library/mt595853.aspx

Configuration:

  Configure PowerPivot solutions and services:
        https://msdn.microsoft.com/en-us/library/mt595855.aspx

Usage:

Nice article by Tom S. on What is PowerPivot for SharePoint (part 1 and part 2).

Promote site to site collection using Powershell

Overview:

In many cases, end users keep creating sub sites and uploading content to an existing site , and the system admin discover that the Content Database size reached the recommended values of Microsoft, in such cases, admin need to take corrective actions to keep the farm running. 

One of the options is to promote some sub-sites to their own site collections and split the site collections across different content Databases.

The script below perform the basic operations to promote a site to a site collection. The script is just a guide to help you in the process, use it at your own risk, and after proper testing.

Script:

#Update the values for the 4 variables below

$oldSPwebUrl="http://server/exporttest"
$exportPath="C:\export\export.cmp"
$Owner="domain\administrator"
$Descr="Test Description"

#Do not change after this line
$oldSPWeb = Get-SPWeb $oldSPwebUrl
$siteRelativeURL = $OldSPWeb.ServerRelativeUrl.trim('/')
$SPWebApp = $oldSPWeb.Site.WebApplication

#Export site
Write-Host "Exporting Site :"$oldSPwebUrl
Export-SPWeb $oldSPwebUrl -Path $exportPath -IncludeUserSecurity -IncludeVersions All -Force

#Change current site URL
Write-Host "Changing Site URL to:"$siteRelativeURL"_org"
Get-SPWeb $oldSPwebUrl | Set-SPWeb -RelativeUrl $siteRelativeURL"_org"

#Create managed Path(Explicit)
Write-Host "Creating managed path:"$siteRelativeURL
New-SPManagedPath -RelativeURL $siteRelativeURL -WebApplication $SPWebApp -Explicit

#Create new site
Write-Host "Creating site collection:"$oldSPwebUrl
$newSPSite = New-SPsite -Url $oldSPwebUrl -OwnerAlias $Owner -Template "STS#0" -Description $Descr
$newSPWeb  = $newSPSite.RootWeb

#Import content
Write-Host "Importing content to site collection:"$oldSPwebUrl
Import-SPweb $newSPWeb -Path $exportPath

Write-Host "Completed."

Notes:

1- The script does not delete the original site, after completing your testing, you can delete it manually or through PowerShell.

Limitations:

1- Alerts are not migrated,

2- Workflows are not migrated.

3- Works only on sites on root site collection under first level, it needs to be altered if sites are in different level, or to be moved to different URL.
4- Navigation settings(top/left) might get affected by the promotion, so, you need to manually check it and update as needed.

Override Author/Creator Simplified

If you don't like long posts, go the the Conclusion section.

In many situations, we are requested to change some items/documents related properties, in general this can be done using the SharePoint user Interface, or using code (either server side code, Power Shell, or CSOM).

Modifying system related properties (ex. Author, Editor, Created , Modified) is not that simple, it should be handled in a different manner, and can be done only through code.

This post will describe the different options based on the list/document library configuration.

First, let us document the 4 properties that we are going to try to update:
SPListItem[SPBuiltInFieldId.Author]
SPListItem[SPBuiltInFieldId.Editor]
SPListItem[SPBuiltInFieldId.Created]
SPListItem[SPBuiltInFieldId.Modified]

Second, let us document the different options to update a SPListItem from code:
SPListItem.Update()
SPListItem.UpdateOverwriteVersion()
SPListItem.SystemUpdate()

Third, we will try to to use the different options, and check the outcome of each.

Update()

The code snippet below tries to override the 4 properties, and uses the Update() of the list item, the outcome of this test ends up as follows:

SPListItem[SPBuiltInFieldId.Author] ==> Changed as requested
SPListItem[SPBuiltInFieldId.Editor] ==> Changed as requested
SPListItem[SPBuiltInFieldId.Created] ==> Changed as requested
SPListItem[SPBuiltInFieldId.Modified] ==> Changed as requested

Notes:
1- If SPListItem[SPBuiltInFieldId.Editor] was removed, it was noticed that the SPListItem[SPBuiltInFieldId.Author] was not changed.
2- If the list/library has content approval enabled, the item/document is changed to "Pending".
3- SPListItem[SPBuiltInFieldId.Modified] must be greater than or equal to the SPListItem[SPBuiltInFieldId.Created], else it will be set to DateTime.Now by SharePoint.
4- If SPListItem[SPBuiltInFieldId.Modified] was not populated from code, it will have the default value of DateTime.Now  by SharePoint.

public void Update(SPFieldUserValue oUser, SPListItem item)
        {
            item[SPBuiltInFieldId.Author] = oUser;
            item[SPBuiltInFieldId.Editor] = oUser;
            // use line below instead if only Author is required to be changed
            //item[SPBuiltInFieldId.Editor] = item[SPBuiltInFieldId.Editor];

            item[SPBuiltInFieldId.Created] = DateTime.Now;  
            item[SPBuiltInFieldId.Modified] = DateTime.Now;
            
            item.Update();
        } 

UpdateOverwriteVersion()

The code snippet below tries to override the 4 properties, and uses the UpdateOverwriteVersion() of the list item, the outcome of this test ends up as follows:

SPListItem[SPBuiltInFieldId.Author] ==> Changed as requested
SPListItem[SPBuiltInFieldId.Editor] ==> Changed as requested
SPListItem[SPBuiltInFieldId.Created] ==> Changed as requested
SPListItem[SPBuiltInFieldId.Modified] ==> Changed as requested

Notes:
1- If SPListItem[SPBuiltInFieldId.Editor] was removed, it was noticed that the SPListItem[SPBuiltInFieldId.Author] was not changed.
2- If the list/library has content approval enabled, the item/document is changed to "Pending".
3- SPListItem[SPBuiltInFieldId.Modified] must be greater than or equal to the SPListItem[SPBuiltInFieldId.Created], else it will be set to DateTime.Now by SharePoint.
4- If SPListItem[SPBuiltInFieldId.Modified] was not populated from code, it will have the default value of DateTime.Now  by SharePoint.

 public void UpdateOverrideVersion(SPFieldUserValue oUser, SPListItem item,DateTime dtStamp)
        {
            item[SPBuiltInFieldId.Author] = oUser;
            item[SPBuiltInFieldId.Editor] = oUser;
            // use line below instead if only Author is required to be changed
            //item[SPBuiltInFieldId.Editor] = item[SPBuiltInFieldId.Editor];

            item[SPBuiltInFieldId.Created] = dtStamp;
            item[SPBuiltInFieldId.Modified] = dtStamp;

            item.UpdateOverwriteVersion();
        }

SystemUpdate()

The code snippet below tries to override the 4 properties, and uses the SystemUpdate() of the list item, the outcome of this test ends up as follows:

SPListItem[SPBuiltInFieldId.Author] ==> Changed as requested
SPListItem[SPBuiltInFieldId.Editor] ==> No Effect
SPListItem[SPBuiltInFieldId.Created] ==> No Effect
SPListItem[SPBuiltInFieldId.Modified] ==> No Effect

Notes:
1- If SPListItem[SPBuiltInFieldId.Editor] was removed, it was noticed that the SPListItem[SPBuiltInFieldId.Author] was not changed.
2- If the list/library has content approval enabled, the item/document remain on its state. (ex. Approved)
3- Editor, Created, Modified fields were not affected by the update.

 public void SystemUpdate(SPFieldUserValue oUser, SPListItem item)
        {
            item[SPBuiltInFieldId.Author] = oUser;
            item[SPBuiltInFieldId.Editor] = oUser;  //No Effect, if removed, Author will not be updated

            item[SPBuiltInFieldId.Created] = DateTime.Now;  //No Effect
            item[SPBuiltInFieldId.Modified] = DateTime.Now; //No Effect
        
            //system update will only allow changing the  Author, even if other fields were set to other values
            item.SystemUpdate(false);
        }

Revised SystemUpdate() method should look like:

public void RevisedSystemUpdate(SPFieldUserValue oUser, SPListItem item)
        {
            item[SPBuiltInFieldId.Author] = oUser;
            item[SPBuiltInFieldId.Editor] = item[SPBuiltInFieldId.Editor];  //No Effect, if removed, Author will not be updated

            //system update will only allow changing the  Author, even if other fields were set to other values
            item.SystemUpdate(false);
        }

Conclusion

1- Use SystemUpdate() if you want to override the Author only, and maintain the item Approval state.

2- Set Editor to current value even if you don't want to override it, this will allow SharePoint to update the Author property.

3- Update() and UpdateOverwriteVersion() will change approval state to Pending, you might need to check if the item was already approved, and re-approve it as needed.