PowerPivot and SQL Azure

When PowerPivot was released, I was sceptical. Yes, it could be a great tool for in-house reporting, but not so with a SaaS application. My experience was that SaaS reporting should be embedded in the application. Maintaining a separate reporting systen such as SSRS, or even Telerik Reporting, in addition to the main application would be too much work. And it turned out that a ASP.NET ROLAP control like DevExpress ASPxPivotGrid would be good enough for both standard reports and custom reports, eliminating the need for another reporting system.

This reasoning applies if there are extra costs involved in supporting a new system. PowerPivot, however, is purportedly a self-service system. If it doesn't impose new costs, why not support it ? Unfortunately, even if there are no report authoring costs, there are still system-level problems that have to be solved before PowerPivot can be used in the public internet. First, you need to configure networking to allow secure access to your data. A direct SQL connection to your database is not always a good idea, and data services can take too much time to develop and set up. Once there is access to data, the next problem is to make sure that your database system scales up to the new demand. Alternatively, you may want to implement resource management so that PowerPivot doesn't hog up too much resources. These are hard problems for a small SaaS provider, but interestingly, they are also problems that Azure promises to solve.

With my simple application running in Azure, I was all set to experiment with PowerPivot. The easiest way to use PowerPivot with Azure is to connect directly to a SQL Azure database. This is done in two steps:

1. In Azure management portal, add your current IP address to the firewall rules for the SQL Azure server. 
2. In PowerPivot, connect to the database. There's a little catch: Ordinary database connection didn't work, PowerPivot just crashed Excel. You need to use "From Other Sources" - "Microsoft SQL Azure"

And there you have it, flexible PowerPivot access to your data. I'm impressed how well it complements web-based reporting. I've used it to answer questions about the data for which the PivotGrid does not adapt easily, such as percent-of-total calculations. The main restriction with PowerPivot is that it suits best for power users with full access to data. Setting up views for row-level security is too much hassle in most cases.

-mika-