Learn About Power BI Data Source Options And SQL Database Connection

Introduction

This article will describe data connection in the Power BI desktop and how to connect a data source in Power BI to create reports and dashboards. Eventually, I will demonstrate the connection of SQL Server Database in Power BI with an example.

Dashboards

Steps

Firstly, launch the Power BI Desktop application.

After that click on Get data, from here you can choose your data source and connect to it.

Power BI

Data Sources

We can connect to several types of data sources in Power BI which I will introduce in this article.

Data Sources

Data Source File

In Power BI we can import data from any file such as Excel, Text/CSV, XML, JSON, Local Folder, PDF, and SharePoint Folder.

File

Data Source Database

We can use any database for example, SQL, MySQL, Postgre, Oracle, Access database, Azure Database, IBM, Amazon, and so on in Power BI to generate reports and dashboards.

Database

Data Source Power Platform

In Power BI we can get data from Power platform: Power BI datasets, Power BI dataflows, Common Data Service, and Power Platform dataflows and generate reports and dashboards graphically.

Power Platform

Data Source Azure

Furthermore, we can connect to Azure databases and generate reports and dashboards using Power BI.

Azure

Data Source Online Services

Additionally, in Power BI we can get data from various online services such as SharePoint List, Dynamic 365, Microsoft Exchange, Azure DevOps, Google Analytics, Adobe Analytics, and so on as illustrated below.

Online Services

Demonstration of SQL Server data connection

Data SourceConnection

In this demo, we will use the SQL Server database as a data source to generate reports in Power BI.

Open the Power BI desktop and then click on Get Data; after that select SQL Server and click on it.

SourceConnection

Provide the Server name and database and Click on OK. It connects to your SQL Database. We can connect to any database and any source as mentioned above.

SQL Database

Data Connectivity Mode

There are two types of connectivity while connecting with a database in Power BI which will be briefly explained below.

Import

It imports the existing data from your database. It means that it copies available data to your Power BI Desktop. When your report runs it shows the reports and visualization with imported data. To get the fresh or new data we must refresh the reports, after refreshing, it again imports the latest datasets.

Direct Query

It does not copy or import the data into the Power BI Desktop; however, it generates relational sources, tables, and columns in the fields list. It queries the data directly to the database when you run the reports. It always visualizes the reports and dashboards with the current set of data so there is no need to refresh reports or dashboards to get the latest updated data. In some cases, it may take time to load data into reports or dashboards.

Choose what's best for you based on your needs.

After that, you will reach the step as shown below. Choose the table or view from which you want to generate reports or dashboards. In my case, I have only one table in my Demo database, so I have only one option to select and I am selecting Employee Table.

Direct Query

Select Table and click on Load

Employee

Your Table Fields will be listed on the right-hand side of your screen as depicted below.

Table Fields

Conclusion

This article has described the availability of data connection in Power BI desktop and how to connect those data sources in Power BI to generate reports and dashboards.

Moreover, it demonstrated how to connect with SQL databases with an example. In the next part, we will continue from here and generate reports using Power BI.