How to improve performance of multiple chart load in dashboard for one billion records

[nagarajmmu]

Hi All

We are working on big data in our application, we are using superset as our visualization tool.

Superset is working well with our big data however in some cases like billion records and over, charts are loading very slow. For every click it aggregates billion records each time for each chart. To explain this better, say if dashboard have 4 charts, which are created by single billion record dataset, total time taken to load all four chart is around 2 to 3 minutes and similar time for each subsequent clicks.
Attached dashboard screenshot below

Also when we click on any value in Pie chart, other chart will take again same amount of time again (2 - 3 minutes) (results are Cached after the first click)

Query details:
Example: Left top most line chart use the below query to load the data at the beginning and take one minute to load chart.
SELECT DATE_TRUNC('month', "COL_DATE") AS "COL_DATE",
AVG(Abs(AMOUNT)) AS "AVG(Abs(AMOUNT))"
FROM
(SELECT *
FROM Sample_1Bil) AS virtual_table
GROUP BY DATE_TRUNC('month', "COL_DATE")
ORDER BY "AVG(Abs(AMOUNT))" DESC
LIMIT 10000;

But after click on any value on Pie chart, same line chart will use the blow query with filter and again take one minute to load.
SELECT DATE_TRUNC('month', "COL_DATE") AS "COL_DATE",
AVG(Abs(AMOUNT)) AS "AVG(Abs(AMOUNT))"
FROM
(SELECT *
FROM Sample_1Bil) AS virtual_table
WHERE "ACCOUNT" IN ('123456')
GROUP BY DATE_TRUNC('month', "COL_DATE")
ORDER BY "AVG(Abs(AMOUNT))" DESC
LIMIT 10000;

Superset is installed on AKS with 12 CPU and 50 GB RAM

We have few questions:

1. Is there any best practices to handle billion records to improve the performance to load and first time clicks.
2. In the traditional BI tools like power bi we could overcome this via Star Schema approach so we query only the portion of data that is needed.
3. Is there a concept of bookmarks or pre-defined caching that could be applied on load.

Thanks
Nagaraj M M

[nagarajmmu]

Hi @sfirke

As we discussed in slack, do you have any input for above issue, please let us know, if you have any solution, it would help us.

Thanks
Nagaraj M M

[michaelritsema]

Looking at your supplied query it seems like Superset doesn't have any idea how many records there are and the problem is the slow query at the database. I'd consider a rollup table