Dimension Hierarchies

[diannewood]

Problem

SML, Tableau and Power BI all define hierarchies as drill-down structures. Hierarchies also provide valuable context to LLMs and BI tools.

Proposal

Add optional hierarchies within dimension_groups:

dimension_groups:
- name: Product
  dataset: Item # Can be overridden
  dimensions:
  - name: Product Name
    columns: [i_product_name]
    is_unique_key: true
  - name: Product Category
    columns: [i_category]
  - name: Product Color
    columns: [i_color]
  hierarchies: # optional
  - name: Product Hierarchy
    levels:
    - name: Product Category
    - name: Product Name
      secondary_attributes:
      - name: Product Color

Notes

• This is the simplest case, not a full proposal

[siljamardla]

The same could be said for metrics. Consider this set of metrics:

• revenue_with_vat
• revenue_without_vat
• revenue_after_all_tax
• revenue_after_hq_cost
  etc

Or something from funnel analysis:

• signup_funnel_step1_completion_rate
• signup_funnel_step2_completion_rate
• signup_funnel_step3_completion_rate

We would like to group these metrics together into a "revenue" group and a "signup funnel metrics" group.

The potential

There will be metadata that can be shared between all the metrics. The amount of boiler plate code can be significantly reduced if we can define some parameters on the group level, automatically propagating to each individual metric. For example, a general signup funnel process description and metric owner would be very appropriate on metric group level. These could then be omitted or have an override on metric level.

Somewhat related to #43 and #42. If units and currencies are introduced, the need for grouping is reduced, but not eliminated (as demonstrated by the funnel example).

[nikitastrezhnev]

The same could be said for metrics

Not sure. This is a little different from the original post and its intention. In these examples with metrics, you probably mean one or both of these two things:

1. These N metrics are semantically very related (e.g. funnel step 1 and funnel step 2 completion rates). These may be calculated from completely different tables, and are not necessarily calculated one from another. In which case this is not about Hierarchies, but about simple thematic groups akin to dimension groups.
2. These N metrics are derived one from another. E.g. revenue_without_vat is revenue_with_vat - vat or whatnot. In which case, this is also not about hierarchies, but about lineage and input/output relations you have described here. Those definitely should allow to be non-hierarchical (relations can go all ways).

Whereas when we talk about Hierarchies, what we are after is establishing clear parent-child relationships between entities. This carries a different type of value than just grouping things to "sit together". When there is a deterministic hierarchy of entities in your application upstream (e.g. products -> subcategories -> categories), in the layer that you expose for consumption ("gold"/"mart" or whatever you call it), most likely you'll have a somewhat denormalised version of the hierarchy (e.g. product's sub-categories and categories thrown into your Orders OBT).

With this, the consumption layer is not actually aware of the hierarchy. Put simply, it doesn't "know" that when Category4 is 'Sliced Cheese', Category2 can ONLY be 'Dairy'. There are a bunch of reasons why it's important that the layer consuming OSI is aware of hierarchical relationships (from display, drill paths and more usable drop-downs, to rollups, to managing SCDs, to managing access). But most importantly - conformed semantics across tools is kinda the point of OSI: we denote meaning, not just fields. “this is a hierarchy”, "these are the levels".

Old guys like SAP have largely figured this out a long while ago, including how it works across source systems and BI (albeit within their own stack). Obviously it's different with MDS but the general meaning of what it means to deal with hierarchies is 100% spot on.

[sfc-gh-wpugh]

I agree this is a useful feature, but we may want to change the title to "Dimension Hierarchies", because there is another type of hierarchy such as manager/employee that logically involve self-joins (even if they are flattened for analysis) which I think is a bit different.

[diannewood]

I changed the title.

[kurtStirewalt]

On the topic of hierarchies, are we considering what are sometimes called "ragged" hierarchies? For instance, in a sales organization, I might want to aggregate sales by employee, by the manager of that employee, the manager of that manager, etc all the way to the top. How would such a hierarchy be represented in this proposal?

[diannewood]

We might want to pin down simple hierarchies before tackling the more complex stuff. There could be a hierarchy "type" that changes the definition when it's "ragged" or "parent-child".

[sfc-gh-wpugh]

I think there are two classes of ragged hierarchies:
1). Fixed depth, but potentially missing value. E.g. <Country, State, City> works great for Seattle, but not as great for Washington DC
2) Arbitrary depth (parent-child/recursive join). E.g. Manager/employee

I agree with @diannewood that we should start simple. For the "fixed depth" ragged hierarchies, there are often data cleaning that folks can do to fill in missing values so it acts like a fixed hierarchy.

However, arbitrary depth is a little trickier and may be handled differently. E.g. flattening the hierarchy in the ELT step to have a column that is then used for later rollups, etc.