How Data Connectivity Modes are different from each other in Power BI

Guideline to choose the appropriate data connectivity mode option in Power BI

Image by Author

Introduction

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

  1. Data Source Categories with description
  2. Define Data connectivity mode
  3.  “Import” mode
  4. “Direct Query” mode
  5. “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.

Image by author

There are 6 data source categories which are supported by Power BI. In the below table, I have captured those with descriptions and examples.

Image by author

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.

Image by author

Let’s now understand in details.

“Import” mode

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.

  1. 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.
  2. 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.
  3. Due to in-memory stored data, the calculation speed is more than any method.

“DirectQuery” mode

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.

  1. 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.
  2. DirectQuery allows only one data source at a time.
  3. Data transformation logic can be varied and limited to this option.
  4. In this mode, every kind of query type cannot be used. For example native queries.
  5. 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.
  6. Some more limitations are there. For example security limitations, data changes frequently.

Two scenarios, when the DirectQuery method is required.

  1. Size of the data as well as data model which is difficult to fit in memory.
  2. 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.

Conclusion

Now we are clear about all data connectivity modes, their advantages, limitations etc.  It will help to prepare better for Power BI interview.

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: