Menu Close

A First Swing at PowerBI: Connecting to Azure SQL Server

NuWave recently participated and placed in the Epic App Challenge at the Intelligence and National Security Summit. We created a novel analytical tool that leverages the power of machine learning in the Azure cloud environment. To read more about our winning product, go here.

I was responsible for making the dashboards showcasing the analytics we were generating, and the tool of choice was PowerBI. While I have utilized other business analytics and data visualization tools, this was one of my first experiences with PowerBI.

The first step in creating a user-friendly dashboard is connecting to the data set. Our data was hosed in the Azure SQL Server database. After selecting the “Get Data” button, a menu pops up, as shown below.

 

After you select the Azure SQL database from the options, you are taken to a screen where you can provide connection information.

The below screenshot is the landing page for the Azure SQL Server instance in the Azure Console. This page has the server name you will need to connect.

 

The next screenshot is the landing page for the Azure SQL database instance linked to the Azure SQL Server instance in Azure Console. This page has the database name you will need to connect.

Because I need to import all of the data, I’ll leave the ‘import’ option selected. If I had more data and wanted to query the database directly, I could choose ‘DirectQuery’ instead. Since the data set I was working with was not large, the ‘import’ option was best suited to my use case. To read more about DirectQuery, read here.

 

The next screen will prompt you for login credentials. This prompt took me a few tries to get right – initially, I was logged in with a different Microsoft account than what was linked to my Azure instance, so it failed to connect.

Unfortunately, once you enter a set of credentials, PowerBI keeps trying to connect with those credentials, regardless of how many times it fails. It does not re-prompt you to enter the credentials again, so you may run into this issue if you do something as simple as mistyping your password. Here’s how you fix it.

Open the Data Source Settings dialog box by navigating to the File menu and clicking on ‘Options and Settings.’ Once in the dialog box, click over to ‘Global Permissions,’ select your data source, and then click “Edit Permissions.”

 

The ‘Edit Permissions’ dialog box lets you edit or delete a set of credentials used to access a data set. If you select the ‘Edit…’ button, the original database connection prompt will show, allowing you to choose how you’d like to connect to the data source. This dialog box is where I could switch my database credentials to access my Azure SQL database.

Another thing that may prevent you from connecting to your Azure SQL database is the firewall rules associated with the server. These rules are configured from within the Azure Console. On the Azure SQL database’s landing page, click the ‘Set server firewall’ button, which takes you to the Firewall settings page.

Once here, use the UI to create a new IP rule for your computer’s IP address. You can find your computer’s public IP address by searching “What’s my IP” in a browser.

Once you are successfully connected, the UI helps you choose which tables from the database you’d like to import. You can preview data sets in-pane, and anything checked will be consequently imported.

If you’d like to perform transformations to your data (change data types, alter column names), you can click the ‘Transform Data’ button to be taken to the Power Query Editor window. Clicking the ‘Load’ button loads the tables, as-is, from the data source.

Don’t worry about transforming the data now. You can modify the data using the Power Query Editor at any time by clicking the ‘Transform Data’ button from the data preview pane, which is where you land after the data sets are successfully loaded.

Now we can get to building visualizations!

 

The first thing I wanted to build was a histogram, but unfortunately, PowerBI does not have a histogram visualization included out-of-the-box. Instead, you can go to the PowerBI AppSource and download templates for other visualization types. To reach the AppSource, select the triple dots icon under the available visualizations, and choose ‘Get more visuals.’

This marketplace-like feature is where you can search through community offerings for different types of visualizations, download them, and then leverage them with your data to show some pretty cool things. Some cool features are packed into this little store (like violin plots, infographics, and a 3-D globe map). For now, I stuck to the standard histogram option, but I’ll be back to try some of the more modern options.

Once added, I can select it from the visualizations tray, and I can click and drag it onto my page.

Some quick and easy configuring gives me an interactive histogram that shows the frequency of each tone value across my data set.

That concludes my 15-minute foray into PowerBI in which I was able to troubleshoot and connect to my cloud-based data set, import a custom visualization template, and configure a histogram to visualize my data.

Posted in Analytics, BI Dashboards, Blog

Leave a Reply

Your email address will not be published. Required fields are marked *