Power BI - Supported Data Sources

---

---

Power BI supports a large range of data sources. You can click Get data and it shows you all the available data connections. It allows you to connect to different flat files, SQL databases, and Azure cloud or even web platforms such as Facebook, Google Analytics, and Salesforce objects. It also includes an ODBC connection to connect to other ODBC data sources, which are not listed.

The reports developed in this tool require authenticated datasets which ensures the visualizations of the report. Invalid datasets can't load into the Power BI tool. When you are publishing the report to the Power BI service, datasets will also be broadcasted.

Data Sources in Power BI

Following are the available data sources in Power BI −

• Flat Files
• SQL Database
• Azure Cloud platform
• Online Services
• Other data sources such as Hadoop, Exchange, or Active Directory

To get data in the Power BI desktop, you need to click the Get data option on the main screen. It shows you the most common data sources first like Excel, SQL server, Analysis Service, etc. You may select any of the options. Then, click the More option to see a full list of available data sources.

When you click "More.." tab as shown in the above screenshot, you can see a new navigation window, where on the left side it shows a category of all available data sources. You also have an option to perform a search at the top.

Following are the various data sources listed −

All Data Sources

Under this category, you can see all the available data sources under the Power BI desktop.

Flat File Types

When you click File, it shows you all flat file types supported in the Power BI desktop. To connect to any file type, select the file type from the list and click Connect. You have to provide the location of the file. The file type can be Excel Workbook, Text/CSV, XML, JSON, Folder, PDF also,

Database

When you click the Database option, it shows a list of all the database connections that you can connect to.

To connect to any database, select a Database type from the list as shown in the above screenshot. Click Connect.

You have to pass the Server name/ User name and password to connect. You can also connect via a direct SQL query using Advance options. You can also select Connectivity mode- Import or DirectQuery.

Note − You can't combine import and DirectQuery mode in a single report.

Import vs DirectQuery

DirectQuery option limits the option of data manipulation and the data stays in the SQL database. DirectQuery is live and there is no need to schedule refresh as in the Import method.

Import method allows to perform data transformation and manipulation. When you publish the data to the PBI service, the limit is 1GB. It consumes and pushes data into Power BI Azure backend and data can be refreshed up to 8 times a day and a schedule can be set up for data refresh.

Advantages of Using DirectQuery

• Using DirectQuery, you can build data visualizations on large datasets, which are not feasible to import in the Power BI desktop.
• DirectQuery doesn't apply any 1GB data set limit.
• With the use of DirectQuery, the report always shows current data.

Limitations of Using DirectQuery

• There is a limitation of 1 million rows for returning data while using DirectQuery. You can perform aggregation of more rows, however, the result rows should be less than 1 million to return the dataset.
• In DirectQuery, all tables should come from a single database.
• When a complex query is used in the Query editor, it throws an error. To run a query, you need to remove the error from the query.
• In DirectQuery, you can use Relationship filtering only in one direction.
• It doesn’t support special treatment for time-related data in tables.

Azure

Using the Azure option, you can connect to the database in the Azure cloud that requires login credentials. The following screenshot shows the various options available under the Azure category.

Online Services

Power BI also allows you to connect to different online services such as Exchange, Salesforce, Google Analytics, and Facebook. The data that drives the semantic model is either imported from local files, Azure databases, or Google Analytics.

The following screenshots show the various options available under Online Services.

Other Data Sources

Following screenshot shows the various options available under other category.

Usually, analysts prefer local files that are resided in the system for testing purposes. Any of the formats like SharePoint folder, CSV files, Excel, etc can be selected by the developers. Organizations have their cloud data which are shared among employees through Azure or any other server. Employees must have credentials to log into the cloud database and Saas, where Power BI provides connectivity with these databases.