The phrases “DirectQuery” and “Join Stay” are in some way complicated. I noticed a number of individuals are utilizing each terminologies as 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 state of affairs may get fairly complicated. on this put up I attempt to clarify the variations and make it extra clear to stop utilizing a fallacious terminology and ensure everyone seems to be on the identical web page after we’re referring to “DirectQuery” or “Join Stay”.
Once we use the “DirectQuery” terminology we are literally speaking about connecting from Energy BI Desktop occasion to an RDBMS kind of information supply like SQL Server DB or Oracle DB.
There are two forms of information connections after we’re connecting to RDBMS like SQL Server or Oracle DB from Energy BI Desktop:
- Import Knowledge: which accurately hundreds information into the underlying mannequin to make it out there in reminiscence
- DirectQuery: which doesn’t load information into mannequin. As a substitute, it runs a number of concurrent queries on the RDBMS aspect (information supply aspect) and will get the outcomes. That is good to help real-time information processing.
Observe: The identical principal applies to SSAS Tabular.
However, when discuss “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 information connection if you join from Energy BI Desktop to an occasion of SSAS:
- Knowledge Import: That is if you need to load information from an SSAS Dice or an SSAS Tabular mannequin. Subsequently, you could write MDX or DAX queries relying on the underlying know-how to load a portion of information from SSAS to your reporting device. That is solely helpful if a enterprise logic hasn’t been carried out within the semantic mannequin (both SSAS dice or Tabular mannequin) and it is advisable to shortly produce a report. For certain the enterprise logic should be carried out in SSAS later.
- Join Stay: The advisable 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 out there for all reporting instruments. Subsequently, Energy BI Desktop turns to a report authoring device solely and you can’t create any new enterprise logics within the report. For certain you’ll be able to create Report Degree measures, however, principally all of the enterprise logic sits in SSAS aspect.
Here’s a generic structure for an enterprise stage design when it comes to information load in several contexts:
We virtually all the time use “Import Knowledge Mode” to load information into SSAS to make it out there for reporting instruments, until information latency requirement dictates utilizing DirectQuery to help real-time information processing.
And we all the time are inclined to “Join Stay Mode” to make studies on prime of an occasion of SSAS (both Multidimensional or Tabular Fashions), until we have to urgently create a report with a selected enterprise logic that hasn’t been carried out 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 is best to instantly ask them to elaborate because the DAX limitations solely apply to the connections with “DirectQuery” mode and NOT “Join Stay”.
