Dataflows going to the source by Import Data Mode or Direct Query?

[Johann_32]

Hello,

I would have a simple question that I wasnt able to find in the Microsoft Documentation:

Is it possible that Dataflows are going to the source database in Direct Query mode, or is a dataflow always connecting via Import mode to the source database? 

Till now I only found that the Dataset is able to go to the Dataflow in Direct Query mode (with Enhanced Compute Entity enabled), but this is a complete different topic.

Thank you

[Johann_32]

Hello Pragati,

thanks a lot for your detailed answer, I now understand.

The reason why I was asking for Direct Query in combination with Dataflows is, that the amount of data that is imported in the Dataflow from the source is very big and it therefore takes a lot of time to reload the whole data all the time. It even may exceed the limit that Workspaces in Pro License are allowed to have.

With Direct Query I was thinking to bypass this issue, but it seems that this would not help in our scenario, as the DF1 (in your example) would still have the long loading time.

I am also aware about the fact that Incremental Refresh in our Dataflows could help us in this topic to only load the data that has really changed lately. So we will now investigate more in this topic.

Thank you!

[Pragati11]

Hi @Johann_32 ,

I think if you want to  bring in data using DirectQuery in dataflows, then there is no point in using dataflows. You can directly bring in tables in PBI in DQ  mode if transformations are not required. Th only benefit of dataflows is to do all the heavy transformations in a different layer before data reaches to PBI. 🙂

[Johann_32]

Hello again @Pragati11,

I would have one final question to that topic, because I now found out about the "Hybrid Tables" functionality that combines Direct Query for getting latest data + import mode for historical data.

I tested and its available for Power BI datasets in the Desktop tool. Can you confirm that this features is missing in Dataflows? (makes sense because of the reasons you mentioned above).

Thank you!

Johann

[Pragati11 · Accepted solution]

Hi @Johann_32 ,

I understand your question now.

So the main benefit of dataflow is to create a separate layer of logical transformations on the source data like the Power Query element in Power BI Desktop. Dataflow is the Power Query capability in the cloud. So the basic necessity for transforming the source data in the transformation layer is it needs to be read in IMPORT mode. Therefore answer to your question is Dataflow connects to source data in import mode.

Consider a scenario where you have DF1 --> dataflow connected to Azure SQL DW which has imported all the relevant tables. Now you want to consume DF1 to another dataflow say DF2 to transform the data imported in DF1.

In this scenario where a dataflow can consume another dataflow for reusable purpose; DF2 connects to DF1 in direct query mode.

Hopefully the explanation helps.

You can find more details here: https://learn.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-directquery

[Pragati11]

HI @Johann_32 ,

I am not very clear on your question here. Are you asking by what mode the data connected in the Dataflows or can Dataflows be consumed by DirectQuery mode in BI Desktop to create datasets?

[Johann_32]

Hello Pragati, 

the first what you refer to, is what I am searching for, "by what mode the data connected in the Dataflows".

Let my try to ask in a different way:

Can a dataflow be set up to go to the source database (Oracle, SAP, ...) in Direct Query mode, instead of Import Data Mode? 

Maybe, I also understood something wrong and dataflows are not using neither of these two ways.

Thank you