[This article was contributed by the SQL Azure team.]
PowerPivot is a powerful, downloadable extension to Microsoft Excel 2010 that allows you to perform business intelligence and analytics against known data sources. One excellent data source you can use is your SQL Azure database. This article will talk about getting started with SQL Azure as a data source, a process that is so easy you will need little help.
One big advantage of using SQL Azure as a data source is that it can be accessed anywhere there is Internet connectivity, and you can store large amounts of data securely and with high availability. Your PowerPivot users can run their reports on the road, without having to VPN into your datacenter. Also, they do not have to travel with a snapshot of data, which is outdated the minute after the snapshot.
Partial Data Scenarios
One scenario is to use SQL Azure Data Sync to push a subset of your full database from your live production SQL Servers in house to a SQL Azure database in the cloud. PowerPivot users could call the SQL Azure database without causing additional strain on your production SQL Server.
Putting only a subset of the database could increase the security of your data. An example of this is uploading the customer’s city, state and sales information, without uploading their name, address, login, and password. This would allow PowerPivot users to aggregate the sales, and product categories against the state of origin, without exposing the sensitive data in your system.
Providing Firewall Access to Your Excel Users
In order to access your SQL Azure database your Excel users will need to have access on the SQL Azure firewall for their client IP address. From their computer they can figure out there client IP address by going to this site: . Then it is up to the SQL Azure administrator to login to the SQL Azure Portal and add that IP address the list of IP addresses allowed access to the SQL Azure server.
Granting Excel User’s Permissions on the SQL Azure Database
One of the best practices for security on SQL Azure to have a login for every user. For PowerPivot, the user just needs read-only access — they will be using PowerPivot for reporting. This previous blog post, walks the SQL Azure administrator through the steps needed to create a read-only user for a database.
Requirements
The PowerPivot site does a good job of listing all the requirements for PowerPivot, one thing you should know is that PowerPivot is an extension for Microsoft Excel, the version that ships withMicrosoft Office Professional Plus 2010 Microsoft Office Professional Plus 2010 Microsoft Office Professional Plus 2010 Microsoft Office Professional Plus. It is a separate download that needs to be installed after Excel is installed.
Creating a Connection
PowerPivot adds another ribbon on your Excel toolbar. Inside that ribbon you need to open the PowerPivot Window to create connections to data sources.
Once the PowerPivot Window is open you want create a connection that Excel can use to access SQL Azure.
Even though SQL Azure is a database, you don’t want to choose From Database, this will work however it requires extra steps. Instead, choose From Other Sources, this will open the Table Import Wizard and give you a handy visual selector for choosing SQL Azure.
After you choose the Microsoft SQL Azure relational database you are presented with a .NET Data Provider for SQL Server dialog that is correctly configure with encryption enabled.
Here is where you enter the server name, login information and the database they want to attach to.
When you press the Next button, you are given a choice about how you want to import data, for this example we are going to select some tables from Adventure Works for SQL Azure which I have installed on my SQL Azure server.
Clicking Next again will give me the list of tables to select.
The next dialog page where you get to select tables is incredible well done, it allows you to select tables, modify columns for the tables you select, tells you the server name that you are connected to, and figures out related table by foreign key references.
Once you have selected your tables and complete the wizard, Excel downloads them and makes the data accessible to you for analysis.
Summary
Do you have questions, concerns, comments? Post them below and we will try to address them.