Native queries to AWS Athena run multiple times

[Alzhaid]

A similar problem appears to be mentioned in other issues (Queries run twice, All queries run twice in the database when opening a dashboard, Native database queries are run multiple times when refreshing) but it seems no explanation or solution has been provided so far.

We open a Report in PowerBI Desktop in which we have two native database queries (a "select * ..." and later a complex select). These run against an ODBC datasource that uses an Amazon Athena driver.

When we press Refresh, we can see at Amazon Athena that both queries are run several times. We can also see at Microsoft.Mashup.Container.NetFX40.... logs that those queries were called several times, through Engine/IO/Odbc/Command/ExecuteDirect

Consequences of this seem to be slower refresh times caused by greater transfer of data between Athena and PowerBI Desktop, plus increased costs on Athena side as data gets scanned several times.

Any help with this would be greatly appreciated, thanks!

[v-qiuyu-msft]

Hi @Alzhaid,

I have sent a email to consult this issue internally. Will update here once I get any feedback. 

Best Regards,
Qiuyun Yu 

[Alzhaid]

Thank you very much, I'll be monitoring this thread!

[v-qiuyu-msft]

Hi @Alzhaid,

I got information internally that this issue will be fixed in the near future release.

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @Alzhaid,

I would like to correct my orignal post after double confirm from PG team. 

The issue regarding the query is running multiple times. It can be related to below option: 

If you turn it off and start seeing the query running twice, these two queries are expected: one is for schema, another one is for data loading. This is currently by design.

Best Regards,
Qiuyun Yu 

[Alzhaid]

Thanks for the answer. I can confirm that after turning off "Background Data" queries run twice. About the PowerBI Desktop design you mention

- in order to get the schema, all the data in the table is sent through the network although it's not needed?

- is this design independent of the ODBC driver used?

[bcarver]

I'm seeing this same behavior in the current versions of Power BI (July 2018) and Excel 2016 (at recent update levels).  I define an incoming query against a large table (1M rows) for my details, then define a new source as a reference to those base details, to extract and summarize against a common source.  I can't seem to get a refresh to happen that runs the original query but doesn't rerun it when I refresh the aggregation query. 

Refreshing the aggregation results in two large queries against the database (if not three in some cases).    

Worse yet, if I cancel them in the database (out from under Power BI desktop) it just keeps submitting more - I have to close the desktop app to get it to stop.  This most often happens in my original attempts to develop queries.