Allow for nested grouping in group_label meta property

[ZeRego]

-- Proposal updated on 2 May --

This approach covers:

• backwards compatible - we still support "group_label" but will mark it as "deprecated" in the docs and ignore it if "group" is available
• reusability - we have a flat list of group definitions which can be used in any order
• future proof: easy to add more group properties ( ordering, color, etc )
• fallback - when group reference is not found we automatically create a group with that label

version: 2
models:
  - name: example_model
    meta:
      groups:
        failed:
          label: Failed
          description: Grouping for failed percentiles
        over:
           label: Over
           description: Subgrouping for over percentiles
        under:
           label: Under
           description: Subgrouping for under percentiles
        success:
          label: Success
          description: Grouping for success percentiles
    columns:
      - name: failed_tdigest
        metrics:
          metric_a:
            type: number
            group: ["failed", "over"] # group value can be a string or an array 
          metric_b:
            type: number
            group: ["failed", "over", "third"] # ATM we will only support 2 levels so any extra levels will be ignored
          metric_c:
            type: number
            group: [ "failed", "custom" ] # if custom is not defined in groups, we will automatically show the sub group as "Custom"
          metric_d:
            type: number
            group_label: "My group label" # group label will be ignore in favour of group property
            group: [ "failed", "over" ]
          metric_e:
            type: number
            group_label: "My group label" # we will show the group as "My group label"
          metric_f:
            type: number
            group: [ "success", "over" ] # I can reuse the group "over" in any order I want

We are only supporting 1 sub group for now to keep the UI simple. We need to rethink our sidebar UI/UX to support more nesting. Feedback from a user on this:

Originally posted by @Martin-Carlsson in #2680 (comment)

Note that the sidebar already has a deep hierarchy with 5 levels, Table -> dimensions/metrics group -> custom group -> dimension/metric -> date interval dimension so perhaps we need to rethink this structure if we want to support nested grouping labels.

Acceptance criteria

• I'm able to have up to 1 sub-groups in my table group labels (more than 1 level of sub-grouping doesn't get rendered in Lightdash)
• The sub-groups appear in the sidebar nested in the groups
• The sub groups are ordered alphabetically within a group

Documentation

• update the docs on groups to include information on how to build subgroups

Analytics events

• add analytics event in project_compiled Lightdash server event for groups_num which counts the number of fields that have the groups parameter used.

Technical notes

• most of the work is done in translator.ts where we parse/convert the data from yml to our data structure
  • we already have a group property that groups all the date interval dimensions
  • we have to change that property to be a string or a string array
  • get the first 2 values from the yml and append the date interval group if available. if no group is available, append the group_label value

[hamzahc1]

Heres a strong vote in favour of nested grouping with good reason for it:
https://getdbt.slack.com/archives/C026WJE4A69/p1658303014033149?thread_ts=1657862826.188669&cid=C026WJE4A69

[stale]

Is this issue still relevant? There have been no updates for 60 days, please close the issue or keep the conversation going!

[stellar-ahmed]

+1

[pelletstove]

+1. I'd like to see this feature.

[stale]

Is this issue still relevant? There have been no updates for 60 days, please close the issue or keep the conversation going!

[i-am-lax]

+1

[Martin-Carlsson]

I still think it could be really nice ✨

[lightdash-bot]

This issue was mentioned by a user in slack: https://lightdash-community.slack.com/archives/C067S48BHRP/p1712739451870299?thread_ts=1712739451.870299&cid=C067S48BHRP

[lplplplp-lp]

+1