Power BI - Supported Data Sources

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

Following are the available data sources in Power BI −

Flat Files

SQL Database

OData Feed

Blank Query

Azure Cloud platform

Onpne Services

Blank Query

Other data sources such as Hadoop, Exchange, or Active Directory

To get data in Power BI desktop, you need to cpck the Get data option in the main screen. It shows you the most common data sources first. Then, cpck the More option to see a full pst of available data sources.

When you cpck “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 psted −

All

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

File

When you cpck File, it shows you all flat file types supported in Power BI desktop. To connect to any file type, select the file type from the pst and cpck Connect. You have to provide the location of the file.

Database

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

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

You have to pass 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 pmits the option of data manipulation and the data stays in SQL database. DirectQuery is pve and there is no need to schedule refresh as in the Import method.

Import method allows to perform data transformation and manipulation. When you pubpsh the data to PBI service, pmit 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 visuapzations on large datasets, which is not feasible to import in Power BI desktop.

DirectQuery doesn’t apply any 1GB data set pmit.

With the use of DirectQuery, the report always shows current data.

Limitations of Using DirectQuery

There is a pmitation of 1 milpon row for returning data while using DirectQuery. You can perform aggregation of more number of rows, however, the result rows should be less than 1 milpon 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 Azure cloud. Following screenshot shows the various options available under Azure category.

Onpne Services

Power BI also allows you to connect to different onpne services such as Exchange, Salesforce, Google Analytics, and Facebook.

Following screenshots shown the various options available under Onpne Services.

Other

Following screenshot shows the various options available under other category.