Using Excel Query for end user reporting against an Azure database

Wed, 22 Jun 2016 12:35:00 GMT

In my recent project for a government agency, I’ve been evaluating options for reporting solutions that don’t require developers to build the reports and empower the business users to view and transform data to meet their needs. I looked at a number of typical off the shelf solutions, including Excel, PowerBI and Tableau. Because this solution used a government Azure tenant, PowerBI was out of the running since it is not current available for the Microsoft government cloud. PowerBI desktop and Tableau were both options, but would likely prove too much of a learning curve for my target customers. As a result, I landed on recommending Excel because of its ease of use, availability of learning resources, and the fact that many of my target customers already use Excel for day-to-day operations anyway.
By default, Excel can connect (via Power Query or the query option in Excel 2016) to SQL Azure, however the ability to include end user parameters for the queries is quite cumbersome and limited. Excel Query has been in the product since 2003, but connecting to an Azure SQL instance is not straight forward because the OBDC dialog screens don't allow you to pass critical information to enable the connection. Below are the steps to create a connection between Excel Query and a SQL Azure instance and allow parameters to be passed from values entered in an Excel sheet. The instructions and screen shots below were created with Excel 2016, but many of the dialog boxes and connectivity methods (OBDC) have not been touched for over 10 years, so the concepts presented here should work for most Excel versions in the wild at this point.
Connect Excel to Azure

As I mentioned above, Excel does not currently offer an out of the box solution to connect to Azure via Excel Query. We’ll need to resort to a bit of hackery to overcome this limitation. Start by creating a dsn file to set up the default connection information to your server. This file will be used once to kick off the connection. After that connection information will be managed directly in Excel and embedded directly into the spreadsheet rather than relying on distribution of the dsn file with the Excel file.
In a text editor, enter the following substituting your environment information for the highlighted items. Make sure to use the .database.SECURE.windows.net address as you won't be able to connect if it isn't set to the secure url from Excel.

[ODBC]
DRIVER=SQL Server
SERVER={SERVERNAME}.database.secure.windows.net
APP=Reporting
WSID=NA
DATABASE={DATABASE}
[Microsoft Office]
UID={USERNAME}
PWD={PASSWORD}
Encrypt=yes
TrustServerCertificate=yes

Save this file anywhere on your computer. You'll only need to access it once if all goes well. At this point, we can start working in Excel.
Open Excel with a new blank workbook. On the data tab, select "From Other Sources" and then "From Microsoft Query". The "New Query" option does allow you to connect to SQL Azure out of the box, but doesn't provide an easy way to set parameters on screen to filter the data to the database without using the "M" modeling language.

From the "Choose Data Source" window, "Browse" to find the dsn file that you created above.

At this point you can step through the query wizard as you would to connect to any other source setting the fields, filters, sort orders, etc. that you need for your connection. At the end of the wizard, select the option to "View data or edit query in Microsoft Query" so that we can configure the parameters.

Configuring parameter passing

New we need to configure the parameter passing. In the query editor make sure the "Show/Hide Criteria" button is selected.

Now you can drag your criteria fields from the table to the criteria field area. For the value, specify a string wrapped in square brackets ([param]) and make sure that the name is unique and not a field in your table(s). If prompted for a value, just ignore the prompt unless you want to preview the data. At this point you are ready to send the query results to Excel. Select File -> Return data to Excel, and specify where you want the resulting table to be sent. Make sure to include space for parameters that you want the user to supply. For example in this case, let's allow a couple lines to let the user specify the claim number that they want to filter by:

We're almost there, next we need to wire up the parameter that they input with the query. Right click anywhere inside of the table and select Table -> Parameters.

In the parameters window, let's set the "param" parameter that we named in the query editor to "Get the value from the following cell" and "Refresh automatically when cell value changes" as shown.

Now our parameters should be set and values will update when you change the values in the parameter input fields.
Update connection properties

In order to share the workbook with others, let's make a couple minor tweaks to the connection to ensure that data is loaded when the workbook is opened and not to save the underlying data when the workbook is closed. Also, although we set the password in the .dsn file in the first step, It isn’t persisted in your workbook for security concerns. We’ll need to set it again here if you want it persisted with your solution.
From the "Data" ribbon, select the Connections option. Find your connection name and click "Properties". In the Usage tab, select the options to "Refresh data when opening the file" and "Remove data from the external data range before saving the workbook”.

Next on the definition tab, notice that the connection string retained the driver, server, database and user from the .dsn file, but the password is not retained by default as that is less secure. If it is valid for your environment, you can specify the shared password for a security limited user by setting the PWD parameter as appropriate.

At this point, save your excel file and you should be ready to share your work with other users. All they need to do is to set the parameter to a valid value and they will start seeing live data streaming directly into Excel.
There's plenty more you can do using Excel as a reporting engine, but this should get you started with the critical steps of connecting Excel to Azure.