Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Connecting to SQL Azure from Excel 2007

with 8 comments

Sometimes I have an idea, spend a bit of time testing it out, and it ends up being a dead end. That’s what happened this evening but there’s at least one interesting bit of info that resulted so I thought I’d blog anyway…

My original thought was this:
* Excel 2007 can connect to SQL Server to retrieve data for use in reports in a worksheet
* SQL Azure is SQL Server in the cloud
* Office Web Apps gives us the ability to view Excel spreadsheets in the cloud
* So can I create an Excel spreadsheet that connects to SQL Azure, build a report using data from there, upload it to the Excel Web App and then refresh the connection so that my spreadsheet in the cloud displays live data from the cloud?

The short answer is no, at least not at the moment. But after a bit of trial-and-error I did get halfway there and manage to hook Excel 2007 up to SQL Azure (nb I’m not talking about using PowerPivot, which does work with SQL Azure, but the built-in Excel functionality). Here’s how:

  • None of the built-in functionality for connecting to SQL Server from Excel 2007 seems to work. However it is possible to connect to ODBC data sources from Excel and SQL Azure can be exposed as an ODBC data source.
  • So, in Excel, go to the Data tab and click on the “From Other Data Sources” and “From Data Connection Wizard”.
  • Select “Other/Advanced” and then the Microsoft OLE DB Provider for ODBC Data Sources and then click Next
  • Select the ‘Use Connection String’ option and paste the ODBC connection string that SQL Azure generates for you into the box.
  • Paste the value of the Uid property in the User name box, and put your password in the Password box. Delete the Uid and Pwd properties from the connection string.
  • Type the name of your database into the Initial Catalog box, then click OK
  • Finally a list of tables in your SQL Azure database appears; choose one, create an Excel data source and then create either a table or pivot table from the data. Click OK, enter your password one last time, and bingo!

Unfortunately, as I said, when I uploaded the resulting spreadsheet to the Excel Web App, I got the following error message:

image

Shame – I can understand why it makes sense for most external data connections not to be supported, but in this case, when the external data you’re connecting to is also in the cloud, it would be nice if an exception could be made.

One day, though, I’m sure a scenario like this will work. When I think about what Microsoft’s story for cloud BI might be like, the Excel Web App is the obvious candidate for the reporting tool. Whether you’re reporting direct from relational data stored in SQL Azure, or from some kind of cube (PowerPivot in the cloud is another obvious direction), Excel is going to be the easiest way to do it for the largest number of people. I do see a role for some kind of SSRS in the cloud too, but even in the Microsoft BI stack at the moment there’s a lot of overlap between SSRS and Excel/Excel Services for reporting; I wonder if this will be rationalised at some point? For example Report Builder has never really caught on as a way of letting end-users build their own reports, so why not forget it, develop Excel for this purpose and somehow extend SSRS’s rich functionality for managing and scheduling reports to work with Excel-based reports? Just a thought.

Written by Chris Webb

January 27, 2010 at 11:14 pm

Posted in Excel

8 Responses

Subscribe to comments with RSS.

  1. Hi Chris,I might be wrong but just thought to mention it anyway.Did you create the firewall rule (IP address range) for your Excel Web App running in the cloud to be able to access SQL Azure database?Regards,Raj

    Rajat

    February 1, 2010 at 7:15 am

  2. Hi Rajat, I tried that: I opened all the ports in the firewall (briefly) but it still didn\’t work.

    Chris

    February 1, 2010 at 9:40 am

  3. Hi Rajat,It works just like you stated it.I noticed that in the connection string the name of your computer is stored under wsid=<computer_name>, and also the user id picked por logging on sql server. Command type of this connection can be "sql" instead of "table", clearly meaning you can craft select statements and maybe even call stored procedures. Finally, there is a connection property for refreshing which can be set to one minute. I guess this last setting takes care of thinking in a timer for a Excel spreadsheet.Thank you very much,Tonci Korsano.

    Tonci

    February 2, 2010 at 10:33 pm

  4. Hi Tonci,Sorry, does this mean you\’ve got a spreadsheet deployed up to the Excel Web App to refresh while connected to Azure? And this is the Windows Live version of the Excel Web App?Chris

    Chris

    February 3, 2010 at 2:03 pm

  5. hi chris,i am sorry i didn\’t answer before. i didn\’t get a notice about your question in your blog.i used, just as you stated, excel from office 2007 with an odbc connection.i noticed a connection property that looks like a working timer.you can see it visually here –> http://tchips2.astgaming.com/sql.azure.timer.excel.2007.jpgit appears as getting a table form sql azure with excel 2007 can refresh at specified minutes intervals.best regards and thank you very much,tonci korsano

    Tonci

    June 23, 2010 at 6:19 pm

  6. hi again,comments cannot be long, so that is why i am sending another comment.you can also send a sql sentence, like in this image –> http://tchips2.astgaming.com/sql.azure.select.statement.excel.2007.jpgbest regards,tonci korsano

    Tonci

    June 23, 2010 at 6:21 pm

  7. hi there,finally, you can call a stored procedure, like in this image –> http://tchips2.astgaming.com/sql.azure.stored.procedure.excel.2007.jpgthis procedure is quite simple, as follows:create procedure getTestTableasset nocount onselect row_number() over (order by t.stamped_at) as row_number, t.* from test.test_login.test_table ttonci korsano

    Tonci

    June 23, 2010 at 6:23 pm

  8. Thanks Tonci, but this only works in Excel on the client, doesn\’t it? It doesn\’t work in the Excel Web App…?

    Chris

    June 23, 2010 at 9:50 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,131 other followers

%d bloggers like this: