general question

3 Topics

High volume Dataverse dataflow times out avoid NESTED FROMs and/or JOINs best approach to resolve?
Background We have a Gen1 Dataverse dataflow that calculates aggregate totals per record (yearly, prior-year, lifetime, and household rollups) and writes them to two output entities, Merge Account and Merge Contact. Each output entity is built from a chain of ~11 Table.NestedJoin (JoinKind.FullOuter) joins over ~12 upstream aggregate queries. The problem The dataflow refreshes without issue on smaller environments but times out on high-volume ones with: Microsoft SQL: "The updated two minutes (2) timeout period elapsed prior to completion of the operation. Please avoid using SELECT *, or NESTED FROMs and/or JOINs to execute the query with default five minutes (5) timeout." ErrorCode = 10478; Number = 40000 (Some environments show error 40197 instead.) It stops on the Merge step. What we've implemented so far We've built a monitoring solution: a cloud flow that detects dataflow refresh failures and logs the details (dataflow name, ID, and error message) into a custom Refresh Audit table, surfaced in a model-driven app so the team has visibility into failures. We've analyzed the failure across multiple environments and confirmed it correlates with data volume — the same dataflow completes on smaller datasets and times out on larger ones, with the filtered row volume and per-record density appearing to be the main drivers. We've identified that the full-outer joins don't appear to fold, so a large nested query is sent to the Dataverse TDS endpoint and hits the query timeout — i.e. it's a timeout, not a data/row error. Approaches we're evaluating — would these be effective? Increasing the query/command timeout for a Gen1 Dataverse dataflow — is that configurable, and where? Using Table.Buffer on the join inputs to force local evaluation and avoid pushing the nested query to the TDS endpoint — would this reliably help, or just shift the bottleneck? Staging the upstream queries into computed/staged entities (or a separate staging dataflow) — is this the recommended pattern, and does it need enhanced compute / premium? For very large datasets (1M+ rows), pushing the aggregation source-side (FetchXML aggregate / SQL view) — is this the more reliable long-term approach? Is there a supported way to get per-step timing / query diagnostics for a dataflow refresh, to pinpoint the heaviest step and enrich our monitoring? Any guidance on which of these works best for high-volume Dataverse dataflows with heavy full-outer join chains would be much appreciated. Thanks!
Solved
Tejakm123
1 month ago Place Power Query
351Views
0likes
5Comments
dataflow
In the below scenario assume that D is the table I use in the report which is a append of 1, A and B. If I refresh DF1 and DF A then its obvious that the last dataflow would also refresh since it has 1 and A as linked entities. Questions: 1. Does the final dataflow refresh twice? 2. If I refresh 1 and A together will it cause the last one, and hence the entire chain to fail? Any best practices? 3. If I care most about A and expect 1 to fail often, how can I ensure that the final DF does not fail? And my append happens with A and B with the new data and 1 with the old data (in the current scenario the entire flow would fail)
Solved
mandy5118
1 year ago Place Power Query
1.3KViews
0likes
4Comments
How to pass cell value as the Query value for a query utilizing Power Query
Good Morning, I am very green to Power Query, so I'm unsure if this is even possible. Base Goal: Pass the value from "SELECT_COL" as the UserQuery "let" statement. Clarifiying Details: I have 2 datasets 1) SURVEY_PARAMS 2) SOURCE_LKP_FILE I have successfully been able to filter down the SOURCE_LKP_FILE until I get just the 1 cell value "SELECT_COL" Code to get to just the query value I want to pass into the UserQuery(I can modify my SOURCE_LKP_FILE and add in or remove anything that's needed to help make this dynamic) below: let Source = SOURCE_LKP_FILE, #"Filtered rows" = Table.SelectRows(Source, each [TABLE_NAME] = "FOWLER_LKP_TBL"), #"Choose columns" = Table.SelectColumns(#"Filtered rows", {"SELECT_COL"}), #"Drill down" = #"Choose columns"{0}[SELECT_COL] in #"Drill down" I want to take the cell value from above and use it as part/whole of my UserQuery(my user query will just be a series of extract by range) I'm a heavy pipeline adf worker and I thought I'd be able to do some form of referencing within here like: let Source = SURVEY_PARAMS, #"Inserted text range" = $SELECT_COL in #"Inserted text range" Any help would be GREATLY appreciated
fowler7878
2 years ago Place Power Query
816Views
0likes
3Comments