Financial reporting and accounting in many countries and industries is typically misaligned with the calendar year. This requires an additional data modeling effort when it comes to data analytics, and a related feature request has been filed by a Cube community member more than five years ago. Despite Cube's data modeling layer has always provided enough flexibility for custom calculations, we have decided to introduce a first-class support for arbitrary time dimension granularities to account for their relevance and to promote and streamline their use.

Today, we're excited to announce the support for fiscal and custom time dimension granularities. Now you're able to define granularities such as a fiscal year, a fiscal quarter, or a week starting on Sunday for any time dimension, query them via all supported APIs, and have these queries accelerated by pre-aggregations.

Modeling custom granularities

The data modeling syntax has been extended to support custom granularities. Now, any time dimension can define a list of granularities that can be used with that time dimension in addition to all built-in granualrities such as year or day.

Consider the following example data model:

cubes:
  - name: custom_granularities
    sql: >
      SELECT '2024-01-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-02-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-03-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-04-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-05-15'::TIMESTAMP AS timestamp


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


        granularities:
          - name: sunday_week
            interval: 1 week
            offset: -1 day


          - name: fiscal_year
            interval: 1 year
            offset: -3 months


      - name: timestamp__sunday_week
        sql: "{timestamp.sunday_week}"
        type: time


      - name: timestamp__fiscal_year
        sql: "{timestamp.fiscal_year}"
        type: time

In this data model, the custom_granularities cube has the timestamp time dimension that defines custom sunday_week and fiscal_year granularities. For convenience, they are also exposed via a couple of proxy dimensions: timestamp__sunday_week and timestamp__fiscal_year.

Querying them in Playground would provide the following result:

Custom granularities in Playground

You can use custom granularities to support weeks starting on Sunday or Saturday, fiscal periods relevant for your industry or accounting policies, and even use cases when you'd like to group your time series data into arbitrarily sized buckets, e.g., "fortnight" or "15 minutes".

If you'd like to use the same custom granularity with multiple time dimensions, it is recommended to use Jinja to minimize code duplication.

Querying custom granularities

Since the REST API query format provides a way to specify a requested granularity, you can naturally query custom granularities via the REST API:

{
  "timeDimensions": [
    {
      "dimension": "custom_granularities.timestamp",
      "granularity": "sunday_week"
    }
  ],
  "dimensions": [
    "custom_granularities.timestamp"
  ]
}

When using the SQL API and working with custom granularities in BI tools, it is recommended to expose custom granualrities via proxy dimensions, as shown above. They will be made available to BIs as separate columns in your datasets:

Accelerating custom granularities

Queries with custom granularities are capable of reusing pre-aggregations that are defined with built-in pre-aggregations. For example, the following pre-aggregation should accelerate queries with both week and sunday_week granularities:

cubes:
  - name: custom_granularities
    # ...


    pre_aggregations:
      - name: main
        # measures:
        #   - ...
        # dimensions:
        #   - ...
        time_dimension: timestamp
        granularity: day

You are also free to create pre-aggregations that specify custom granularities in their granularity parameter. Such pre-aggregations will only accelerate queries with that specifc custom granularity. That might be useful, for example, if you already have a pre-aggregation with the year granularity and don't want to switch it to month or day so that it also matches your queries with fiscal_year or fiscal_quarter.

What's next?

Custom granularities support is available in Cube Core and Cube Cloud starting from v0.36.1. When upgrading to v0.36, please make sure to check the release notes for breaking changes and deprecations.

You can learn more about these data modeling updates in the documentation. Take a refreshing look at the time dimension concept, check the custom granularities syntax, see how you can reference a built-in or custom granularity via a proxy dimension. Finally, explore this practical recipe with typical custom granularity examples.

We encourage you to give custom granularities a try and share your feedback in the Slack community. We'd like to know how you use custom granularities in your data modeling practice and how we can help improve your experience.