SQL query with user defined parameters (slicer, filter, input value) - Big Data

[Astorre]

Hi guys,

I have an SQL table with ~2TB of time series sensor data, and couple billions of rows, and I want to visualize parts of the table in Power BI.

The table itself is fast - considering the size - because there are a lot of indexes on it. But still if I make a report of it Power BI wants to load the whole table - which is impossible. However I never need the entire table, only really small portions of it.

I want to be able to let the user choose which type of data he/she needs, and only query those!! In my mind it is a simple task, because I put a couple of filters on the page, the user chooses which types of data in which time frame he needs, and than Power BI composes and SQL command (with a couple of WHERE conditions) and only gets those records! BUT for some reason (after couple of days of searching) I cannot find a way to do it...

I undestand that I can break my table up to smaller pieces, and make reports to each portion of the table, but than I lose the dynamics of it... I deprive from my user the opportunity of customization! And than Power BI is no better than Excel (actually it would be worse). I hope this isn't the case and I'm just missing something super obvious 🙂

[V-lianl-msft]

Hi @Astorre ,

Such a large amount of data,i would suggest you to use Premium capacity.

https://powerbi.microsoft.com/en-us/blog/aggregations-for-petabyte-scale-bi-available-in-the-power-b... 

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

[Tad17]

Hi @Astorre 

I'm not sure if I understand your need, but I don't think dynamic queries are an option. It kind of defeats the point of Power BI. My thought is you should import the entire SQL table using import method, set up a scheduled refresh when no will be using the report, and then set up a slicer or multiple reports/dashboards to suit the various end user needs. This is what Power BI is intended for.