[Lens] Improve table suggestions when dragging fields onto the table

[wylieconlon]

When a user drops a field into the Lens table, the results are unexpected. The main reason is that the table suggestions are built with the same logic as bar/line suggestions, and then limited. Tables need their own suggestion logic.

Examples of unexpected behavior:

• Bucketed aggregations like Terms are added at the top level instead of the bottom level.
• When a number field is added to an empty table, the default suggestion is Date Histogram + Number. Date histograms are less useful in tables.
• String fields show too little data in tables, as the default size is 3 across Lens. Tables can show denser information, especially for string fields.
• If a field like geo.src is already used in the table, the generated table will usually be reductive instead of additive. Users don't expect reductive suggestions here.

Solving these problems is likely dependent on cleaning up suggestion logic and solving some of the architectural issues with how datasources and visualizations work together.

[elasticmachine]

Pinging @elastic/kibana-app (Team:KibanaApp)

[wylieconlon]

• [x] Bucketed aggregations like Terms are added at the top level instead of the bottom level.
• [ ] When a number field is added to an empty table, the default suggestion is Date Histogram + Number. Date histograms are less useful in tables.
• [ ] String fields show too little data in tables, as the default size is 3 across Lens. Tables can show denser information, especially for string fields. https://github.com/elastic/kibana/issues/62386
• [ ] If a field like geo.src is already used in the table, the generated table will usually be reductive instead of additive. Users don't expect reductive suggestions here.

[flash1293]

Another improvement (which is more performance related): Dragging new string fields into the workspace will add them as top value dimensions. This will get relatively expensive for a lot of fields as nested terms aggregations have to be created.

In a lot of cases the user only wants to show a value from the document and it will always be the same one for all documents (like a partially aggregated doc table). These large nested term aggregation are quickly becoming expensive.

My suggestion:

• Default to last value for the second dragged in string field instead of top values
• Maybe introduce a new dimension group for string metrics because users might not get it otherwise

[wylieconlon]

@flash1293 that sounds good, and this is a very common pattern when constructing data tables. We can give these suggestions a relatively high score because the Table is the only visualization that allows buckets + strings as metrics.

I don't think we need a new dimension group for this, I'm not sure what benefit it gives us.

[flash1293]

The dimension group would nudge users to not drop string fields into the "rows" group - it's a little weird "last value" is a "metric" in this case. But we can also start without this.