How to get data from SQL Server into Power BI Desktop?
Power BI is capable of using many databases as data sources. This makes it a flexible business intelligence software that offers solutions suitable for the infrastructure of every company and institution.
Which database can we connect to with Power BI?
SQL Server is one of the most popular data sources in Power BI. It is an important data source, analysis, and reporting tool for Power BI.
How do we get data from SQL Server?
- Select the SQL Server database tab from the "Get Data" section in Power BI Desktop application.
- On the screen that opens, we must enter information about the database we want to connect to.
- We press the OK button after filling out all of the fields mentioned above.
Server: Enter the name of your Microsoft SQL Server. In this example, I have defined the server I set up on my computer. So I defined on my computer named SAMSUNG/ SQLEXPRESS.
Generally, in companies or institutions, SQL Server is installed on a server that is always open. The IP number of that server (such as 10.10.100.6) can also be written in this section, but the recommended setting is to write the Server name. (like SQLEXPRESS)
Database: Write the name of the database from which we will get the data. If you leave this field blank, all databases in the SQL Server you have written will be listed.
Data Connectivity mode: Select data connectivity mode.
What are the differences between Import and DirectQuery?
a.Import
It allows changes on the Power BI-Edit Queries screen on the imported data with the Import option. In Schedule Refresh, a maximum of 8 schedule adjustments can be made per day. These times are in half-hourly segments. (like 07:30 AM, 9:00 PM)
b.DirectQuery
We cannot change the data in the DirectQuery option because we get it directly from the data source rather than importing it into Power BI.
So to put it plainly, we cannot make any of the changes we can make on the Power BI Desktop-Edit Queries screen. If we try to make changes, we get the message, "This step results in a query that is not supported in DirectQuery Mode."
How do we modify the DirectQuery data?
If we want to change the data, we can do this by using the SQL codes within the data source itself.
Furthermore, Schedule Refresh in DirectQuery is different from a fixed timeframe used in Import. Time frequencies in the form of once every 15 minutes, once every day, etc., are created.
By pressing the Load button, you can load the data into Power BI Desktop or switch to the Power BI Desktop – Edit Queries screen by pressing the Edit button.
Setting SQL Server Connection Permissions
If you see a screen similar to the one below, you must configure the SQL Server connection settings so that Power BI knows the username and password to access SQL Server and refresh the data.
Windows Authentication: If you are authorized to connect to SQL Server with your Windows account, You can select "Use my current credentials". If you are authorized to connect with an alternative Windows account, you must select "Use alternate credentials" and fill in the "User name" and "Password" fields that will become active below.
SQL Authentication: If you are authorized to connect to SQL Server with SQL Authentication, you must define this account by filling in the "User name" and "Password" fields from the Database menu.
