Forum Discussion
Direct Query - can my filter be integrated in SQL select? 340 mil rows, table visual, Azure SQL DWH
Dear reader,
I am fairly new to Power BI so I might miss some crucial information. Hopefully some of you can nudge me in the right direction.
Current Situation
In my company some people need to find combined data from 3 tables on row level (not aggregated), resulting in a tabular view with the possibility for export. The original combined dataset will consist of 340 million rows. Filters will allow the user to finetune their search, often resulting in smaller results ranging from 10-50.000 rows. This smaller filtered table is what they are interested in. Currently, we make a monthly export of this dataset and import it directly in PowerBI. Disadvantages of this:
- We have to manually perform some steps where we would like to avoid doing the export/import steps
- Data is not real-time, since we do this monthly
Goal
We are investigating the use of Direct Query to build the combined table of 340 million rows, show them in a table visual, allow the usage of filters (slicers) to finetune the table that will eventually be exported to Excel. Basically we want the user to directly query on our DWH with SQL by using the PowerBI filter system.
Problems we haven't solved yet
- Table visual has the limitation of 1 million rows, resulting in an error. Q: is it possible to just show the first 1000 rows, instead of trying to load 340 million rows in a visual? I don't understand why this must result in an error. Or is there another way to solve this? I can't reduce the number of rows to < 1 million, since people need to be able to filter on the whole dataset.
- Performance: when I use filters to finetune the table visual, I feel that DirectQuery does the following:
1. combine 3 tables to create 340 million rows
2. filter on this 340 million rows
> change the filter? redo step 1 and 2.
This is very slow. It takes up to 30 seconds to give a resulting table (some small as only 50-100 rows). I basically want to do this:
1. Change SQL select query including my filters, so the SQL directly filters in the DWH, returning a result much quicker
I was wondering if this is possible. Thank you kindly for your time, I am looking forward to your replies!
1 Reply
You can use Dynamic M Query Parameters to pass user-specified filters to a SQL query. See if you can adapt the solution below:
The one million row limitation applies to any intermediate query, not just the final query that is displayed in the visual. You may want to create a view in Azure SQL instead of joining the three tables in custom SQL within Power Query (potentially better performance). To submit all user-specified filters at once (instead of individually), you can use a single Apply button:
