3. Data Source

A variety of data sources could be connected to Power BI. The Get Data button toggles all of the data sources that may be used to import data into Power BI.

4. Connecting to SQL Server

Let’s look at an example of connecting a SQL server dataset to Power BI Desktop and using that to examine the data.

Database Example

We’ll have to use a publically available SQL Server installation on AWS for the demonstration, as well as a database built from the Superstore dataset.

Our goal as Data Analysts would be to assess the data and identify significant areas of concern inside this hypothetical firm. 

Getting into the sample database

SQL Server Management Studio should be installed (SSMS). SSMS seems to be a free and fully integrated management environment for every SQL system. SSMS allows you to install, manage, and modify the data-tier components that your applications rely on, and also create inquiries and workflows.

After you’ve downloaded and installed the software, you’ll see the page below, which will prompt you for particular credentials.

1. Insert the given information:

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

Authentication: SQL Server Authentication

Login: SQL

Password: SQL

The ‘SuperStoreUS’ database will indeed be accessible as a ‘Read Only’ user. As our sample, the instance is ec2–52–14–205–70.us-east

2. compute.amazonaws.com, the databases are SuperstoreUS and Test, and the tables inside the SuperstoreUS database are Customers, Orders, and so on. As a result, there could be lots of instances, each of which can have multiple databases, each of which would contain various tables.

5. Importing SQL data into Power BI

The data is organized into searches by Power BI Desktop. It implies that all of the information is organized in a table-like format.

Making the connection

Proceed to the start button by opening Power BI Desktop. The Get Data tab pane gives you a number of options when it comes to sources of data that could be linked to the Power BI Desktop. We’ll establish a connection to the SQL Server.

When you select SQL Server, a fresh screen will appear, asking again for the server with which we wish to connect our Power BI Desktop. Fill in the blanks, and you’re finished. We could now inspect the details of the selected table by clicking on it. For uploading a certain table, merely check the box beside it click the load button.

Modes of Data Connectivity

Power BI Desktop may link to a SQL database in two distinct ways, both of which display on the homepage.

Import

The import technique, as the title suggests, ‘imports’ the chosen tables onto Power BI Desktop. The imported data is therefore used by Power BI to create a visualisation or perform any adjustments. We must reload the data to view any modifications in the data, which re-imports the full data set.

DirectQuery

No information is incorporated or transferred to Power BI Desktop if DirectQuery is selected as an option. Power BI Desktop searches the fundamental data source while we generate or process the information through visualisations, ensuring that we’re still dealing with the most recent data. Nevertheless, unlike the importing method, this approach has restricted data processing capabilities.

6. Query Editor

All of the chosen tables would be imported into Power BI Desktop & displayed in the Data view as distinct datasets. .

We can make changes to our datasets from here. We’ll use the Query Editor to accomplish this. The query generator could be used to edit datasets regardless of the data source. The query editor allows us to perform operations such as rebranding a dataset, eliminating a single or several columns, and so on.

The number of functional inquiries is displayed on the left pane, while the Query Settings pane on the right provides all of the stages related with a query.

Advanced  Editor

The script for the inquiry that has been run against the source of data is displayed in the Advanced Editor. The syntax is M, which stands for Power Query Formula Language. It is also possible to write your own code.

Make a backup of your work

Choose Close & Apply from the Query Editor’s File menu once you’ve made the appropriate data changes in the query editor. The adjustments will be applied to the data in Power BI Desktop.

 Top 50 frequently asked Power BI Interview Questions !

Subscribe to our YouTube channel to get new updates..!

7. Merging Datasets

When we wish to integrate one or maybe more datasets into one, merging datasets proves useful. The Query Editor can also assist with this merger. We’ll pull multiple tables(3) from the SuperstoreUS database this time.  Customers, Orders, and Returns are the three tables.

The three specified tables are listed in the Fields column. Now, go to the Edit Queries option and click the Combine option from the dropdown menu, which will reveal the Merge option. We’ll use the merged existing queries to construct a new query.

We pick the tables to be combined as well as the kind of join we need in the Merge dialogue box. Columns that are similar to both tables must be chosen.

Let’s start by creating a new query called Orders and Customers. Through this query, we’ll join the Orders and Customers tables. All of the key columns from the orders table, as well as correlation columns from either the customer’s table, were included in the new query. Except for the last one, which reflects the Customers query, eliminate all the correlation columns. Next, from the Customers query, choose columns to merge into the newly merged query.

The merged query is composed of a single database that has all of the required columns. Rather than operating with several data sources, which might cause confusion, you could now easily interact with one single database.

8. Building & Publishing a Dashboard

We may begin the Dashboard construction process since we had the dataset prepared along with all the alterations completed. The Power BI dashboard often referred to as a canvas, is a single sheet that contains multiple visualizations that serve to convey a narrative. The reports’ visuals, known as tiles, are attached to the dashboard.

Now just to see what recommendations we can glean from the superstore data set.

• States seeking profit
• Profit & Sales by Segment

We’ll obtain a dashboard that looks like the one below after we’ve formatted the size, look, and colour.

Publishing
Data would be only valuable if it can be transferred across individuals or groups. By publishing the Dashboard or reports to the Power BI Service, they can be shared with others. The Dashboards/Reports can then be seen or interacted with using the Power BI Apps.

Conclusion:

When SQL and Power BI are combined, data analysis is carried out properly. We can integrate SQL Server to Power BI with ease and retrieve information right into it. Customers can switch connections with a single click in Power BI to execute in-memory searches to a bigger dataset. SQL is a handy tool that, when combined with Power BI’s expertise, may assist in monitoring and reporting more effectively and informative.

Related Articles:

1. KPI in Power BI
2. Looker vs Power BI
3. MSBI vs Power BI