Guideline to choose the appropriate data connectivity mode option in Power BI
Power BI can connect with a wide variety of data sources. For any data analysis project, data consumption process starts with an understanding of business requirements and data sources available to us. Data connectivity mode is defined after analysing all these above.
In this blog, I am going to explore the different data sources and their data connectivity mode options.
Things to learn
- Data Source Categories with description
- Define Data connectivity mode
- “Import” mode
- “Direct Query” mode
- “Live Connection” mode
Data Source Categories with description
To connect with a data source, we need to click on the Home tab and select Get Data under Data group. A drop-down list opens with common data sources. After clicking on More, the full data window opens.
There are 6 data source categories which are supported by Power BI. In the below table, I have captured those with descriptions and examples.
Define Data connectivity mode
Data connectivity mode is an option or method based on which data is consuming in Power BI data model. The most common way is import option. For files and folders, there can only be imported data. When it comes to databases, there are two ways, Import and DirectQuery. A special case of DirectQuery is called Live Connection. This is available for SQL Server Analysis Services and Power BI Service.
Let’s now understand in details.
When we import data, we load a copy of data in Power BI. This imported data consumes RAM and disk space because of data stored in files. The reason behind is a memory engine called xVelocity. Power BI is based on this. Once we publish the report to a server, data consumes the infrastructure of the server.
The main limitation is that we cannot load more data beyond hardware limitation. However, to bypass this issue, we can use filtered data gets loaded and leverage to use Power BI cache option which keeps data in a compressed state.
In spite of this implication, there are several advantages to this method.
- We can use all of the functionality of Power BI without restrictions. It includes all transformations which are available in Power Query Editor, as well as all DAX functions during data modelling.
- We can use data from more than one data source can be included in the same data model. For example some data from a file source, some data from the database. Then combine them in Power Query.
- Due to in-memory stored data, the calculation speed is more than any method.
When we are using DirectQuery option, we are not loading data in Power BI. Data remains the same in the data source, only keeping metadata in Power BI. Metadata means data about data. It stores table name, column name, data type and relationships. As a result, Power BI works as a visualization tool.
The main advantage of this method is that we are not limited by the hardware of the system.
But there are some limitations.
- With DirectQuery, for each visual, a user interacts and sends a query to the data source. Then query response time depends on the underlying data source performance. It could be a bearable amount of time or might feel slow for some users. It happens with every user whoever is working with a report at the same time.
- DirectQuery allows only one data source at a time.
- Data transformation logic can be varied and limited to this option.
- In this mode, every kind of query type cannot be used. For example native queries.
- Data modelling features like calculated measures, hierarchies, relationships etc. are limited. Most importantly no inbuilt date table is available for time intelligence calculation. To build that, there should be a date table in the data source.
- Some more limitations are there. For example security limitations, data changes frequently.
Two scenarios, when the DirectQuery method is required.
- Size of the data as well as data model which is difficult to fit in memory.
- When underlying data is changing frequently and report should display near to real-time data.
“Live Connection” mode
The Live Connection mode is similar to DirectQuery method. But it’s different in some ways.
- No relationship in Live Connection.
- No data transformation
- Data modelling is very limited.
A live connection is available only for SQL Server Analysis service and Power BI service.
Now we are clear about all data connectivity modes, their advantages, limitations etc. It will help to prepare better for Power BI interview.