_edited_edited.png)
What is Power BI
Power BI is a cloud based robust business intelligence tool developed by Microsoft that enables users to analyze and visualize the data from multiple sources, including SQL Server databases. It is a powerful as well as a flexible tool for connecting with and analyzing a wide variety of data. Also, Power Bi has an amazing out of the box connection capabilities like easily integrating with databases.
This offers increased advantages regarding functionalities and comes in handy for Data Scientists who are used to working in SQL.
It provides an optimized, live connector to SQL Server so that we can easily create charts, reports, and dashboards by directly working with a large amount of data.
In summary Power BI is versatile and user-friendly tool for connecting, analyzing, and presenting data from SQL Server and other sources.
Power BI Desktop is a free application you install on your local computer that lets you connect to, transform, and visualize your data. With Power BI Desktop, you can connect to multiple different sources of data, and combine them into a data model which is called modeling.
What is SQL Server
SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is built on top of SQL, a standard programming language for interacting with relational database. Also, provides a secure and scalable platform for data storage and access. It is widely used in various industries for data storage, analysis and reporting. We can easily connect Power BI to SQL Server and utilize the capabilities of both tools to gain valuable insights from data.
What is SQL Server Management Studio
SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure. Use SSMS to access, configure, manage, and develop all components of SQL Server. It enables you to manage Analysis Services objects, such as performing back-ups and processing objects.
SSMS runs on Windows only. If you need a tool that runs on platforms other than Windows, you can use Azure Data Studio. Azure Data Studio is a cross-platform tool that runs on macOS, Linux, as well as Windows.
You can also use Management Studio to organize packages into folders, run packages, import and export packages, and upgrade Integration Services Packages.
How to import data to SQL Server
CSV files serve as a widely used format for storing tabular data in a plain text format. They allow organizing data into rows and columns, with each line representing a separate record and the values separated by commas.
Here, I am going to import a csv file into SQL Server first, then we can connect SQL Server to Power BI.
To import data into SQL Server, we need to install Microsoft SQL Server Management Studio in our system.
After installing SSMS the window will be as shown below.
After installing SSMS (SQL Server Management Studio) the window will be like this. Here we need to select the server's name. Sometimes it will pop up automatically. Then select connect button.
After that we can see the Object Explorer window with Databases, Security and Server Objects.
Now we need to import csv file into our SQL Server. For this, I have to create a new database as shown below.
I gave the new database name as sampledb and then selected ok button.
 We can see the sampledb database which we created just now. Then, right click on the sampledb and select tasks and then import Flat File option.
In this window just selected the Flat File Source option. It shows the window where we can browse our source data file path.
Here, we can browse the file from our system,
Then the data preview which we uploaded will appear as shown below.
click the next button.
Then we can see all the columns with datatypes. If any changes, we need to do we will do here and click on the next button.
Now click on the Finish button where importing data will be done.
Now close the window and refresh the database sampledb. We are now able to see the file uploaded in the sampledb.
Once the import is completed, you should see a summary of the process, including any potential errors or warnings that may have occurred during the import.
Connect Power BI to SQL Server
Create a new report in power BI Desktop
Open Power BI desktop and select the option Import data from SQL Server.
In the dialog, select the Server and Database (optional). The Data Connectivity mode defaults to ‘Import’ – leave it on this option. The import option actually loads the data into the Power BI file, making it quicker to work on, but is not suitable for very large datasets. The alternative Direct Query option doesn’t load the data into Power BI, but instead runs queries against the source data each and every time a visualization is refreshed, or a filter changed.
You can now select the table that we just created. Here I selected the store table and then clicked the load button.
Once the data has been loaded, we will see the tables in the Fields pane on the right-hand side. Now we can create the visualizations that we need for our report.
For example, creating a clustered bar chart to show the sales product wise.
As a summary, we imported the data into SQL Server and connect the SQL Server database to Power BI, for creating reports.
In the same way we can connect database to Power BI from PostgreSQL also. I will show it briefly.
  Open Power BI desktop and select the option, Database and PostgreSQL database, then select connect button.
It will pop the window to give server details as,
After importing the data, we can select the tables which we want to load and then create reports in Power Bi Desktop.
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 or Reports.
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 handle connections with a click to apply in-memory queries to a larger dataset. SQL is a pretty useful tool with the expertise of Power BI can help to make the analysis more powerful and insightful.
References
Thanks for reading my blog.
Comentarios