Missing data handling for time-dimension based queries

[MadhusudanN]

• [X] Add
• [X] Reply on GitHub

[igorlukanin]

Hi @MadhusudanN 👋 Thanks for a very detailed and thorough question!

So I notice the Cube UI is the one which is handling the missing data and showing it as 0 where data is missing from the backend. Can some one please confirm on this behaviour.

Your conclusion is exactly correct. Let me explain what is happening here:

• As you were able to confirm yourself, Cube's APIs (REST, SQL, etc.) return query results as they are. If there's no measure value(s) for a particular time dimension value, it would not be present in the result set, as you would expect.
• Cube's JavaScript SDK (a set of tools to help integrate Cube's APIs with front-end applications) "fills missing dates" by default (see the fillMissingDates option of PivotConfig). Such defaults generally makes sense because embedded analytics applications usually put data on charts. There's also an option to override these defaults with fillMissingDates: false.
• Cube's Playground uses Cube's JavaScript SDK and inherits these defaults.

I hope it clarifies things.

Is it something that renders Playground unusable for you?

[MadhusudanN]

Hi @igorlukanin Thanks a lot for the reply. It is clear. I have one question regarding the support for Fiscal Year, Fiscal Quarter etc. in the time dimension. For example if we need to get sales total based on Fiscal Quarter, do we have support for that in cube. Please see the screenshot below for illustration.

Can you please let me know is there a way we can achieve this with Cube.

[igorlukanin]

@MadhusudanN Sure! Just FYI, there's an open discussion about fiscal year support in date calculations that you can join.

However, in your particular use case, a very viable and working approach would be to define a separate dimension for the fiscal quarter and use it in your queries. Consider the following data model that defines such a dimension for Australia, where FY2024-Q1 starts in July 2024:

cubes:
  - name: fiscal
    sql: >
      SELECT '2024-01-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-02-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-03-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-04-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-05-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-06-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-07-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-08-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-09-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-10-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-11-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-12-15T00:00:00.000Z'::TIMESTAMP AS timestamp

    dimensions:
    - name: timestamp
      sql: timestamp
      type: time

    - name: fiscal_year_internal
      sql: "EXTRACT(YEAR FROM {timestamp} - INTERVAL '6 MONTH')"
      type: string
      public: false

    - name: fiscal_quarter_internal
      sql: >
        CASE
          WHEN EXTRACT(MONTH FROM {timestamp}) BETWEEN 7 AND 9 THEN 1
          WHEN EXTRACT(MONTH FROM {timestamp}) BETWEEN 10 AND 12 THEN 2
          WHEN EXTRACT(MONTH FROM {timestamp}) BETWEEN 1 AND 3 THEN 3
          WHEN EXTRACT(MONTH FROM {timestamp}) BETWEEN 4 AND 6 THEN 4
        END
      type: string
      public: false

    - name: fiscal_quarter
      sql: "'FY' || {fiscal_year_internal} || '-Q' || {fiscal_quarter_internal}"
      type: string

Here's what you'll get querying the fiscal_quarter dimension:

Note that fiscal_year_internal and fiscal_quarter_internal would need to be adjusted to the fiscal calendar that you'd like to use because different fiscal calendar schemes use different month offsets.

I hope it helps 😊

[igorlukanin]

FYI, I've extracted my comment into this recipe: https://cube.dev/docs/guides/recipes/data-modeling/fiscal-year-quarter-dimensions