[SEMANTIC-121] [Feature] Can we use columns from base model in the expression on derived metrics?
Closed this issue · 2 comments
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
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).
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.