Introduction
This article will describe possibility of data connection in Power BI desktop and how to connect data source in power BI to create reports and dashboard. Eventually, I will demonstrate connection of SQL Server Database in Power BI with an example.
Steps
Firstly, launch 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 dashboard 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.
Sample Demonstration: SQL Server Data Connection
Data Source Connection: 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 name, Database 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 refresh it again imports 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 database when you run the reports. It always visualizes the reports and dashboards with current set of data so no need to refresh reports or dashboard to get latest updated data. Some cases, it may take time to load data into reports or dashboard.
Choice is in on your own. Based on your need which one is better for you, you can select this option.
After that, you will reach to 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 right hand side of your screen as depicted below.
Conclusion
This article has described availability of data connection 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 next part, we will continue from here and generate reports using Power BI.