Dataflows don't support Query Folding?

[gebberryOffice]

Working with a DataFlow on premium... because there is too much data to manipulate (100MM+rows) locally.

Issue is it seems on service dataflows don't support query folding?  hunh?  

I created a custom Parameter.. called MinDate, so that when I edit the PBIX on desktop.. I set MinDate to a date in the future.  My last step of pulling the Data flow.. is to set Date > MinDate.

when I close and apply... desktop pulls back all 24 gb of data..long long wait, and then applies the filter (0gb of data in the pbix)

when I publish up.. same thing..have to wait for all the data.

we've tried a number of other things..using a linked flow as the source in dataflows but always come back to the same issue... dataflows don't support query folding?  hunh?

How can I edit my Dataset/Model?  Without having to wait so long.

Using native query with query folding and the paramater worked way better.

Any ideas on how I can edit without a 2 hour wait..and waste of bandwidth.

[aechague]

Same question here... Trying to optimize a dataflow that calls data from another dataflows does not accelerates the processing time, perhaps due to lack of query folding...

[DebbieE]

I havent seen an answer on this. Is it a yes or a no> Do dataflows support query foldign because right clicking on each Applied step, I dont even get the native query option. Let alone see it greyed out

[v-xicai · Accepted solution]

Hi @gebberryOffice ,

1. For your first question:>>Issue is it seems on service dataflows don't support query folding?

Checking if query folding takes place is simply done by right-clicking on a step in the query editor and by verifying if the "view native query" option isn't greyed out.

If "View Native Query" is greyed out, it means query folding isn't taking place for that step.

You can refer to the links: https://www.mssqltips.com/sqlservertip/3635/query-folding-in-power-query-to-improve-performance/

                                               http://blog.pragmaticworks.com/power-bi-checking-query-folding-with-view-native-query

Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.

1. For your second question:>>How can I edit my Dataset/Model?  Without having to wait so long.

You can optimize your model from respects below:

• Tables or columns that are unused should be removed if possible. 
• Avoid distinct counts on fields with high cardinality – that is, millions of distinct values.  
• Take steps to avoid fields with unnecessary precision and high cardinality. For example, you could split highly unique datetime values into separate columns – for example, month, year, date, etc. Or, where possible, use rounding on high-precision fields to decrease cardinality – (for example, 13.29889 -> 13.3).
• Use integers instead of strings, where possible.
• Be wary of DAX functions, which need to test every row in a table – for example, RANKX – in the worst case, these functions can exponentially increase run-time and memory requirements given linear increases in table size.
• When connecting to data sources via DirectQuery, consider indexing columns that are commonly filtered or sliced again – this will greatly improve report responsiveness.  

For more guidance on optimizing data sources for DirectQuery, see DirectQuery in SQL Server 2016 Analysis Services.

You can refer to the links to learn more: https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance,

Best Regards,

Amy

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

[gebberryOffice]

that answer doesn't really help that option is not available for dataflows.. which always have that option greyed out, at least for the march/april updates of desktop.

After further research testing what I found is this in case it helps anyone:

Native query does work if you define all the flows in the same powerquery.  eg don't create 1 query vs a flowand then refer to it in another.. but create the reference in the same flow..and it will fold.

Using below as sample FlowB does NOT fold,  FlowC DOES Fold (!!)

eg 

FlowA =

let

  source = flow("abc")

in 

 source

FlowB = 

let

  source = flow("bcd")

  output=  table.combine(FlowA, source)

in

  output

FlowC =

  let

       source = flow("bcd")

       source2 =flow("abc") 

       output = table.combine (source, source2)

in 

      output

[Anonymous]

Thanks for the input! I am also struggling a bit with the overall performance of Power BI Dataflows. 

I tried to load common dimensions/facts from SQL to Power BI Dataflows, so I only have to load once a day, and datasets don't bother SQL anymore regarding dataset refreshes (because Dataflows would be the entermediate data layer). 

Loading Dataflows is duable, but than I have to load 20 datasets from those dataflow entities, and there I run in multiple 'slow queries' because it is probably not folding the queries (like it would normally if connect directly to SQL). So in Power BI Premium you'll get a lot of 'out of memory' issues because of slow queries. 

My next step is to try the 'enhanced compute' feature in Power BI Premium, and connect all my datasets to Dataflow Linked Entities and try to measure if there is a performance boost. 

I think the Dataflow concept is really great and is definately the way forward, will need to mature a bit I think, it also needs to have some more Enterprise features. (versioning, roles & data security, overwriting existing Dataflow entities via API.)

@gebberryOffice let me know if you have some need experiences/insights with Dataflows, because this was an older post. Thanks!

[robarivas]

Hello @Anonymous Were you able to find a viable solution? Did using the linked entities make a significant difference?

[Anonymous]

Hi @Anonymous & @robarivas. Were you guys able to find any solution for this.