Forum Discussion

MS_fn's avatar
MS_fn
Frequent Visitor
3 years ago
Solved

Dataflow - different results between preview and later access

Hello community,
 
how could it be that the results of a dataflow is different to the results if this dataflow is loaded within another dataflow or within the Power BI desktop? The dataflow based on another dataflow which selects columns and rows from a further dataflow and adds a special sorting. On this sorting is now a string-aggregation applied and to the resulting records is a row-index added. 
 
The shown results within the dataflow preview are correct. But if I then access this dataflow the ordering of the records is different and also the results of the string-aggregation are wrong. It looked that the data-preview doesn't represent the real ETL processing respectively the results aren't stored in this way. Could anyone shed some light what's going on? Any reasons and solutions or at least workarounds?
 
The left picture-part showed the correct dataflow results and the right part the wrong results directly loaded in the desktop. The record 16 is highlighted as example and also the record 38 in which I found the misordered key-value. Also notice that the ordering of the article in [Artikel Concat] is different.


Many thanks for your support.

Dataflow
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi ,  MS_fn 

    Thanks for your quick response! According to your description, this may cause by the ehance compute engine.

    As searched, this function is mainly used to create a SQL cache to implement Query folding to increase the performance of dataflow. 

     

    Once the enhanced compute engine is enabled in the Power BI Premium capacity settings, and the dataflow settings and configuration (as illustrated above) dictate that the engine is used for a given dataflow, this is what happens:

    （1）When the dataflow is refreshed, the Power Query for each entity is executed, and the output of the query is persisted in the dataflow’s CDM folder as CSV data and JSON metadata
    （2）For any entity for which the enhanced compute engine is enabled, the output of the entity’s Power Query is also loaded into a table in a SQL database instance managed by the Power BI service

     

    And there is differnce about ""what exactly do you mean by supported queries?"":

     And for more information, you can refer to if can help you :
    Dataflows enhanced compute engine – will it fold? – BI Polar (ssbipolar.com)
    Power BI dataflows enhanced compute engine - YouTube
     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

6 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi ,  MS_fn 

    According to your description, You can see the correct data in the preview of dataflow, but when you connect to dataflow, you can see different data.

    For the preview of Dataflow, the query is sent directly to the data source, and the latest data of the data source is obtained every time you view it. The data connected to dataflow using Desktop is the data obtained from the latest refresh of dataflow. This may be the cause of the data inconsistency.

     

    • MS_fn's avatar
      MS_fn
      Frequent Visitor
      3 years ago

      Hi v-yueyunzh-msft,

       

      many thanks for your answer. 


      I'm quite sure that the cause couldn't be an inconsistency of the data in regard to the version of the data because I tried it at least a dozens times - the single dataflow and the entire chain of dataflows and looking from various following dataflows as well as from several pbix on the results and then adding later the RowNo (which isn't essential to my transformation) just to track which source-records becomes which target-records.

       

      I'm not absolutely sure but I think I found a way to fix the issue by disabling the enhanced compute-engine to the previous dataflow which performed the mentioned sorting (and only for this dataflow). Afterwards the results were like expected and for another similar use-case I replicated the measures - without the sorting and with the sorting in aggregating dataflow and then the one before and it worked again only if I disabled the enhanced compute-engine in the previous sorting dataflow.

      Therefore, is my deduction right that neither the various processing-steps within the Power Query preview nor multiple chained dataflows are really performed against the data-source and changing them else it are just on-the-fly previews? That there are no fixed sub-datasets created else just some kind of meta-data which contained TRUE/FALSE flags for records/columns, a sorting-index and similar information?

       

      I searched for the issue and my deduction in the help but didn't find anything which explained how and in which order the ETL processing is performed - acts it always in this way or are there any exceptions? Further how could it be customized and administers? Because by hundreds of dataflows and some need a disabled compute-engine and some not and which impact would it have regarding to run-times and the resource-consumption within the capacity? Are there any sources for more detailed information?

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi ,  MS_fn 

        Thanks for your quick response! According to your description, this may cause by the ehance compute engine.

        As searched, this function is mainly used to create a SQL cache to implement Query folding to increase the performance of dataflow. 

         

        Once the enhanced compute engine is enabled in the Power BI Premium capacity settings, and the dataflow settings and configuration (as illustrated above) dictate that the engine is used for a given dataflow, this is what happens:

        （1）When the dataflow is refreshed, the Power Query for each entity is executed, and the output of the query is persisted in the dataflow’s CDM folder as CSV data and JSON metadata
        （2）For any entity for which the enhanced compute engine is enabled, the output of the entity’s Power Query is also loaded into a table in a SQL database instance managed by the Power BI service

         

        And there is differnce about ""what exactly do you mean by supported queries?"":

         And for more information, you can refer to if can help you :
        Dataflows enhanced compute engine – will it fold? – BI Polar (ssbipolar.com)
        Power BI dataflows enhanced compute engine - YouTube
         

        Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

         

        Best Regards,

        Aniya Zhang

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly