[SEMANTIC-121] [Feature] Can we use columns from base model in the expression on derived metrics?

[edwardlim26]

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 dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

In order for me to create an expression for one derived metric, I need to create multiple metrics from my base table which I don't want to use in the final aggregated table. Some of the derived metrics involves a large amount of parameters (or columns from base table specifically) to be aggregated and I have to have them in my models file for each of them, and the time-grains, dimensions have to be declared over and over again.

So, I would like to be able to reference columns on base models in SQL style AND be able to perform sql i.e. CASE WHEN on my expressions.

My metric:

name: trading_revenue
   label: Trading Revenue
   description: Company Trading Revenue

   calculation_method: derived
   expression: "(({{metric('profit')}} + {{metric('swap')}} + {{metric('commission')}} ) * -1) * {{metric('account_currency_multiplier')}}" 

   timestamp: trade_date_ts
   time_grains: [day]

   dimensions:
     - registration_country
     - brand
     - legal_entity
     - market

How it works now:

- name: profit
    label: Profits
    model: ref('deals')
    calculation_method: sum
    expression: profit

    timestamp: trade_date_ts
    time_grains: [day]

    dimensions:
      - registration_country 
      - brand
      - legal_entity
      - market

  - name: swap
    label: Swap
    model: ref('deals')

    calculation_method: sum
    expression: swap

    timestamp: trade_date_ts
    time_grains: [day]

    dimensions:
      - registration_country 
      - brand
      - legal_entity
      - market

  - name: commission
    label: Commission
    model: ref('deals')

    calculation_method: sum
    expression: commission

    timestamp: trade_date_ts
    time_grains: [day]

    dimensions:
      - registration_country
      - brand
      - legal_entity
      - market

   - name: account_currency_multiplier
    label: Account Currency Multiplier
    model: ref('deals')

    calculation_method: sum
    expression: account_currency_multiplier
    timestamp: trade_date_ts
    time_grains: [day]

    dimensions:
      - registration_country 
      - brand
      - legal_entity
      - market

Ideally it should be:

name: trading_revenue
    label: Trading Revenue
    description: Company Trading Revenue

    calculation_method: derived
    expression: "(({{metric('profit')}} + {{metric('swap')}} +  {{metric('commission')}} ) * - 1) * {{metric('account_currency_multiplier')}}" 

    timestamp: trade_date_ts
    time_grains: [day]

    dimensions:
      - registration_country
      - brand
      - legal_entity
      - market

Describe alternatives you've considered

I have considered querying it from my .sql file where I invoke my metric.calculate() but seems counterintuitive for the purpose of dbt metrics that's created to look at metrics without having to write more queries.

Who will this benefit?

This would benefit AEs or data analysts who'll be able to create derived metrics with SQL and be able to customize expressions based on base tables.

Are you interested in contributing this feature?

Anything else?

No response

[callum-mcdata]

Hey @edwardlim26 ! Appreciate you opening this issue - I can see how this is a frustrating amount of yml to add! That being said, I'm not entirely sure what your proposal is. Do you mind clarifying the difference between what the current implementation is and the proposed implementation?

I wonder if your issue might be addressed in some of the foundational work we're proposing to make metric definitions much easier. See this issue here in core. We're proposing making it significantly easier to define metrics. An example of this would be defining the appropriate dimensions once in the entity and having that be inherited by all your metrics (unless specified otherwise).

[callum-mcdata]

Hi @edwardlim26 ! I am closing this issue out because of our announcement here: https://docs.getdbt.com/blog/deprecating-dbt-metrics. Future development of the Semantic Layer will be moving over to https://github.com/dbt-labs/metricflow with the release of dbt-core 1.6 in July 2023.