Power BI Desktop provides different data connectivity modes for various data sources, each with its own set of features and limitations. These data connectivity modes include Import, DirectQuery, and Live Connection.
Import Mode:
In Import mode, all the data is stored in Power BI Desktop, providing offline access to the data. This mode supports Data Analysis Expression (DAX), which enables users to create custom columns and measures. However, import mode can only load up to 1 GB of data. The data in Import mode does not refresh automatically, and users need to set up a schedule refresh to keep the data up-to-date.
Features:
- Supports DAX
- Offline access to data
- Custom columns and measures can be created
- Supports Schedule refresh..
Limitations:
- Supports the data up-to 1GB.
- No Automatic data refresh.
- Limited processing power for large datasets.
DirectQuery Mode:
In DirectQuery mode, data is accessed directly from the source database, and the data cannot be accessed offline. This mode does not support changes in data types after data is loaded. Users cannot create calculated columns in DirectQuery mode, and it only supports limited DAX. Every time the report is refreshed, data is fetched from the database, and the refresh time depends on the size of the dataset.
Features:
- Access to real-time data
- No data size limitation
- Supports some DAX functions
- Data is always up-to-date
Limitations:
- No offline access to data
- Calculated columns are not supported.
- Limited DAX support
- Slow query performance for large datasets
Live Connection:
This mode is available for connecting to specific data sources, such as Analysis Services, Azure Analysis Services. Live Connection mode enables users to access complex data models with MDX and DAX. With Live Connection mode, users do not need to import data into Power BI Desktop.
For example, if a user wants to create a dashboard with the latest sales data from an Analysis Services cube, they can use Live Connection mode to connect to the cube and create visualizations that update automatically.
Features:
- No need to import data into Power BI Desktop
- No Data Size Limitation
- Access to complex data models
- Supports MDX and DAX
- No Need to set Schedule Refresh.
- Better Query Performance for large Dataset
Limitations:
- Only available for connecting to specific data sources
- Changes made in Power BI Desktop are not supported
- No offline access to data
