Dataflow - different results between preview and later access

[MS_fn]

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.

[v-yueyunzh-msft]

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.

You can try to refresh the dataflow, and then connect to the dataflow to see if the data is still inconsistent.

 

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

[MS_fn]

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 · Accepted solution]

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

[MS_fn]

Hi @v-yueyunzh-msft ,

many thanks for the information. I went through the provided links and links from there as well as many other similar resources and I think I do comprehend the aim of the query folding and using cached data to optimize the performance but the howto is further quite unclear and why it's resulting in my issue. Within the most ETL scenarios the cache-access will probably be working without resulting in unexpected results but if a certain order of measurements and records are mandatory for the wanted views there seems to be a need to disable this feature.

Now I won't to dive deeper into the matter and therefore I regard it as solved. Again, many thanks for your support.

[bk2001050]

Hey @MS_fn,
Thanks for your post. I am facing the exact same issue and my enhanced compute engine is misbehaving, changing some seemingly random values in a specific column. It took me a while to troubleshoot the problem but turning it off since to have fixed it for now. Did you ever find out why this happens and how to avoid it?

[MS_fn]

No it's not solved - neither this specific case nor the more general issue that a data-update to a dataflow (just clicking on the update-button within the workspace) results reliable in updated and correct data. It worked instead by opening the dataflow and saving it again and performing then directly afterwards the update.

I think this behaviour is related to the implemented caching-logic - which might need to be configurred in some way (maybe activating an incremental refresh would also trigger a real update) or maybe just completely disabled.

Currently I didn't dive deeper into the matter because it's only a side-project to evaluate if Power BI is an alternatively for our other reporting. At the moment it didn't look like as if Power BI as environment has enough capabilities in regard to a multi-layer ETL and analytics in the UI - at least for our current requirements.