🎬Power BI: Connecting to an Online SQL Server Relational Database and Creating Visualizations
Welcome to another Power BI tutorial! In this tutorial, we will guide you through the process of connecting Power BI Desktop to an online SQL server database. Below, you will find the server address, username, and password needed for the connection.
To begin, click on the SQL server button located on the home tab to connect to the SQL server.
Next, enter the server name and the name of the database you wish to connect to.
Select "Import" as the data connectivity mode and click "OK."
You will then be prompted to enter the username and password for the database connection.
Since we are connecting to an online SQL server, navigate to the "Database" tab.
Please note that any username and password entered here will be permanently stored on your computer. If you wish to remove these passwords, you will need to remove the data sources from the query editor.
Enter the username and password, then click "Connect" to establish a connection to the online SQL server database.
In the navigator window, you can select the tables you want to import. Choose the desired tables and click "Load" to import them into your Power BI application.
Once the tables are loaded, you can find them in the data tab, where you can utilize them to create Power BI visuals.
It`s important to note that the database we are using is a relational database. In the model view, we need to establish relationships between the tables by connecting them using their respective IDs.
First, arrange the tables for better visibility and management.
Power BI sometimes creates relationships automatically. Remove any automatically created relationships.
Next, connect the "Departments" table to the "Units" table using the department ID.
Then, connect the "Designations" table to the "Employees" table using the designation ID.
Use the education ID to connect the "Education" table.
Connect the "Units" table to the "Employees" table.
To assign basic salaries to employees, connect the "Salary" table to the "Employees" table using the designation ID.
Now, let`s create a title for our dashboard using the text box visual.
From the "Departments" table, add the "Title" field to the dashboard.
Convert this visual into a pie chart.
Add the "First Name" field from the "Employees" table to the values field of the pie chart.
Rename the legend to "Departments" and the values field to "Number of Employees" to display the number of employees by department.
Place the pie chart in an appropriate location on the visual.
To display the number of employees by designation, duplicate the existing pie chart.
Remove the "Departments" field from the legend area and replace it with the "Title" field from the "Designations" table.
You can use filters to hide senior management and only show employees.
Next, display the number of employees by education by duplicating an existing chart.
Replace the "Designations" field in the legend area with the "Description" field from the "Education" table.
Rename the designation field to "Education" to display the number of employees by education.
To display the number of employees by location, use a map visual.
Add the "Address" field from the "Employees" table to the location area.
Add the "First Name" field to the bubble size area as a count.
Rename the count of the "First Name" field to "Number of Employees" and the "Address" field to "Location".
Add a visual to display the number of employees by gender.
Add the "Gender" field from the "Employees" table to the y-axis.
Add the "First Name" field to the x-axis as a count.
Rename the x-axis to "Gender" and the y-axis to "Number of Employees".
Change the visual type to a pie chart.
Finally, add a scatter chart to display the number of employees by salary.
Add the "Basic Salary" field from the "Salary" table to the y-axis of the scatter chart.
Add the "First Name" field to the x-axis as a count.
Rename the x-axis field to "Number of Employees" and the y-axis field to "Basic Salary".
That`s it! You have successfully imported data from an online SQL server, created a relational database, and designed visualizations using that database.
We hope you found this tutorial helpful. Thank you for watching, and don`t forget to like and subscribe for more tutorials and updates.
Link to the file: https://drive.google.com/file/d/1Eg4CsGqTfUtO_twvYtbTbyNCbXCvS37T/view?usp=sharing
#PowerBI #Tutorial #SQLServer #DataVisualization #DataAnalysis #DataConnectivity #RelationalDatabase #PowerBIDesktop #OnlineDatabase #DataImport
Chapters:
00:23 - Connecting to an online SQL Server Database
02:02 - Creating Relationships between Tables
03:28 - Creating an HR Dashboard