Under­stand­ing Power BI’s vari­ous con­nectiv­ity modes is essen­tial to real­ise its full poten­tial: whether you use Power BI Desktop or the Power BI ser­vice, how you con­nect to your data sources has a sig­ni­fic­ant impact on your ana­lyt­ical capabilities.

In this art­icle, we’ll look at the dif­fer­ent con­nectiv­ity modes in Power BI, how they work, and when they’re most use­ful. From the ver­sat­ile Import mode to the real-time access provided by Dir­ectQuery through the flex­ib­il­ity of Com­pos­ite mode, we’ll walk you through the dif­fer­ent options so you can make informed decisions based on your spe­cific requirements.

But before we get into the modes, let’s look at the semantic mod­els. These mod­els are more than just struc­tures – they are a method of organ­ising data in a logical and mean­ing­ful man­ner. Semantic mod­els, which abstract and trans­form phys­ical data­base objects into logical dimen­sions, lay the ground­work for effect­ive ana­lysis in Power BI.

Once we’ve under­stood the con­cepts behind the semantic mod­els, we’ll look at Power BI’s four primary con­nectiv­ity modes:

  • Import: Ver­sat­ile and effi­cient, suit­able for most scenarios.
  • Dir­ectQuery: Ideal for real-time access but with cer­tain limitations.
  • Com­pos­ite: Offer­ing the best of both worlds by com­bin­ing Import and DirectQuery.
  • Live Con­nec­tion: Facil­it­at­ing dir­ect access to data sources without import­ing them into Power BI.

Fur­ther­more, Power BI offers a wide range of data sources to con­nect to, cater­ing to a vari­ety of user needs. Power BI integ­rates seam­lessly with online ser­vices like Sales­force and Dynam­ics 365, data­bases like SQL Server and Amazon Red­shift, as well as simple files in Excel and JSON formats. What’s more, you can con­nect to other data sources such as Spark, web­sites, and Microsoft Exchange, broad­en­ing the scope of your ana­lyt­ical efforts.

Let’s get down to the intric­a­cies of Power BI’s con­nectiv­ity modes!

Import Mode

The most com­mon con­nectiv­ity for Power BI is Import mode, where Power BI Desktop stores the data inside Power BI cache.

If the data size is less than 1 GB, or if the data is not con­tinu­ally chan­ging, then we can use Import mode. All the data’s inter­ac­tions and fil­ters will be applied to this com­pressed cache source rather than the ori­ginal data source.

To import the data from our source, fol­low the steps in the screen­shot below (these steps may vary depend­ing on the data source, be it Excel files, SQL data­bases, or queries).

Use Cases

Before diving deeper into Import mode and its char­ac­ter­ist­ics, we need to explore its most com­mon use cases, which depend on the needs of the user and the project.

The most com­mon use cases are:

  • The need for high-per­form­ance data visualisations.
  • The data­set is not extremely large or complex.
  • Data needs to be trans­formed or enriched before analysis.
  • The need for off­line access to the reports.

Advant­ages

Using this mode provides sev­eral sig­ni­fic­ant advant­ages. First, its in-memory query­ing enables excep­tion­ally fast per­form­ance, allow­ing for swift query exe­cu­tion. You also have full access to all M and DAX func­tions, enabling extens­ive data manip­u­la­tion and cal­cu­la­tion cap­ab­il­it­ies. The mode also allows for a wide range of field format­ting options and offers com­plete free­dom in data mod­el­ling, regard­less of the data source types or variations.

Char­ac­ter­ist­ics

As we already know, Import mode is the most com­monly used mode in Power BI, boast­ing a lot of dis­tin­guish­ing characteristics:

  • In Import mode, Power BI imports data from the data source into its own internal model. This means that data is copied and stored within the Power BI file (.pbix).
  • It can per­form data trans­form­a­tion tasks dur­ing the import pro­cess using Power Query, a power­ful ETL tool integ­rated into Power BI Desktop. This allows us to clean, reshape and enrich the data before it’s stored in Power BI.
  • It deliv­ers optimal per­form­ance for data visu­al­isa­tions since the data is pre­loaded into memory, ensur­ing a fast response when inter­act­ing with visu­al­isa­tions. It also sup­ports the exe­cu­tion of com­plex cal­cu­la­tions with ease.
  • The amount of data you can import is lim­ited by the machine’s memory and your licens­ing tier (i.e. Power BI Pro or Power BI Premium).
  • Import mode enables off­line access to your data, mean­ing you can cre­ate, modify and refresh reports even when you’re not con­nec­ted to the data source.
  • You can ensure data secur­ity and com­pli­ance through Power BI’s secur­ity set­tings and access controls.
  • The data in Import mod­els is only as up-to-date as the latest refresh.

Note: Power BI Pro has lim­it­a­tions on the size of data­sets you can pub­lish to the cloud, while Power BI Premium offers more scalability.

The fig­ure below shows the dif­fer­ent devel­op­ment modes and sources for Import mode:

Dis­ad­vant­ages

We have already explored the advant­ages and char­ac­ter­ist­ics of Import mode, but what about its dis­ad­vant­ages? While Import mode can indeed offer fast exe­cu­tion, it does come with some draw­backs. One sig­ni­fic­ant issue is the poten­tial for resource pres­sure, as the entire model must be loaded into memory before any query­ing can take place, which can strain avail­able sys­tem resources and impact over­all capacity.

Addi­tion­ally, the refresh fre­quency of mod­els in Import mode may pose a chal­lenge, as they require sched­uled refreshes to ensure that the data remains up-to-date.

Dir­ectQuery

As the name implies, Dir­ectQuery is a method of retriev­ing data dir­ectly from the data source. It allows you to dir­ectly con­nect with a live data­base and it does not load or copy data into the Power BI model: data comes dir­ectly from the data source. The dash­board quer­ies the data source in real time dur­ing runtime, mean­ing that every single request is sent dir­ectly to the data source, which then pulls the rel­ev­ant data into Power BI.

Use Cases

When you cre­ate Power BI reports, the users expect the reports to be refreshed peri­od­ic­ally and the data to be con­stantly updated. This can be done manu­ally, but Dir­ectQuery offers a more effi­cient solu­tion: sched­uled refresh. With Dir­ectQuery, you can con­nect dir­ectly to a data­set, allow­ing your pro­ject to access live data.

The most com­mon use cases are when:

  • Real-time or near-real-time access to data is a requirement.
  • The data­set is too large to import into Power BI.
  • The data is already well-pre­pared in the data source.
  • Secur­ity policies neces­sit­ate enfor­cing data secur­ity at the source level.

How­ever, Dir­ectQuery is only sup­por­ted by a lim­ited num­ber of data sources, includ­ing Amazon Red­shift, Azure HDIn­sight Spark, Azure SQL Data­base, Azure SQL Data Ware­house (now Azure Syn­apse Ana­lyt­ics), IBM Netezza, Impala, Oracle Data­base, SAP Busi­ness Ware­house, SAP HANA, Snow­flake, Spark, SQL Server, and Teradata Data­base.

Below we can see how to set up DirectQuery:

Advant­ages

Access­ing data dir­ectly from the source offers sev­eral advant­ages. This approach elim­in­ates the size lim­it­a­tion, as no data is stored in the Power BI file, ensur­ing you never encounter issues related to data volume. It also reduces both cache usage and stor­age require­ments for the file.

How­ever, it’s import­ant to note that changes to the under­ly­ing data are not imme­di­ately reflec­ted in exist­ing visu­als and a refresh is still neces­sary. Power BI Desktop will resend the required quer­ies for each visual and update them accordingly.

The fig­ure below shows the devel­op­ment mode and the sup­por­ted data sources for DirectQuery:

Char­ac­ter­ist­ics

Let’s now explore the dif­fer­ent char­ac­ter­ist­ics of Dir­ectQuery mode:

  • Dir­ectQuery mode estab­lishes a live con­nec­tion to the data source, such as an SQL data­base or ana­lysis ser­vices. Instead of import­ing data, Power BI sends quer­ies dir­ectly to the data source whenever a user inter­acts with a visualisation.
  • Data is accessed from the source in real time, ensur­ing that the reports reflect the most up-to-date information.
  • Data trans­form­a­tion is per­formed within the data source itself, not within Power BI, mean­ing that the data should be made suit­able for ana­lysis before using DirectQuery.
  • Dir­ectQuery mode can handle very large data­sets since it doesn’t store data internally.
  • It’s import­ant to ensure that your data source has appro­pri­ate access con­trols and secur­ity meas­ures in place.

Dis­ad­vant­ages

While it’s true that Dir­ectQuery mode offers sev­eral advant­ages, it also comes with some draw­backs. This mode provides lim­ited func­tion­al­ity, mean­ing fewer Power Query oper­a­tions are avail­able, par­tic­u­larly in terms of visu­al­isa­tions. Neither does Dir­ectQuery offer the full range of Power BI fea­tures, as it only provides two tabs in Power BI Desktop: Report and Rela­tion­ship. Although you can modify rela­tion­ships in this mode, it may be a slower con­nec­tion com­pared to other types of con­nec­tions. Finally, per­form­ance issues may arise, espe­cially with com­plex cal­cu­la­tions or aggregations.

Note: When you open a dash­board, the tiles reflect the data at the time of the last refresh, not neces­sar­ily the latest changes made to the under­ly­ing source. You can refresh an open dash­board to ensure that it’s up-to-date.

Below we can see the model struc­ture for DirectQuery:

Com­pos­ite Mode

Now that we’ve covered Import mode and Dir­ectQuery mode, let’s explore the one that com­bines the best of both: Com­pos­ite mode, which allows you to mix and integ­rate mul­tiple Dir­ectQuery data sources within a single report. This fea­ture enables a report to have two or more data con­nec­tions, mean­ing greater flex­ib­il­ity. With Com­pos­ite mod­els, you can com­bine data from mul­tiple Dir­ectQuery sources and also mix data from both Dir­ectQuery and Import data sources.

Here we can see the dif­fer­ent data­set sources for the dual mode:

Use Case

Com­pos­ite mod­els are typ­ic­ally used in the fol­low­ing scenarios:

  • Per­form­ance Enhance­ment: Boost per­form­ance by set­ting up appro­pri­ate stor­age modes for each table.
  • Com­bin­ing Dir­ectQuery and Import Data: Seam­lessly com­bine dif­fer­ent data sources.
  • Link­ing to Enter­prise Semantic Mod­els: Con­nect and lever­age enter­prise-level semantic models.

Advant­ages

Com­bin­ing two con­nectiv­ity modes nat­ur­ally brings sig­ni­fic­ant advant­ages. Com­pos­ite mode offers the best of both Import and Dir­ectQuery modes. Let’s take a look at the bene­fits of using Com­pos­ite mod­els in Power BI:

  • Fast Per­form­ance: Import-mode par­ti­tions provide fast query responses.
  • Real-Time Data Updates: Dir­ectQuery requests fetch the latest data changes from the data source.
  • Resource Util­isa­tion: Fewer data refreshes are needed, espe­cially when work­ing with large datasets.
  • Many-to-Many Rela­tion­ships: Enables com­plex data modelling.
  • Com­bin­ing Data Sources: Can com­bine data from dif­fer­ent source groups.

Note: Once you’ve chosen Import mode, you can’t go back to DirectQuery.

Note: Import mode is gen­er­ally faster than Dir­ectQuery because all data is retrieved from the Power BI Desktop cache.

In the fig­ure below, we can see how dif­fer­ent data sources are rep­res­en­ted in Power BI across vari­ous modes:

Table Stor­age Modes

In Com­pos­ite mod­els, you can spe­cify the stor­age mode for each table depend­ing on factors such as usage, data size, and other con­sid­er­a­tions. The avail­able stor­age modes are:

  • Dir­ectQuery: Ideal for tables with large data volumes or those which need near-real-time res­ults. Data will never be impor­ted into these tables.
  • Import: Great for tables that aren’t used for fil­ter­ing and group­ing fact tables. It’s the only option for tables based on sources that do not sup­port Dir­ectQuery mode.
  • Dual: Typ­ic­ally used for dimen­sion-type tables that might be quer­ied together with Dir­ectQuery fact-type tables from the same source.
  • Hybrid: This mode allows you to add both an import par­ti­tion and a Dir­ectQuery par­ti­tion to a fact table, cap­ture the latest data changes in real time, or provide fast access to fre­quently used data through import par­ti­tions, leav­ing infre­quently used data in the data warehouse.

Dis­ad­vant­ages

Using a mixed con­nectiv­ity type in Power BI does limit some cap­ab­il­it­ies. Tables in the Power BI report that use Dir­ectQuery still face lim­it­a­tions with time-intel­li­gence DAX func­tions and many of the data trans­form­a­tions in Power Query.

Hybrid Tables

The most used source in Com­pos­ite mode is the hybrid table, a large table that com­bines both Import mode par­ti­tions and a Dir­ectQuery partition.

The import-mode par­ti­tions deliver very fast query per­form­ance, while the Dir­ectQuery par­ti­tion ensures that the latest data updates are included.

The advant­ages of using a hybrid table are:

  • Fast Per­form­ance: Import-mode par­ti­tions provide fast query responses.
  • Real-Time Data Updates: Dir­ectQuery ensures that the latest data changes are fetched from the data source.
  • Resource Util­isa­tion: Fewer data refreshes are needed, espe­cially for large datasets.

In the example below, we can see that in Com­pos­ite mode, the tables ori­gin­at­ing from a Dir­ectQuery are high­lighted in light blue, which means that the data is real-time. The tables shown in dark blue are loaded using Import mode.

Power BI Quer­ies in Com­pos­ite Mode

In Com­pos­ite mode, we can use dif­fer­ent quer­ies to retrieve data, depend­ing on the res­ult we want to achieve:

  • Quer­ies using only Import or Dual table(s): Power BI retrieves all data from the model cache. This scen­ario is com­mon for dimen­sion-type tables quer­ied by fil­ters or slicer visuals.
  • Quer­ies using Dual table(s) or Dir­ectQuery table(s) from the same source: Power BI retrieves all data by send­ing one or more nat­ive quer­ies to the Dir­ectQuery source.
  • Quer­ies using Dual table(s) or Hybrid table(s) from the same source: This scen­ario is a com­bin­a­tion of the pre­vi­ous two. Power BI retrieves data from the model cache when avail­able and sends quer­ies to the Dir­ectQuery source when necessary.
  • All other quer­ies: These quer­ies involve cross-source group rela­tion­ships, such as when an Import table relates to a Dir­ectQuery table or when a Dual table relates to a Dir­ectQuery table from a dif­fer­ent source.

Live Con­nec­tion

In Live Con­nec­tion, only the con­nec­tion string is retrieved. Neither the data nor the schema is brought into Power BI. As a res­ult, we can­not make any changes to the data model within Power BI.

Live Con­nec­tion and Dir­ectQuery might seem sim­ilar because neither stores data in the Power BI model, but they are fun­da­ment­ally dif­fer­ent con­nectiv­ity types and can­not be used interchangeably.

Using Live Con­nec­tion means that no data is stored in the Power BI model, so all inter­ac­tions with a report will dir­ectly query the exist­ing Ana­lysis Ser­vices model.

Note: There are some lim­it­a­tions regard­ing DAX meas­ures in Live Con­nec­tion. Only Report Level Meas­ures are avail­able, mean­ing these meas­ures are stored within the Power BI report and are not writ­ten back to the Ana­lysis Ser­vices model. How­ever, all report­ing cap­ab­il­it­ies in Power BI remain avail­able when using Live Connection.

Can Live Con­nec­tion be Used in a Com­pos­ite Con­nectiv­ity Type?

Live Con­nec­tion estab­lishes a con­nec­tion to a semantic layer such as Ana­lysis Ser­vices or a Power BI data­set. In an enter­prise scen­ario, hav­ing a semantic layer between your data source sys­tems and Power BI is cru­cial, as it sim­pli­fies the data in the data ware­house, mak­ing it more access­ible and usable for the business.

This approach aligns com­plex data with famil­iar busi­ness terms, remov­ing the need for addi­tional data pre­par­a­tion. As a res­ult, it offers a com­plete and con­sol­id­ated view across the organisation:

To con­clude, we can­not use Live Con­nec­tion to a cent­ral­ised ver­sion of the truth and extend it with our own data through an Import con­nectiv­ity type. How­ever, this cap­ab­il­ity has been fre­quently reques­ted by the Power BI community.

Dir­ect Lake

Dir­ect Lake mode allows you to ana­lyse data dir­ectly from a data lake without the need to query a lake­house or ware­house end­point. It elim­in­ates the need to import or duplic­ate data into a Power BI model.

his mode is based on load­ing Par­quet-format­ted files (a cru­cial com­pon­ent in the world of big data stor­age and ana­lyt­ics, play­ing a key role in data lakes by improv­ing per­form­ance when work­ing with large data­sets) dir­ectly from a data lake.

Before we dive deeper into this con­nectiv­ity mode, let’s look at the dif­fer­ence between a lake­house and a data lake. A data lake is a stor­age repos­it­ory that holds vast amounts of raw data in its nat­ive format, such as logs, images, and sensor data. It’s designed for scalab­il­ity and flex­ib­il­ity, allow­ing data to be inges­ted without prior trans­form­a­tion. In con­trast, a lake­house com­bines the bene­fits of a data lake with struc­tured data man­age­ment. It organ­ises data into tables and schemas, mak­ing it easier to query and ana­lyse using tools like SQL. Essen­tially, a lake­house bridges the gap between raw data lakes and tra­di­tional data warehouses.

Use case

Dir­ect Lake is ideal for ana­lys­ing both very large mod­els and mod­els with fre­quent updates at the data source:

Note: Dir­ect Lake is sup­por­ted only on Power BI Premium P and Microsoft Fab­ric F SKUs.

Lake­house Requirements

Before using Dir­ect Lake, you must pro­vi­sion a lake­house (or ware­house) with one or more Delta tables in a work­space hos­ted on a sup­por­ted Power BI or Microsoft Fab­ric capa­city. The lake­house provides the stor­age loc­a­tion for your Par­quet-format­ted files in OneLake, and also serves as an access point to launch the web mod­el­ling fea­ture to cre­ate a Dir­ect Lake model.

The fol­low­ing pre­requis­ites are needed to be able to con­nect Power BI to Databricks:

  1. Power BI Desktop 2.85.681.0 or above.
  2. Access token for Dat­ab­ricks or AAD access to Databricks.
  3. An Azure Dat­ab­ricks cluster or Dat­ab­ricks SQL warehouse.

Pre­vi­ously, a data ware­house was needed to expose your data in the data lake to front-end tools like Power BI. This often res­ul­ted in data duplic­a­tion, as illus­trated in the schema below:

SQL End­point

When pro­vi­sion­ing a lake­house, an SQL end­point for SQL query­ing and a default model for report­ing are auto­mat­ic­ally cre­ated and updated with any tables added to the lake­house. Although Dir­ect Lake mode doesn’t query the SQL end­point when load­ing data dir­ectly from OneLake, the SQL end­point is neces­sary when a Dir­ect Lake model must seam­lessly fall back to Dir­ectQuery mode. This fall­back occurs when the data source uses spe­cific fea­tures, such as advanced secur­ity or views, that Dir­ect Lake can­not pro­cess. Dir­ect Lake mode also quer­ies the SQL end­point for schema- and secur­ity-related information.

Data Ware­house

As an altern­at­ive to a lake­house with an SQL end­point, you can pro­vi­sion a ware­house and add tables using SQL state­ments or data pipelines. The pro­ced­ure for pro­vi­sion­ing a stan­dalone data ware­house is almost identical to that of a lakehouse.

The Dif­fer­ence Between the Power BI Con­nectiv­ity Modes

Now that we’ve reviewed each con­nectiv­ity mode, let’s sum­mar­ise and identify the dif­fer­ences between them. The table below high­lights the dis­tinc­tions between the four con­nectiv­ity modes we’ve covered:

The Dif­fer­ence Between Real-Time, Live & DirectQuery

In a real-time dash­board, new data rows can be inges­ted from vari­ous meth­ods or sources, such as Power BI’s REST API, Stream Ana­lyt­ics, or other stream­ing ser­vices like Pub­Nub. The main dif­fer­ence is that a real-time data­set uploads data dir­ectly without any user inter­ac­tion, whereas Live Con­nec­tion refreshes data whenever requested.

Can we use Live Con­nec­tion with a Com­pos­ite Con­nectiv­ity Type?

Using Live Con­nec­tion is quite com­mon in enter­prise scen­arios. Live Con­nec­tion estab­lishes a dir­ect con­nec­tion to a semantic layer, such as Ana­lysis Ser­vices or a Power BI data­set. In an enter­prise set­ting, hav­ing a semantic layer between your data source sys­tems and Power BI is imper­at­ive, as it sim­pli­fies the data in the data ware­house, mak­ing it more access­ible and usable for the business.

How­ever, it is cur­rently not pos­sible to use Live Con­nec­tion to cent­ral­ise your data through a Com­pos­ite con­nectiv­ity type. This func­tion­al­ity has been repeatedly reques­ted by the Power BI community.

Dir­ect Lake vs. Import Mode

To learn more about how Dir­ect Lake in Microsoft Fab­ric per­forms as com­pared to Import Mode, you can watch our on-demand webinar on the topic.

Con­clu­sion

In this art­icle, we’ve explored and gained a solid under­stand­ing of all the avail­able con­nectiv­ity types that Power BI provides, includ­ing the new­est and most up-to-date option, Dir­ect Lake. We’ve also examined the advant­ages, dis­ad­vant­ages, and use cases of each con­nectiv­ity mode to help determ­ine which is most suit­able, depend­ing on the data.

The choice of data model imple­ment­a­tion also depends on the spe­cific needs of the pro­ject. To sum­mar­ise the dif­fer­ences between the mod­els we’ve seen:

  • In Import mode, Power BI loads all data into the data model.
  • In Dir­ectQuery mode, Power BI only loads metadata, such as column head­ers, into the data model.
  • In Live Con­nec­tion mode, Power BI con­nects to the data source in real time without load­ing any data into the data model, requir­ing the data model to be cre­ated dir­ectly within the data source.
  • In Dir­ect Lake mode, Power BI accesses data stored in OneLake dir­ectly, without load­ing it into the model or rely­ing on an SQL end­point, mak­ing it ideal for large data­sets with fre­quent updates.
  • Lastly, Com­pos­ite mode allows you to com­bine Import and Dir­ectQuery con­nec­tions in a single model, offer­ing flex­ib­il­ity by enabling both dir­ect and cached data access within the same dataset.

Are you look­ing to optim­ise your Power BI con­nectiv­ity and unlock the full poten­tial of your data? Whether you need to under­stand the best mode for your spe­cific use case, stream­line your data pro­cesses, or explore advanced fea­tures like Dir­ect Lake and Com­pos­ite mod­els, our team is here to help.