Using MS Excel to Access A Relational SQL Database in Azure

There are great Microsoft tools to access to SQL Server in Azure. However, as a Database Developers and Administrators we need to help the end user to access to the information using a familiar and intuitive interface. Most of the end users have a good knowledge in MS Excel. That is why it is recommended to connect our SQL Azure database to Excel. That way, the end user will be able to easily create reports, charts and generate useful information. In this chapter, we will show how to connect to SQL Azure using Microsoft Excel.

Step by step

1. Open the Microsoft Excel 2013.
2. Go to the Data Menu and click the From Other Sources icon>From SQL Server.
3. In the Azure Portal in the Virtual Machines section, verify the virtual machine name used. Copy and paste the virtual machine name to be used.
4. Paste the Azure virtual machine name with SQL Server and specify the SQL Server User name and the SQL Server Password.
5. Select a Database. In this example, we will use the AdventureWorks2014 database. There are two options here. You can connect to a specific table/view or you can select multiple tables. In this example, we will connect to a specific table.
6. Note that the icons for tables and views are different. You can also differenciate tables or views using the Type column.
7. For this example, we will use the Employee table of the AdventureWorks database, but any table with data can be used for this test.
8. In Excel, an odc file is created to store the connection information. The ODC are Office Data Connection. You can specify where to store the file and specify a description, a friendly name and search keywords to search the files easily. Press the Browse button.
9. By default, the ODC files are stored in my documents>my data sources folder, but you can specify the path of your preference.
10. If you open the file with a text editor, you will notice that it is a simple file with xml format and that it can be easily edited.
11. You can import the data in a table, PivotTable Report, PivotChart and only create the connection. You can create the data in the existing worksheet or in a new one.
12. Once you press OK, You will notice that the data from the employees table is now in Excel.
13. If you try to edit the data you will edit it in Excel, but the changes are not reflected in the SQL Server Azure table. You mainly have access to the data to create special reports that can be easily done in MS Excel.
14. You can also use filters as you do with normal data in MS Excel.
15. Now, let’s play with some formulas. Add the Age Column to the current table in Excel.
16. What we are going to do is to calculate the Age of a person using the birth date. We will do this using Excel. Add the following formula in the Age column:
17. =DATEDIF(G3,NOW(),”y”)
18. The formula shows the difference between the Birthdate column (G3) and the current date (NOW) in years (“y”).
19. You can also create a nice dynamic reports using Power View. To access to Power View, select the data and go to the INSERT menu and select the Power View icon.
20. If it is your first time running Power View, it will ask you to enable it and install Silverlight (if it is not installed).
21. Once enabled and installed, you will have a new Sheet in Excel with dynamic data.
22. You can create filters dynamically. In this sample, we will create a simple report, in this sample; we will see the graphs of males and female employees based on the Business Entity ID.
23. Select the Pie Chart option.

BEST SQL 2016 HOSTING RECOMMENDATION

ASPHostPortal.com provides its customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Windows server. ASPHostPortal.com offers SQL 2016 hosting starts from $5. ASPHostPortal also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable SQL 2014 Hosting, ASPHostPortal should be your best choice.