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: http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2931078&kbln=en-us

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)

Investigating

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: http://www.wictorwilen.se/Post/Fix-the-SharePoint-DCOM-10016-error-on-Windows-Server-2008-R2.aspx) 
• 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'.

Bingo!

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: http://support.microsoft.com/kb/822499

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!

Take a photograph, make it searchable

The digital workplace has had the ability to photograph a Whiteboard for some time, whether for personal notes or to share with others. There are also mobile apps dedicated to this - even Google Drive has a 'Scan' feature with OCR text recognition, allowing the text to be indexed and thus discoverable.

Now there's something from Microsoft. It's called Office Lens. Though unfortunately only in the Windows Phone Store at the moment. Let's hope they release also an Android and iOS version.

http://blogs.office.com/2014/03/17/office-lens-a-onenote-scanner-for-your-pocket/

SharePoint Configuration Guidance for 21 CFR Part 11 Compliance

In April 2012, Microsoft published a White Paper titled SharePoint Configuration Guidance for 21 CFR Part 11 Compliance – I urge any SharePoint person involved in with Computer Systems Validation (CSV) subject to read it.

It’s rudimentary and unlikely that you will use exactly the use cases mentioned, but the concepts are there. It mentions one or two server technologies which may not be available in tightly governed organisations but I think that the real value is where it goes on to detail what can be done with Microsoft Office 2010 and SharePoint 2010 out-of-the-box.

Whether you agree or disagree, it then goes on to explain how exactly the Office clients and the SharePoint server platform meet aspects of 21 CFR Part 11 Compliance.

Direct link to the white paper: http://download.microsoft.com/download/9/A/A/9AA9CACF-B414-466B-BBED-86507D1D24FF/SharePoint-Configuration-Guidance-for-21-CFR-Part-11-Compliance.pdf

SharePoint ECMA libs in non-master page

If you have a SharePoint .aspx page that has no master page content or containers and you want to include the SharePoint ECMA client object model libs, SharePoint is very particular about how you do that.

As we know, including the libraries is as simple as including the following line of markup:

<SharePoint:ScriptLink runat="server" ID="sl" Name="SP.js" OnDemand="false" Localizable="false" />

Note: the OnDemand attribute is set to false here to ensure that the script files are loaded immediately and so that we don't have to over-complicate with ExecuteOrDelayUntilScriptLoaded()

The key thing to remember with this piece of markup is that it MUST appear after the form element. So it will look a little like this:

<form id="form1" runat="server">
<SharePoint:ScriptLink runat="server" ID="sl" Name="SP.js" OnDemand="false" Localizable="false" />


I had originally tried to put this in the <head> ... </head> element and was left head-scratching as to why it failed to include the scripts.

Configuring SQL Server Denali Reporting Services SharePoint 2010Integration

Great guide pointing out that if you should happen to install SQL Server Reporting Services 2012 before installing SharePoint, you must then let SharePoint 2010 know it's there and install the service.

http://denglishbi.wordpress.com/2011/07/22/configuring-sql-server-denali-reporting-services-sharepoint-2010-integration/

Thanks Dan!

XSLTListViewWebPart: Using CAML Query Parameters

After much searching around followed by a fair bit of trial and error, I figured out how to make use of Data View Parameters in a CAML Query in an XSLTListViewWebPart. Bear in mind I am configuring this using SharePoint designer.
So I have a list web part on a page, I open up that page using SharePoint Designer 2010 and begin editing under the hood. Once you've added a Data View Parameter you're then able to embed it as a parameter into your CAML query like so (Parameters in Bold):

<Query>
     <OrderBy> … </OrderBy>
     <Where>
     <Contains>
           <FieldRef Name="FileRef" />
           <Value Type="Text">{year}/{month}</Value>

</Contains>

</Where>
</Query>

Despite being so easy to implement, not once did I find it documented anywhere. I've labelled this post as I would have searched for it in the hope it helps out others looking for the same thing.

DataSourceID is either missing or set to an empty string

When fiddling around with the CoreResultsWebPart in SharePoint designer, or any web part for that matter, it’s easy to make mistakes in the markup.

However, more specifically, when you make a mistake in the markup of the CoreResultsWebPart it will always give the error DataSourceID is either missing or set to an empty string:

So of course, the first thing you do after seeing this is check your data source. I dragged another web part onto the page and compared the configuration with the one I’d been playing with in SPD. No difference – odd.

It was only through looking through the web part code that I noticed I had not closed a <div> tag that I’d put in for display of an image. I added the closing div tag </div> and voila! The web part no longer presented me with an error.

Having search around for instances where people were having the same or similar problem, somebody had suggested that you get this generic error no matter what’s wrong with your CoreResultsWebPart. So if you see the error and you believe the data source is intact, don’t be too alarmed – check your Web Part markup.