Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

PowerBi should never load details table unfiltered

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. 

 

martin__smith_0-1598025078264.png

 

 

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

 

 

 

Status: New
Comments
v-yuta-msft
Community Support

@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
Frequent Visitor

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.