Native database queries are run multiple times when refreshing

Question

I am using Native Database Queries as some of the logic needed to extract data is complex and there is already well tested SQL to get the right data.

However, what I am finding is that if I build a table of data (Table1) using a Native Query and then I create a second table from the first using:

let
Source = #"Table1", ...

then when I refresh the data from Power BI through the Gateway that the SQL under the Native Query is run multiple times (I know as it takes a while to run and I can see multiple jobs in the SQL Activity Monitor.

I had assumed that if you built one table from another that the first table would be built and then the other built from it - is there some way I can force this behaviour as having a long running SQL query running multiple times does not make sense?

phil_seamark · Answer

Hi&nbsp;andrewsimmans&nbsp;How much transformation are you doing in the Query Editor?&nbsp;If it's not too heavy and you really need to restrict how much you call your source, you can always duplicate your table in DAX&nbsp;

andrewsimmans · Answer

Hi Phil&nbsp;I need to join tables to form what is in essence a many to many between two tables. The logic is a bit complex to explain here but this is all about handling point in time reporting and thus being able to analyse information using historical&nbsp;dimension&nbsp;information&nbsp;for the date&nbsp;being reported, I then use DAX to filter out the relevant rows based on the date selected.&nbsp;So I am really wanting to know if I can force the SQL to run just once on the query it is declared as the source for and not have it run again because I use that Power Query as a source for another Power Query.&nbsp;Thanks

anonymous · Answer

Hi andrewsimmans,
&nbsp;
&gt;&gt;So I am really wanting to know if I can force the SQL to run just once on the query it is declared as the source for and not have it run again because I use that Power Query as a source for another Power Query.
&nbsp;
You can create a duplicate query from original query and uncheck the "include the refresh" option of the duplicates query after the refresh.
&nbsp;

&nbsp;
Regards,
Xiaoxin Sheng

andrewsimmans · Answer

Thanks Xiaoxin&nbsp;A good thought - I gave that a try - unfortunatly what that does is stop the data refreshing - I thought if I switched off refresh&nbsp;on Table 2 then table 2 would still pick up the refresh done in Table 1, but what happened was that Table 1 had the new data in it and Table 2 still had the old.&nbsp;I then tried it the other way round switching off refresh on table 1 and switched it on for Table 2 got a surprise - table 2 had the new data in it - even though Table 1 still had the old data in it - proving that where one Table is based on another what Power Query is doing is taking the Script fromTable 1 and prefxing that onto Table 2 - rather than taking the data fro Table 1&nbsp;So I guess what I want to do is impossible - I will need to think of another way of addresssing the problem.

anonymous · Answer

Hi andrewsimmans,
&nbsp;
Did you&nbsp;test below steps?
&nbsp;
1. Get data from datasource. (source table)2. Create a duplicate query of source table and named it to backup xxx, then cancel the refresh option of current query.
3. Modify the original source data and refresh the source table.
4. Repeat steps 3,4.
&nbsp;
After above steps, you can see the history data(backup xxx), but I found it seems only works on powr query side. If you apply the change and return to the report view, these table will show the newest data as same as source table.
&nbsp;
Regards,
Xiaoxin Sheng

Forum Discussion

Native database queries are run multiple times when refreshing

6 Replies

Related content

Multiple databases

Fabric SQL Database SQL endpoint is not refreshing

Database connectivity.

Database refresh date and power bi refresh date

Mirrored Snowflake Database Storage