The WorkerThread Blog

we know stuff so you don't have to

Final Post on this Blog

Posted by workerthread on June 17, 2014

Other commitments and demands on my time have meant that I’ve not been able to post anything new here for a long time  – not since October 2012 in fact!  I’ve now decided that the blog will close down some time soon but for the moment I’ll leave all of the older posts up here in case they are helpful to anyone.

May all of your SharePoint installations be successful, and all of your SQL Server databases keep healthy!

Posted in Uncategorized | Comments Off on Final Post on this Blog

SQL Server 2012 – Fixing Up Post-Patch Issues

Posted by workerthread on October 11, 2012

I have an “all in one” virtual machine for demonstrating the Business Intelligence features of SQL Server 2012 in combination with SharePoint 2010.   My VM is based on the “Kiwi Build” setup documented in the TechNet Wiki article here (although mine is using VMWare Workstation instead of Hyper-V).  As well as all varieties of Analysis Services (Tabular, MultiDimensional, PowerPivot) my VM also runs SharePoint, Master Data Services and Data Quality Services.

Since the initial release of SQL Server 2012, there have so far been three Cumulative Updates and this week a very large security update.  I generally check these patches out using my VM and each time I’ve come across specific post-patch issues which need to be addressed.  I thought it would be worth sharing these here, both for my own future reference and hopefully to assist others.   Note that these issues are my experience on my VM – you may encounter very different problems when you apply the patches, most of which are caused by the updated assembly versions that the patch installed – I probably can’t help you with those.

So this week, as usual, I first took a snapshot of the VM, then applied the security update. This installed without problem and I could see following the update that all of the expected SQL Server services were running as was SharePoint Server.   The post-patch fixing up is only needed once you start using the various SQL Server services –

PowerPivot Service in SharePoint

First I went to a PowerPivot Gallery in SharePoint, opened a workbook to display a pivot table with slicers.   When I clicked on a slicer to filter the data, I immediately got a message saying “an error occurred during an attempt to establish a connection to the external data source” –


To fix this and get PowerPivot working again, you need to re-run the PowerPivot Configuration Tool.   When this loads it checks the current farm status and in my case returned the following message telling me “newer versions of the PowerPivot solution files have been detected” –


And as you can see, the “Upgrade” option is already selected –


I did as I was told, ran the “upgrade features” option and once this completed, the error message had gone from SharePoint and I could once again use my published PowerPivot models.

Data Quality Services

Next I opened the Data Quality Services Client and attempted to make a connection to the server.  I got a very verbose error message essentially telling me “an error occurred in the Microsoft .NET Framework while trying to load assembly id 65627” –


A quick online search led me to this document which discusses installing Cumulative Updates or patches on Data Quality Services.   It doesn’t explicitly mention assembly ID 65627 (although other online articles do) but as it says, you do have to run DQSInstaller.exe – upgrade to fix the issue above which did the job for me. 

Master Data Services

Finally (for me anyway) Master Data Services.   When I opened the Master Data Manager web client, the message I got was “the client version is incompatible with the database version” and a request to update components using Master Data Configuration Manager –


So, off I went to Master Data Services Configuration Manager, connected to my MDS database, and got the same advice about upgrading the database –


Which I duly did, and once complete Master Data Services and Manager were back up and running (actually I think this is a little misleading because the original MDS error suggests that the client version is out of step, but the database upgrade fixes it).

So there you have it.   From what I’ve seen so far all or some of the above steps will be needed after patching SQL Server 2012, depending upon which features you have installed.   At the time of writing I haven’t tried applying Service Pack 1 which is currently at CTP level but if anyone has, please leave a comment.

Update December 2012 – I can confirm that after applying SQL Server 2012 Service Pack 1 it was again necessary to complete the steps outlined above fix the issues.   As Cumulative Update 1 for SQL Server Service Pack 1 arrived fairly soon after I also tested this and found that SP1 CU1 didn’t cause anything to break.

Posted in Microsoft SharePoint, PowerPivot, SQL Server | 2 Comments »

Excel 2013 Preview – Webservice Function and Flash Fill

Posted by workerthread on August 9, 2012

From everything I’ve seen so far Excel 2013 has some great new features for anyone interested in data analysis and Business Intelligence tools.   Both PowerPivot and Power View are included in this release (more on this later) and there are lots of new OLAP-specific features for anyone using Excel as a front end for multi-dimensional tools.

I saw this blog post from Chris Webb with an example of the new WEBSERVICE function.   As it happened I was recently looking at a file of locations for London bicycle hire stations – I thought it might be interesting to use this with some bike journey statistics against Excel 2013 Power View and maybe use the new mapping feature to show the busiest hire locations.   My file showed geographical location info as Easting and Northing and I thought I would be a good exercise to see if I could convert these to latitude/longitude columns for use in a Power View map.

Hunting around online, I found this article on converting easting/northing to latitude/longitude for an interactive map project.  The article uses a web service at to send an easting/northing pair and return the lat/long data in JSON format.   So will this work with Excel 2013 –


The answer is, yes it will, and I get JSON data returned which looks like this –

{"lat":"51.523344763137","lng":" -0.1838464075195"}

And of course I can copy the formula containing the WEBSERVICE function down the column –


Next I need to parse the JSON data so I end up with two new columns, one each for Latitude and Longitude.   Now it is of course possible to split strings of single-column data into separate columns using various combinations of MID, FIND and LEN functions and this would certainly work.  And there are various Excel VBA JSON parser examples around online.  But Excel 2013 has a new, interesting Flash Fill feature which you can read more about in this Excel Team blog post.   Giving Excel 2013 sample patterns to work with is the key here.  I wanted to try this with the data in my web service results column.

It’s worth noting a point made in step 3 of the Excel team post: “We decided to disable automatic Flash Fill for numeric data. After all, there are only 10 unique characters in our numbering system, and it’s just too easy to come up with random patterns that don’t make good sense”.   So to pull out the latitude values as numerical values I needed to to format the numerical precision on the column (in this case to 12 decimal points) and use the Data tab “Flash Fill” option –

And it worked!


My longitude values didn’t flash fill with numbers so successfully.   I think the problem here lay with the variable number of decimal places, so I got this message –


But the good news is if I entered the first example value with a label prefix, as soon as I start typing the second value, Flash fill recognises what I’m doing and suggests the fill I’m looking for –


UPDATE – if I settle for a lower precision and go for lowest common denominator of decimal places this does actually work with numbers.  So for example entering first 2 values with 8 digits of decimal value (such as –0.1838464) will allow me to flash fill with numeric data all the way down the column with no errors.

So overall pretty encouraging results.   Moving forward, if I were going to use the cleaned up web service results mashed up with other data in PowerPivot/Power View I would probably remove the web service function calls leaving behind just the cleaned-up data, thereby minimising the workbook refresh time.

One slightly disappointing finding, the WEBSERVICE function doesn’t currently seem to work in either SkyDrive or Office Web Apps in the Office 365 preview but maybe this will change by the time Excel 2013 is released.

Next up, a post on Excel 2013 PowerPivot and Power View.

Posted in Business Intelligence, Office | 2 Comments »