Wednesday, 2 April 2014

PowerView Issues following SQL 2012 SP1 CU9

Not so long ago, I helped to build a SharePoint 2013 farm for some testing and more recently, we (some colleagues and I) started to plan how we'd upgrade to SharePoint 2013 SP1 and SQL Server 2012 SP1 CU9.

The farm consists of two servers:
  1. server01 - SharePoint 2013 Web Front End and Application Server 
  2. server02 - SQL Server 2012, hosts config and content databases; other services include Database Services, SSAS, SSAS Tabluar etc. 
Unfortunately I didn't have the time to commit to installing the updates myself, but here's roughly what happened, according to the install log:
  1. server01
    1. SharePoint 2013 SP1 was installed
    2. Some post update cleanup tasks on server01; Disk cleanup, log trimming etc.
    3. Reporting Services Add-In for SharePoint installed (2012_SP1_RSShrPnt_CU9_2931078_11_0_3412_x64; rsSharePoint.msi) 
  2. server02
    1. SQL Server 2012 SP1 CU9 installed (SQLServer2012_SP1_CU9_2931078_11_0_3412_x64)
SQL Server 2012 SP1 CU9:

Once the updates were applied, a colleague committed to some testing. We were looking to be sure that some of the more complex Business Intelligence architectural elements were still operating as we'd expect them to.

That was not the case:
  • PowerView data retrieval failed
  • PowerPivot rendering failed
  • PowerPivot / PowerView previews were not working in the Carousel or Gallery mode
In order to determine what the issue was, I set about tackling these one by one - divide and
conquer style - starting with the PowerView data retrieval.

The Issue

In a document library, there's a .bism file which has been configured to connect to the AdventureWorks tabular database on an instance of Analysis Services (SSAS) on server02. When you click on the .bism file, a PowerView canvas opens and allows navigation of the data model. That works fine.

What doesn't work fine is attempting to add dimension columns to the canvas. This error would appear:
Full details of the error:

System.Exception: Error: Invalid token in RPDS stream. StringTable token: 0x12.
at Microsoft.ReportingServices.Rendering.RPDSProcessing.RPLReader.ReadStringTable(Int64 offset, RPLContext context) at Microsoft.ReportingServices.Rendering.RPDSProcessing.RPLReader.ReadVersionedReport(RPLReport report, RPLContext context)
at Microsoft.ReportingServices.Rendering.RPDSProcessing.RPLReader.ReadReport(RPLReport report, RPLContext context) at Microsoft.ReportingServices.Rendering.RPDSProcessing.RPLReport..ctor(BinaryReader reader) at Microsoft.Reporting.AdHoc.DataManagement.Internal.DataManager.PopulateRimWithRplData(Stream stream, HashSet`1 itemsToNotParse) at Microsoft.Reporting.AdHoc.DataManagement.Internal.DataManager.HandleRenderEditResponse(IAsyncEnumeratorCallback aec)


When trying to figure out what was wrong, I did the usual:
  • Check Event Logs - Nothing unusual here. IIS WAMREG Local Activation issue showed up. I fixed this to eliminate there being any possibility of it having an impact on this situation. (guide:
  • Checking of SharePoint ULS logs - SharePoint appeared to be calling the Reporting Services endpoint just fine. Although there was an exception at the time of data retrieval:

    Error occurred while canceling the command for DataSet 'Tablix1DataSet'. Details: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.Connect() at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.CancelCommand(AdomdConnection originalConnection) at Microsoft.ReportingServices.DataExtensions.CommandWrapper.Cancel() at Microsoft.ReportingServices.Diagnostics.DataExtensionConnectionBase.HandleImpersonation(IProcessingDataSource dataSource, DataSourceInfo dataSourceInfo, String datasetName, IDbConnection connection, Action afterImpersonationAction) at Microsoft.ReportingServices.ReportProcessing.CommandWrappedForCancel.Cancel() at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.CancelCommand() bb43839c-cec4-50c6-4146-1d1167b52b19

    As it was different to the original issue, I noted it down and progressed with the original error. 
  • Run SQL Profiler - I captured the communication between PowerView and the SQL Server SSAS Tabular instance. I even copied the query and went and executed it separately to be sure there was no issue. Again, all fine:
  • Further head scratching...

So what next, what could I be missing?

After some more looking around and searching online, it got me thinking about whether all of the SQL Server 2012 Shared Components on the SharePoint server were updated or not.

Then a light-bulb moment: didn't the installation report say that that rsSharePoint.msi add in was installed/updated on the SharePoint server? Sure... But what about the other SQL Server Shared Components?

Sure enough, when I ran the SQL Server 2012 SP1 CU9 update package on the SharePoint server, it reported that the 'Upgrade Status' of all of the components was 'Not Installed'.


I noted down the patch level, hit next a few times and let the update package do it's thing.

Once complete, I ran the package again and this time the 'Upgrade Status' reported 'Installed' for the PowerPivot and the Shared Features. The Patch Level had also changed from 11.1.3128.0 to 11.1.3412.0.
I also went and tested the issues that we'd previously experienced - most had been resolved. One or two were still present and waiting on timer jobs to execute in order to fix.

Why did you run the SQL Server 2012 SP1 CU9 update on a SharePoint server?

It's very simple: there are SQL Server Shared Features that must be upgraded too. It's not enough to install only the Reporting Services Add-In for SharePoint.

In short, in this CU9 Scenario, SQL Server patching that should be applied to SharePoint 2013 is as follows:
  • SP Web Front Ends
    • 2012_SPx_RSShrPnt_CUxx_KBxxxxx_11_00_xxxx_arch
    • Applies only to WFE Servers that wish to integrate with Reporting Services (adds pages, features and content types) 
  • SP App Servers
    • SQLServer2012_SPx_CUxx_kbxxxxxx_11_00_xxxx_Arch
    • Applies only to servers running PowerPivot for SharePoint and any additional Shareds Features such as Reporting Services in SharePoint Mode 
  • SP Web Front End & App Server
    • Install/update all that would apply; in this case, both of the above packages applied.
  • SQL Server - SQL Server Software Update Package
    • SQLServer2012_SPx_CUxx_kbxxxxxx_11_00_xxxx_Arch
    • ...and any other additional CU update package that applies to the services you have installed.
SQL Server Package Naming Convention:

The message here is simple.... The landscape has become rather complex, what with the many interfaces between SharePoint and SQL Server now. So make sure you're updating all of the SQL Server Shared Features as well as core SQL Services!