Introduction
This article will describe data connection in 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.
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.
Data Sources
We can connect to several types of data sources in Power BI which I will introduce in this article.
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.
Data Source: Database
We can use any databases, for example, SQL, MySQL, Postgre, Oracle, Access database, Azure Database, IBM, Amazon and so on in power BI to generate reports and dashboard.
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 dashboard graphically.
Data Source: Azure
Furthermore, we can connect to Azure databases and generate reports and dashboards using Power BI.
Data Source: Online Services
Additionally, in Power BI we can get data from various online services as like SharePoint List, Dynamic 365, Microsoft Exchange, Azure DevOps, Google analytics, Adobe Analytics and so on as illustrated below.
Demonstration - SQL Server Data Connection
Data SourceConnection
In this demo we will use sql Server database as data source to generate reports in Power BI.
Open Power BI desktop and then click on Get Data after that select SQL Server and click on it.
Provide server and database name and click on Ok. It connects to your SQL Database. We can connect to any database and any source as mentioned above.
Data Connectivity Mode
There are two types of connectivity while connecting with 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 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 a current set of data so there is no need to refresh reports or dashboard to get latest updated data. In some cases, it may take time to load data into reports or the dashboard.
The choice is yours, based on your need. Select whichever one is best for you.
After that, you will reach the step as shown below. Choose your table or view from which you want to generate reports or dashboard. For 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.
Select Table and click on Load
Your Table Fields will be listed in the right hand side of your screen as depicted below.
Conclusion
This article has described the availability of data connections in Power BI desktop and how to connect those data sources in power BI to generate reports and dashboard. 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.