PDA

View Full Version : Managing connections



Michlus
2016-09-22, 12:44 AM
Hi.
I have PQ retrieving data from 2 SQL scripts.
I'm doing many manipulations, merges etc throughout some 15 connection-only queries.
I have 2 main outputs which I use with pivot tables.
One output (output1) is derived from one SQL datasource (loaded into a table, but I'm planning to change that to Data Model only).
The second output is derived from the second SQL script + also output1 (it does calculations and comparisons).
Here is my problem:
How do I ensure that the connection to the first datasource is evoked only once?
Currently, whatever order I refresh the pivot tables (or engage the connections), it will always fetch the data from the datasource1 TWICE: once, triggered by the first connection/output, and the second time - triggered by the second connection/output (which as mentioned requires data from both datasources for calculations).
Any help would be greatly appreciated.
Michel

Michlus
2016-10-05, 12:46 AM
Anyone?

drew
2016-10-05, 04:44 AM
Anyone?

can you do what you need on the server in a stored procedure or two that call and return to each other so that you only have to manage one round trip instead of managing many in power query? idk if thats possible in your environment.
good luck

Michlus
2016-10-05, 02:45 PM
can you do what you need on the server in a stored procedure or two that call and return to each other so that you only have to manage one round trip instead of managing many in power query? idk if thats possible in your environment.
good luck

Thank you for your reply.
No, I'm afraid that this is impossible. This is a Production DB; I can't change anything on it, only select query.
Are you saying that it's impossible to have a PQ query use the output data from another PQ query, without triggering it to fetch (again) the data from the DB?