[SL-3321] [Feature] Allow use dimensional attributes of another model when defining a measure.

[tanuuuuuuu]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing metricflow functionality, rather than a Big Idea better suited to a discussion

Describe the feature

I would like to be able to reference dimensional attributes of another model when defining Measures.
This is needed when defining aggregate values that combine fact and dimension tables in dimensional modeling.

Example:

semantic_models:
  - name: order_fact
    model: ref('fct_order')

    entities:
      - name: order_fact
        type: primary
        expr: order_key

      - name: user_dimension
        type: foreign
        expr: user_key

    measures:
      - name: number_of_unique_users
        agg: count_distinct
        expr: "{{ Dimension('user_dimension__user_id') }}"  # Enable this syntax (Encountered an error: Compilation Error Could not render {{ Dimension('user_key__user_id') }}: 'Dimension' is undefined)

fct_order:

order_key	user_key	order_id	order_date	order_price
1	1	o1	2025-01-01T00:00:00	1000
2	2	o2	2025-02-01T00:00:00	1200
3	3	o3	2025-03-01T00:00:00	800

dim_user:

user_key	user_id	state_code
1	u1	NY
2	u1	CA
3	u2	CA

Describe alternatives you've considered

Store user_id as a degenerate dimension in the fact table.

Example:

semantic_models:
  - name: order_fact
    model: ref('fct_order')

    entities:
      - name: order_fact
        type: primary
        expr: order_key

      - name: user_dimension
        type: foreign
        expr: user_key

    measures:
      - name: number_of_unique_users
        agg: count_distinct
        expr: user_id

fct_order:

order_key	user_key	order_id	user_id	order_date	order_price
1	1	o1	u1	2025-01-01T00:00:00	1000
2	2	o2	u1	2025-02-01T00:00:00	1200
3	3	o3	u2	2025-03-01T00:00:00	800

dim_user:

user_key	user_id	state_code
1	u1	NY
2	u1	CA
3	u2	CA

Pros:

• Simplifies queries by eliminating joins

Cons:

• Data redundancy: user_id stored in both fact and dimension tables
• Increased storage requirements in fact table
• Violates dimensional modeling best practices

Who will this benefit?

Data teams implementing dimensional modeling (star schema).

Are you interested in contributing this feature?

No response

Anything else?

In Looker, it looks like it can be defined as follows.

measure: number_of_unique_users {
  type: count_distinct
  sql: ${dim_user.user_id} ;;
}

Warning

I'm not an expert on Looker, so the specification itself or the syntax may be incorrect.

_SL-3321