Get Data is perhaps the most important feature of Microsoft Power BI. After all, if you can’t effectively integrate source data with your Power BI solutions, none of the other capabilities matter.
There are really two aspects of Get Data. The first is selecting your data source and the second is selecting the type of connection.
Selecting data sources from Get Data is not too mysterious, but it is important to understand that the type of data source you choose dictates the connection options and how the data can be refreshed. This is a frequent cause of confusion and thus the inspiration for this blog.
So what are the three modes for integrating data sources in Power BI (via Get Data), and how do they differ?
Import – Data from your data source are replicated in the Power BI data model. I refer to this as the Resident approach because the data in your datasets “live” in Power BI. Because the data is resident, it will need to be refreshed periodically in order to stay current with your source systems.
Direct Query – With this mode, data in your datasets are Non-Resident. This means that data from your datasets are not stored in Power BI. When interacting with reports data is retrieved “on the fly” from the source database with SQL commands generated by Power BI. With Direct Query the data surfaced in Power BI is always in sync with your source systems.
Direct Query is only available for a subset of database platforms supported by Power BI: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Oracle, Teradata and SAP Hanna.
Be aware that with Direct Query there can be database performance challenges you will need to address. You may also encounter limitations with DAX that may require creative workarounds.
Live Connection – As with Direct Query, your data are Non-Resident and therefore database queries to retrieve data are also generated on-the-fly. Live Connection is only available for SQL Analysis Services (SSAS) data sources (both MDX and Tabular). Unlike Direct Query, Live Connection with SSAS tends to perform well because SSAS is specifically designed for high performance analytics. SSAS must always live on premise (or in a cloud-hosted VM).
What are the key considerations for selecting the integration mode in Power BI?
1. Data Source Type
Direct Query is only applicable for SQL database sources. Support for this mode is currently limited to a subset of SQL database platforms available in Power BI. Live Connection is only applicable to SSAS (Tabular and MDX). For all other data sources, Import is your only option.
2. Data Latency Requirements
If your data must always be current with your source database (near real time), Direct Query is the preferred method. Alternatives include developing a custom solution using the Power BI Rest API or Azure services such as Event Hub and Streaming Service.
SSAS can also be configured to connect to your source database using Direct Query mode. Live Connection could then be used to connect your on premise SSAS with Power BI. This approach, however, involves two hops to deliver data to Power BI and this will add latency.
3. Data Refresh Options
When using Import mode, some form of data refresh is required to keep your data current in Power BI. If your source data is on premise, you will typically need to install and configure the On Premise Gateway (aka Enterprise Gateway).
4. Connecting to your on premise data via Direct Query and Live Connection
The On Premise Gateway is required for enabling Power BI to communicate with your on premise data sources.
Summary
The simplest and (typically) best performing option for integrating your data with Power BI is Import. This is the most common mode for integrating your data in Power BI. Direct Query and Live Connection are the appropriate options when your data must remain on premise or you need low latency.
Note: When you are using Direct Query or Live Connection, the options available in your authoring environment (Desktop and Excel) will be limited in various ways. With Live Connection you will be limited to simply creating reports. With Direct Query you will still be able to define relationships and create DAX, but in a more restrictive way than with the Import method.
Read more about Direct Query, Live Connect and Data Refresh:
Data Refresh: https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-data/
Direct Query Mode for Power BI Desktop: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/
Direct Query Mode for Azure SQL: https://powerbi.microsoft.com/en-us/documentation/powerbi-azure-sql-database-with-direct-connect/
Direct Query Mode for SSAS Tabular: https://msdn.microsoft.com/en-us/library/hh230898.aspx
