Universal Date & Fiscal Calendar¶

Purpose¶

When we build KPIs (Key Performance Indicators) for the CFO Pack, almost every calculation depends on the concept of time. For example:

Month-over-Month (MoM) growth compares revenue between two consecutive months, such as March vs. April.
Quarter-to-Date (QTD) shows performance for the current fiscal quarter up to the present date.
Year-to-Date (YTD) accumulates values starting from the beginning of the fiscal year up to today.
Working Capital Days metrics like DSO (Days Sales Outstanding) and DPO (Days Payable Outstanding) require counting only business days and excluding weekends and holidays.

The challenge is that ERP systems (SAP, Oracle, NetSuite, etc.) do not provide a clean, human-readable “time dimension.” Instead, they rely on system codes, fiscal variants, and cryptic fields such as BUKRS, GJAHR, BUDAT, PERIV. Different companies—and even subsidiaries of the same group—may:

Use different fiscal year start months (e.g., Jan–Dec, Apr–Mar, Jul–Jun).
Use different period structures (e.g., 12 calendar months, 13 periods, or 4-4-5 retail calendar).
Maintain different holiday schedules or plant shutdown calendars, which directly affect calculations based on working days.
Follow different financial close processes, i.e., when final financials are officially signed off.

Without a universal, standardized date layer, developers and analysts would constantly face ERP-specific complexity inside every KPI query. This results in errors, inconsistencies, and fragile implementations that cannot scale across entities.

Problem Context¶

Fiscal year diversity: Entities may follow different fiscal years such as Jan–Dec, Apr–Mar, or Jul–Jun.
System encoding: ERP systems typically store time in system codes (GJAHR, BUDAT, PERIV) rather than user-friendly formats.
Non-standard calendars: Some entities use 13-period structures or retail-oriented calendars like 4-4-5.
Business-day relevance: Metrics like DSO/DPO require working days, not calendar days.
Close process gating: Finance requires reporting to be restricted to closed periods to prevent premature or inaccurate reporting.

Solution Approach¶

The Universal Date Table provides a structured, standardized way of handling dates and fiscal calendars across multiple entities and systems. The approach includes:

Canonical Date Dimension (dim_date)
Stores all Gregorian calendar dates in a contiguous sequence.
Serves as the foundation for all joins.
Entity-Specific Fiscal Calendar (dim_fiscal_calendar)
Defines fiscal years and periods for each entity.
Handles diverse fiscal starts and period structures.
Bridge Table (bridge_date_fiscal)
Maps every posting date to its corresponding fiscal period for a given entity.
Ensures entity-aware reporting alignment.
Holiday Calendar (dim_holiday)
Stores both global and entity-specific holidays or shutdowns.
Enables working-day aware KPI calculations.
Flags and Controls
is_business_day flag ensures working-day calculations.
is_close_complete ensures that only finalized fiscal periods are reported.

What the Universal Date Table Solves¶

ERP Neutrality: A single canonical date model works across all ERPs (SAP, Oracle, NetSuite, etc.).
Entity-Aware Alignment: Each entity’s fiscal year and period definitions are accounted for in dim_fiscal_calendar.
Business-Day Awareness: By integrating holiday and weekend flags, KPIs like DSO/DPO can be calculated accurately.
Close Process Enforcement: Prevents publishing metrics for periods that are not officially closed.
Consistency Across Reports: Ensures that “April FY25” means the same across P&L, Balance Sheet, Cash Flow, or Sales dashboards.
Simplified Development: Developers no longer need to hardcode fiscal logic. A simple join fetches fiscal context:

join fact_invoices i
  on i.date_key = d.date_key
join bridge_date_fiscal b
  on b.date_key = d.date_key
 and b.entity_id = i.entity_id

Data Quality Rules¶

Dim Integrity
dim_fiscal_calendar: No gaps or overlaps per (entity_id); boundaries must be valid.
bridge_date_fiscal: Every (entity_id, date_key) must map to exactly one fiscal row.
dim_date: Must have contiguous coverage for the requested reporting horizon.
Referential Integrity
All fact date_key values must exist in dim_date.
All fact (entity_id, fiscal_year, fiscal_period) must exist in dim_fiscal_calendar.
Close Gating
KPIs that depend on close status should not be produced if is_close_complete = false.
Business Day Validation
For working-day metrics (e.g., DSO, DPO), the denominator must include only is_business_day = true days.

Performance & Indexing¶

Add partitioning keys on large facts by date_key, or by (entity_id, fiscal_year, fiscal_period) for period-based facts.
Materialize rolling windows (e.g., last 12 fiscal months) through database views to speed up UI filters.
Keep bridge_date_fiscal cached in memory since it is relatively small compared to transactional fact tables.

Example Queries¶

Select MoM fiscal periods (entity aware):

select a.*
from fact_kpi_actuals a
         join dim_fiscal_calendar f
              on f.entity_id = a.entity_id
                  and f.fiscal_year = a.fiscal_year
                  and f.fiscal_period = a.fiscal_period
where f.is_close_complete = true;

Compute business days in a fiscal month:

select count(*) as business_days
from bridge_date_fiscal b
where b.entity_id = :eid
  and b.fiscal_year = :fy
  and b.fiscal_period = :fp
  and b.is_business_day = true;

Gate KPIs by close:

select a.*
from fact_kpi_actuals a
join dim_fiscal_calendar f
  on f.entity_id = a.entity_id
 and f.fiscal_year = a.fiscal_year
 and f.fiscal_period = a.fiscal_period
where f.is_close_complete = true;

Governance & Ownership¶

Owner
Finance Ops → Responsible for defining fiscal period boundaries and close flags.
Data Platform → Responsible for generating and validating the Universal Date Table.
Local Controllers → Responsible for maintaining holiday and local shutdown definitions.
Change Management
Any change in fiscal definitions requires backfilling bridge_date_fiscal.
Affected Gold layer KPIs must be recomputed to reflect new mappings.

Relevance for UI & Visualization¶

Consistent Filters & Slicers: Fiscal Year, Quarter, Period selectors come directly from the Universal Date Table.
Close-Aware Dashboards: Periods where is_close_complete = false can be greyed out.
Holiday Awareness: Dashboards can overlay holidays/shutdowns on charts.
Hierarchical Drilldowns: Seamless navigation from Year → Quarter → Month → Week → Day.
Cross-Entity Views: Subsidiaries with different fiscal calendars can still be consolidated in a unified Group CFO dashboard.

System Design Takeaways¶

When designing ETL, models, or dashboards, always:

Define holidays in dim_holiday (global + entity-specific).
Declare fiscal years in dim_fiscal_calendar with start months and period structures.
Mark consolidation nodes in dim_entity with is_consolidation_node = true.
Maintain close status in dim_fiscal_calendar.
Always use the bridge table for fiscal alignment instead of hardcoding.
Treat date_key as the canonical key across all fact tables.

ER Diagram¶

Universal Date Table ER Diagram

Table Structures¶

//// Annexure A — Universal Date & Fiscal Model (DBML)

Table dim_entity {
  entity_id             varchar(20) [pk, note: 'Subsidiary / BU key']
  entity_name           varchar(120) [not null]
  parent_entity_id      varchar(20)  [ref: - dim_entity.entity_id, note: 'Self-FK for consolidation']
  currency              varchar(10)  [not null]
  country               varchar(50)
  is_consolidation_node boolean      [not null, default: false]
  active                boolean      [not null, default: true]
}

Table dim_date {
  date_key        int [pk, note: 'YYYYMMDD']
  full_date       date        [not null]
  day_number      int         [not null, note: '1–31']
  day_name        varchar(10) [not null]
  day_of_week     int         [not null, note: '1=Mon..7=Sun (ISO)']
  week_number     int         [not null]
  month_number    int         [not null]
  month_name      varchar(20) [not null]
  quarter_number  int         [not null]
  year_number     int         [not null]
  is_weekend      boolean     [not null, default: false]
  is_business_day boolean     [not null, default: true]
}

Table dim_fiscal_calendar {
  fiscal_calendar_id  int [pk, increment, note: 'Surrogate key']
  entity_id           varchar(20) [not null, ref: > dim_entity.entity_id]
  fiscal_year         int [not null]
  fiscal_period       int [not null, note: '1–12 or 1–13']
  fiscal_period_name  varchar(20)        // e.g., "FY25 P03" or "Apr FY25"
  start_date          date [not null]
  end_date            date [not null]
  quarter_number      int
  is_close_complete   boolean [not null, default: false]
}

Table bridge_date_fiscal {
  bridge_id          int [pk, increment]
  date_key           int         [not null, ref: > dim_date.date_key]
  fiscal_calendar_id int         [not null, ref: > dim_fiscal_calendar.fiscal_calendar_id]
  entity_id          varchar(20) [not null, ref: > dim_entity.entity_id] // redundant for fast joins
  fiscal_year        int         [not null]
  fiscal_period      int         [not null]
  fiscal_period_name varchar(20)
}

Table dim_holiday {
  holiday_id       int [pk, increment]
  holiday_date     date         [not null]
  holiday_name     varchar(100) [not null]
  country_code     varchar(2)
  entity_id        varchar(20)  [ref: > dim_entity.entity_id] // null = org-wide
  is_org_wide      boolean      [not null, default: false]
  is_half_day      boolean      [not null, default: false]
  remarks          varchar(255)
}

Developer Notes¶

All fact tables (balances, invoices, P&L, payments, etc.) must include date_key and join through dim_date.
For fiscal reporting, facts must also join via bridge_date_fiscal to ensure proper mapping to fiscal years/periods.
UI filters (year, quarter, month, period) must be sourced from dim_fiscal_calendar and dim_date.
Holiday exclusions in KPIs (DSO, DPO, etc.) must reference dim_holiday.