Forum Discussion

andrewsimmans's avatar
andrewsimmans
Helper I
9 years ago

Native database queries are run multiple times when refreshing

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...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi andrewsimmans

 

How much transformation are you doing in the Query Editor?

 

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 

  • andrewsimmans's avatar
    andrewsimmans
    Helper I
    9 years ago

    Hi Phil

     

    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 dimension information for the date being reported, I then use DAX to filter out the relevant rows based on the date selected.

     

    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.

     

    Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi andrewsimmans,

       

      >>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.

       

      You can create a duplicate query from original query and uncheck the "include the refresh" option of the duplicates query after the refresh.

       

       

      Regards,

      Xiaoxin Sheng

      • andrewsimmans's avatar
        andrewsimmans
        Helper I
        9 years ago

        Thanks Xiaoxin

         

        A good thought - I gave that a try - unfortunatly what that does is stop the data refreshing - I thought if I switched off refresh 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.

         

        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

         

        So I guess what I want to do is impossible - I will need to think of another way of addresssing the problem.