[Lens] In table visualization, users can transpose rows into columns

[wylieconlon]

Lens users currently define their aggregations in a column-oriented way. Users should be able to select any of their bucketed columns to be transposed for display purposes- this can be used to make the display more dense. This will be most clear with some examples.

Here is a relatively small table with 2 buckets and 2 metrics. Because there are 2 buckets, the user can choose the following transpose options:

• No transposing
• Transpose the first column (date)
• Transpose the second column (filters)
• Transpose both first and second columns

No transposing

The original data:

Date	Filters	Count	Sum
3/24/20	A	20	102
3/24/20	B	2	23
3/25/20	A	3	50
3/25/20	B	6	81
3/26/20	A	7	97
3/26/20	B	8	12

Transpose the first column

Filters	3/24/20 - Count	3/24/20 - Sum	3/25/20 - Count	3/25/20 - Sum	3/26/20 - Count	3/26/20 - Sum
A	20	102	3	50	7	97
B	2	23	6	81	8	12

Transpose the second column

Date	A - Count	A - Sum	B - Count	B - Sum
3/24/20	20	102	2	23
3/25/20	3	50	6	81
3/26/20	7	97	8	12

Transpose first and second columns

	3/24/20 - A	3/24/20 - B	3/25/20 - A	3/25/20 - B	3/26/20 - A	3/26/20 - B
Count	20	2	3	6	7	8
Sum	102	23	50	81	97	12

Implementation note

I propose that we implement this in visualization options for the table visualization, not as a feature of the Lens datasource or Lens editor. The main reason is that we don't need this for other visualizations, since the visual encoding would be the same.

One requirement that we need to keep is the behavior of applying a filter based on a table cell. The semantics of this should be the same, even though the table cells will be presented in a new order.

Also, as you can see in the example above, not every transposed table is as dense as others: it's possible that we can recommend the "densest" display of the table by applying some heuristics, such as "suggest that users transpose the column with the lowest expected cardinality"- this will usually not be the date histogram column, but might be the Terms aggregation.

[elasticmachine]

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

[flash1293]

If we implement this as two separate dimension groups of the table visualization, it's very hard to configure the aggregation nesting order because there's not unified list off all bucket aggregations to reorder (e.g. if there are two row dimensions and two column dimensions, they could be ordered column1,row1,column2,row2, but that's not visible in the UI).

There are a bunch of ways to address this, but after syncing with @timroes and @MichaelMarcialis I think the best approach is to enforce an aggregation order of always prioritizing the row dimensions over the column dimensions, without any way to mix them (e.g. it will always be row1,row2,column1,column2 - the user can reorder the rows and the columns individually, but not mix them). This is less flexible than allowing all combinations, but IMHO the reduced flexibility is worth the simplified user interaction.

To allow this, we probably need some changes in how visualization and datasource interact to ensure two groups of buckets in all cases.

[wylieconlon]

@flash1293 I agree that we should aim for a limited user experience to make it easier to understand. I like part of your proposal, but there is part of it that has confused me. I'm confused about the "row" vs "column" language and I think it's important that we clarify this. There are three words that we could use to describe the existing behavior of tables in Lens:

• Break down by: The current wording
• Columns: What we previously called it in Lens
• Rows: What @AlonaNadler has asked us to rename "break down by" to

So I have two questions about this:

1. Can you restate the proposal in terms of "top-level" aggs vs "inner aggs" instead of rows vs columns?
2. Can you clarify what you discussed about the wording, since we have ambiguous names?

It might help to see any sketches of the UI that you've talked about.

[flash1293]

Fair point. The UI would look like this:

"Break down rows" is a dimension group taking bucketed dimensions, same for "Break down columns". "Metrics" is only taking metric dimensions.

The "Break down rows" dimension group is functioning in the same way the "Break down by" dimension group is working now while "Break down columns" dimensions will be transposed and shown as additional columns for each metric. In your examples above, these would be the mapping of dimensions to dimension groups:

• "No transposing" - Date and Filters "Break down rows", Count and Sum "Metrics"
• "Transpose the first column" - Filters "Break down rows", Date "Break down columns", Count and Sum "Metrics"
• "Transpose the second column" - Date "Break down rows", Filters "Break down columns", Count and Sum "Metrics"
• "Transpose first and second columns" - Date and Filters "Break down columns", Count and Sum "Metrics"

The limitation is the nesting - all dimensions in "Break down rows" will be the top-level aggs, while "Break down columns" will be the inner aggs nested within the dimensions of "Break down rows". This nesting will be enforced in all cases, e.g. if a dimension is dragged from "Break down rows" to "Break down columns", the aggregation nesting is changed as well. Within each group, the nesting is following the visual order of the dimensions. The order of the table columns in the visualization is synced with this as well (like it's happening now).

This means that on configuring your examples above in this UI, The cases "Transpose the first column" and "Transpose the second column" would produce different ES queries because the nesting order would have to be different (the data would be the same in this specific case but could be different if "Top values" is used instead of Filters).

[wylieconlon]

@flash1293 That clarifies what you were thinking, thanks! That UI looks pretty similar to what I would expect as well.

My biggest priority is to optimize the defaults for 80% of the most common use cases, and I don't think that the defaults you've suggested are doing that. These are two of the most common queries we have, and they wouldn't display nicely with the defaults you've shown:

• A stacked XY chart broken down by Top 5 values, then auto date histogram (132 total rows)
• Top 5 values, then Top 3 values (21 total rows)

For both of these examples I would want to transpose the first aggregation, since it has fewer unique values than the second aggregation. But what if the user is in the 20% case, where the first aggregation shows 100 values, and the second aggregation has 7 values? I think we should support the most common case and the less common.

Assuming that we share an understanding of these queriees, I would want these features:

1. Default to transposed columns above rows- the opposite of what you suggested
2. If we can come up with a simple UI for it, I'd like to let users flip the order of rows vs columns. This will handle cases where high-cardinality aggs are above low-cardinality aggs.
3. Very clear wording that explains the difference between the first 2 groups. Seems like a good use case for in-product help.

Putting this together, here's an example of the kind of table UI that I am imagining. I think it would handle the default case better, and is also more flexible:

[flash1293]

After giving it a bit more thought I think you are right and nesting row agg within column aggs is a better default.

@MichaelMarcialis and me discussed a way to flip the nesting as well, I wonder whether we can split this part out and see how common that need is in real-world use cases. IMHO a fixed nesting already provides a lot of value and might be an easier to manage first iteration.

[nushrivastava]

Is there a way to see more than top 5 values in lens. I am trying to form a table with all the client ips and the number of requests from each ip, as of now it only gives me the ips that have most number of requests. Can I control this through any setting?

[flash1293]

@nushrivastava If you click the dimension in the config panel to the right, you can change the number of top values. This kind of question is better suited for the forum (https://discuss.elastic.co/), if you have follow-up questions please post them there.