The phrases “DirectQuery” and “Join Stay” are in some way complicated. I noticed a lot of individuals are utilizing each terminologies as alternate options. However, the context of “DirectQuery” and “Join Stay” are very totally different certainly. Subsequently, if use a a terminology once we’re speaking a couple of totally different context then the entire state of affairs may get fairly complicated. on this submit I attempt to clarify the variations and make it extra clear to stop utilizing a unsuitable terminology and ensure everyone seems to be on the identical web page once 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 sort of information supply like SQL Server DB or Oracle DB.
There are two forms of information connections once 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 accessible in reminiscence
- DirectQuery: which doesn’t load information into mannequin. As an alternative, it runs a number of concurrent queries on the RDBMS facet (information supply facet) and will get the outcomes. That is good to help real-time information processing.
Notice: The identical principal applies to SSAS Tabular.
However, when discuss “Join Stay”, we’re referring to the info connection sort from a reporting software 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 once you join from Energy BI Desktop to an occasion of SSAS:
- Knowledge Import: That is once 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 expertise to load a portion of information from SSAS to your reporting software. 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’s essential to rapidly produce a report. For positive the enterprise logic should be carried out in SSAS later.
- Join Stay: The advisable sort of connection used for reporting in an Enterprise BI answer. On this sort, all enterprise logics are captured within the semantic mannequin and made accessible for all reporting instruments. Subsequently, Energy BI Desktop turns to a report authoring software solely and you can not create any new enterprise logics within the report. For positive you possibly can create Report Degree measures, however, principally all of the enterprise logic sits in SSAS facet.
Here’s a generic structure for an enterprise stage design by way of information load in several contexts:
We virtually at all times use “Import Knowledge Mode” to load information into SSAS to make it accessible for reporting instruments, except information latency requirement dictates utilizing DirectQuery to help real-time information processing.
And we at all times are inclined to “Join Stay Mode” to make studies on high of an occasion of SSAS (both Multidimensional or Tabular Fashions), except we have to urgently create a report with a selected enterprise logic that hasn’t been carried out in SSAS but.
Now that you understand 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”.
