The Clearly Podcast

Power BI - Import vs Direct Query vs Live Connection

Summary

This week, Andy discovers sound effects!

We also talk about the relative merits of importing data into the Power BI data model vs querying the data source directly.

Import is generally supported by all data sources.  Direct Query and Live Connection are more limited.  Generally speaking, databases are more likely to support Direct Query as well as import; file-based sources tend to only permit import.  A detailed breakdown of sources and supported connection methods can be found here.

It is also worth looking carefully at the implications of using Direct Query before choosing it as method.

Finally, remember if you are connecting to an Analysis Services model, you should use a live connection, otherwise the structure will be flattened and you will have to rebuild it in Power BI.

You can download Power BI Desktop from here.

If you already use Power BI, or are considering it, we strongly recommend you join your local Power BI user group here.

Transcript

Andy: Welcome to the Clearly Podcast, Episode 3: "Import vs. Direct Query vs. Live Connection," recorded on October 14, 2020,. Today, we'll discuss the three Power BI data connection methods, their pros and cons, and use cases, including hybrid models.

Shailan: Let's start with the import model. This method is widely recommended. When you create reports using Power BI Desktop, you can import data from sources like Excel, SQL databases, or online sources. Importing data brings it into Power BI's in-memory model, making visualizations fast and responsive because the data is already in the model.

Andy: Exactly. With the import model, you get fantastic speed, especially with large datasets. However, a common concern is that data is usually refreshed daily, which some users find insufficient. Yet, for many scenarios, such as daily or weekly reporting, this is quite effective. For real-time needs, Direct Query might be necessary. Tom, can you explain Direct Query?

Tom: Sure. Direct Query connects directly to relational data sources, querying them in real-time. Unlike import, it doesn't store data within Power BI, which can strain the source systems with frequent queries. Direct Query is slower and often less efficient than import models, but it's useful for extremely large datasets that can't fit into Power BI's in-memory model.

Shailan: And don't forget Live Connection, which connects to pre-built data models in Analysis Services, either tabular or multidimensional. This method can be faster than Direct Query since the heavy lifting is done by Analysis Services. However, it's only as real-time as the data model it connects to, which might also be refreshed periodically.

Tom: Correct. Live Connection is great for centralized models shared across multiple systems. It ensures everyone reports off the same version of truth, avoiding discrepancies. The downside is that you can't extend the model within Power BI—you rely on the pre-built model.

Andy: Tom, can you discuss when Direct Query is genuinely necessary?

Tom: Direct Query is essential when datasets are too large for import. However, it can heavily impact performance and source system load. You should optimize the backend to handle the queries Power BI will generate. Sometimes, a hybrid model, combining import and Direct Query, is the best solution. Import what you can and use Direct Query for detailed, real-time needs.

Andy: And with Direct Query, you're limited in using DAX functions in Power BI. Most transformations should be done in the backend to ensure performance isn't compromised.

Shailan: If you're unsure which method to use, start with import. It's typically sufficient for most needs. Only use Direct Query or Live Connection if necessary.

Tom: Agreed. For more information, check the Power BI documentation on Microsoft's website. They have comprehensive guides on these methods and their limitations.

Andy: That wraps up our discussion. Any final advice?

Tom: Get hands-on with Power BI and explore its capabilities. Understanding the limitations of each method is crucial to avoid pitfalls.

Shailan: Yes, and always check Microsoft's documentation for the latest updates and limitations.

Andy: Thanks for listening. Visit clearlycloudy.co.uk (UK) or clearlysolutions.net (USA) for more information. Join us next week for "When Hybrid Models Save the Day." Have a great week!