Preview upcoming changes to the GUI query builder (updated)

Hey everyone, we’re excited to share a preview of next version of Metabase which has a more powerful graphical query builder that allows you to create joins, do post-aggregation filtering, or multiple stages of aggregating, and more. More details here.

If you’ve had a chance to play with the preview, let us know what you think here! We’d love your feedback. Thanks!

7 Likes

New query builder interface is great.
Multiple levels of filter just amazing.
Joining multiple sets of results - I nearly wet myself.

Few comments:

  1. I miss the old ‘Ask a question’ button. Hiding the Custom Question button in a drop down seems odd. If there’s a preference for keeping the layout simple, I’d hide the ‘Write SQL’ in the drop down and rename ‘New Custom Question’ to ‘Ask a question’, but on the bar rather than hidden. All that’s visible at the moment is the simplistic browse and the complex write SQL.
  2. In the new query interface, if you add a table, create a custom expression, then add a 2nd table, you lose the custom expression. Given the iterative nature of designing dashboards etc, I don’t want to have to rewrite a bunch of expressions just because I forgot a table.
  3. If I add table A. Then join to table B. That works fine. If I now add table C, I can’t join B to C only A to C.
  4. When viewing data in the initial table format, clicking the little chart button at the bottom brings up the list of visualisation options. I expected to be able to click the little table button to return to just the table. Doesn’t do anything.
  5. If I drill down through a chart, then click back, I’m no longer taken back to the chart. I go back to the visualisation before the chart.
  6. The icon for alerts (bottom right) is the same as the icon for activity (top right).

Keep up the good work.

4 Likes

Sorry, 2nd comment for today. I have to do some work, so probably the last until tomorrow.

  1. Any chance of having UNION as an option when joining data sets?
  2. For as long as I’ve been using Metabase, it’s always been possible to use the browser back button to go back a step in the visualisation. This is no longer consistent. Need to be either added back or a back/undo button added to the UI.
  3. You can join a result set to another table in the same database or to a saved question (which can be in another database). Would be nice to join to a table in another database all together (possibly by creating a saved question on the fly?)
1 Like

Just as AndrewMBaines is mentioning, I as well, really like the new changes.
I came across a few things worth bringing up:

Joins:
I LOVE that you have added this feature and that you can specify what type of join it is, though there were a few things that disappointed me.

you can only join to the main table and not between different tables is a bit sad, and to be honest not that useful in the long run.
Sure you can then add a join on the previous result, but that just created yet another subquery which isn’t ideally for perfomance.
A great addition to this would also be that you have the ability to select which columns you want in the query before visualizing the results.
You could easily end up with a lot of un-needed columns in the initial query, which could end up in a performance issue, just to remove them again.

Write SQL:
When clicking on the tutorial (nice small addition) the page becomes unresponsive and you need to close the page or go back through the browser to continue working.

Over all I must also say that I REALLY LOVE this approach you guys have taken and really do see potentials for it, unfortunately, just a few things that could be improved.

Overall:
Having the ability to specify join types is good
The ability to join A -> B -> C (when following FKs) is missing but would be great. It can be done using “previous results” but that isn’t as intuitive.
Join sections need clear delineation. Make it clear that everything for a certain result set goes together (either using indentation or some kind of group outlining)
Button placement needs work. They appear to be glitchy and showing up in places where they should not. I think there should be a set of buttons for each join section that are toggleable with a background color indicating they are being used. They should also only show up in a single consistent location unless there is a good reason not to.

  1. In new sql editor once you hit the chart icon you can’t go back to the table icon (when the button is clicked nothing happens)
    1b. Buttons inconsistent. If I switch to using a different graph type then it done it closes the graph panel. If I then click on the graph icon it no longer opens the panel
  2. There doesn’t appear to be a way to join multiple tables in a chain. I can’t join A -> B -> C (without being creative and using previous results but this isn’t immediately intutive).
  3. Clicking around sometimes causes duplicate subsections. For example I have the exact same “summarize” section twice with the same data. I’m not sure how it happened.
  4. Being able to see the query and the results at the same time was very useful. It doesn’t seem that can be done now. I’m not sure how you would get the best of both worlds.
  5. I joined Orders -> Product then results to Reviews. UI loaded slow and was rather unresponsive. It won’t scroll very well and it takes time before it response to a click.
  6. It didn’t immediately click with me how to get back to the editor. Perhaps this is because it is alone at the top right while “Summarize” and “Filter” are at the top left.
  7. When joining previous results there should be some kind of visualization of grouping in the UI. Here are two ideas:
    7a. Indent the column, filter, and summarize sections for each “Join data” section. This will make it apparent they belong together and create a clear differentiation between groups
    7b Surround each join grouping (with column, filter, and summarize) by a thin blue line to indicate they go together
    8 Buttons are all over the place. Somethings it appears they are being put in spots that they should not be. I think the buttons should only be in one spot (either at the bottom of each section or at the top). There are small versions of icons showing up under different sections inconsistently. To replicate setup a join data then remove all of the sections then just start pushing buttons.
    8a. For example I seem to be in a state where there are filter and summarize buttons under the join data section and under the add column section.
    8b. I also have a row limit button under the add column section but it doesn’t make sense for it to be here as there already is another one at the bottom. If a click it I can get into a situation with multiple “row limit” sections
  8. Why do some buttons have background colors while others do not? When I see this situation it makes me think the background color means it is already selected while the lack of a color means it is not. This does not appear to be the case.
    9a. I think all 6 buttons should always show for each section. They should have a background color if they are enabled or not if they are not. The ‘x’ button could be removed in favor of selecting / unselecting icons.
  9. The “join data” button is special and should be in a different location. It doesn’t modify the existing join grouping… instead it adds a new one.

Thanks for trying things out and for giving us all the great feedback, guys! We’ve been going over it all, and I’ll have some questions and replies to some specific items in a minute.

@benjamin.kassentoft re: this:

When clicking on the tutorial (nice small addition) the page becomes unresponsive and you need to close the page or go back through the browser to continue working.

Are you talking about the book icon that opens the data reference side panel, which shows you the tables and columns in the selected DB? If so, I’m not able to reproduce this so far. Are you working with an especially large DB? Does this happen if you’ve selected the Sample Dataset?

Edit: @flamber set me straight. :slight_smile: That tutorial was for the original query builder. We’ll need to remove that link.

@maz I’m pretty sure it’s the /question#?tutorial link “How do I use this thing?”, if you click top + button > New custom question > (select table) > Click “Hide editor”

1 Like

The fact that you can’t click on the toggle again seems to be a bug. Thanks for spotting it.

But more importantly, it seems you both have run into something that’s causing confusion. That toggle is meant to switch you easily back and forth between showing you the raw results of your query vs. the visualization of it. The intended primary way to visualize results or change your visualization is to click the Change visualization link/button in the bottom-left. The fact that the toggle opens up the visualization picker if you haven’t visualized at all yet was meant to be a shortcut, but it seems to be confusing.

To put words into your mouths:

  • The change visualization button should look more like a button.
  • Maybe the data/viz toggle shouldn’t appear until you’ve chosen an initial visualization.
  • Putting the data/viz toggle so close to the viz button and chart options gear seems to have created confusion. Maybe it goes in the center of the footer? Maybe it needs a tooltip?

It’s awesome to finally see this starting to take shape. Incredible work! :champagne: :cake:
Here’s some things I’ve noticed. I’ve tried to skip points that were already made by others great feedback or are on the PR checklist.

  1. Pivot doesn’t seem to function. It only shows first column header, the are empty. And all row cells are empty.
  2. How prominent should the top Activity bell button be? Must admit that I’ve never used that page. Maybe moved under :gear:? That would make the :heavy_plus_sign: :gear: more consistent too, since they’re both a popover.
  3. In the notebook (Show editor), even after some time, I still have to hover the icon-buttons, since I’m not sure what they do. There seems to be enough space to write the action too, and skip the hover.
  4. Can the Preview-button just stay in place. So you can toggle preview without moving mouse?
  5. The “Done” button in “Change visualization” should have a different look, since it looks like the pill-tabs, and is different from the “Visualize” button in notebook.
  6. I love the data/viz toggle, but maybe it should just be called “Show raw data” and act like a button toggle, and only shown when visualization is non-raw-table.
  7. When doing a Combo (Line+Bar) multi-series, clicking the little X to the right of a legend, should it remove that? It doesn’t work then. (maybe already on the PR checklist?).
  8. In notebook, “Row limit” button uses same icon as “Show/hide editor”.
  9. Can the animations be speeded up a little? It feels little slow to jump around in the visualization options or show/hide editor.
  10. There’s something wrong with the hover tooltips (Firefox). On some graphs with little data, it shows tooltip, but a little misaligned, but when I do “Orders: Count by Created At Previous 30 Days and Category”, then it doesn’t show any tooltip.
1 Like

Really like the direction here, post aggregation filters and chained summaries are going to make my life a whole lot easier, it removes a whole class of native queries that I used to have to wrote, great job.

Couple of interface things I’ve found

  • Took me longer than I’d like to admit to find the “New custom query” button. I’d prefer for that to be more in your face than the “Write SQL” button.
  • For “Custom Queries”, while I really like the new more powerful builder, I know it will be too much for many of my users. Maybe a button on the top menu to start a new “simple query” without going through the table browser? Table browser is tricky if you’ve got a lot of tables as there is no search or filter on it.
  • Sometimes in the “Visualization Options” dialog, it will close the dialog after making a single click. I.e. to set up a conditional format on a table it’s one click to add a rule then the dialog closes, reopen the dialog, edit that rule, click the “Which columns should be affected” drop down and select column, dialog closes, reopen dialog, edit rule, select from the “When a cell in this column …” list, dialog closes … etc. So I can get it all done but takes ages. It’s not just table conditional formats, I’ve struck this on other visualisation options too. It’s intermittent, sorry I can’t be more specific.
  • Drill throughs (“View these XX”) on visualisations based on notebook queries with joins isn’t workign consistently for me. Getting errors like Error: Table 314 is not present in the Query Processor Store. when I drill down on a bar chart for instance. If the “Summarize by …” is on the base table then the drill through works, if it’s on the joined table then is doesn’t seem to.

Nice work.

Thanks for the notes, Flamber!

  1. Repro’d and added to to-dos. Seems to only happen to me when pivoting and then toggling the data-viz on/off.
  2. We’ll consider that. We think the nav bar still needs some smoothing out.
  3. Gotcha. The hope was to play down the action buttons on previous steps to help elevate the actions on the bottom step, but that’s useful feedback.
  4. Yeah, I think that makes a lot of sense. :+1:
  5. One of our to-dos is to reconcile the different button styles we’ve introduced. :ok_hand:
  6. I’m pretty sure I agree it should only show once you’ve visualized your results, but I do think the toggle UI pattern seems right for this.
  7. I’m seeing this when my result has a series breakout (like count of orders by time and category) but not when I have multiple metrics. Added to the to-do list.
  8. Yeah, the editor one will be changed to something different.
  9. We’ll take a look at that.
  10. Yeah, seems like tooltips aren’t showing up for any browser for line/area/bar charts that have a series breakout. Added to the to-do list.

Just a note on #10
It might already have been fixed by Paul last week, but not sure if those changes have been merged in notebook-mode.
EDIT: Scratch that, seems like it’s also broken on master.

Thanks for the feedback, @notrom!

  1. Good to know. Do you currently primarily use the GUI query builder more than SQL? Or is it that the new query builder now will cause you to use it more than SQL?
  2. Maybe the thing to solve here is to add search/filtering to the table browser. Otherwise, I’m not sure what a “simple query” button would do, as we need to know what data they’d like to query. Can you explain what you were envisioning?
  3. I’m not seeing this behavior. What browser are you using?
  4. Yep, known issue with the way we’ve implemented multi-step questions and joins via nested queries. It’s on the to-be-fixed list.
  1. We use the existing GUI builder wherever possible, and only use SQL as a last resort. The new query builder will mean less raw SQL questions for us which is great as I have to write those myself. My users aren’t SQL savvy.
  2. Search on the table browser would be useful for me, even if it was a component/tab on the global search results. I was trying to say the “simple query” button could get you to the same place as clicking on a table in table browser, a bit more familiar to the old “Ask a question” button.
  3. Happening in both Chrome and Firefox, but I haven’t nailed down the exact conditions and can’t even re-produce myself now, but I did see it yesterday. Sorry that’s not useful.
  4. Great, glad it’s on the list :slight_smile:
1 Like

@maz
I am refering to this part here below, where you click on the “How do I use this thing”:


Using MySQL 5.7 not that big DB

It happens regardless of which DB I am using, is it the sample data or not. and when you click “Tell me more”, nothing happens

Hi, Maz,

I am very happy that Metabase is developing the question interface and it has to be more complex because people are asking complex questions.

I am very interested in pivoting tables.

  1. I can confirm @flamber problems with pivoting. I tried to pivot sales by month and year and I get the years not only in the headings but also in the data fields (middle of the table.)
  2. Do you plan to add nested pivoting - for example month and account manager on left headings, years on top headings and sales in the data cells, with appropriate subtotal and grand total?

BR,
Itso

@crunchtoinfo Hi, Isto! Long time no talk.

  1. Thanks for reproducing this. It’s on our list of bugs to fix. :+1:
  2. Not in this upcoming release, but soon after. It is currently on our roadmap. I still have that doc with your great feedback on the previous pivot table designs, which we’ll be referring to once we’re able to spin that project back up again.

Thanks,
Maz

1 Like

Super on 2. Looking forward to it and ready to be involved if needed.

1 Like
  1. :fu: for the ability to change date format on a per column on the displayed table

  2. “add a column” option - help link broken https://metabase.com/docs/v0.33.0-preview/users-guide/04-asking-questions.html#creating-a-custom-field

  3. Row limit - enhance to allow “fetch next ‘n’ rows”

  4. Why does clicking on “summarize” default to Count of Rows? If you have a large table this behaviour might be problematic

  5. The “change visualization” option seems hidden - Prefer the simplicity of the current version regarding this functionality - displayed at the top of table not in the footer.

  6. Confess to struggling with the whole utility of the what appears to be the single item “Summarize by” functionality. My initial instinct was to try to group by multiple columns but that particular UI doesnt seem to support that option

  7. When closing the “editor” it might be worth automatically triggering “Get Result”, its not obvious to user they need to refresh…

  8. As with current system seems to default to pivot mode when dealing with a group of 3 columns. Again, with large data this can be annoying (in current system as well)

  9. yeah - the more I play the more I would get rid of the whole "Pick what you want to view/Summarise by sidebar =“Edit summary”. Keep it simple and just use the “Editor”. Though displaying the filter criteria is helpful.

  10. Not sure if you still support using an existing “question” as a datasource for a new query let alone supporting joining to that entity

Thats what I have for now - hope it doesnt sound overly critical/picky :slight_smile: