Blog

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.

Following are the available data sources in Power BI −

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

To get data on 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. Then, click the More option to see a full list of available data sources.

When you click the “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

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

File

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.

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 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.

The import method allows performing data transformation and manipulation. When you publish the data to the PBI service, the limit is 1GB. It consumes and pushes data into the 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 is not feasible to import in 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 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 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. 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 following screenshots show the various options available under Online Services.

Other

Following screenshot shows the various options available under another category.