SQL with Power BI

4. Connecting to SQL Server

Let us walk through an example depicting how to connect SQL server database to Power BI Desktop and then use it to analyze the database.

Sample Database

For the demonstration purpose, we will be using a publicly accessible SQL Server instance on AWS and a database which has been created based on the Superstore dataset.

This dataset contains information about products, sales, profits, etc. and our aim as Data Analysts is to analyze the data and find critical areas of improvement within this fictitious company. This SQL server instance has been hosted by Ken Flerlage.

Accessing the sample database

• Install the SQL Server Management Studio(SSMS). SSMS is a free and integrated environment for managing any SQL infrastructure. With SSMS one can deploy, monitor, and upgrade the data-tier components used by your applications, as well as build queries and scripts.
• Once downloaded and installed on to your system, you will see the following screen asking for specific credentials.

Enter the following credentials:

Server Name: ec2-52-14-205-70.us-east-2.compute.amazonaws.com
Authentication: SQL Server Authentication
Login: SQL
Password: SQL

You will now be granted a ‘Read Only’ access to the ‘SuperStoreUS’ database.

For our example, ec2–52–14–205–70.us-east-2.compute.amazonaws.com is the name of the instance, SuperstoreUS and Test are the databases, and Orders, Customers etc. are the tables within the SuperstoreUS database. Thus there can be multiple instances, and each instance can further contain numerous databases which can also have multiple tables.

5. Importing SQL data into Power BI

Power BI Desktop organizes the data into queries. This means all the data is laid out into a table like structure.

Setting up the connection

Open the Power BI Desktop and navigate to the start screen. Here the Get Data tab pane offers a lot of choices in terms of the data sources that can be connected to Power BI Desktop. We will connect to the SQL Server.

On Clicking the SQL Server option, a new screen will open up which will ask for the Server to which we want our Power BI Desktop to be connected. Enter the details and its done.

We can now click on the desired table and view its contents. To load a particular table, simply tick the checkbox next to it and load it.

Data Connectivity Modes

SQL database can be connected to Power BI Desktop in two ways, both the options which appear on the main screen.

Import

As the name suggests, import method ‘imports’ the selected tables into Power BI Desktop. Power BI then uses this imported data for creating a visualization or doing any manipulations. To see any changes in the underlying data, we need to refresh the data which imports the entire data set again.

DirectQuery

If DirectQuery is used as an option, no data is imported or copied into Power BI Desktop. While we create or interact with data through visualizations, Power BI Desktop queries the underlying data source, which means we are always working with the current data. However, this method provides limited options as to data manipulation, unlike the import method.

Here is a link that goes in depth regarding the Direct Query method.

6. Query Editor

All the selected tables will be loaded into the Power BI Desktop and will be displayed as individual datasets in the Data view.

From here, we can modify our datasets. For this, we will take the help of the Query Editor. Query editor can be used for modifying datasets irrespective of their data source. We can do manipulations like renaming a dataset, removing a single or multiple columns, etc. in the query editor.

The Left pane displays the number of active queries while the right pane is called the Query Settings pane and displays all the steps associated with a query.

The Advanced Editor

The Advanced Editor displays the code for the query that is being executed against the data source. The syntax corresponds to M, the Power Query Formula Language. One can also create their own code.

Saving your work

After having performed the necessary modifications in data through the query editor, select Close & Apply from Query Editor’s File menu. This will apply the changes to the data in the Power BI Desktop.

7. Merging Datasets

Merging datasets comes in handy when we want to combine one or more datasets into one. This merging is also facilitated through the Query Editor. This time we will load in three tables from the SuperstoreUS database. The tables are Orders, Customers, and Returns.

The Fields column is populated with the three selected tables. Now, click on the Edit Queries button and navigate to Combine option, where the dropdown will expose the Merge option. We shall use the create a new query by merging existing queries.

The Merge dialog box opens up (as shown in the following figure), and we select the tables to be merged and the type of join we want. We need to select columns which are common to both tables.

Let’s create a new query and name it as Orders and customers. We will combine the Orders and Customers table through this query.

This new query contains all the primary columns from the orders’ table and relationship columns from the customers’ table. Delete all the relationship columns except the last one, which represents the Customers query. Then select columns from Customers query to add to the new merged query.

The following demo will make the process more clear.

This merged query consists of a single database with all the desired columns. We can now easily work with this single database instead of working with multiple data sources which can lead to confusions.

8. Building & Publishing a Dashboard

Once we have the dataset ready with all the manipulations done, we can proceed for the Dashboard creation process. A Power BI dashboard, also known as canvas, consists of many visualizations on a single page which helps to tell a story. These visualizations called tiles are pinned to the dashboard from the reports.

Let’s now try to understand what insights we can get using superstore data set (Source: Power BI Dashboard):

• Profit by States

• Sales & Profit by segment

• Sales & Profit by Region

• Sales by Sub-Category

• Profit by region

• Quantity

After formatting the size, appearance, and color, we will get a dashboard which resembles the one below.

Superstore Dashboard

Publishing

Data is only useful when it can be shared among people or organization. The generated Dashboard or reports can also be shared by publishing it to the Power BI Service. We can then use the Power BI Apps to view or interact with the Dashboards/Reports.

9. Conclusion

Using SQL and Power BI together takes the data analysis to the next level. We can easily connect the SQL Server to Power BI and extract the data directly into it. Power BI enables the users to toggle connections with a click to apply in-memory queries to a larger dataset. SQL is a pretty useful tool and when leveraged with the expertise of Power BI can help to make the analysis more powerful and insightful.

References

• What is Power BI Desktop?
• Power BI Dashboard – Creating Dashboard In Power BI From A Report (By Vishal Padghan,edureka)