Import Data
This connection means that Power BI will cache the data meaning Pulling data from Data Source and into Power BI Desktop File.
Advantages:
- Caching the data makes you take full advantage of Vertipaq Engine. Report Performance will be better using Import.
- Able to Use full capabilities of Power BI using relationship types, M and Dax Functions. No Limitations to data modelling.
- Using Import, you can combine data sources from various data sources.
Disadvantage:
- Without Power BI premium, dataset size is limited to 1GB. 10GB or more with Premium.
- Using Import will limit you and you cannot switch back to using Direct Query.
Direct Query
Direct Query will grab the schema of the data (Table Structure, Column names etc.) and the data stays at the Data Source itself. Ideal for working with large amount of data.
Advantages:
- Data is queried from the data source, so the data is most up to date. The report refresh occurs every 15 minutes.
- Power BI Desktop file is much smaller and caching the data does not occur using Direct Query.
- Less storage capacity is required on Service as not caching means not having to store compressed data.
Disadvantages:
- Not all sources support Direct Query.
- Not able to use all normal Power Query Transformation Features. Particular Dax Functions are not available in this method. Refrain from using Time Intelligence Functions.
- You get slower performance in Direct Query because querying the data occurs at run time.
Live Connection
- Specific to three type of data sources Azure Analysis Services, Analysis Services on prem or a Power BI Dataset.
- There is no Table view.
- This type of connection does not store a second copy of the data into the memory.
Composite models
- Composite Models is a feature where it allows you to mix and match the connection type.
- As of December 2020, you can bring in the data via both Direct Query and import, or Direct Query from multiple sources.