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 - S2 Dataflows, data from S1 Dataflows, transformed

3. Datasets using S2 Dataflows

 

We have now started to update these to use the Enhanced Compute Engine, with promising results

Key questions

a. Should the Enhanced be turned on for S1 AND S2, or ONLY S1, or ONLY S2? Or different settings (Optimised) for each?

b. What type of entity should the S2 dataflow use? Linked etc?

c. What method should S2 dataflow use - Direct Query or Import? Would be good to be able to filter the S1 data for use in S2 (reduce the size) depending on the requirement

d. We have set up Power Automate to trigger the S2 after S1 completes - is that the best solution based on the updates to any of the above?

 

Thanks again

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

5 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    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?