Handling missing data for time dimensions

Help
,
1

I have a use-case where I want to see SalesQuota by month. In my database, I might not have rows for a particular month in which case I want to map it to a default value (like 0, for example).

The SQL generated in MetricFlow does a simple group by which only displays the sales quota for the months that are existing in the table but does not display 0s for months that do not exist as shown below.

SELECT
  DATE_TRUNC('month', QuotaDate) AS metric_time__month
  , SUM(SalesQuota) AS sum_sales_quota
FROM AdventureWorks2019.SALES.m_salespersonquotahistory sales_person_quota_history_src_7
GROUP BY
  DATE_TRUNC('month', QuotaDate)
ORDER BY metric_time__month

From what I understand, time isn’t considered a first-class citizen when querying a particular measure, instead we pick up what is there in the data and then show it.

If I were to visualize the salesquota over time i.e., over several months, I would necessarily want to view all the months data in the graph with 0s placed wherever there is missing data.

I am not a customer of dbt Semantic layer, I would like to know if this use-case is handled in any way at a higher level or can it be handled in the MetricFlow itself?

Here is an example:

In AdventureWorks2019 schema, we have SalesPersonQuotaHistory

image-2024-1-5_0-3-50
image-2024-1-5_0-3-501202×390 33.7 KB

version: 2
semantic_models:
  - name: sales_person_quota_history
    description: |
      sales person quota history
    model: ref('m_salespersonquotahistory')
    defaults:
      agg_time_dimension: quota_date
    entities:
      - name: sales_person_id
        type: foreign
        expr: BUSINESSENTITYID
      - name: rowguid
        type: primary
        expr: (BusinessEntityID, QuotaDate)
    dimensions:
      - name: quota_date
        type: time
        expr: QuotaDate
        type_params:
          time_granularity: month
    measures:
      - name: sum_sales_quota
        description: sales quota
        agg: sum
        expr: SalesQuota

metrics:
  - name: sum_sales_quota
    description: Sales quota.
    type: simple
    label: sum_sales_quota
    type_params:
      measure: sum_sales_quota

m_salespersonquotahistory.sql

select * from Sales.SalesPersonQuotaHistory
2

Either handle it on the reporting tool (this is a common case so your tool should support it) or add 0’s to your dataset.

Note: @Jack Nguyen originally posted this reply in Slack. It might not have transferred perfectly.