Microsoft makes it simple to deploy Azure SQL Databases. Once deployed, Microsoft Excel and Power Pivot make reporting against those databases a pretty much perfect experience. In this post, I’ll demonstrate how you can connect these technologies and produce meaningful reports.

Deploy the Azure SQL Database

After creating your Azure SQL server and specifying the admin login, here are the steps to deploy the AdventureWorksLT test database:

1. Login to your Azure portal at https://portal.azure.com
2. Locate, and drill into the “SQL databases” service.
3. Click the “+ Add” button at the top of the SQL databases configuration screen.

Then, in the configuration pane that launches, specify the required parameters. In this post, there are two important options to highlight:

1. Choose “Sample (AdventureWorksLT)” under the “Select source” option.
2. Change the “Pricing Tier” to “Basic 2 GB”.
   • This is for a demo/testing, and the “Basic 2 GB” option costs significantly less than the default Standard.
   • At the time of this post, the cost is, “Starting at 4.99 USD / month” for Basic versus 15.00 USD / month for Standard.

Press the “Create” button at the bottom of the configuration pane, and you’ll be taken back to the SQL databases configuration screen. Deployment takes around 1 – 2 minutes.

From here, the only other step you need to take is to ensure you have added the machine from which you’ll be using Microsoft Excel to your database firewall rules. Maybe you’ll want to run a quick check to confirm that you can login and that the database contains content. You can do this online using the Azure SQL Query Editor:

With the database in place, move on to creating your database connection in Excel with Power Pivot.

Enable the Power Pivot Add-in and Connect to Azure SQL in Excel

Enable Power Pivot

First, make sure you have the Power Pivot COM Add-In enabled in Microsoft Excel. Do you have the Power Pivot tab available in the ribbon?

If not, here’s what you need to do:

1. Open Excel and go to “File”
2. Then, click “Options”
3. In the dialog that appears, click “Add-ins”
4. Change the “Manage” drop-down to “COM Add-ins” and click the “Go…” button

Then, in the next dialog, check the checkbox next to “Microsoft Power Pivot for Excel”, press “OK”, and you’re set.

Connect to your Azure SQL Database

After you enable the Power Pivot Add-in, navigate to its tab in the ribbon, and choose “Manage” from the Data Model group. That will launch the Power Pivot for Excel interface where you then choose “From Database” in the ribbon “Home” tab, and specify SQL as your data source:

After this, the Table Import Wizard window should appear and you can specify your Azure SQL Database connection parameters:

Once you click “Next”, choose, “Select from a list of tables and views to choose the data to import”, and now move on to modeling and reporting.

Power Pivot Tables and Charts

Selecting Data to Import

Here are the tables I’m going to use for the post:

Click the Finish button, and you should receive the “Success’ dialog:

Import Custom Table into Power Pivot Data Model and Create Relationship

One of the reports I need to pull is for the count of sales in a sales region, where sales region refers to the region of the sales person’s territory. There is no such information in the AdventureWorks database. So, I’m going to create a table in an Excel worksheet and import it into the Power Pivot Data Model:

Of course after the recording above, I gave “Table2” a more appropriate name of “SalesPersonRegion”.

Perform Analysis with Charts and Tables

With that done, let’s create the report so we can see which regions are most productive:

This is sample data, and based on the way I distributed the regions, there ended up not being any sales for the East Coast. But, I have to admit that I wasn’t certain about that, so I double checked to see who exactly was performing, and ensured sales were only coming out of 3/4 of the regions:

And, when all was done, I continued to analyze by Product, Customer, etc. and came out with the following dashboard:

Conclusion

As you’ve read, it only takes a few clicks and specifying two security settings to launch a fully functional test Azure SQL Database. Similarly, that’s about all it takes to import that database intto Power Pivot for Excel – a few clicks and providing connection settings. Once done, you can create custom tables and import them into your Power Pivot Data Model and then create Pivot Tables and Pivot Charts through standard Excel steps-and-procedures.

Categories: Azure, Business Management, Microsoft Excel, Office 365 and O365

Tags: Azure, Business Management, Microsoft Excel, Office 365 and O365, Power Pivot, SQL