The phrases “DirectQuery” and “Join Stay” are in some way complicated. I noticed numerous persons are utilizing each terminologies as alternate options. However, the context of “DirectQuery” and “Join Stay” are very completely different certainly. Subsequently, if use a a terminology after we’re speaking a few completely different context then the entire scenario may get fairly complicated. on this submit I attempt to clarify the variations and make it extra clear to stop utilizing a flawed terminology and ensure everyone seems to be on the identical web page after we’re referring to “DirectQuery” or “Join Stay”.

After we use the “DirectQuery” terminology we are literally speaking about connecting from Energy BI Desktop occasion to an RDBMS kind of knowledge supply like SQL Server DB or Oracle DB.

There are two forms of knowledge connections after we’re connecting to RDBMS like SQL Server or Oracle DB from Energy BI Desktop:

• Import Information: which accurately hundreds knowledge into the underlying mannequin to make it obtainable in reminiscence
• DirectQuery: which doesn’t load knowledge into mannequin. As an alternative, it runs a number of concurrent queries on the RDBMS aspect (knowledge supply aspect) and will get the outcomes. That is good to assist real-time knowledge processing.

Observe: The identical principal applies to SSAS Tabular.

However, when speak about “Join Stay”, we’re referring to the info connection kind from a reporting device like Energy BI Desktop OR Excel to an occasion of SSAS, both SSAS Multidimensional or SSAS Tabular.

There are additionally two forms of knowledge connection once you join from Energy BI Desktop to an occasion of SSAS:

• Information Import: That is once you need to load knowledge from an SSAS Dice or an SSAS Tabular mannequin. Subsequently, chances are you’ll write MDX or DAX queries relying on the underlying know-how to load a portion of knowledge from SSAS to your reporting device. That is solely helpful if a enterprise logic hasn’t been applied within the semantic mannequin (both SSAS dice or Tabular mannequin) and you have to shortly produce a report. For certain the enterprise logic should be applied in SSAS later.
• Join Stay: The really helpful kind of connection used for reporting in an Enterprise BI resolution. On this kind, all enterprise logics are captured within the semantic mannequin and made obtainable for all reporting instruments. Subsequently, Energy BI Desktop turns to a report authoring device solely and you can not create any new enterprise logics within the report. For certain you’ll be able to create Report Degree measures, however, mainly all of the enterprise logic sits in SSAS aspect.

Here’s a generic structure for an enterprise stage design by way of knowledge load in several contexts:

We virtually all the time use “Import Information Mode” to load knowledge into SSAS to make it obtainable for reporting instruments, until knowledge latency requirement dictates utilizing DirectQuery to assist real-time knowledge processing.

And we all the time are likely to “Join Stay Mode” to make experiences on prime of an occasion of SSAS (both Multidimensional or Tabular Fashions), until we have to urgently create a report with a particular enterprise logic that hasn’t been applied in SSAS but.

Now that you already know the variations between “DirectQuery” and “Join Stay” if somebody says “there are some DAX limitations if we use “Join Stay” it’s best to instantly ask them to elaborate because the DAX limitations solely apply to the connections with “DirectQuery” mode and NOT “Join Stay”.