Data acquisition via Power BI: Import or direct query mode?

by | 5 March 2020

The emergence of HANA databases is bringing real-time reporting to the forefront.

Until recently, the question of updating data was one of the first questions asked during the scoping phase. When D+1 reporting was possible, we did not return to it. The architecture put in place, as well as the functional response to needs, were based on this assumption.

Today, users start by mentioning that they need real time data before they even draw a model of their report.

In the first versions of Power BI, the " direct query " mode was relatively weak, and had many constraints (connection to a single table or model allowed, impossibility to create additional columns in the desktop, ...).

Successive versions of Power BI now allow access to data in real time, via refreshment or in a composite manner. Nevertheless, this choice is much more structuring than it seems. So be careful not to take the easy way out with real-time reporting just because "more can be less".

Note:

These two additional features are not covered in this article, for the sake of simplicity. However, they allow for the optimisation of what happens after the choice of the connection mode in run mode.

 

Power BI allows an import mode and/or a direct query mode

 

Import mode

Import mode is the historical operating mode of Power BI. In this case, Power BI datasets are refreshed in a scheduled and discrete manner.

During a refresh, all model creation steps are executed sequentially:

  • Connection to data sources,
  • Loading raw (refreshed) data,
  • Transformation,
  • Creation of the model (link between data).

The resulting dataset is cached in order to provide optimal interactivity for the connecting user.

If you have ever seen Power BI demonstrations with interactive visuals and cross-filtering, this is most likely how it works.

 

Direct query mode

The " direct query " mode allows the data to remain in the source database. SQL queries can then be sent systematically:

  • During the creation of the report (the desktop sends a request to each action to update the visual).
  • Each time a user browses (Power BI service takes over and sends requests).

In this mode of operation, the real-time aspect is achieved since the data is fed back from the database with each interaction.

 

Composite mode

The composite mode allows within the same dataset to have :

  • Data sources refreshed by import,
  • Other live connected data sources.

In this case, the Power BI cache contains the data from the sources connected in import. Then, when a user interacts, the Power BI service creates SQL queries (depending on the context), transmits them to the right database, in order to update the page elements in real time.

 

The choice of acquisition mode is (almost) definitive in Power BI

 

The acquisition mode is determined when connecting to the data source, and is the first step in creating a report.

The definition of the report settings is final and cannot be changed later.

There is one exception, however. It will be possible for a user to switch a data source set to "direct connection" to "import" mode at any time.

Once in "Import" mode, no backtracking is possible and the user will not be able to switch back to "direct connection".

 

What are the main lines to follow before choosing one mode or the other in Power BI?

 

Before making a choice, the following criteria should be considered.

 

1 - Favour Import mode when possible

 

The editor's recommendations are to favour importing in order to make the most of the speed of calculation of power BI for a fluid user experience.

The following points should be checked:

  • Limitation of the data set?
    • Depending on your licenses, a maximum model size exists (1GB for a pro user or depending on the type of capacity for premium users).
  • Is real time necessary at all costs?
    • Remember that Power BI Premium can refresh a dataset up to 48 times a day.

 

2 - Evaluate the limitations of DirectQuery before taking action

 

Questions to ask:

  • What will be the load on the source system?
    • The database will often be stressed, with the risk of reporting bottlenecks in a transactional system.
  • What is the reaction time of a request?
    • The editor gives the limit of 5 seconds not to be exceeded. Beyond that the user experience will not be good.
  • Are there any limitations specific to my data source?
    • Not every data source provides access to the same functionality in Power BI in direct connection mode.
    • For example, SAP HANA is not currently compatible with a composite model.

The connection to SAP BW is a good example to illustrate that the choice of the connection mode is primarily contextual:

  • A justification for import mode can be found in Gartner's Magic Quadrant 2020.

"Connectivity to SAP BW and HANA direct queries is problematic - a known issue that Microsoft is working on. Customers generally choose to load data into Power BI instead, which is more performant."

  • Microsoft's recommendation is to use direct query mode when connecting to an OLAP source (to avoid the effects of aggregating non-additive measures):

"To make it easier to get the appropriate aggregated data (based on the needs of the visual) directly from the source, queries should be sent per visual as is the case in DirectQuery."

For further discussion, the following document from the editor opens the discussion on limitations in different scenarios (link: https: //docs.microsoft.com/fr-fr/power-bi/desktop-directquery-about)

 

3 - Anticipate the pitfalls of real time and train users.

 

In composite mode, the problem of an unrefreshed data source compared to a real-time data source can be found.

  • This can lead to incidents in the eyes of the user who is not aware of this limitation.

The interaction between the visuals all send a query to the database.

  • In a context with many users and many visuals, the number of requests sent to the database can become a risk.

When designing a report, each action sends a query to the database.

  • Good practices exist but need to be passed on, especially in a self-service context.

 

Conclusion

 

The user experience is the strongest point of Power BI today. Anyone who sees a Power BI model will remember the smooth and intuitive navigation.

This fluid navigation is based on models acquired in import mode, pre-computed and stored in cache memory.

Real-time reporting is certainly possible with Power BI, but this will be at the expense of the previous arguments.

There is a risk of degrading the user experience in favour of real time reporting due to the many limitations that exist via DirectQuery connections.

It is therefore important to understand the consequences of this choice and to explain them to the decision-makers, to avoid losing one of the strengths of this tool in favour of a 30-minute delay between the new data and its appearance in the reporting.

The following two tabs change content below.

Emilien Gaignette

Emilien is a senior BI consultant who has participated independently in numerous BI solution implementation projects. He has also been involved in SAP ERP implementation projects and therefore has a good knowledge of the ERP and the company's businesses. Pedagogical and passionate, he enjoys sharing his knowledge.
Share This