BAM: PivotTables not working on the BAM Portal

I’ve used the same base VPC for my various BizTalk Server 2006 images over the last few years, this has worked fine apart from a few annoying bugs that of course plagued every single instance of a VPC using it, of course I never really spent the time to iron them out so suffer from them every now and again :-(

All are most certainly down to me not having exactly the right bits installed or not having the configuration quite right.  One annoying problem was the Pivot Table view on the BAM Portal refusing to work by coming up with the following unhelpful error:

“The query could not be processed: Cannot connect to the server 'DARRENJBTS06'. The server is either not started or too busy.”

Over the years I’ve tried just about everything to fix it: Configuration, Security, Service Packs,etc. but have never managed to solve it until now - after some digging internally it seems that my base VPC was missing Version 9.0 of the Analysis Services OLE DB Provider, installing this resolved the issue straight away, the update is listed on this page along with some other updates.

Hope it helps!