This content is part of the Essential Guide: Maximizing the benefits of Excel add-ins: PowerPivot and Power View
Problem solve Get help with specific problems with your technologies, process and projects.

BI secret to using Excel in Microsoft SQL Azure: PowerPivot

So, you’re comfortable with Excel and you’ve already deployed to the cloud. Is there a trick to using Excel in Microsoft SQL Azure? Indeed there is. It’s the PowerPivot add-in.

Microsoft Excel has entrenched itself solidly as a data analysis tool, in part because many of the people performing business intelligence (BI) work cut their teeth doing stats in Excel and find it a comfortable environment to work in. To that end, many folks have found ways to use Excel as a BI tool with SQL Server -- as a front end for manipulating data reported from the server.

That’s SQL Server on the ground, but what about up in the cloud? Since SQL Azure, Microsoft’s cloud database service, is SQL Server (that is, in the cloud), are there ways of using Excel in Microsoft SQL Azure as a BI tool? The short answer is yes, but it requires a little work.

BI blogger Chris Webb attempted to do something like this with Excel 2007 and the Excel Web App, but found the toolset too limited. His idea was to create an Excel spreadsheet with live data links to SQL Azure, upload it to the Excel Web App and have that data refresh in real time. No such luck. He was able to use open database connectivity (ODBC) as the connection layer, but he had trouble linking to Azure through Excel. And the Excel Web App doesn’t support external data connections, so the resulting spreadsheet consisted of a static data snapshot -- not very useful for live BI.

What doeswork is the PowerPivot add-on in the desktop version of Excel 2010. PowerPivot allows Excel to perform real-time data analysis against a remote source. Normally, it’s used in conjunction with SQL Server, and it works with any SQL Azure data source as well.

The first step is to set up a data connection in Excel to SQL Azure. It’s not terribly different from connecting to a conventional SQL Server source. One important thing is that you will need to make sure your SQL Azure instance has its firewall open to allow access from the client in question. Azure instances have firewall settings locked down by default to refuse connections from anything except the Web-based management console.

Next up is installing and using PowerPivot in conjunction with Azure. Good news: PowerPivot supports Azure natively, which means your data can be cached and manipulated locally without an active connection and refreshed on demand. (It’s not quite live BI, but close enough for most people.) This offline caching and manipulation is handy if you want to do things like build service cubes, since Azure does not have native support for such things -- at least not yet. 

Also worth mentioning here is that you cannot edit remote SQL Azure data through Excel. Excel can only be used to fetch, transform and report on the data, but it can’t make changes to it. One can assume most database administrators would like to keep it that way, since being able to make casual changes to what might be mission-critical data is a bad idea. If you’re determined to do this, though, and you have confidence that your Excel users are not going to make a mess of things, there are a few ways to pull it off.

The folks at SQLServerPedia have a walkthrough that explains how to do this with nothing more than Excel and ODBC. It involves a specially crafted Excel spreadsheet that obtains data from the remote server and then updates it whenever there’s a change. Most of the magic is done through macros, so you don’t need to perform any additional commands. If you want a full-blown product that turns Excel into a data-editing front end, check out SaveToDB, which works with both SQL Server and SQL Azure and has a sophisticated way of handling the data interchange between the client and back end.

Anyone who is serious about using Excel as a BI environment -- and not just with SQL Azure -- should use the 64-bit version of Excel whenever they can and run that on a machine with 4 GB of memory or more for the best possible performance. The more data is cached in memory, the faster the analysis.

Serdar Yegulalp has been writing about computers and IT for more than 15 years for a variety of publications, including InformationWeek and Windows Magazine.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.