NorthStar Analytics
Pricing

HiDocs High-Quality Ghost Documentation Theme

How to get data from SQL Server into Power BI Desktop?

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?

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?

How do we get data from SQL Server?1
  1. Select the SQL Server database tab from the "Get Data" section in Power BI Desktop application.
  2. On the screen that opens, we must enter information about the database we want to connect to.
  3. We press the OK button after filling out all of the fields mentioned above.
How do we get data from SQL Server?2

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.

How do we modify the DirectQuery data?

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.

‌ Setting SQL Server Connection Permissions

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.

Make your business data transparent!
  • Subscribe to a package

    Subscribe and explore the limits of your business with data or thinking to expand your agency value proposition with Google Data Studio dashboards?

  • Request unlimited dashboards

    You can request as many dashbaords you might have and as many revisions as you might need. We create a queue of your tasks, the first tasks that come in are the tasks that we’ll deliver first.

  • Your Data Studio Department

    Optimise your business with complete transparency through dashboards or provide value-added Google Data Studio service to your agency clients.

Make your business transparent

The fastest easy-to-use Google Data Studio dashboards without any recurring cost.