How to use a role-playing dimension in a Data Warehouse

João Barros 04 de July de 2026 4 min read

In a sale, the order date, the ship date and the delivery date all point to the same calendar, yet they mean different things. A role-playing dimension solves exactly this: it reuses a single dimension table in several roles within the same Data Warehouse, keeping the model simple, consistent and easy to maintain. It is a classic pattern from Kimball dimensional modelling and shows up in almost every real BI project.

Prerequisites

A Data Warehouse with a star schema (SQL Server, Azure Synapse, Microsoft Fabric or equivalent).
A date dimension (DimData) already created and populated.
A fact table with more than one date column.
Basic SQL knowledge (JOIN and views).

Step 1: Identify the multiple dates in the fact table

Picture a FactVendas table with three different dates: when the order was placed, when it was shipped and when it was delivered. Each one is a foreign key pointing to the same date dimension. Storing several dates is normal — they represent the order lifecycle.

CREATE TABLE FactVendas (
    VendaKey          INT           NOT NULL,
    DataEncomendaKey  INT           NOT NULL,
    DataEnvioKey      INT           NOT NULL,
    DataEntregaKey    INT           NOT NULL,
    ProdutoKey        INT           NOT NULL,
    Quantidade        INT           NOT NULL,
    ValorTotal        DECIMAL(12,2) NOT NULL
);

Step 2: Reuse the same dimension with aliases

The most common mistake is to create three separate date tables. There is no need: just join the same DimData three times, giving each role a different alias. Duplicating the dimension would mean maintaining three ETL processes, using more storage and risking inconsistencies when the calendar changes; reusing it avoids all of that.

SELECT
    f.VendaKey,
    de.Data AS DataEncomenda,
    dv.Data AS DataEnvio,
    dt.Data AS DataEntrega,
    f.ValorTotal
FROM FactVendas AS f
JOIN DimData AS de ON f.DataEncomendaKey = de.DataKey
JOIN DimData AS dv ON f.DataEnvioKey     = dv.DataKey
JOIN DimData AS dt ON f.DataEntregaKey   = dt.DataKey;

Each alias (de, dv, dt) represents a role of the same dimension. This keeps a single source of truth for the calendar, with no duplicated data.

Step 3: Create views to make each role explicit

So that analysts and BI tools see clear names, create one view per role, renaming the columns. This is the cleanest way to expose a role-playing dimension in a Data Warehouse, and views take up virtually no space.

CREATE VIEW dim.DataEncomenda AS
SELECT
    DataKey  AS DataEncomendaKey,
    Data     AS DataEncomenda,
    Ano      AS AnoEncomenda,
    NomeMes  AS MesEncomenda
FROM DimData;
GO

CREATE VIEW dim.DataEnvio AS
SELECT
    DataKey  AS DataEnvioKey,
    Data     AS DataEnvio,
    Ano      AS AnoEnvio,
    NomeMes  AS MesEnvio
FROM DimData;

Repeat the same pattern for dim.DataEntrega. Every view points to the same DimData, but presents names specific to each role.

Step 4: Query through the views

Now the queries become readable and each date carries its own context — ideal, for example, for comparing the total by order year with the total by ship year.

SELECT
    e.AnoEncomenda,
    v.AnoEnvio,
    SUM(f.ValorTotal) AS Total
FROM FactVendas AS f
JOIN dim.DataEncomenda AS e ON f.DataEncomendaKey = e.DataEncomendaKey
JOIN dim.DataEnvio     AS v ON f.DataEnvioKey     = v.DataEnvioKey
GROUP BY e.AnoEncomenda, v.AnoEnvio;

Check the result

Run two simple checks. First, the number of rows when using the views should match the fact table — if it is lower, you have lost rows in a JOIN:

SELECT COUNT(*) AS TotalLinhas
FROM FactVendas AS f
JOIN dim.DataEncomenda AS e ON f.DataEncomendaKey = e.DataEncomendaKey
JOIN dim.DataEnvio     AS v ON f.DataEnvioKey     = v.DataEnvioKey;

Then group first by AnoEncomenda and then by AnoEnvio: if the totals differ, you have confirmed that each role works independently.

Conclusion

With a role-playing dimension you avoid duplicating tables, guarantee a consistent calendar and make reports far clearer. The next step is to take this model into Power BI: since only one active relationship can exist between two tables, you will use inactive relationships with USERELATIONSHIP or copies of the dimension. Which other dimension in your model — such as customer or geography — could benefit from multiple roles?