Forum Discussion
Staging Dataflows -- Linked tables from dataflow still causing gateway server to run query
Community ChampionWhile you are working, the dataflow is folding queries back to the server, and it does this every time you make a change.
It depends on how large the data is, but you can block that folding during development by adding Table.Buffer() around one of the statements. So if you have something like a Table.AddColumns(#"Some Step Name", "New Field", each [Value] + 1), change it to Table.Buffer(Table.AddColumns(#"Some Step Name", "New Field", each [Value] + 1))
Now it will hold the table in memory and do everything locally in the gateway or service.
When you are done and have everything working, remove Table.Buffer. The dataflow will send one query to the server to get the data for as much of your transformations as it can (what will fold) and will do the rest locally.
Note: If your table has millions of records, this won't work. It will just cause your memory to fill up. If that is the case, for the purposes of work, add a step early on to select the top 10,000 records or something. Enough to work with, but without holding a few GB of data in memory. Then do the Table.Buffer trick.
Then remove Table.Buffer and the top 10,000 records limiter step before the final save.
So as my Dataflow A feeds other reports and Dataflows I don't want to change it to TopN.
Will this work?
Dataflow B:
1. Link Dataflow A:[SomeTable]
2. Reference [SomeTable] to become a calculated table:
3. TopN for 10,000 rows
4. Table.Buffer
5. Perform transformations
6. Remove Table Buffer after work is completed and ready to use in a report.
If that doesn't work, what is the standard solution for using a live active database? Our server absolutely hates me and with table locks from other users and long read cycles from me, I cripple our systems almost immediately. (Other known issues at play of course) What I want to do is read each table only once a day, build transformations and then set a refresh schedule to occur outside of business hours.
- edhans
I think that is correct. Your thinking here is 100% on point, but during the development of the dataflow it is a load on the server.
Another alternative - do you have a test or dev environment? You could develop the dataflow on that, then simply change the database/server names when done.
But even if you don't, once the dev work is finished, the dataflow will be the only impact on your SQL server, refreshing however many times a day it needs to. Everyone else will write reports from the dataflows.
One other thought. Not totally best practice, but will really reduce load on the servers. Just copy the tables straight to the dataflow, unaltered except maybe date filters so you don't pull in 20 years of data. Then write your transformation dataflows off of those. You'll import more data but that is less of a load on the servers vs continually writing transformations.
If you were good with M code, you could then take those transformations and apply to the actual SQL tables. Essentially you are creating your own DEV environment. But you have to know how to work in the advanced editor to move the M code around. On a scale of 1-10, I'd say you need to be a 5-7 in M code to do that effectively.Thank you for your help Edhans,
edhans wrote:But even if you don't, once the dev work is finished, the dataflow will be the only impact on your SQL server, refreshing however many times a day it needs to. Everyone else will write reports from the dataflows.
The Dev work includes all of the downstream dataflows correct?
One other thought. Not totally best practice, but will really reduce load on the servers. Just copy the tables straight to the dataflow, unaltered except maybe date filters so you don't pull in 20 years of data. Then write your transformation dataflows off of those. You'll import more data but that is less of a load on the servers vs continually writing
I was under the impression I was importing directly to the data flow. How do I go about copying the tables to the data flow? That is what I think I was trying to do?
To my knowledge, I am not using direct query.
Thanks again,
