Daniel Calbimonte

Using MS Excel to access to a relational SQL Database in Azure

August 14, 2015 by

Introduction

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.

Requirements

I am assuming that you already have a VM in Azure installed.

  • An Azure subscription.
  • A VM Machine in Azure with SQL Server already installed (check my article about Azure VMs).
  • A local machine with Windows installed.
  • The Adventureworks or any other database with tables and views already created. In this article, we will use the Adventureworks.
  • You will need to Add a SQL Server Endpoint, enable the default SQL Server Port, and enable rules for the EndPoint. See this article for details. A SQL Server authentication enabled and a SQL login and password.
  • For this article, I am using Excel 2013, but the 2010 will work.

Getting started

  1. Open the Microsoft Excel 2013.

    Figure 1. The Microsoft Excel

  2. Go to the Data Menu and click the From Other Sources icon>From SQL Server.

    Figure 2. Connection to SQL Azure from Excel

  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.

    Figure 3. The Azure Portal, retrieving the Server name in Azure.

  4. Paste the Azure virtual machine name with SQL Server and specify the SQL Server User name and the SQL Server Password.

    Figure 4. Login information to SQL Azure using the SQL Authentication

  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.

    Figure 5. Selecting the SQL Azure Database

  6. Note that the icons for tables and views are different. You can also differenciate tables or views using the Type column.

    Figure 6. The tables and views

  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.

    Figure 7. The Employee table

  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.

    Figure 8. The odc file with the connection

  9. By default, the ODC files are stored in my documents>my data sources folder, but you can specify the path of your preference.

    Figure 9. The odc file path

  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.

    Figure 10. The file content of the odc file

  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.

    Figure 11. How the SQL Server data will be displayed

  12. Once you press OK, You will notice that the data from the employees table is now in Excel.

    Figure 12. The SQL Azure data 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.


    Figure 13. Trying to edit the SQL data

  14. You can also use filters as you do with normal data in MS Excel.

    Figure 14. Using Excel filters with the SQL data
  15. Now, let’s play with some formulas. Add the Age Column to the current table in Excel.

    Figure 15. Adding a column to the table

  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:

    =DATEDIF(G3,NOW(),”y”)

    The formula shows the difference between the Birthdate column (G3) and the current date (NOW) in years (“y”).

    Figure 16. Adding a Formula in Excel

  17. As you can see, the Excel formulas can be easily used with the Azure data in SQL.

    Figure 17. The results of the formula applied

  18. 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.

    Figure 18. Enabling Power View

  19. If it is your first time running Power View, it will ask you to enable it and install Silverlight (if it is not installed).
  20. Once enabled and installed, you will have a new Sheet in Excel with dynamic data.

    Figure 19. The Power View information

  21. You can create filters dynamically.

    Figure 20. Power View Fields

  22. 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.

    Figure 21. Selecting columns to graph

  23. Select the Pie Chart option.

Figure 22. Power View Charts

As you can see, the pie is created.

With Power View you can also create dynamic charts, working with geographycal maps, etc. As you can see, working with SQL Server in an Azure VM using Excel is very easy.

Conclusion

As you can see, it is very easy to connect to SQL Server once the permissions are set. You can handle the SQL data to generate reports, graphs but not to edit the information. The connection is stored in odc files, which can be edited with any text editor. Excel 2013 with Power View enables you to create pretty nice dynamic reports.

References

For more information, refer to these links.

Connect Microsoft Excel To SQL Azure Database
Connect Microsoft Excel to Windows Azure SQL Database
Getting Started with Power View in Excel 2013


Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams.

View all posts by Daniel Calbimonte
Daniel Calbimonte
SQL Azure

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams. View all posts by Daniel Calbimonte

1,978 Views