Open PBIX file executes the query multiple times

[Anonymous]

We have a connection via ODBC to a Redshift environment and run queries against them.

We have an issue whereby the desktop is kicking off the sql query multiple times and causing CPU and disk space issues.

There is often only one query set up in the file so I can not fathom why it would want to kick it off more than once - mostly it kicks off 3 versions.

This is happening on opening the file so I have disabled the Background Data option. Only now it does it when I click edit queries. it seems that as well as kicking off multiple times, it is also running the entire query during what I believe is a 'evaluation' stage. I thought clicking edit query simply tested the connection existed and maybe the code syntax but it seems it runs the code entirely?

Any help is much appreciated.

[bthodla]

I have the same issue when connecting to Presto using an ODBC driver. A simple app with a SELECT from a single table causes the query to be executed four times. If the query results in a slightly larger dataset, it invariably times out.

[v-caliao-msft]

@Anonymous,

I have tested it on my local environment, click Edit Queries button won't run the query. Only when you click refersh, or made some modification on your dataset and save change the, Power BI will execute this query. I have monitor the data source by using SQL Server profiler. 

Start monitor

Click Edit Queries

Add column

Close & Apply

Regards,

Charlie Liao

[Anonymous]

Hi and thanks for the response.

I think our set up must be different. We use open VPN to connect to the 3rd party where are data is held. 

I have had our 3rd party monitor what happens at each stage and originally, when I opened a file it started multiple jobs there side. Once I had disabled the preview option, it started nothing on opening, but did the multiple jobs when I click edit queries. 

I have monitor grabs from them but am struggling to work out how to link them in here. 

I tested with other developers in our company and the same happened. When they opened a Power BI file with a connecction to Redshift, it kicked off a process for 28 minutes. In that scenario, their job only ran once. 

When I click on edit queries, I get a message in the bottom saying 'WAITING FOR DSN='<OUR REDSHIFT>'. (CLICK HERE TO CANCEL) - if I cancel, we can see that the job(s) continue to run on the redshift side.

Thanks for any suggestions.

[Sharpiebizkit]

KirstenJude is right. I have a similar setup where I use an ODBC connection to hit an AWS cloud source. We hit a virtualization layer that connects to Redshift, and Hive via Presto. The behavior described happens to me as well. Power BI is kicking off multiple instances of the query when clicking "Edit Query". It causes frequent timeout errors.