Advanced Date Dimension with Fiscal Calendar

Advanced Date Dimension with Fiscal Calendar

View Code to generate a date dimension from a range of dates including Fiscal Calendar and advanced attributes

Data dimensions are common in data warehouses and provide lots of useful precalculated details about a date that help with reporting and analysis. It’s common to store these as a physical table, but in BiqQuery we can use a view to generate on the fly the dimension which will auto-extend daily and have a zero cost.
This version extends the basic date dimension Basic Date Dimension to include a fiscal calendar based on 4 week periods. It also includes the ability to specify 53 week years.
There are other fiscal calendar structures in different organisations however this method can be adjusted to suit your requirements.

CREATE OR REPLACE VIEW `projectid`.dataset.dim_date_with_fiscal_cal
AS
WITH
fiscal_start AS
(
SELECT CAST('2007-03-30' as DATE) as cal_start_date, [2011,2016,2022] as years_with_53_weeks
), all_days AS
(
SELECT day AS date,
    EXTRACT(YEAR FROM day) AS year,
    EXTRACT(WEEK FROM day) AS week,
    EXTRACT(MONTH FROM day) AS month_no,
    FORMAT_DATE("%m", day) AS month_no_fmt,
    EXTRACT(DAY FROM day) AS day_no,
    FORMAT_DATE("%d", day) AS day_no_fmt,
    EXTRACT(YEAR FROM day) || '-' || FORMAT_DATE("%m", day) AS year_month,
    EXTRACT(YEAR FROM day) || '-Q' || EXTRACT(QUARTER FROM day) AS year_qtr,
    EXTRACT(DAYOFYEAR FROM day) AS day_of_year,
    CAST(CASE WHEN EXTRACT(DAYOFWEEK FROM day) > 5 THEN EXTRACT(DAYOFWEEK FROM day)-5 ELSE EXTRACT(DAYOFWEEK FROM day)+2 END AS STRING) AS day_of_week,
    FORMAT_DATE("%A", day) AS day_name,
    FORMAT_DATE("%B", day) AS month,
    FORMAT_DATE("%b", day) AS short_month,
    FORMAT_DATE("%a", day) AS short_day_name,
    CASE WHEN FORMAT_DATE("%a", day) in ('Sat','Sun') THEN True ELSE False END AS is_weekend,
    DATE_TRUNC(day, MONTH) AS start_of_month,
    LAST_DAY(day, MONTH) AS end_of_month,
    EXTRACT(DAY FROM LAST_DAY(day, month)) AS month_length,
    EXTRACT(QUARTER FROM day) AS quarter,
    DATE_TRUNC(day, QUARTER) AS start_of_quarter,
    LAST_DAY(day, QUARTER) AS end_of_quarter,
    DATE_DIFF(CURRENT_DATE(), day, YEAR) AS year_diff,
    DATE_DIFF(CURRENT_DATE(), day, MONTH) AS month_diff,
    DATE_DIFF(CURRENT_DATE(), day, WEEK) AS week_diff,
    DATE_DIFF(CURRENT_DATE(), day, DAY) AS day_diff,
    cal_start_date,
    EXTRACT(YEAR FROM cal_start_date) AS cal_year_start,
    years_with_53_weeks
FROM fiscal_start CROSS JOIN
    UNNEST(
    GENERATE_DATE_ARRAY(DATE('2007-03-30'), DATE_ADD(CURRENT_DATE(), INTERVAL 3 YEAR ), INTERVAL 1 DAY)
) AS day
),

fiscal_year_weeks AS
(
SELECT year+1 as fiscal_year,
  year-cal_year_start AS year_from_start,
  cal_start_date,
  CASE WHEN year+1 in UNNEST(years_with_53_weeks) THEN 53 ELSE 52 END fiscal_weeks
FROM all_days
GROUP BY 1,2,3,4
),

fiscal_year_starts AS
(
SELECT fiscal_year, fiscal_weeks,cal_start_date,
  IFNULL(SUM(fiscal_weeks) OVER (
    ORDER BY fiscal_year
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) ,0) AS week_to_start
FROM fiscal_year_weeks f
), fiscal_year_start_end AS
(
SELECT fiscal_year, fiscal_weeks,
  DATE_ADD(cal_start_date, INTERVAL week_to_start WEEK) AS fiscal_year_start_date,
  DATE_ADD(DATE_ADD(cal_start_date, INTERVAL week_to_start + fiscal_weeks WEEK), INTERVAL -1 DAY) AS fiscal_year_end_date
FROM fiscal_year_starts f
)
,

all_dates_inc_fiscal_cal AS
(
SELECT d.date,
    year,
    week,
    month_no,
    month_no_fmt,
    day_no,
    day_no_fmt,
    year_month,
    year_qtr,
    day_of_year,
    day_of_week,
    day_name,
    month,
    short_month,
    short_day_name,
    is_weekend,
    start_of_month,
    end_of_month,
    month_length,
    quarter,
    start_of_quarter,
    end_of_quarter,
    year_diff,
    month_diff,
    week_diff,
    day_diff,
    cal_start_date,
    fiscal_weeks,
    fy.fiscal_year,
    fiscal_year_start_date,
    fiscal_year_end_date,
    DATE_DIFF(d.date, fiscal_year_start_date, DAY) AS fiscal_year_day_counter,
    DIV(DATE_DIFF(d.date, fiscal_year_start_date, DAY), 7)+1 AS fiscal_week,
    CASE WHEN DIV(DATE_DIFF(d.date, fiscal_year_start_date, DAY), 7)+1=53 THEN 13 ELSE DIV(DATE_DIFF(d.date, fiscal_year_start_date, DAY), 28)+1 END AS fiscal_period,
    CASE WHEN d.date >= fiscal_year_start_date and d.date < DATE_ADD(fiscal_year_start_date, INTERVAL 16 WEEK) THEN 1
         WHEN d.date >= DATE_ADD(fiscal_year_start_date, INTERVAL 16 WEEK) AND d.date < DATE_ADD(fiscal_year_start_date, INTERVAL 28 WEEK) THEN 2
         WHEN d.date >= DATE_ADD(fiscal_year_start_date, INTERVAL 28 WEEK) AND d.date < DATE_ADD(fiscal_year_start_date, INTERVAL 40 WEEK) THEN 3
         WHEN d.date >= DATE_ADD(fiscal_year_start_date, INTERVAL 40 WEEK) AND d.date <= fiscal_year_end_date THEN 4
    ELSE 0 END AS fiscal_qtr
    -- -------
FROM all_days d
  INNER JOIN fiscal_year_start_end fy on d.date BETWEEN fiscal_year_start_date AND fiscal_year_end_date
),

all_dates_inc_fiscal_cal2
AS (
SELECT d.date,
    d.year,
    d.week,
    d.month_no,
    d.month_no_fmt,
    d.day_no,
    d.day_no_fmt,
    d.year_month,
    d.year_qtr,
    d.day_of_year,
    d.day_of_week,
    d.day_name,
    d.month,
    d.short_month,
    d.short_day_name,
    d.is_weekend,
    d.start_of_month,
    d.end_of_month,
    d.month_length,
    d.quarter,
    d.start_of_quarter,
    d.end_of_quarter,
    d.year_diff,
    d.month_diff,
    d.week_diff,
    d.day_diff,
    d.fiscal_weeks,
    d.fiscal_year,
    d.fiscal_qtr,
    d.fiscal_period,
    d.fiscal_week,
    FORMAT("%d", d.fiscal_year) || '-Q' || d.fiscal_qtr fiscal_year_qtr,
    FORMAT("%d", d.fiscal_year) || '-' ||  FORMAT("%02d", d.fiscal_period) fiscal_year_period,
    FORMAT("%d", d.fiscal_year) || '-' ||  FORMAT("%02d", d.fiscal_week) fiscal_year_week,
    d.fiscal_year_start_date,
    d.fiscal_year_end_date,
    MIN(d.date) OVER ( PARTITION BY d.fiscal_year,d.fiscal_qtr) AS fiscal_qtr_start_date,
    MAX(d.date) OVER ( PARTITION BY d.fiscal_year,d.fiscal_qtr) AS fiscal_qtr_end_date,
    MIN(d.date) OVER ( PARTITION BY d.fiscal_year,d.fiscal_period) AS fiscal_period_start_date,
    MAX(d.date) OVER ( PARTITION BY d.fiscal_year,d.fiscal_period) AS fiscal_period_end_date,
    MIN(d.date) OVER ( PARTITION BY d.fiscal_year,d.fiscal_week) AS fiscal_week_start_date,
    MAX(d.date) OVER ( PARTITION BY d.fiscal_year,d.fiscal_week) AS fiscal_week_end_date,
    d.fiscal_year_day_counter,
    ROW_NUMBER() OVER ( PARTITION BY d.fiscal_year,d.fiscal_qtr ORDER BY d.date) AS fiscal_qtr_day_counter,
    ROW_NUMBER() OVER ( PARTITION BY d.fiscal_year,d.fiscal_period ORDER BY d.date) AS fiscal_period_day_counter,
    ROW_NUMBER() OVER ( PARTITION BY d.fiscal_year,d.fiscal_week ORDER BY d.date) AS fiscal_week_day_counter,
    DATE_DIFF(d.date, cal_start_date, DAY) AS fiscal_day_id,
    DATE_DIFF(d.fiscal_year_end_date, cal_start_date, YEAR) AS fiscal_year_id,
    CASE WHEN d.fiscal_year_start_date = d.date THEN 1 ELSE 0 END AS fiscal_year_start_marker,
    CASE WHEN MIN(d.date) OVER ( PARTITION BY d.fiscal_year,d.fiscal_period) = d.date THEN 1 ELSE 0 END AS fiscal_period_start_marker,
    CASE WHEN MIN(d.date) OVER ( PARTITION BY d.fiscal_year,d.fiscal_qtr) = d.date THEN 1 ELSE 0 END AS fiscal_qtr_start_marker,
    CASE WHEN MIN(d.date) OVER ( PARTITION BY d.fiscal_year,d.fiscal_week) = d.date THEN 1 ELSE 0 END AS fiscal_week_start_marker,
FROM all_dates_inc_fiscal_cal d
), current_day_values AS
(
  SELECT MAX(cd.fiscal_year_id) AS current_fiscal_year_id,
         MAX(cd.fiscal_day_id) AS current_fiscal_day_id,
         SUM(cd.fiscal_qtr_start_marker) AS current_fiscal_qtr_id,
         SUM(cd.fiscal_period_start_marker) AS current_fiscal_period_id,
         SUM(cd.fiscal_week_start_marker) AS current_fiscal_week_id,
  FROM  all_dates_inc_fiscal_cal2 cd
  WHERE cd.`date` <= CURRENT_DATE()
)
SELECT d.date,
    d.year,
    d.week,
    d.month_no,
    d.month_no_fmt,
    d.day_no,
    d.day_no_fmt,
    d.year_month,
    d.year_qtr,
    d.day_of_year,
    d.day_of_week,
    d.day_name,
    d.month,
    d.short_month,
    d.short_day_name,
    d.is_weekend,
    d.start_of_month,
    d.end_of_month,
    d.month_length,
    d.quarter,
    d.start_of_quarter,
    d.end_of_quarter,
    d.year_diff,
    d.month_diff,
    d.week_diff,
    d.day_diff,
    d.fiscal_weeks,
    d.fiscal_year,
    d.fiscal_qtr,
    d.fiscal_period,
    'P' || FORMAT("%02d", d.fiscal_period) || 'Wk' || CAST(DIV(DATE_DIFF(d.date, d.fiscal_period_start_date, DAY), 7) + 1 AS STRING) AS fiscal_period_week,
    d.fiscal_week,
    d.fiscal_year_qtr,
    d.fiscal_year_period,
    d.fiscal_year_week,
    d.fiscal_year_start_date,
    d.fiscal_year_end_date,
    d.fiscal_qtr_start_date,
    d.fiscal_qtr_end_date,
    d.fiscal_period_start_date,
    d.fiscal_period_end_date,
    DATE_DIFF(d.fiscal_period_end_date, d.fiscal_period_start_date, DAY) AS fiscal_period_length,
    d.fiscal_week_start_date,
    d.fiscal_week_end_date,
    d.fiscal_year_day_counter,
    d.fiscal_qtr_day_counter,
    DIV(DATE_DIFF(d.date, d.fiscal_qtr_start_date, DAY), 7)+1 AS fiscal_qtr_week_counter,
    d.fiscal_period_day_counter,
    CAST(DIV(DATE_DIFF(d.date, d.fiscal_period_start_date, DAY), 7) + 1 AS STRING) AS fiscal_period_week_counter,
    d.fiscal_week_day_counter,
    d.fiscal_year_id,
    SUM(d.fiscal_qtr_start_marker) OVER (ORDER BY d.date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS fiscal_qtr_id,
    SUM(d.fiscal_period_start_marker) OVER (ORDER BY d.date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS fiscal_period_id,
    SUM(d.fiscal_week_start_marker) OVER (ORDER BY d.date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS fiscal_week_id,
    d.fiscal_day_id,
    cd.current_fiscal_year_id - d.fiscal_year_id AS fiscal_year_diff,
    cd.current_fiscal_qtr_id - SUM(d.fiscal_qtr_start_marker) OVER (ORDER BY d.date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS fiscal_qtr_diff,
    cd.current_fiscal_period_id - SUM(d.fiscal_period_start_marker) OVER (ORDER BY d.date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS fiscal_period_diff,
    cd.current_fiscal_week_id - SUM(d.fiscal_week_start_marker) OVER (ORDER BY d.date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS fiscal_week_diff,
    cd.current_fiscal_day_id - d.fiscal_day_id AS fiscal_day_diff,
    /* 7 days */
    CASE WHEN d.day_diff BETWEEN 0 AND 6
        THEN true ELSE false END AS is_last_7_days,
FROM all_dates_inc_fiscal_cal2 d
    CROSS JOIN current_day_values cd
-- Test filter remove from view
--WHERE day_diff between 0 and 30
    ;

Example of using the dim date

select distinct fiscal_year_week, fiscal_year, Fiscal_week, fiscal_week_start_date,fiscal_week_end_date, fiscal_period_start_date, fiscal_period_end_date, fiscal_weeks
FROM `projectid`.dataset.dim_date
where fiscal_week_diff in (0,1)

2 Likes

This is really helpful. Thanks for sharing @stretch, this is going to save people a huge amount of time.