Power BI Get Data: Import vs. DirectQuery vs. Live

Summary

In this video, Adam Saxton from Guy in a Cube explains the different ways to get data into Power BI, including Import, DirectQuery, Live Connection, and Composite Models. He breaks down the benefits, limitations, and best practices of each method, emphasizing performance optimization, star schema design, and dataset management. Adam highlights how Import offers full functionality with cached data, DirectQuery handles massive datasets without storing data in Power BI, and Live Connections link to external models. Composite Models allow mixing these approaches for flexibility. Practical tips, examples, and cautions are provided to help users make informed decisions when connecting data in Power BI.

Takeaways

• 😀 Import mode in Power BI stores a copy of the data within Power BI, making it super fast with data compression for efficiency.
• 😀 Import mode supports all data sources in Power BI, allowing full functionality with DAX, relationships, and Power Query.
• 😀 Import mode has limitations on dataset size: 1GB for non-premium users, up to 10GB for premium users, and even larger with large model support.
• 😀 To ensure optimal performance in import mode, it’s recommended to follow the star schema model, using fact and dimension tables rather than a single large table.
• 😀 DirectQuery mode connects to data sources but only retrieves the schema, not the data itself, keeping the data in the source while enabling real-time queries.
• 😀 DirectQuery is beneficial for handling large datasets that exceed import mode’s size limit, but not all data sources support it.
• 😀 DirectQuery can lead to performance issues if not implemented correctly, especially when querying operational databases or poorly modeled data.
• 😀 Users should avoid using time intelligence functions in DirectQuery as they may reduce performance and lead to inefficient queries.
• 😀 Live connection is specific to connecting to external models like Analysis Services or Power BI datasets, and it does not store data in Power BI Desktop.
• 😀 Composite models allow you to combine data from multiple connection types (e.g., import, DirectQuery, and live connection) for more flexible and efficient reporting.

Q & A

What does importing data in Power BI mean?

Importing data, also called cached data, means pulling data from a source and bringing a copy into Power BI. The data resides inside the Power BI Desktop file or the published dataset, allowing for fast queries and full use of Power BI features like DAX functions, relationships, and Power Query transformations.

What are the main benefits of using import mode in Power BI?

The main benefits of import mode include fast performance due to cached data, data compression that allows storing large datasets in a small space, and access to all Power BI features without restrictions.

Are there limitations on dataset size when using import mode?

Yes, if you're using Power BI Pro, the dataset size is limited to 1 GB when published to the service. With Power BI Premium, the default limit is 10 GB, which can be extended further with large model support depending on the premium capacity.

How does DirectQuery differ from import mode?

DirectQuery does not store the data in Power BI. Instead, it stores only the schema (table structure and column names) and queries the source data live. This allows working with massive datasets beyond import limits but comes with performance considerations and feature limitations.

What are some performance considerations when using DirectQuery?

Performance issues can arise if the source database is not modeled cleanly (e.g., not using star schema), if time intelligence functions are used, or if the underlying database cannot support sub-second query response. Each visual can generate multiple queries, so the data source must handle interactive loads efficiently.

Which data sources typically support DirectQuery?

Relational-type data sources typically support DirectQuery. A full list of supported data sources is available in the Power BI documentation.

What is a live connection in Power BI, and how is it different from DirectQuery?

A live connection connects directly to an Analysis Services model or a Power BI dataset. Unlike DirectQuery, which pulls the schema and queries the data, live connections do not bring data into Power BI Desktop at all. You only interact with the external model, and the data view is not available.

What are composite models in Power BI?

Composite models allow mixing different connection types, such as import, DirectQuery, and live connections, in a single model. This enables combining, for example, an imported CSV table with a live connection to a Power BI dataset, allowing flexible data modeling for complex scenarios.

How can aggregation tables help with massive datasets in Power BI?

Aggregation or summary tables can be imported to handle most queries locally, while the detailed data resides in a DirectQuery table. This reduces query load on the source and improves performance for large datasets.

What best practices are recommended for modeling data in Power BI?

Keep the model simple and clean, use star schema designs with fact and dimension tables, and avoid using single giant tables. Following these practices ensures better performance, easier maintenance, and optimized queries.

What should users be cautious of when using DirectQuery with operational databases?

Users should be cautious because operational databases may not be optimized for analytical queries, leading to slow performance. DirectQuery works best with cleanly modeled, star-schema relational databases.

Can you make changes to a model when using a live connection?

No, when using a live connection, the model is external and cannot be modified from Power BI Desktop. Any changes need to be made in the source Analysis Services model or Power BI dataset.