Role-Playing Dimensions in a Star Schema: Step by Step

In a data warehouse it is common for a fact table to store several dates — the order date, the ship date and the due date. Building a separate date dimension for each one multiplies maintenance and invites inconsistencies. Role-playing dimensions solve this: a single dimension, reused in several roles within the same star schema. The name comes from theatre — the same "actor" (the dimension) appears on stage in different roles.

Prerequisites

• A data warehouse with a date dimension (DimDate) already built, using a surrogate key.
• A fact table with more than one date (for example, FactSales).
• Access to SQL Server or an equivalent engine and, for the second approach, a Power BI model.
• Basic knowledge of the star schema (facts and dimensions).

Step 1: Design the fact table with several dates

Start by making sure the fact table has one foreign key per relevant date, all pointing to the same DimDate. Note that there are not three dimensions: there is only one, referenced several times. Dates that have not happened yet (ship, due) can stay NULL until they occur.

CREATE TABLE FactSales (
    SalesKey     BIGINT IDENTITY(1,1) PRIMARY KEY,
    OrderDateKey INT NOT NULL,   -- FK to DimDate
    ShipDateKey  INT NULL,       -- FK to DimDate
    DueDateKey   INT NULL,       -- FK to DimDate
    ProductKey   INT NOT NULL,
    Quantity     INT NOT NULL,
    Amount       DECIMAL(18,2) NOT NULL
);

Step 2: Keep a single date dimension

Do not duplicate DimDate. One table — with a row per day and columns such as CalendarYear, quarter, month and weekday — serves every role. This way "Q1" means exactly the same regardless of which date you are analysing, and maintenance stays in one place.

Step 3: Approach A — role views in the database

When the model is consumed directly through SQL, the clearest option is to expose the same dimension through views with meaningful names. Each view represents one role of DimDate.

CREATE VIEW DimOrderDate AS SELECT * FROM DimDate;
GO
CREATE VIEW DimShipDate  AS SELECT * FROM DimDate;
GO
CREATE VIEW DimDueDate   AS SELECT * FROM DimDate;
GO

Each join then uses its matching view, making the query easy to read:

SELECT od.CalendarYear AS OrderYear,
       SUM(f.Amount)   AS Sales
FROM FactSales AS f
JOIN DimOrderDate AS od ON od.DateKey = f.OrderDateKey
GROUP BY od.CalendarYear
ORDER BY od.CalendarYear;

This approach is simple and universal: any tool that reads SQL sees three dimensions with distinct names. The trade-off is managing more database objects, so it helps to document that they all share the same DimDate.

Step 4: Approach B — multiple relationships in the semantic model

In Power BI or Analysis Services, import DimDate once and create one relationship for each date key in the fact table. The engine allows only one active relationship between two tables; the rest stay inactive (dashed in the diagram). This avoids ambiguity: by default the engine needs to know which path to follow between the tables. The active relationship is usually the order date.

To analyse by an inactive date, activate the relationship only inside the measure, with USERELATIONSHIP:

Sales by Ship Date =
CALCULATE (
    SUM ( FactSales[Amount] ),
    USERELATIONSHIP ( FactSales[ShipDateKey], DimDate[DateKey] )
)

The normal sales measure keeps using the order date; this new measure filters by the ship date, with no need to duplicate the dimension.

Tip: give each role measure a clear name — "Sales by Ship Date", "Sales by Due Date" — so whoever reads the report immediately knows which date is in use.

Verify the result

Put the two measures side by side in a matrix, with DimDate years on the rows. If the yearly totals differ between the order measure and the ship measure, the roles are working: each filters by a distinct date. In the SQL approach, compare the total grouped by DimOrderDate with the same total grouped by DimShipDate — the values should diverge whenever the order and the shipment fall in different years.

Conclusion

With a single date dimension reused in several roles, the model becomes simpler, more consistent and easier to maintain. The same technique applies to other dimensions — for example, a DimEmployee in the roles of "salesperson" and "approver". Which of your business dates deserves a measure of its own?