Requirements & approach:- Single canonical Date dimension (surrogate key date_id) that contains one row per calendar date (global UTC), plus attributes for all calendar systems. Use this as the single source-of-truth and drive all time-intelligence via joins and mapping tables.Date dimension core columns (for each date):- date_id (PK), date (date), year, month, day, weekday- iso_year, iso_week, iso_week_start_date, iso_week_end_date- calendar_month_start_date, calendar_month_end_date- country_code (nullable) fields? (prefer mapping table below)Fiscal/calendar extension (support per-country/company):- fiscal_calendar_id (FK) + fiscal_year, fiscal_period (number), fiscal_period_start_date, fiscal_period_end_date, fiscal_quarter- week_alignment_id mapping table that stores week definitions per country (e.g., week_start_day, week_numbering rule — ISO vs. country-specific, business-week vs. financial-week)- Precompute: fiscal_period_offset rules (e.g., 4-4-5) and store per fiscal_calendar_id with computed start/end dates for each fiscal period in a Fiscal_Periods table (fiscal_calendar_id, fiscal_year, period_number, period_start, period_end)Design patterns for calculations:- Always join facts to Date.date (date_id) and then join date → Fiscal_Periods on date between period_start and period_end using fiscal_calendar_id appropriate for that fact’s country/entity. This preserves consistent membership for periods.- Period-to-period comparisons: derive period keys (fiscal_year||period_number OR week_key like fiscal_year*100+week_number) from Fiscal_Periods or Weeks table and aggregate facts by that key. Compare current period key to prior period key (use LEAD/LAG on periods table to find prior period_id).- Rolling N-period metrics: compute rolling sums on pre-aggregated period-level metrics (preferred) or use window functions over date ordered by period_start. E.g., SUM(metric) OVER (PARTITION BY entity ORDER BY period_start ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW).- Consistent week/month boundaries across countries: use Weeks table per week_alignment_id that includes week_id, week_start_date, week_end_date, week_number, week_year. Join facts via date between week_start/week_end or map date→week_id in Date dimension.- Time-intelligence functions in BI tools: expose canonical period keys and natural labels (e.g., fiscal_month_name, week_label) and precomputed comparisons (previous_period_id, same_period_last_year_id) to simplify front-end.Implementation notes & edge cases:- Store both calendar and fiscal start/end dates to handle unequal period lengths (e.g., 4-5-4). Use computed tables refreshed nightly.- Handle countries with variable week definitions (e.g., Middle East Sunday–Thursday) by parameterizing week_start_day.- Leap years & 53-week years: Fiscal_Periods and Weeks table must encode actual period membership to avoid relying on simple date arithmetic.- Performance: pre-aggregate daily facts to period-level for heavy dashboards; index on date and period_start/end; cache period mapping.- Governance: document canonical calendar IDs, publish semantic layer fields (period_key, prior_period_key, rolling_N_label) so analysts use consistent logic.Example SQL join pattern:sql
-- map fact row to fiscal period for country-specific calendar
SELECT f.entity_id, fp.fiscal_calendar_id, fp.fiscal_year, fp.period_number, SUM(f.amount) as amt
FROM fact_sales f
JOIN dim_date d ON f.sale_date = d.date
JOIN fiscal_periods fp
ON d.date BETWEEN fp.period_start AND fp.period_end
AND fp.fiscal_calendar_id = f.fiscal_calendar_id
GROUP BY f.entity_id, fp.fiscal_calendar_id, fp.fiscal_year, fp.period_number;
