THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Some thoughts on PivotViewer and SQL Azure

Today there was an exciting announcement from Microsoft’s Business Intelligence (BI) team, they have released a preview of a tool called PivotViewer which is a visualisation tool for BI data stored in Analysis Services. If you’re familiar with Pivot from Live Labs or their follow-up project the PivotViewer Silverlight control then you’ll be pretty familiar with the sort of interaction that it provides. If not then this video of Gary Flake at TED should put you in the picture (6m18s long).

We tend to have problems embedding videos on SQLBlog so instead you can head to Gary Flake: is Pivot a turning point for web exploration? on

Here’s a snippet from Cristian Petculescu’s blog post And It Is Live...:

The CTP1 of PivotViewer for Reporting Services has just shipped! Get your free download from here. As I was saying in an earlier post, it is a concept project. It's not supported, and not a feature.
However, it is really cool! It allows you to easily create stunning visualizations on top of your BI data - and these visualizations are fully dynamic:

Kasper do Jonge follows up with CTP1 of PivotViewer for Reporting Services is live:

Although, the typical scenario is for data to be in PowerPivot, and the PowerPivot server (Analysis Services 2008R2 in SharePoint mode) is a required prerequisite, the following data sources are supported:

  • Analysis Services 2008R2 (Adomd.NET provider)
  • SQL Server 2008 or later (SqlClient namespace)

I’m delighted to see powerful tools like this coming out, one hope I have is that it helps to lowers the artificial boundaries that we build around systems categorising them as BI or none-BI. I firmly believe that data analysis is a skill that we can bring to all disciplines and, not just so-called “BI”, and tools like this should help to break down those barriers.


Kasper de Jonge provided more information about installing the PivotViewer control, first some pre-requisites:

You need to have SharePoint 2010, powerpivot server (AS for SharePoint), reporting services in SharePoint mode

Then the install instructions:

  • Open an command prompt with Administrator access and run:
    BICollectionDBProvision.exe -sPowerPivot\powerpivot -op
    This will create a database on a local database in my case “PowerPivot\powerpivot”
  • Open the SharePoint 2010 Management Shell command prompt with Admin access:
    .\installBIPivot.ps1 “-whttp://powerpivot” -oi –y

Then the demos:

BICollectionSampleBuilder -rhttp://powerpivot/ReportServer_SQL -whttp://PowerPivot -gSampleGallery –dSampleDocuments


Now all you have to do is create a page in SharePoint and add a “PivotViewer BI Report Crawler” webpart to your page


create a second page and add the “PivotViewer BI Data Browser” webpart to the new page and in the properties set the Inventory to “Accountmanagers”.

This is great content from Kasper and he makes the point that this is all very easy which I’m sure is true but you know what would be even easier? Not having to do any of this, that’s what. Those of that are familiar with Microsoft products are familiar with a long list of instructions like this but now there’s a new kid in town – SQL Azure The great thing about SQL Azure is that you can provision yourself a whole SQL Server instance in the time it takes to type in a credit card number and I look forward to the day when we can do the same for Analysis Services and Reporting Services (which will hopefully include this PivotViewer control). The promise of the cloud computing business model is that installation headaches go away and we can get on with what we are good at – creating value out of these products.



UPDATE: here is a video from Donald Farmer that shows PivotViewer in action:
Published Friday, July 16, 2010 11:10 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



mwaterreus said:


I do agree that Cloud based services are very interesting and will play a big part of tomorrow's data and BI-services one way or another. However I do wonder if companies are really interested in outsourcing their valuable data to an external source, even if it is good old big reliable microsoft.

Do you really expect that companies will trust SQL Azure enough to entrust their valuable and sensitive data to it?

I am interested in your view on this subject.

July 19, 2010 2:53 AM

SSIS Junkie said:

The SQL Azure team have just announced in their blog post CTP1 of Microsoft® Project Code-Named “Houston”

July 21, 2010 10:29 AM

Leave a Comment


This Blog


Privacy Statement