Cómo crear una dimensión de fecha en un Data Warehouse

Casi todos los informes de BI filtran o agrupan por tiempo — por año, por mes, por trimestre. Tener una dimensión de fecha (una tabla de calendario) en tu Data Warehouse es la forma más limpia y rápida de lograrlo, en lugar de calcular fechas una y otra vez en cada consulta. A continuación verás, paso a paso, cómo crear una dimensión de fecha en SQL y rellenarla con un script sencillo y reutilizable.

Requisitos previos

• Una base de datos SQL Server o Azure SQL (el concepto se aplica a cualquier Data Warehouse SQL).
• Permisos para crear esquemas y tablas (CREATE TABLE).
• Nociones básicas de SQL (CREATE, INSERT, SELECT).

Paso 1: Entender qué es una dimensión de fecha

Una dimensión de fecha tiene una fila por día y varias columnas descriptivas: año, mes, nombre del mes, trimestre, día de la semana y un indicador de fin de semana. La clave de la tabla suele ser un número entero con formato AAAAMMDD (por ejemplo, 20260704), llamada clave subrogada (surrogate key). Es pequeña, ordenable y fácil de unir a tus tablas de hechos.

Paso 2: Crear la tabla

Empezamos creando un esquema dim (opcional, pero ayuda a organizar) y la tabla que guardará el calendario. Asigna a cada columna el tipo más pequeño que sirva — un detalle simple que ahorra espacio en una tabla con miles de filas.

CREATE SCHEMA dim;
GO

CREATE TABLE dim.Data (
    DataKey     int         NOT NULL PRIMARY KEY,
    Data        date        NOT NULL,
    Ano         smallint    NOT NULL,
    Trimestre   tinyint     NOT NULL,
    Mes         tinyint     NOT NULL,
    Dia         tinyint     NOT NULL,
    NomeMes     varchar(20) NOT NULL,
    DiaSemana   tinyint     NOT NULL,
    NomeDia     varchar(20) NOT NULL,
    FimDeSemana bit         NOT NULL
);

Paso 3: Generar las fechas con un CTE recursivo

Para rellenar la tabla necesitamos una fila por día entre dos fechas. Un CTE recursivo genera esa secuencia sin tablas auxiliares. Define el intervalo (por ejemplo, de 2015 a 2035) y deja que SQL cuente los días uno a uno.

DECLARE @Inicio date = '2015-01-01';
DECLARE @Fim    date = '2035-12-31';

WITH Calendario AS (
    SELECT @Inicio AS Data
    UNION ALL
    SELECT DATEADD(DAY, 1, Data)
    FROM Calendario
    WHERE Data < @Fim
)
SELECT Data FROM Calendario
OPTION (MAXRECURSION 0);

Error común: por defecto, SQL Server detiene un CTE recursivo a los 100 niveles y devuelve el error "maximum recursion". La cláusula OPTION (MAXRECURSION 0) elimina ese límite.

Paso 4: Insertar los datos con columnas calculadas

Ahora combinamos el CTE con funciones de fecha para rellenar todas las columnas. Fíjate en dos buenas prácticas: la clave DataKey se crea con el estilo 112 (formato AAAAMMDD) y el día de la semana se calcula con una fórmula que no depende de la configuración de idioma del servidor (@@DATEFIRST), devolviendo siempre lunes = 1.

SET LANGUAGE Portuguese;

DECLARE @Inicio date = '2015-01-01';
DECLARE @Fim    date = '2035-12-31';

WITH Calendario AS (
    SELECT @Inicio AS Data
    UNION ALL
    SELECT DATEADD(DAY, 1, Data)
    FROM Calendario
    WHERE Data < @Fim
)
INSERT INTO dim.Data
    (DataKey, Data, Ano, Trimestre, Mes, Dia, NomeMes, DiaSemana, NomeDia, FimDeSemana)
SELECT
    CAST(CONVERT(char(8), Data, 112) AS int)              AS DataKey,
    Data,
    YEAR(Data)                                            AS Ano,
    DATEPART(QUARTER, Data)                               AS Trimestre,
    MONTH(Data)                                           AS Mes,
    DAY(Data)                                             AS Dia,
    DATENAME(MONTH, Data)                                 AS NomeMes,
    ((@@DATEFIRST + DATEPART(WEEKDAY, Data) - 2) % 7) + 1 AS DiaSemana,
    DATENAME(WEEKDAY, Data)                               AS NomeDia,
    CASE WHEN ((@@DATEFIRST + DATEPART(WEEKDAY, Data) - 2) % 7) + 1 >= 6
         THEN 1 ELSE 0 END                                AS FimDeSemana
FROM Calendario
OPTION (MAXRECURSION 0);

La instrucción SET LANGUAGE Portuguese; hace que DATENAME devuelva los nombres en portugués ("janeiro", "segunda-feira"). Si prefieres otro idioma, cambia solo esa línea.

Verificar el resultado

Confirma que la carga funcionó con dos consultas rápidas: una para ver las primeras filas y otra para contar los días y detectar duplicados.

SELECT TOP 10 * FROM dim.Data ORDER BY DataKey;

SELECT COUNT(*)              AS TotalDias,
       COUNT(DISTINCT DataKey) AS ChavesUnicas,
       MIN(Data)               AS Inicio,
       MAX(Data)               AS Fim
FROM dim.Data;

Si TotalDias es igual a ChavesUnicas, no hay duplicados. Para el intervalo de 2015 a 2035 deberías obtener 7 670 filas.

Conclusión

Con una dimensión de fecha lista, tus medidas de tiempo (year-to-date, comparaciones interanuales, filtros por trimestre) se vuelven mucho más simples y rápidas. El siguiente paso es unir la columna DataKey a tus tablas de hechos y, si usas Power BI, marcar esta tabla como "tabla de fechas" para activar la time intelligence. ¿Qué otros atributos tienen sentido para tu negocio — festivos, semana fiscal, temporada alta?