There are many articles, white papers available on the internet regarding the difference between Power BI Storage modes, storage mode also called connection type. So here I am writing the best of my knowledge for the definition of storage mode type and its benefit & limitation.
Power BI Storage Modes
What is Power BI Storage Modes
Types of Storage Mode
Benefits and Limitation of Storage of Mode
What is Power BI Storage Modes?
1. Storage mode determines tables of the data is stored and how queries will be sent to a data source
2. Specify the storage mode of tables when you define data source connection
Types of Storage Modes?
3. Live Connection
1. Import mode to develop data model that imports data from one or more data sources.
2. Imported tables are cached: data will be stored in in-memory storage of the power bi server.
3. When we set Import mode and data is imported from SQL Server, then a copy of that data will be stored in the memory engine of Power BI.
4. In the Modeling tab, New Measure, New Column, and New Table is enabled.
5. Report, Data, Model is enabled in the left pane of Power BI Component.
Whenever you refresh a visualization in Power BI report, it will query the in-memory structure, rather than sending the query to the SQL Server data source.
Import Mode Benefits
Import Mode Limitation
When data is changing frequently or real time data reflect the latest data in the Visualization/Report in Power BI then DirectQuery may be best option.
When we set DirectQuery storage mode, data will be reside in data source, Power BI engine transfer M code (Power Query language)/DAX expression into T-SQL query to the data source and get the result back.
DirectQuery Mode Benefits
DirectQuery Mode Limitations
Dual (Composite) Mode
The dual storage mode is between Import and DirectQuery. it is a hybrid approach, Like importing data, the dual storage mode caches the data in the table. However, it leaves it up to Power BI to determine the best way to query the table depending on the query context.one table can act either as DirectQuery or Import respective to the relationship to other tables Use composite models:
- Combines data from one or more DirectQuery sources.
- Combines data from DirectQuery sources and Import data.
Dual (Composite) Mode Benefits
Dual (Composite) Mode Limitations
Live Connection Mode
Live connection works with Analysis services If enabled on Premium capacity.
LiveConnect mode is similar in that Power BI forwards the queries to the source, it is important to note that Power BI does not have to transform queries in LiveConnect mode. The queries go directly to the Analysis Services instance hosting the database without consuming resources on shared capacity or a Premium capacity.
The dataset returns result from the underlying data source whenever a report or dashboard queries the dataset. Power BI transforms and forwards the queries to the data source.