Forum Discussion
Dataflows don't support Query Folding?
Hi gebberryOffice ,
- 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.
- 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.
Hi gebberryOffice ,
- 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.
- 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.
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
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!