[SL-1458] [Feature] Unable to traverse linkable dimensions beyond a certain level about metricflow HOT 2 CLOSED

Hey @gowthamgutha, thanks for opening an Issue. I'm gonna change this to a feature request since we're aware of this limitation. We currently only allow 2 hops, mainly because adding a third hop greatly increases the complexity of the join resolutions paths, and opens up a lot of ambiguous join paths.

It's something we're aware of, and we've already started doing some work to better handle ambiguous join resolution. Take a look at this PR for an example: #914.

The recommended path right now is to do some denormalization in dbt if you need metricflow to be able to join across more than 2 hops.

from metricflow.

Thanks for the update @Jstein77 . I've used de-normalized views to solve my use-case.

m_address.sql

{{ config(schema='Person') }}
select sp.Country, Person.Address.* from Person.Address left outer join {{ref('m_stateprovince')}} as sp on Person.Address.StateProvinceID = sp.StateProvinceID

m_stateprovince.sql

{{ config(schema='Person') }}
select Person.CountryRegion.Name as Country, Person.StateProvince.* from Person.StateProvince left outer join Person.CountryRegion on Person.StateProvince.COUNTRYREGIONCODE = Person.CountryRegion.COUNTRYREGIONCODE

The above two models have been changed to facilitate the use-case sumlinetotal by billing country.

The actual linkable dimension path ought to be sales_order_id__bill_to_address_id__state_province_id__country_region_id__country_name.

Now, with the below semantic model changes for billtoaddress, we can make sales_order_id__bill_to_address_id__country since we added the country in the m_address as shown above, so it can become a dimension in the billtoaddress semantic model.

  - name: billtoaddress
    defaults:
      agg_time_dimension: modified_date
    description: |
      sales order header
    model: ref('m_address')
    entities:
      - name: bill_to_address_id
        type: primary
        expr: AddressID
      - name: state_province_id
        type: foreign
        expr: StateProvinceID
    dimensions:
      - name: country
        expr: Country
        type: categorical
      - name: city
        expr: City
        type: categorical
      - name: modified_date
        expr: ModifiedDate
        type: time
        type_params:
          time_granularity: month

from metricflow.