Supporting Multiple PowerPivot Versions
Since the release of SQL Server 2012, users can choose between two different versions of PowerPivot for Excel: SQL Server 2008 R2 and SQL Server 2012. Both PowerPivot versions are free of charge, but the 2012 add-in includes more features than its predecessor and offers several improvements to the user interface, so it’s reasonable to assume that users would want to upgrade. However, not all users are rushing at once to the Microsoft Download Center. Some are perfectly happy with the 2008 R2 add-in, others might not even be aware that a 2012 version exists, and yet others might want to upgrade but can’t because they don’t have permissions to install software on their workstations. Whatever the reason, if your users are working with different PowerPivot add-ins, they might encounter interoperability issues on the desktop and in SharePoint environments.
On the desktop, the 2008 R2 and 2012 PowerPivot add-ins are not 100% compatible. The 2012 version can open a 2008 R2 PowerPivot workbook. You can interact with the data, such as by clicking on slicers, but when you try to display the underlying data tables in the PowerPivot window, you are prompted to upgrade the model, as in the following screenshot. The 2012 PowerPivot add-in cannot simply modify a 2008 R2 model because this would introduce incompatible database features. So, the 2012 add-in must first upgrade the model, which can take some time depending on the size of the workbook.
Having to wait for an upgrade to finish is inconvenient, but more importantly, if you decide to save your upgrade changes, you can no longer share your workbook with 2008 R2 users. These users can still open this workbook, but they cannot interact with the data anymore. Clicking on a slicer produces an error that the initialization of a data source failed, and trying to open the PowerPivot window results in another error, shown in the following screenshot. Because the 2008 R2 add-in is not forward compatible, it cannot open the upgraded model. You must now use the 2012 PowerPivot add-in. Downgrading a model is not an option.
In SharePoint, interoperability is likewise limited. SQL Server 2008 R2 PowerPivot for SharePoint does not support workbooks created with the 2012 version of PowerPivot for Excel because the 2008 R2 Analysis Services instance running on the PowerPivot application server cannot load a 2012 model. If you upload a 2012 workbook to SharePoint and try to interact with it in the browser, you receive an error that the system cannot refresh the data for a data connection, and the ULS log on the server might show a ConnectionInfoException, as in the following screenshot. So, make sure you upgrade your farms to SQL Server 2012 PowerPivot for SharePoint if your users want to upload and share 2012 PowerPivot workbooks.
Unfortunately, SQL Server 2012 PowerPivot for SharePoint isn’t free of challenges either when it comes to different workbook versions. For starters, make sure you install the Analysis Services OLE DB Provider (MSOLAP) from the SQL Server 2008 R2 SP1 Feature Pack on your application servers running Excel Calculation Services (ECS). Otherwise, you might find that ECS has trouble loading 2008 R2 workbooks. You might also need the MSOLAP provider from the SQL Server 2012 Feature Pack to load 2012 PowerPivot workbooks. SQL Server 2012 Setup installs this MSOLAP version automatically with PowerPivot for SharePoint, but if you run PowerPivot on separate application servers you must install the 2012 MSOLAP provider on your ECS servers explicitly. Note that you also need to install SQL Server 2012 Management Tools on your application servers because of an XMLA assembly dependency introduced with the 2012 version of MSOLAP. For details, check out the MSDN article “Install the Analysis Services OLE DB Provider on SharePoint Servers” at https://technet.microsoft.com/en-us/library/bb500210(v=SQL.110).aspx
Why do you need multiple MSOLAP versions? The reason is that Excel 2010 registers version-specific MSOLAP references in the data connections of your PowerPivot workbooks. As the following screenshot reveals, data connections in 2008 R2 PowerPivot workbooks point to MSOLAP.4 and data connections in 2012 workbooks point to MSOLAP.5. MSOLAP.4 is the 2008 R2 data provider. MSOLAP.5 is the 2012 data provider. ECS recognizes the references and expects to find the corresponding provider versions on the application server. If the specified provider is not installed, ECS cannot establish the data connection and cannot load the model.
With the 2008 R2 and 2012 MSOLAP providers installed on your ECS application servers in a 2012 PowerPivot farm, you can load and interact with 2008 R2 and 2012 PowerPivot workbooks in the browser. SQL Server 2012 PowerPivot for SharePoint also supports the Workbooks as a Data Source feature for both workbook versions. However, the Scheduled Data Refresh feature is only available for 2012 PowerPivot workbooks. For the same reasons that the 2012 PowerPivot add-in for Excel cannot modify a 2008 R2 model on the client, the 2012 version of PowerPivot for SharePoint cannot refresh a 2008 R2 model on the server. If you haven't enabled AutoUpgrade, you must first upgrade the model in the Excel client to the 2012 version before Scheduled Data Refresh can succeed.
By default, 2012 PowerPivot for SharePoint does not automatically upgrade 2008 R2 workbooks, but it’s possible to change this behavior by enabling AutoUpgrade. You can select the option to Upgrade workbooks to enable data refresh in the PowerPivot Configuration Tool. You can find this option on the Create PowerPivot Service Application page for new installations or on the Upgrade PowerPivot System Service page for upgrades from 2008 R2 PowerPivot for SharePoint, as in the following screenshot. You can also set this option by using the Set-PowerPivotSystemService cmdlet. The following command enables the AutoUpgrade feature:
Set-PowerPivotSystemService –WorkbookUpgradeOnDataRefresh:$true
One word of caution, though. Do not enable AutoUpgrade without first preparing your users. Here at Microsoft, AutoUpgrade was enabled in one of the main farms without further notification and then helpdesk received numerous calls from users complaining about corrupted workbooks. Of course, the workbooks were perfectly fine, they were merely auto-upgraded to the 2012 version when Scheduled Data Refresh ran, so the 2008 R2 add-in could no longer load the models, as explained earlier. Upgrading the clients to the 2012 version of PowerPivot for Excel solved the issue, but it still was an unpleasant experience for users and helpdesk. It doesn’t have to be this way. Make sure your users upgrade their clients before you enable AutoUpgrade in PowerPivot for SharePoint. On the other hand, if you cannot ensure that your clients are ready, don’t enable this feature. Scheduled Data Refresh will fail for 2008 R2 workbooks in this case, but users can still download, upgrade, and re-upload their workbooks individually to work around this issue. In a way, this approach helps to accelerate 2012 add-in adoption because upgrading a workbook requires the user to have the 2012 PowerPivot add-in for Excel installed.
Putting it all together, perhaps the best approach to move to SQL Server 2012 PowerPivot across an organization is to focus on upgrading SharePoint farms first without enabling AutoUpgrade. Don’t forget to inform your users that Scheduled Data Refresh no longer succeeds for existing workbooks after the upgrade unless they install the 2012 PowerPivot add-in on their desktops and upgrade their workbooks manually. Upgrading your farms to 2012 PowerPivot ensures that 2008 R2 and 2012 users can share their workbooks in SharePoint. Now, you can focus on upgrading the client base, and then you can optionally enable AutoUpgrade to unblock Scheduled Data Refresh for all remaining old workbooks in your farms. Keep in mind, however, that workbooks are only upgraded as part of the data refresh process, so workbooks that don't participate in Scheduled Data Refresh are not affect by AutoUprade and remain 2008 R2 workbooks.
In any case, the move from 2008 R2 to 2012 PowerPivot isn’t frictionless. At times, you might encounter an issue and then it is useful to verify the workbook version. This is tricky because 2008 R2 and 2012 PowerPivot workbooks look alike and error messages are sometimes vague, such as “Unable to refresh the data for a data connection.” Fortunately, you can determine the PowerPivot version of a workbook with relatively little effort, as the following PowerShell script function demonstrates, which I created with the help of developer Leonid Lyakhovitskiy. Thanks Leonid for clarifying where exactly the PowerPivot add-ins store their version information.
# The GetPowerPivotVersion function returns the
# the build number of the Microsoft.Office.PowerPivot.ExcelAddIn.dll
# that was last used to save the PowerPivot model.
# For example:
# 10.50.1600.1 corresponds to SQL Server 2008 R2 RTM PowerPivot for Excel 2010.
# 11.0.2100.60 corresponds to SQL Server 2012 RTM PowerPivot for Excel 2010.
# 00.0.0000.00 indicates that the specified workbook is not a PowerPivot workbook.
Function GetPowerPivotVersion($fileName)
{
# Initially, assume this isn't a PowerPivot workbook.
# i.e. there is no PowerPivotVersion.
$ppVersion = "00.0.0000.00"
try
{
# Start Excel and open the workbook.
$xlApp = New-Object -comobject Excel.Application
$wbk = $xlApp.Workbooks.Open($fileName)
try
{
# Retrieve the version info from the PowerPivotVersion custom XML part.
$xlPart = $wbk.CustomXMLParts.SelectByNamespace("https://gemini/workbookcustomization/PowerPivotVersion")
$version = $xlPart.Item(1).SelectSingleNode("//ns0:CustomContent")
$ppVersion = $version.Text
}
catch
{
try
{
# The PowerPivotVersion custom XML part was not found.
# Check the SandboxNonEmpty value to determine if this is a 2008 R2 RTM workbook.
$xlPart = $wbk.CustomXMLParts.SelectByNamespace("https://gemini/workbookcustomization/SandboxNonEmpty")
$nonEmpty = $xlPart.Item(1).SelectSingleNode("//ns0:CustomContent")
if($nonEmpty.Text -eq "1")
{
# SandboxNonEmpty value is 1, so this is a 2008 R2 RTM workbook.
$ppVersion = "10.50.1600.1"
}
}
catch
{ # SandboxNonEmpty not found = plain workbook.
# Just suppress the exception...
}
}
# Close the workbook and quit Excel
$wbk.Close($false)
$xlApp.Quit()
}
catch
{
Write-Error $_
}
#return the results
return $ppVersion
}
The GetPowerPivotVersion function starts Excel, loads the specified workbook, and then reads the version information from a PowerPivotVersion custom XML part. If it is found, its value is returned—but note that there is one special case: Original 2008 R2 PowerPivot workbooks don’t contain this PowerPivotVersion part. So, if the version info cannot be found, GetPowerPivotVersion checks another custom XML part called SandboxNonEmpty to see if a PowerPivot model exists. If it does, the workbook is a 2008 R2 PowerPivot workbook and the function returns the version of the 2008 R2 RTM add-in. Otherwise, it’s a plain Excel workbook without a PowerPivot model and the function returns 00.0.0000.00.
And that’s it. GetPowerPivotVersion works with file paths as well as workbook URLs if you have the Desktop Experience installed on your workstation (https://technet.microsoft.com/en-us/library/cc754314.aspx). Of course, this script function is not super-fast or optimized for bulk-operations because it launches Excel for every file, but you could use it to iterate through a collection of workbooks in a document library. Here’s another script function that does just that and a screenshot of a test run in the PowerShell Integrated Scripting Environment (ISE).
# Iterates through the specified document library in the referenced site
# and returns the PowerPivot version for all workbooks in this library
# in form of a hashtable.
Function GetWorkbookVersions($siteUrl, $docLib)
{
# Create an empty hashtable
$wbkInfo = @{}
try
{
# Instantiate the SharePoint Lists Web Service
$uri = $siteUrl + "/_vti_bin/Lists.asmx?wsdl"
$listsSvc = New-WebServiceProxy -Uri $uri -UseDefaultCredential
# Create the request to retrieve all the default fields
$xmlReq = New-Object -TypeName System.Xml.XmlDocument
$viewFields = $xmlReq.CreateElement("ViewFields")
# Get the list of all the items in the document library
$nodeListItems = $listsSvc.GetListItems($docLib, $null, $null,
$viewFields, $null, $null, $null)
# Iterate through the items in the list
foreach($item in $nodeListItems.data.row)
{
# Make sure we are dealing with a workbook
if($item.ows_DocIcon -eq "xlsx")
{
# Get the PowerPivot version and add it
# together with the workbook URL to the hashtable
$wbkInfo[$item.ows_EncodedAbsUrl] = GetPowerPivotVersion $item.ows_EncodedAbsUrl
}
}
}
catch
{
Write-Error $_
}
# Return the results
return $wbkInfo
}
As you can see, the version numbers in my document library are quite all over the place, which is not astonishing because I kept updating my PowerPivot add-in during the course of the development work. I hope you’ll find these script functions helpful. As always, keep in mind that the code has not been sufficiently tested, so don’t use it in a production environment. Feel free to modify the code according to your needs and use it at your own risk.