cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kevhav Member
Member

Why does a reference query pull from the DB?

I am using the Query Editor in Power BI Desktop, with a file in "Import" mode.

 

I have a query "Foo", the first step of which is to pull data from a SQL Server database (= Sql.Database("SERVER", "Database", "[Query = "SELECT..."). The SQL query is very slow -- it takes several minutes to complete. 

 

I also have query "Foo.1", the first step of which is to reference the "Foo" query  (= #"Foo"). And, I believe the following also applies if I have a query "Foo.2" which appends the "Foo" query with something else.

 

When in the Query Editor I make modifications to the "Foo.1" query (only), then click "Close & Load", then the "Refresh" screen pops up and, for "Foo.1" only, it says "Evaluating..." for a while, then is says "Waiting for SERVER/Database..." for a while, then it loads all the rows into the data model in the PBIX file.

 

That is, it seems to be running my very slow SQL query against the source database. But why?! I think only the "Foo" query should run the very slow query against the source database. When modifying/refreshing downstream queries like "Foo.1" and "Foo.2" (which use reference/append/etc. to get data from "Foo"), shouldn't Power BI Desktop simply refer to the "Foo" data that is already there, already loaded into the PBIX file? Why run the very slow query against the database again?

 

It slows down my development effort, having to wait every time I change a downstream query. And running the query eats up a lot of resources on my server. (Oh, and it doesn't even actually refresh the data with fresh data from the database! Because I never refreshed the original "Foo" query!)

 

Is there some way I can prevent this, now? Or, could Power BI be "fixed" to prevent this? (Or, is this necessary for some reason that I don't see?) Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
BhaveshPatel Super Contributor
Super Contributor

Re: Why does a reference query pull from the DB?

 

Please Check out this Article from Chris Webb's BI Blog. You can understand the logic behind this.

 

https://blog.crossjoin.co.uk/2016/11/20/referenced-queries-and-caching-in-power-bi-and-power-query/

 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

1 REPLY 1
BhaveshPatel Super Contributor
Super Contributor

Re: Why does a reference query pull from the DB?

 

Please Check out this Article from Chris Webb's BI Blog. You can understand the logic behind this.

 

https://blog.crossjoin.co.uk/2016/11/20/referenced-queries-and-caching-in-power-bi-and-power-query/

 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors