How to optimize queries and filters for dashboards?

[r.bianco]

Hello,

I need to create a dashboard for a really large data set (production and storage), which will grow up by like 150k records every month.

The table has various informations, but I only need some of thems, grouped and filtered, the relevant columns are article, category, year, month, quantity.

I say I want a chart for monthly (year/month) production filtered by category (category, year, month, sum(quantity)), but also I would like to see the detail, single article, of the whole production in a table, which will be filtered as well by category.

If I try every single query alone the dashboard is reactive.
If I try with both the 2 separate queries for chart and table, the dashboard hang up and sometimes it timeouts (I agree that it's not the right way, but it was a try).
If I put article, category, year, month, sum(quantity) in a query and use it for both the chart and the table it works, but slow down like 6-8 times as it need to group the data in memory.

It would make sense if the filter starts with all unselected so it starts quickly and allows me to select just what I want to see, I rarely want to see all.

Does somebody have a suggestion?

[Lech Kulikowski]

Hello,

In this case, you can add several queries (data sources) with necessary grouping, filtering for each dashboard elements.

Thank you.