TLDR:

Is it better to use multiple dynamic data sources and queries with different parameters for each Klip or even element of a Klip, or is it better to create a single dynamic request, identical for each Klip and expect that the data is only being refreshed once?

Full Story

I have inherited a legacy dashboard with about 20 widgets and 25-ish dynamic data sources. It's incredibly slow to load and each data source essentially extracts data from raw jsonb records that I'd consider raw data or staging tables in a database.

I've have implemented a data ingestion process and re-engineered this 'unstructured raw data fest' to a dimensional model. I want to expose 2 views to klipfolio that cover all of the data I'll ever need. For example, let's say I have a fast loading view with the columns: 

• ProductName
• Date
• Orders
• Units
• Returns
• Revenue 

The current legacy dashboard has dynamic data sources for pretty much every element of each Klip. For example:

• For a dropdown allowing to filter on ProductName, there is a data source providing a distinct list of product names sold within a preselected date range. 
• For a bar/line chart showing the daily sales over a date range and for all or the selected product, it uses another data source showing the sum of sales by date for either all products or a specific product name. 
• For another bar/line chart showing the daily revenue, there is yet another data source. 
• For a table showing the top-selling products within the date range, it has a fourth data source getting the sum of sales per product.
• ... until you reach 25-ish data sources. 

In some cases two klips will use the exact same data source but set slightly different parameters eg. bulding LIKE filters subtly differently, which I expect to be unintended.

I'm assuming that if I standardize this to as few data sources as possible and as few different dynamic calls this should increase performance and reduce queries to the database? I would then filter within Klipfolio widgets eg for the example above: 

• There would be one dynamic data source giving me every row within the preselected date range and colums for all used metrics. 
• I would use GROUP on the ProductName column. 
• I would aggregate the sales and revenue by date in a table, then use this as the source for the line charts.  
• I would aggregate the sales per product for the table for the last element. 

I'm assuming this would mean Klipfolio would only need to connect to the database once and be much faster in loading the data. 
I'm also expecting this to drastically simplify maintenance in case I want to update a data source in future. The only reason against this approach is that I would potentially need to ensure I'm not exceeding the 10MByte limit but I don't see this as an issue at present. I would then still try to minimize the total number of data sources as much as I can with maybe one for the aggregation by product over a date range and one for the aggregation by date over all of the products.