Como criar uma dimensão de data num Data Warehouse

Quase todos os relatórios de BI filtram ou agrupam por tempo — por ano, por mês, por trimestre. Ter uma dimensão de data (uma tabela de calendário) no Data Warehouse é a maneira mais limpa e rápida de o conseguir, em vez de calcular datas repetidamente em cada consulta. A seguir vês, passo a passo, como criar uma dimensão de data em SQL e preenchê-la com um script simples e reutilizável.

Pré-requisitos

• Uma base de dados SQL Server ou Azure SQL (o conceito aplica-se a qualquer Data Warehouse SQL).
• Permissões para criar esquemas e tabelas (CREATE TABLE).
• Noções básicas de SQL (CREATE, INSERT, SELECT).

Passo 1: Perceber o que é uma dimensão de data

Uma dimensão de data tem uma linha por dia e várias colunas descritivas: ano, mês, nome do mês, trimestre, dia da semana e um indicador de fim de semana. A chave da tabela costuma ser um número inteiro no formato AAAAMMDD (por exemplo, 20260704), a que se chama chave substituta (surrogate key). É pequena, ordenável e fácil de ligar às tuas tabelas de factos.

Passo 2: Criar a tabela

Começamos por criar um esquema dim (opcional, mas ajuda a organizar) e a tabela que vai guardar o calendário. Dá a cada coluna o tipo mais pequeno que chegue — um detalhe simples que poupa espaço numa tabela com milhares de linhas.

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
);

Passo 3: Gerar as datas com um CTE recursivo

Para preencher a tabela precisamos de uma linha por dia entre duas datas. Um CTE recursivo gera essa sequência sem precisares de tabelas auxiliares. Define o intervalo (por exemplo, de 2015 a 2035) e deixa o SQL contar os dias um a um.

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);

Erro comum: por omissão, o SQL Server pára um CTE recursivo aos 100 níveis e devolve o erro "maximum recursion". A cláusula OPTION (MAXRECURSION 0) remove esse limite.

Passo 4: Inserir os dados com colunas calculadas

Agora combinamos o CTE com funções de data para preencher todas as colunas. Repara em duas boas práticas: a chave DataKey é criada com o estilo 112 (formato AAAAMMDD) e o dia da semana é calculado com uma fórmula que não depende das definições de idioma do servidor (@@DATEFIRST), devolvendo sempre segunda-feira = 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);

O SET LANGUAGE Portuguese; faz com que DATENAME devolva os nomes em português ("janeiro", "segunda-feira"). Se preferires outro idioma, muda apenas esta linha.

Verificar o resultado

Confirma que a carga correu bem com duas consultas rápidas: uma para ver as primeiras linhas e outra para contar os dias e detetar 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;

Se TotalDias for igual a ChavesUnicas, não há duplicados. Para o intervalo de 2015 a 2035 deves obter 7 670 linhas.

Conclusão

Com uma dimensão de data pronta, as tuas medidas de tempo (year-to-date, comparações homólogas, filtros por trimestre) ficam muito mais simples e rápidas. O próximo passo é ligar a coluna DataKey às tabelas de factos e, se usares Power BI, marcar esta tabela como "tabela de datas" para ativar a time intelligence. Que outros atributos fazem sentido no teu negócio — feriados, semana fiscal, época alta?