[Feature] Allow using dimensions in derived metric definitions

[siljamardla]

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

There are common use cases where a derived metric is a mix of a simple metric and a dimension, for example:

• fact table with revenue metric, dimension table with currency exchange rate
• fact table with revenue metric, dimension table with the VAT %

I would like to define ratio and derived metrics that would take not only metrics, but also dimensions as input. For example:

metrics:
  - name: revenue_in_eur
    type: derived
    type_params:
      expr: revenue_in_local_currency * dim_country__currency_exchange_rate
      metrics:
        - name: revenue_in_local_currency
     # pseudo code
     dimensions:
        - name: dim_country__currency_exchange_rate

Describe alternatives you've considered

I don't think there's a workaround we could use by defining metrics on the dimension table, because the dimension table is typically much less granular. For example I might be able to group the revenue by product, customer, date and whatnot while the dimension table that holds the currency exchange rate has a grain of country (and date) only.

The only existing workaround is to prepare the fact table by bringing in the redundant dimension column that we can then make a metric out of and use in the derived metric.

Who will this benefit?

All the users of the semantic layer, cloud and core.

Are you interested in contributing this feature?

Happy to help with any design questions and testing

Anything else?

No response