PowerBi should never load details table unfiltered

[martin__smith]

I have a Details view accessed via direct query.

This returns over a billion rows and has some long string columns. The query behind it has an APPLY that does a nested loops join for each row returned which is very expensive on the full data set.

I never want PowerBi to do a SELECT * from that view without any WHERE clause as it kills the database server - and nor should it need to as there is an aggregated table set up as described here. The visuals referencing this view are all able to be satisified from the (imported) aggregate table - the details are just needed to be there in order to show additional data on clicking the "show data point as a table" option. The queries generated by this include filtering and a TOP clause that reduces the rows requested from the details view.

The "Include in report refresh" option is disabled for this data source and I thought this option had solved the issue. However I am finding that when I change the parameter values used by the connection to another environment and "close and apply" it sends the "select * from detailsview" request to the new environment. 

Is there any way to stop this happening? I can refuse the query permission to run but that blocks the entire report from loading. 

[v-yuta-msft]

@martin__smith ,

Have you tried create a view table in sql server and then load the view table into power bi desktop?

Regards,

Jimmy Tao

[martin__smith]

Hi Jimmy - Both the details and aggregate tables are in fact views. 

The aggregate table is pointing to a SQL Server view in import mode and the details table is pointing to a SQL Server view in direct query mode. 

What I am trying to avoid is PowerBi sending a SELECT * FROM detalsview when applying changes to the query. (Such as changing the parameters used by the connection string).

This brings back over a billion rows from the details view - I have no idea what it does with them as the visuals only need the select * from aggregateview. Presumably it just loops through them and discards them. If it is doing validation I would like an option to defer validation until runtime or limit the number of rows looked at for validation.

At runtime the details view is only needed for drilling down into details behind a row in the aggregated view - so this applies filters to the query rather than bringing back the whole thing.