Incremental Refresh - TOP 1000

[FormigoJoao]

Hello,

I am trying to set up Incremental Refresh and I noticed that it is doing a lot of TOP 1000, basically for each partition. A TOP1000 isn't that much but it adds up and since we are dealing with a big database and limited resources it does add up.
I've seen Patrick's video from guyinacube where he says that changing the privacy levels to anything other than Private works and doesn't send those TOP1000 but I don't think it really works. I've tried that and with all the privacies and those top 1000 will still show up at refresh time.

Is it possible to not have these TOP1000? Thank you 

[ShaneRyder]

I faced the same issue and despite updating Privacy level to 'Organisational' the top 1000 queries persisted. Having raised this to Microsoft they were unable to replicate the problem. Following investigation we had noted our On-Premise Gateway version was very old (and also no longer supported by MS) and was proposed as possibly being the issue. We have now updated our Gateway software to latest version (3000.146.10) and happy to report we are no longer seeing these top 1000 queries persist. Hope this helps

[yaniv-amr]

@FormigoJoao 

I've done some testing, and looks like the dataset must be published to PPU/Premium workspace.

When published to Pro workspace or to "My Workspace" the TOP 1000 queries would show up, even with Organizational privacy level. 

No idea why it's like that, but this is really problematic for large DBs (Azure SQL DB). I hope someone from PBI team could help here.

[v-yadongf-msft]

Hi @FormigoJoao ,

Can you describe the problem you’re having in more detail?

Are you looking to reduce the amount of data on incremental refresh, or are you experiencing incremental refresh timeouts / slowness?

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FormigoJoao]

Hello

The problem is more on the DB side, we have a SQL DB with a lot of data and we are trying to limit the resources as much as possible but powerbi seems to be the biggest issue with so many queries running and we are trying to figure out a way to supress these queries as much as possible so that we don't push the database's DTUs to the limit since we do have other things that need to be ran on the backend and they can be affected and even fail if the DB is at maximum

[lbendlin · Accepted solution]

In an enterprise environment the reporting database is decoupled from the production database. You use tools like Informatica to periodically update the reporting database from the production database.  That way the Power BI chattiness (which you cannot modify) will not impact your production environment.

[lbendlin]

Probably not.  I assume you have a Pro license. Consider raising a Pro ticket at https://powerbi.microsoft.com/en-us/support/pro/  and then work with the SI to get ahold of the product team that manages the partition handling. In the end it all comes down to meta data.

Chris Webb's BI Blog: Why Is Power BI Running My SQL Query Twice? Chris Webb's BI Blog (crossjoin.co...