Forum Discussion

CrazyHorse66's avatar
CrazyHorse66
Helper I
4 years ago
Solved

Optimum set up for Dataflows

Hi, thanks in advance for your support   We have an ETL process set up using Dataflows, not without it's issues but generally working ok 1. Ingest - S1 Dataflows, data from source 2. Transform - ...
Dataflow
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi， CrazyHorse66 

    a. The main purpose of using the Enhanced Compute Engine is to drastically reduce the refresh time required for long-running ETL steps on compute entities. If you have many ETL operations (such as performing joins, distinct, filte) on S2, you should enable the enhanced compute engine for S2.

    b.The following list describes some of the reasons you may choose to use linked tables:

    • If you want to reuse a table across multiple dataflows, such as a date table or a static lookup table, you should create a table once and then reference it across the other dataflows.

    • If you want to avoid creating multiple refreshes to a data source, it's better to use linked tables to store the data and act as a cache. Doing so allows every subsequent consumer to leverage that table, reducing the load to the underlying data source.

    • If you need to perform a merge between two tables.

    c.You can use DirectQuery to connect directly to your dataflow, and thereby connect directly to your dataflow without having to import its data. Direct query avoids separate refresh schedules and it is aslo useful for working on a filtered view of data inside a dataflow. 
    Limitation: Large dataflows may have trouble with timeout issues when viewing visualizations. Large dataflows that run into trouble with timeout issues should use Import mode.

    d. Personally, I also prefer to use Power Automate to trigger multiple dataflows and Power BI datasets sequentially.

    Please refer to below tutorials for more details.
    Premium features of dataflows 

    Create a dataflow using linked tables 

     

    Best Regards,
    Community Support Team _ Eason

CrazyHorse66's avatar
CrazyHorse66
Helper I
4 years ago

I really appreciate your reply, thanks

 

Please could you provide further information:

a. Should the engine only be enabled for S2 then? Or S1 AND S2?

 

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    Hi, CrazyHorse66 

    If S1 is used to extract all of the required from a data sourcee and all ETL operations are performed in S2, you just need to enable the Enhanced Compute Engine for S2.

     

    By the way, in Premium Gen2, Enhanced Compute Engine is turned off by default. It is automatically turned on when the dataflow is connected to another dataflow.

     

    Best Regards,
    Community Support Team _ Eason

    • CrazyHorse66's avatar
      CrazyHorse66
      Helper I
      4 years ago

      Hi, a follow up questions please - if enhanced is enabled for both s1 (ingest) AND s2 (transform) is this a bad thing in terms of performance, or can cause conflict between the 2 working together in any way?

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        4 years ago

        Hi, CrazyHorse66 

        It is not recommended to enable it for S1, it could lead to slower refresh times:

         

        • When the enhanced compute engine is enabled, it requires some memory to function properly. As such, memory available to perform a refresh is reduced and therefore increases the likelihood of refreshes to be queued, which in turn reduces the number of dataflows that can refresh concurrently. To address this, when enabling enhanced compute, increase the memory assigned for dataflows to ensure the memory available for concurrent dataflow refreshes remains the same.

        • Another reason you may encounter slower refreshes is that the compute engine only works on top of existing entities. If your dataflow references a data source that's not a dataflow, you won't see an improvement. There will be no performance increase, since in some big data scenarios, the initial read from a data source would be slower because the data needs to be passed to the enhanced compute engine.

        Best Regards,
        Community Support Team _ Eason