Using Azure SQL and PowerBI to report on Speaking Events

Presenting Nov 9, 2018

Someone I have followed on Twitter for a long time, Paul Andrew and was lucky to meet in person this year posted a Tweet about his speaking stats and how he'd visualised it in a PowerBI Dashboard.

I decided it was time to overhaul my #PowerBI community speaking dashboard! Especially now I've been to a few more international events... makes the maps more fun :-)

TSQL and PBIX on #GitHub here if you'd like to use it: https://t.co/JFTShSggWl pic.twitter.com/WqKZIjwhVY
— Paul Andrew (@mrpaulandrew) November 7, 2018

Which I thought looked absolutely amazing, so I set about copying what he'd done for my own speaking involvement. I only started public speaking in August 2017, so I don't have quite the same cool stats that Paul has but I think it would be really cool to capture what I've done so far and see where I go in the future. Plus I get to play with some Azure and PowerBI deploying the solution.

Getting Started

In order to implement Paul's solution you need a SQL database and access to PowerBI. You can use any SQL database that you have available, I am going to implement my solution with an Azure SQL database. I only need a very small database as it will only be used sporadically and won't store much data.

Deploy an Azure SQL Database

To create my SQL server within Azure I have decided to use Azure CLI and the Azure Cloud Shell

Below is the Azure CLI code that I used:

The above code will deploy a Resource Group, a Logical SQL server, a SQL database and configure it so that your client (desktop) IP address will be allowed to connect with the SQL server.

Now that you have the SQL Server and database created you need to connect to it and start adding data.

Connecting to your Azure SQL Database

Connecting to your Azure SQL server or database is no different than how you would interact with an on-premises SQL server/database. You can use SQL Server Management Studio (SSMS), Visual Studio Code, Visual Studio, or another tool that you like to use for SQL interactions. I spent a lot of time within Visual Studio Code, so this is going to my connection tool.

In order to connect to SQL using Visual Studio Code you need to have the SQL Server(mssql) extension installed. The Microsoft Official Documentation covers off how to install this extension if you need some assistance.

Once that is installed to connect to the SQL database you created earlier follow these steps:

Open a new file either through the GUI or using CTRL+N
By Default Visual Studio Code creates a new file in Plain Text, we need to convert this to SQL. Press CTRL+K,M to change it to SQL
Now you want to connect to the SQL server, do to his press CTRL+SHIFT+P to launch Visual Studio Code's Command Palette
Type sqlcon and select the MS SQL: Connect option
Select Create Connection Profile
The first prompt will ask for your SQL server name, this will be name of the SQL server you created earlier followed by ".database.windows.net"
The second prompt will ask for the database name, again this is the name of the database you created earlier
The next two prompts are related to the username and password you set earlier
The next prompt will ask you if you want to save the password so you won't be prompted again for this information, configure as appropriate
The last prompt ask you to specify a name for the connection profile

Visual Studio Code will now start to connect to your SQL server, you'll see progress in the bottom right hand corner, it will say connecting and then change to the SQL server name once connected.

Now you are interacting with the database you need to configure the database.

Setting up the Database

It maybe worth while increasing the specification of your SQL server while doing this stage, and then reducing it back to basic as it will run faster. However, you can keep it to basic and the queries will run fine.

Paul has shared some SQL scripts within his GitHub repo

I've been a bit selective with the SQL that I've used from Paul's below is what I used to configure my database:

The above, sets up your database and also creates the two tables that you need. One for recording your speaking event information and one for the event logos.

Entering your Data

Now that you've got the infrastructure set up, it's time to start adding your speaking data.

You have two tables, one that holds all your speaking stats (SpeakingLog) and the other table (EventLogos) is used to store the location of the logos of events you've spoken at.

Inserting the data in the SpeakingLogs table is quite easy, I've done a bulk import of the speaking data from last year and this year. Using code as below:

Insert into [dbo].[SpeakingLogs] 
Values 
('11/08/2018', 'STEM', 'Reading', 'United Kingdom', '51.461231', '-0.925947', '25', 'Azure', 'Regular'),
('08/30/2017', 'Azure UG', 'Edinburgh', 'United Kingdom', '55.953388', '-3.188900', '40', 'Azure', 'Lightning Talk');

You can view the results of your data input with this command:

Select * from [dbo].[SpeakingLogs]

The next part of the data input is to add in the logos of the events you've attended, again a bulk import of this would look like this:

Insert into [dbo].[EventLogos]
VALUES
('STEM', 'https://www.stem.org.uk/sites/all/themes/custom/stem_base/assets/img/patterns/logo-stem-new.svg'),
('Microsoft Cloud User Group', 'https://www.mscug.com/wp-content/uploads/2018/02/MSCUG-logo.jpg');

And you can view the results of your input with this command:

Select * from [dbo].[EventLogos]

Be sure to match up the EventName you've inputed into the SpeakingLogs table with the EventName you use in the EventLogos table. If they don't match your data visualisation within PowerBI will be off.

Visualise the data in PowerBI

You will need PowerBI Desktop installed on your machine for this next stage. You can download the latest version form here

PowerBI is something I've dabbled with in the past and delivered solutions within for customers but it isn't something I'm an expert in. Paul has shared his .PBIX (PowerBI file) on his GitHub page here

I downloaded the .PBIX file and opened it within PowerBI Desktop. The first thing to do is change the Data Source within the .PBIX file to your SQL database. To do this follow these steps:

Click on Edit Queries > Data Source Settings on the top toolbar

datasource

Right click on the data source currently there and select Change Source
Input your SQL server name and click OK
When you return to your PowerBI Report you'll see a yellow ribbon along the top. Asking you to apply the changes
When you click on the apply changes button, you will be prompted to enter credentials for SQL database. Ensure you click on the Database option and input your username and password

PowerBI will connect to your SQL database and start to pull in your data, you should see the report refresh and apply your data.

Within Paul's setup he uses Postcode to track and map the location of his speaking engagements, however I am using Latitude and Longitude, as I've found it more accurate. To account for this change you will need to change the settings on the map.

Click on the map visualisation
Down the right hand side you will see the various different data fields
Remove the Postcode field from Location
Select Lat for the Latitude field, ensuring you pick 'Don't Summarize' for the data
Select Long for the Longitude field, again ensuring you pick 'Don't Summarize' for the data