Introduction

As per the title, in this article the magic of Business Intelligence with the combined forces of Excel 2013 + Azure SQL + O365 SharePoint Online will be demonstrated using a step-by-step walkthrough.

Requirements

• Excel 2013 Professional's Power Query and Power Map add-in

• Microsoft Power BI for Office 365

• SQL Azure database

• A laptop or desktop whose graphics card supports DirectX 10.

Setting the Scene

We have a company named Adventure Works whose database is on SQL Azure.  This database contains information such as employee, vendor and customer addresses, product description etc. In Excel 2013 we are going to create data connections to these SQL databases and then post them on SharePoint Online. For more information on SharePoint Online integration with Azure SQL you can refer to my article Office 365 SharePoint Online BCS with Azure SQL Server Database

Making the data connection

Open your Excel 2013 and select a Blank workbook:

Go to the DATA ribbon as shown below:

In Get External Data section click on the From Other Sources option and then select From SQL Server. Below is a screenshot:

In the server name section type in your Azure SQL server name. In the log on credentials section select the second option and type in your username and password. This is the same username and password you use to log into your Azure SQL database using SQL Server Management Studio. Click Next. Below is a screenshot:

 

If the server name is correct and if your username and password is correct then you should see the option to select your database name as shown below. In this scenario 'AdventureWorks2012' is selected:

By default Connect to a specific table option is selected, however, you also have the option to either connect to all the tables and views or connect to a specific table or to multiple tables. In this scenario I have unchecked the connect to a specific table and clicked next.

You have the option to change the file name and provide a friendly name as well. In this scenario the default names are left as it is. 

Click Finish.

Since we have not setup and custom authentication settings above, you will see the below SQL Server Login window for the password as shown below. 

As shown below you can now select multiple tables. These tables include type views and tables. You also have the option to import the relationships between selected tables.

In Import Data you have four different options to select from, in this scenario Table view has been selected.

 The data connections load time is dependent on the number of tables or views, size of the database, your internet speed and your desktop's performance. While the connection is being made you will see the below.

Once the connection is made you will see new worksheets. Since I selected four different tables/views in the data connection, I have four new worksheets as shown below.

Change the name of the worksheet to signify the data. You can always go back to Data click on Connections, select the connection and click on properties to view the table and view names. 

This is a good time to save the worksheet.

Building the Pivot Chart

Select the Employee worksheet and then click on the INSERT ribbon. 

Click on the Pivot Chart, by default you will see the Employee worksheet has been selected. Click OK.

You will now presented with a blank pivot table, chart and the fields available.

 In this scenario the job title name and count is going to be displayed in the pivot table and the chart. In the PivotChart fields, select the JobTitle. By default it will be populated in the AXIS area. You have to manually drag and drop the JobTitle field into the VALUES area. Click in the drop-down arrow near the JobTile field in AXIS and select Field Settings as shown below

As shown below, select Custom and then choose the Count function

Below is what you should see

You have the option to go to the Design ribbon and change the look and feel of the chart's style.

Building the Power View

Select the StoreAddress worksheet then go to the INSERT ribbon and click on Power View. If this is your first time using this feature then you will receive the below alert. Simply click on Enable. If you do not have Silverlight installed then you will be asked to install that as well.

The Power View gives a Power Point type look and feel, however, it does have some neat features you can use. In this scenario we are going to view the Map and the Tiles features.

Map

We would like to see the names, address types for all the country regions. From the Power View Fields on the right, click on the AddressType field and drag and drop it in the Filters. Do the same for the CountryRegionName and Name fields. Below is what you should see

 

On the top left of the ribbon, click on the drop down option for Table and select Matrix

Click on any blank spot on the window. This will clear all the selected fields in the Power View section. As you can see in the below screenshot the blank area highlighted by the yellow 'X' is where we clicked. As you can see the Power View fields have been deselected

Now select CountryRegionName field and then click on Map. Below is what you will see. You will notice the maps have several icons which mark the countries. If you click on any one of those, the table on the left will automatically filter. In the below example the icon on Canada has been clicked and hence the table filters only those names that are located in Canada.

Tile Type

Create another Power View as described above and select the fields shown below

As shown above, the TILE by area is blank. Select the CountryRegionName field below and drag and drop it in the TILE by area. Now you should see a horizontal tile with the country names. Clicking on any one of the countries will filter the table below. 

Building the Power Map

If you have the Power Query and Power Map add-ins installed successfully then you should see the POWER QUERY ribbon option as shown below

Click on the POWER QUERY ribbon and then click on From Database. Below is a screenshot

You will immediately notice the wide variety of options available for data connections. For this scenario we are going to choose the From Microsoft Azure SQL Database option as shown below.

Fill in the Azure SQL server name and the database name. Below is a screenshot of the server and database name used for this example. Click OK

If the server and database name is correct and if the username and password is correct then a Navigator will appear on the right hand side of your Excel worksheet. Below is a screenshot

Let's take a closer look at this database connection that has been made. If you hoover your mouse over each of the table names you will see a preview of that database loading. The load time is dependent on the size of the database, your internet speed and your desktop's performance. Below are screenshots of the loading and the preview. Note that the preview itself has all the names and the total number of columns in the database.

The table from the database has more information than we need hence click on the EDIT option as shown above which will open the Query Editor. The Query Editor has the same look and feel as an Excel worksheet but it also gives you the Query Settings option to the right. Every steps that you have taken will be recorded under the APPLIED STEPS. For this example we are going to only keep a few columns and remove the rest. The step to remove the columns has been recorded as shown below

Click on Close & Load. Now a new worksheet will be created. You will also see the Workbook Queries option to the right. 

Data Catalog

Data Catalog is where you can save all the queries that you have written and share it with others. Click on the 'Send to Data Catalog' button as shown below. Type in the username and password of the account that has access to 'Microsoft Power BI for Office 365'

You will be presented with the below option where you can give your query a name and setup the permissions for access

Now that we have the data with addresses, go to INSERT and click on Map.

You will see the Launch Power Map as shown below.  Click on it.

You will now be presented with a new windows similar to what you see below. As you can see this Power Map view does have the PowerPoint look and feel which options such as adding scenes, create videos etc. Bing maps is the only option available.

Below is a video which shows you the several features that are available. This video also demonstrates how the addresses from the Azure SQL database were pulled.

(For high resolution video Click Here)

SharePoint Online

Now that we have our Excel spreadsheet log into your Office 365 using the account that has the Power BI subscription. Below is a screenshot of what the home page of the Power BI site looks like.

Click on Sites. Then click on settings and then Site contents. 

Find your document library and upload the excel spreadsheet that you created. Once the document is uploaded go back to Site Contents and click on Power BI as shown below

If this is the first time you are accessing the Power BI site then you will get the below message. You can either click on the 'X' on the top right or click on the 'Use my own data' Below is a screenshot

You will now be able to see the excel spreadsheet you just uploaded. Below is the screenshot of the excel spreadsheet that was uploaded in this scenario

To feature the reports on the front and center of the Power BI site you need to enable the report. Below is a screenshot of where the enable option is.

Once you click Enable, below is the message you will receive. Click Enable

Enabling reports can take a few minutes. You will see the below message

Once it is completed you will see a Report enabled message as shown below

You can click on the Open report button as shown above which will lead you to the excel spreadsheet you built. You view all the worksheets that you had created including the Power View. Below screenshots shows the Power View and the new worksheet we created using the Azure SQL database

Finally, you can feature your report on the Power BI home page by clicking on the ellipsis next to the report and then clicking on the Add to Featured Reports

Now your report is featured on the home page as shown below

Conclusion

Excel's Power Query, Power Map and the ability to save all your queries in the Power BI's data catalog is potentially a one-stop-show for all your BI requirements. In Excel the databases from Azure SQL can easily be pulled and manipulated directly in Excel with a no-code solution and the queries can be saved for future use in the Office 365's data catalog. Finally, SharePoint Online can be used to feature the reports that you have built for your enterprise to view. 

Helpful links

Microsoft Power Query for Excel 

Power Map for Excel 2013

Configure Excel and Excel Services by SQL Server Analysis Services on SharePoint 2010