I have created a question with a pivot table visualization that is sourcing its data from another saved question. I was hoping that all data (from the database) will be fetched by the saved question in a single query and the new pivot table question will 'work on' / 'use' the fetched data (and not hit the database again & again to get the data). But I see that there are multiple queries going to the DB.
The pivot I am trying to build is a typlical one - produce a monthly product orders (order #s, sum total of sales) for last 3 months from the saved all_orders question (which pulls all the orders in the DB with some transformations with SQL, not just last 3 months).
I notice that my pivot table question is making 32 queries (of the saved question) to the database - looking at the metabase log. I was expecting to see just 1 query from the saved question towards the database. This is drastically slowing up my pivot question.
Any tips on how to improve this from the metabase end?
Thanks,
PS: I dont have control on changing anything on the database server end. I can just write a query and extract data, which I am doing in the all_orders saved question already
The Pivot Table visualization will create queries to generate the subtotals.
Should'nt the pivot visualization work on the data fetched from the "save question"? From what I notice, it runs the query in the saved question "n" times and hence the performance impact - like you explained, creates multiple queries to build the subtotals. My understanding (or assumption) of building a question on top of an existing question was that we work with that data and not a new query itself. It is a dead end if this is how it works.
Which "32 queries" are you referring to? It's not possible to see that in the log of Metabase - only on the query log on the database.
Sorry if that 32 queries is misleading. That magic number 32 is probably specific to my data & my visualization. The log I am referring to is the output from running .jar file which we log/redirect to a file.
java -jar /var/metabase/metabase.jar >> /var/log/metabase.log
I was referring to the number of times the base query was triggered by counting it from the above log.
@sanbee Post the log - that would make it much easier for me to understand.
Metabase is not a database, so the subtotal-queries are made against your database - based on the saved question's query.
While it's possible to use saved questions for a lot of things, it's difficult for Metabase to fully understand the column results and to create perfectly optimized queries based on a SQL query.
I would recommend that you try with a database View, so Metabase gets much more metadata and is better able to generate optimized subtotal-queries.