The basic concept of Live Connection is very similar to that of DirectQuery. Just like DirectQuery, when you use a Live Connection no data is actually imported into Power BI. Instead, your solution points directly to the underlying data source and leverages Power BI Desktop simply as a data visualization tool. So, if these two things are so similar, then why give them different names? The answer is because even though the basic concept is the same, DirectQuery and Live Connection vary greatly.
One difference that should quickly be noticeable is the query performance experience. It was mentioned in the last section that DirectQuery can often have poor performance depending on the data source type. With Live Connection, you generally will not have any performance problem because it is only supported by the following types of data sources:
- SQL Server Analysis Services Tabular
- SQL Server Analysis Services Multidimensional
- Power BI Service
The reason performance does not suffer from these data sources is because they either use the same xVelocity engine that Power BI does, or another high-performance storage engine. To set up your own Live Connection to one of these sources, you can choose the SQL Server Analysis Services database from the list of sources after selecting Get Data. Here, you can specify that the connection should be live:
Note
If a dataset is configured for a Live Connection or DirectQuery, then you can expect automatic refreshes to occur approximately every hour or when interaction with the data occurs. You can manually adjust the refresh frequency in the Scheduled cache refresh option in the Power BI service.
So far, this sounds great! You have now learned that you can connect directly to your data sources, without importing data into your model, and you won't have significant performance consequences. Of course, these benefits don't come without giving something up, so what are the limitations of a Live Connection?
What you will encounter with Live Connections are limitations that are generally a result of the fact that Analysis Services is an Enterprise BI tool. Thus, if you are going to connect to it, then it has probably already gone through significant data cleansing and modeling by your IT team.
Modeling capabilities such as defining relationships are not available because these would be designed in an Analysis Services Model. Also, the Power Query Editor is not available at all against a Live Connection source. While at times this may be frustrating, it does make sense that it works this way because any of the changes you may desire to make with relationships or in the query editor should be done in Analysis Services, not Power BI.