Designing a Date Dimension with Role-Playing Views

Hello! In the previous lesson, you used hierarchy levels to make rollups unambiguous. A calendar is a familiar balanced hierarchy, but an order line may have several different dates: when it was ordered, when shipment was requested, and when it actually shipped. Those dates can share calendar definitions without sharing a business meaning.

In this lesson, you’ll design a day-grain date dimension, connect two date roles to an order-line fact, and expose those roles through PostgreSQL views. The result should let a report author ask “ordered in January” and “requested for shipment in February” without confusing the two. Estimated study time: about 40 minutes.

One calendar row per day

A date dimension has one row per calendar date. Unlike an operational date column, it carries the labels and classifications analysts repeatedly need: calendar year, month, weekday, fiscal period, and potentially business-specific holidays. The date itself identifies a day; the other attributes describe how that day participates in reporting.

Start with the “Calendar and time” section of Modeling Dimension Tables in Warehouse from Microsoft Learn. Concentrate on the “Date dimension” subsection: its one-row-per-date grain, suggested attributes, future-date coverage, and special members. You can leave the adjoining “Time dimension” subsection for another occasion.

For a concrete design, suppose the business asks for order quantity and net amount by order month and by requested shipment month. A useful date row might contain:

The fiscal values assume a fiscal year that starts in July and is named for the year in which it ends. That convention is an example, not something to infer from a source timestamp: fiscal boundaries and year names require an agreed business definition. Notice also why calendar_year_month is useful. Grouping on the text “February” alone would combine February from every year.

Date keys such as 20250203 are a common exception to the usual preference for meaningless dimension surrogate keys. They are compact and recognizable, but reports should group and filter on date attributes, not parse meaning from the key. Reserve special keys for dates that cannot yet be resolved.

Build and populate the date dimension

The following PostgreSQL example establishes the grain and generates two years of ordinary dates. It keeps time of day out of the dimension. Its holiday attribute is left unpopulated: a holiday classification would need an agreed calendar rather than a guess based on weekday.

CREATE TABLE dim_date (
    date_key             integer PRIMARY KEY,
    calendar_date        date UNIQUE,
    calendar_year        integer,
    calendar_quarter     integer,
    calendar_month       integer,
    calendar_month_label text,
    calendar_year_month  integer,
    iso_week_year        integer,
    iso_week             integer,
    fiscal_year          integer,
    fiscal_period        integer,
    fiscal_quarter       integer,
    is_weekend           boolean,
    holiday_label        text
);

-- The date is unknown, or a planned date has not yet been determined.
INSERT INTO dim_date (date_key, holiday_label)
VALUES
    (0, 'Unknown date'),
    (-1, 'Not yet determined');

WITH dates AS (
    SELECT day_ts::date AS calendar_date
    FROM generate_series(
        '2025-01-01'::date,
        '2026-12-31'::date,
        interval '1 day'
    ) AS g(day_ts)
)
INSERT INTO dim_date (
    date_key, calendar_date, calendar_year, calendar_quarter,
    calendar_month, calendar_month_label, calendar_year_month,
    iso_week_year, iso_week, fiscal_year, fiscal_period,
    fiscal_quarter, is_weekend
)
SELECT
    to_char(calendar_date, 'YYYYMMDD')::integer,
    calendar_date,
    EXTRACT(year FROM calendar_date)::integer,
    EXTRACT(quarter FROM calendar_date)::integer,
    EXTRACT(month FROM calendar_date)::integer,
    to_char(calendar_date, 'FMMonth'),
    to_char(calendar_date, 'YYYYMM')::integer,
    EXTRACT(isoyear FROM calendar_date)::integer,
    EXTRACT(week FROM calendar_date)::integer,
    EXTRACT(year FROM calendar_date)::integer
        + CASE WHEN EXTRACT(month FROM calendar_date) >= 7
               THEN 1 ELSE 0 END,
    ((EXTRACT(month FROM calendar_date)::integer + 5) % 12) + 1,
    (((EXTRACT(month FROM calendar_date)::integer + 5) % 12) / 3) + 1,
    EXTRACT(isodow FROM calendar_date) IN (6, 7)
FROM dates;

The distinction between calendar_year and iso_week_year is intentional. Near a year boundary, an ISO week can belong to a different week-year than the date’s calendar year. Likewise, is_weekend implements a Saturday/Sunday convention; replace it if the business calendar uses another one.

The special rows have no calendar_date or period attributes. That prevents an unknown planned date from being presented as a real day or included in a monthly total under an invented month. A fact can still reference their keys instead of carrying a null foreign key. Extend the generated range before loading facts with later dates; the dimension is useful only if its real-date rows cover the dates the facts need.

Two business dates, one physical calendar

Now declare the fact grain: one row represents one order line. An order date and a requested ship date are each single-valued for that row under this example’s business rules. The fact therefore needs two independently named foreign keys.

Watch Role Playing Dimensions from LeapFrogBI. The first segment establishes why two fact foreign keys can reference one dimension; the second shows why views give each role clearer names.

The diagram below uses requested ship date, not actual ship date. That qualification matters: a customer’s requested date may differ from the date a shipment leaves the warehouse. If both are analytical requirements, they need separately named roles.

In PostgreSQL, a minimal fact table for this grain can enforce that both date keys resolve to the shared dimension:

CREATE TABLE fact_order_line (
    order_number            text NOT NULL,
    order_line_number       integer NOT NULL,
    order_date_key          integer NOT NULL
        REFERENCES dim_date (date_key),
    requested_ship_date_key integer NOT NULL
        REFERENCES dim_date (date_key),
    quantity                integer NOT NULL,
    net_amount              numeric(12, 2) NOT NULL,
    PRIMARY KEY (order_number, order_line_number)
);

Here (order_number, order_line_number) identifies the order-line event; the two date keys do not identify it. Multiple lines—or different orders—can legitimately share both dates. For a loaded row, resolve each source business date independently to dim_date.date_key. If the requested ship date has not been set, use the “Not yet determined” member; do not substitute the order date merely to satisfy the foreign key.

Expose role-playing views and query them safely

Both views below read the same physical rows. Their purpose is to present different names and relationships to report authors; they do not duplicate calendar data.

CREATE VIEW dim_order_date AS
SELECT
    date_key            AS order_date_key,
    calendar_date       AS order_date,
    calendar_year       AS order_year,
    calendar_month      AS order_month_number,
    calendar_year_month AS order_year_month,
    fiscal_year         AS order_fiscal_year,
    fiscal_period       AS order_fiscal_period
FROM dim_date;

CREATE VIEW dim_requested_ship_date AS
SELECT
    date_key            AS requested_ship_date_key,
    calendar_date       AS requested_ship_date,
    calendar_year       AS requested_ship_year,
    calendar_month      AS requested_ship_month_number,
    calendar_year_month AS requested_ship_year_month,
    fiscal_year         AS requested_ship_fiscal_year,
    fiscal_period       AS requested_ship_fiscal_period
FROM dim_date;

A view is not required for PostgreSQL to join the dimension twice: two aliases of dim_date could do that. The views make the intended roles explicit to someone exploring the reporting model. Their separate key names also make the joins harder to reverse accidentally.

Suppose two order lines contain the following data:

The same facts answer two different monthly questions. By order month, January has 80 and February has 50. By requested ship month, February has 130. Neither result is contradictory: the grouping role changed.

You can retain both contexts in one query:

SELECT
    od.order_year_month,
    rs.requested_ship_year_month,
    SUM(f.net_amount) AS net_amount
FROM fact_order_line AS f
JOIN dim_order_date AS od
  ON f.order_date_key = od.order_date_key
JOIN dim_requested_ship_date AS rs
  ON f.requested_ship_date_key = rs.requested_ship_date_key
WHERE od.order_date IS NOT NULL
  AND rs.requested_ship_date IS NOT NULL
GROUP BY
    od.order_year_month,
    rs.requested_ship_year_month
ORDER BY
    od.order_year_month,
    rs.requested_ship_year_month;

For the two rows above, the query reports 80 for the January-order/February-requested-ship combination and 50 for the February-order/February-requested-ship combination. Each view has one row per date key, so each fact row matches once in each role; joining both roles does not itself multiply the amount. The WHERE clauses deliberately exclude the special date members from this real-date comparison report. A completeness report would instead keep them and display their status explicitly.

There is one important boundary to this design. If an order line can have several requested shipment dates because it is rescheduled, you must decide whether this fact records the original request, the current request, or a particular dated event. Adding another date key does not, by itself, preserve every revision. That is a grain and history question, rather than a reason to create multiple copies of the calendar.

Conclusion

A reusable date dimension has one row per real day, descriptive calendar attributes, sufficient past and future coverage, and explicit members for unresolved dates. When a fact has several business dates, give each date its own foreign key and clearly named role-playing view, while retaining one physical calendar. The test of the design is whether “by order month” and “by requested ship month” are both easy to express—and yield the intended, different answers.

Next, you’ll consider degenerate and junk dimensions: two other ways to give analytical context a clear place without creating unnecessary dimension tables.