[SL-1725] [Feature] Reference a measure from another semantic model.

[Jstein77]

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 want to be able to reference a measure in another semantic model at a different entity grain. For example, say I have model called caregiver_visits and another model called visits. visits contains a measure visit_id__scheduled_duration_mins which I want to aggregate at to the caregiver_visits entity. I should be able to express that in the spec. An example of what that might look like is:

semantic_models:
  - name: caregiver_visits
    model: ref('caregiver_visits')
    entities:
      - name: caregiver_visit
        type: primary
        expr: concat( coalesce(caregiver_id, 'no_caregiver_id'), visit_id )
      - name: visit_id
        type: foreign
      - name: branch_id
        type: foreign
        expr: agency_id
      - name: care_recipient
        type: foreign
        expr: care_recipient_id
    dimensions:
      - name: record_updated_at
        type: time
        type_params:
          time_granularity: day
    measures:
      - name: total_scheduled_duration_hours
        label: Visit Duration
        agg: sum
        expr: visit_id__scheduled_duration_mins
        create_metric: true

Querying the total_scheduled_duration_hours metric would resolve to something like the following SQL

select
    total_scheduled_duration_hours
from 
   caregiver_visits
left join (
       select
             concat( coalesce(caregiver_id, 'no_caregiver_id'), visit_id ) as caregiver_visit
           , sum(total_scheduled_duration_hours)
       from 
            visits
       group by 
           1
) as visits_sub_q
ON visits_sub_q.caregiver_visit = caregiver_visits.caregiver_visit

Describe alternatives you've considered

The alternative right now is to create an import CTE in the caregiver_visits model, and reference the measure directly in the caregiver_visits semantic model.

with total_scheduled_duration_hours as (
select
    sum(total_scheduled_duration_hours) as total_scheduled_duration_hours
  , concat( coalesce(caregiver_id, 'no_caregiver_id'), visit_id ) as caregiver_visit
from visits
)

select
* from 
  caregiver_visit cv
left join total_scheduled_duration_hours th on cv.caregiver_vist = th.caregivervist

_{From SyncLinear.com | SL-1725}