Power BI: Import Mode Vs DirectQuery Mode

  • Article

Introduction

In this article, we will explain the

Import and DirectQuery data connectivity modes in Power BI.

You may be also interested to Install and Configure Power BI Report ServerOn-Prem.

Data Connectivity Mode

 In Power BI Desktop, when you connect to a data source, there is two connectivity mode as listed below:

  • Import Mode. [Import copy of data].
  • DirectQuery Mode. [Connect directly to the data source].

Import Mode

The Import mode imports or copies the data set into Power BI desktop, to import the full data set again and get the new changes, you must refresh the data via schedule or refresh button.

Pros of Import Mode 

  • All Power BI features are available in Import Mode, like alternative login to the database connection, relationship filtering with “Both” directions …etc.
  • Faster than DirectQuery Mode, due to the data loaded into the memory, the query calculated from loaded data.
  • DAX expressions are fully supported.
  • Single and Both directions in the relation filtering are available.

Cons of Import Mode

  • Depends on the imported data size, A lot of consumed memory and disk space
    • On your machine (during the implementation),
    • On the online/on-prem server (when it published).
  • The Power BI file size can’t be bigger than 1 GB.
  • You will get an error If the file size is bigger than 1 GB, in this case, you must have the Power BI Premium that allows having 50 GB file size.
  • No recent data without a refresh.

DirectQuery Mode

In the DirectQuery Mode, the Power BI report connects directly to the data source to always view the current data, no data copies in Power BI file, just small metadata like tables, fields names will be stored.

Pros of DirectQuery Mode

  • Always use the current data.
  • Scalable, No limitation for 1 GB dataset.
  • Build reports with large datasets.

Cons of DirectQuery Mode

  • Slower than the Import mode.
  • Can’t use alternative database credentials.
  • Can’t combine multiple data sources in Power Query. All must come from a single data source.
  • No data tab, only report and relation tab.
  • The “Single direction” is only available. Meanwhile, you can enable “Both direction” for DirectQuery in the preview features.
  • Time capabilities are not available.
  • The date columns like Day, Month and Year are not supported.
  • The returned data to Power BI must be <= 1 million rows.
  • Many DAX functions are not supported. Meanwhile, you can overcome this limit by Allowing Unrestricted Measure in DirectQuery Mode.

Applies To

  • Power BI Report Server.
  • Power BI Service.

Conclusions

In this article, we have explored the data connectivity mode in Power BI, we think you can now decide which mode should you use.

 We have also explained,

  • Pros and cons of Import Mode.
  • Pros and Cons of DirrectQuery Mode.

References

See Also

Back To Top