A concepção da dimensão data num sistema de BI

Um texto escrito por Pedro Miguel Perfeito, de Lisboa, Portugal, para o site de suporte da Microsoft PT. Portanto, os detalhes técnicos apresentados referem-se ao SQLSERVER.

INTRODUÇÃO

A dimensão mais comum e mais utilizada em praticamente todos os sistemas de Business Intelligence (BI) é a dimensão data ou DimData. São várias as propostas de implementação relativas a esta dimensão, mas normalmente desatualizadas, complexas ou algumas apesar de interessantes, aplicam-se apenas a contextos específicos sem um enquadramento face a um sistema de BI.

Este artigo tem como objetivo explicar de forma clara e objetiva a criação da dimensão data ou tempo num sistema de BI. É uma dimensão/perspectiva de análise utilizada em praticamente todos os modelos dimensionais e muitas vezes “reciclada” numa mesma base de dados. Ou seja, fisicamente é criada apenas uma única tabela, mas em termos lógicos pode existir mais que uma como, por exemplo, a data de uma venda, data de uma entrega de encomenda, data de contratação, entre outras normalmente conhecidas como role-play dimensions.

ESTRUTURA DA DIMENSÃO DATA

A estrutura de uma dimensão data é caracterizada na grande maioria dos casos por uma única tabela desnormalizada que apesar da conseqüente redundância, permite uma maior intuição e melhor performance nas consultas realizadas pelo utilizado final.

Figura 01. Estrutura de dimensão data proposta

Neste artigo, a dimensão data inclui os atributos normalmente mais utilizados como ano, trimestre, mês, semana e dia, para além de duas hierarquias CalendarioPorMes e CalendarioPorSemana. Este exemplo poderá ser a base para mais complexas implementações como inclusão do ano fiscal, atributos como feriados, nome dias da semana (terça-feira, domingo…) ou ainda outras especificações dependendo naturalmente dos requisitos necessários de cada sistema.

É boa prática na modelagem dimensional recorrer-se a criação de chaves substitutas nas dimensões/perspectivas de análise, contudo a sua implementação não é obrigatória, exceto no caso de aplicação da técnica apresentada por Ralph Kimball para dimensões de variação lenta tipo 2: SCD2.

Esta chave substituta para a dimensão data é também conhecida por ser uma chave substituta smartkey, onde o seu valor possui informação (dados com contexto), ou seja, através, por exemplo, do número inteiro 20091203, rapidamente percebemos que esta chave corresponde a um registro correspondente da data de 3 de Dezembro de 2009. Caso fosse um inteiro Identity sequencial (normalmente utilizado em restantes chaves substitutas de outras dimensões) na visualização da tabela de fatos, seria necessário realizar sempre uma junção (join) com a dimensão data para ter a correspondência entre esse número inteiro Identity sequencial (ex: 4890) e a data correspondente.

Apesar de terem existido várias abordagens relativamente à chave da dimensão data, o uso desta smartkey é hoje em dia praticamente unânime, sendo as suas vantagens reais principalmente na:

    1. Contextualização de cada registro na tabela de fatos
    2. Na redução de risco na passagem entre ambientes (desenvolvimento/qualidade/produção), evitando a existência de chaves diferentes para uma mesma data.
    3. Para processamento das partições do cubo multidimensional na base dados OLAP
    4. Aplicação das técnicas de data mining através de ferramentas Microsoft onde é sugerido o formato YYYYMMDD.

A figura seguinte representa a estrutura de dados normalizada aplicada neste artigo para a dimensão data. Através desta estrutura será mais fácil compreender as duas hierarquias existentes, bem como compreender as chaves únicas que devem estar associadas a cada nível de uma hierarquia de modo a garantir a relação de ‘um-para-muitos’ existente em qualquer hierarquia.

Figura 02. Exemplo de uma estrutura de dados normalizada para a dimensão data

No final, a dimensão data terá então uma estrutura desnormalizada, onde as hierarquias assumem um papel fundamental, permitindo o drill-down e o roll-up sobre a informação armazenada na tabela de fatos.

Figura 03. Exemplo de uma estrutura de dados desnormalizada para a dimensão data.

Naturalmente que os campos chaves que permitem criar as hierarquias, podem ser escondidos do usuário final, que apenas tem interesse pelos descritivos como a data, a semana, mês, trimestre e ano. Estes descritivos poderão inclusive, ser alterados face ao proposto neste artigo, tendo apenas em atenção a atualização do seu comprimento em caso de alteração. (Ex: “T1” para “2009 T1” ou “Janeiro” para “Janeiro 09”…)

PROCESSO DE CARGA – ETL

O processo de povoamento de um Data Warehouse refere-se a todo o trabalho realizado na extração, transformação e carga dos dados operacionais num repositório central normalmente designado por Data Warehouse ou Data Mart no caso de um âmbito mais reduzido. Este processo de povoamento é normalmente caracterizado por dois tipos de povoamento: o povoamento inicial, onde é preparado todo o sistema com o carregamento inicial de todas as estruturas de dados do Data Warehouse, e o povoamento regular ou periódico, onde é realizado periodicamente a atualização da informação armazenada no Data Warehouse (dependendo dos requisitos do negócio, poderá ser necessária uma atualização de hora em hora, diária, semanal, mensal, entre outros…)

Relativamente à dimensão data, será descrito a seguir, uma abordagem possível para o processo de povoamento da mesma. Naturalmente algum ajuste poderá ser equacionado, não só para refletir os requisitos de negócio, como também para refletir a arquitetura técnica que irá suportar o sistema de Business Intelligence a implementar.

Este processo de povoamento requer praticamente em todos os casos, de uma área de retenção (Staging Area) que poderá ser uma área em memória ou uma área física como abordada neste artigo. No caso de optar por não utilizar esta memória física, poderá alojar a tabela “preDimData” diretamente no Data Mart criado neste artigo. A inclusão ou não de uma base de dados para suporte ao processo de povoamento, requer uma análise de custo-benefício, pois a sua manutenção implica custos que poderão ser justificados pelo volume/complexidade de cada sistema a implementar.

Esta abordagem caracteriza-se numa primeira fase pelo carregamento inicial das datas a considerar no sistema, e depois uma segunda fase de atualização periódica com a inserção das últimas datas em falta no período. A figura 04 descreve de uma forma mais clara a abordagem seguida:

Figura 04. Arquitetura proposta para a dimensão data

Esta abordagem considera a existência de uma área de apoio ao ETL (Data Staging Area), caso contrário a tabela “preDimData” poderia estar alojada diretamente no Data Mart.

No passo 01 (através do script 01. Criação Tabela preDimData.sql), é criada fisicamente a tabela “preDimData”, onde todas as suas colunas são calculadas automaticamente através de computed columns, exceto a coluna data que é a base de cálculo das anteriores.

No passo 02 (através do script 02. Carregamento Inicial Tabela preDimData.sql) é realizado o carregamento desta mesma tabela, que poderá inclusive carregar datas de próximos anos no caso de não pretender atualizar periodicamente a dimensão data com as datas em falta no período.

No passo 03 (através do script 03. Criar DimData.sql) é criada fisicamente a tabela DimData no Data Mart.

Se optar pela atualização periódica desta dimensão, poderá fazê-lo automaticamente através da stored procedure do passo 4 descrito na figura 04.

Depois de atualizada a tabela “preDimData” será necessário refletir a atualização também na tabela DimData existente no Data Mart em estudo.

Uma possibilidade será pela criação de um simples lookup, ou por algo mais elaborado recorrendo à de Change Data Capture que automaticamente recolhe as alterações ocorridas desde a ultima atualização.

Caso pretenda criar e carregar uma única vez a dimensão data com todos os anos atuais e futuros (sem necessidade de atualização periódica) poderá ficar apenas pelos passos 01 e 02. (renomeando naturalmente a tabela de preDimData para um nome mais elucidativo como DimData).

CONCLUSÃO

Pretendeu-se com este artigo exemplificar a concepção da dimensão data e o seu enquadramento num sistema de BI. O objetivo foi simplificar e contribuir com uma base de “trabalho” para futuras implementações que deverão ser adaptadas à realidade de cada projeto. Como terá observado, várias possibilidades poderão ser agora seguidas com base neste artigo.

script 01.Criação Tabela preDimData.sql

------------------------------------------------------------------
-- Nome: 01.Criação Tabela preDimData.sql
-- Autor: Pedro Perfeito (pperfeito@hotmail.com)
-- Descricao:     Script de SQL responsável pela criação da tabela
--                      preDimdata. Todas as colunas excepto a data são
--                      computed columns.
-- Data Criação: 2010-07-14
-- Data Alteracao: -
-- Alterado por: -
-- Comentários: No caso de pretender alterar o comprimento
--                      dos descritivos, bastará alterar a função
--                      convert da coluna respectiva
----------------------------------------------------------------
CREATE TABLE [dbo].[preDimData](
 [Data_dimkey] AS CONVERT(INTEGER,(CONVERT([char](4),datepart(year,[data]),(0))
 + SUBSTRING(('0' + CONVERT([char](2),datepart(month,[data]),(0))),LEN(CONVERT([char](2),datepart(month,[data]))),2)
 + SUBSTRING(('0' + CONVERT([char](2),datepart(day,[data]),(0))),LEN(CONVERT([char](2),datepart(day,[data]))),2))),
 [Ano]  AS (datepart(year,[data])),
 [TrimestreID]  AS CONVERT(char(6),CONVERT([char](4),datepart(year,[data]),0)+'T'+CONVERT([char](2),datename(quarter,[data]),0)),
 [TrimestreNome]  AS CONVERT(char(2),('T'+CONVERT([char](2),datename(quarter,[data]),0))),
 [MesID]  AS CONVERT(char(7),(CONVERT([char](4),datepart(year,[data]),(0))+'M'+CONVERT([char](2),datepart(month,[data]),(0)))),
 [MesNome]  AS CONVERT(varchar(20),(datename(month,[data]))),
 [SemanaID]  AS CONVERT(char(7),(CONVERT([char](4),datepart(year,[data]),(0))+ 'S'
 + SUBSTRING(('0' + CONVERT([char](2),datepart(week,[data]),(0))),LEN(CONVERT([char](2),datepart(week,[data]))),2))),
 [SemanaNome] AS  CONVERT(char(9),'Semana '+ SUBSTRING(('0' + CONVERT([char](2),datepart(week,[data]),(0))),LEN(CONVERT([char](2),datepart(week,[data]))),2)),
 [SemanaDia]  AS datepart(weekday,[data]),
 [Data] [datetime] NOT NULL
) ON [PRIMARY]

02.Carregamento Inicial Tabela preDimData.sql

----------------------------------------------------------------
-- Nome: 02.Carregamento Inicial Tabela preDimData.sql
-- Autor: Pedro Perfeito (pperfeito@hotmail.com)
-- Descricao: Script de SQL responsável pelo carregamento da
--                         tabela preDimdata.
-- Data Criação: 2010-07-14
-- Data Alteracao: -
-- Alterado por: -
-- Comentários: Por default a primeira data a ser carregada é a
--                         data 2000-01-01, enquanto que a ultima data a
--                         ser carregada é a data de execução (Getdate()).
--                         Estas datas podem ser alteradas.
----------------------------------------------------------------
DECLARE @Day datetime
DECLARE @i INT
DECLARE @nrows INT
 -- Actualizar aqui a data inicial
SET @Day = '2000-01-01'
SET @i = 0
--SET LANGUAGE Portuguese
SET LANGUAGE brazilian -- Usado brasileiro para colocar mes com primeira letra a maisculo
 -- Por default a ultima data criada é a data de execução
-- Poderá ser colocada uma outra data superior
SET @nrows = DATEDIFF(d, @Day, GETDATE()) + 1
 WHILE @i < @nrows
BEGIN
INSERT preDimData (Data) values (@Day)
SET @Day = DATEADD(d, 1, @Day)
SET @i = @i + 1
END
 -- Nrº Registos inseridos
SELECT COUNT(*) AS NrDatasCriadas FROM preDimData
-- Resultado final após carregamento
SELECT * FROM preDimData

script 03.Criar DimData.sql

----------------------------------------------------------------
-- Nome: 03.Carregamento Periodico Tabela preDimData.sql
-- Autor: Pedro Perfeito (pperfeito@hotmail.com)
-- Descricao:     Stored procedure responsável pela actualização
--                      diária da tabela preDimdata
-- Data Criação: 2010-07-14
-- Data Alteracao: -
-- Alterado por: -
-- Comentários: -
----------------------------------------------------------------
CREATE PROCEDURE usp_upd_preDimData
AS
DECLARE @Date DATETIME
DECLARE @i INT
DECLARE @nrows INT
-- Verificar ultima data existente
SELECT @Date=max(Data)+1 FROM preDimData
SET @i = 0
-- Numero de datas a actualizar
SET @nrows = datediff(d, @Date, getdate()) + 1
-- Inserir cada uma das datas em falta
WHILE @i < @nrows
BEGIN
INSERT preDimData (Data) VALUES (@Date)
SET @Date = dateadd(d, 1, @Date)
SET @i = @i + 1
END

— Retorna nrº de datas actualizadas

RETURN @nrows

04.Criar DimData.sql

 ----------------------------------------------------------------
-- Nome: 04.Criar DimData.sql
-- Autor: Pedro Perfeito (pperfeito@hotmail.com)
-- Descricao:     Script de SQL responsável pela criação da tabela
--                      DimData com base na tabela preDimData
-- Data Criação: 2010-07-14
-- Data Alteracao: -
-- Alterado por: -
-- Comentários: -
----------------------------------------------------------------
--SET LANGUAGE Portuguese
SET LANGUAGE brazilian -- Usado brasileiro para colocar mes com primeira letra a maisculo
SELECT *
INTO DimData
FROM preDimData
GO
ALTER TABLE DimData ALTER COLUMN data_dimkey int not null
GO
ALTER TABLE DimData ADD CONSTRAINT pk_data PRIMARY KEY CLUSTERED ([data_dimkey])

SELECT * FROM DimData

Até a próxima

2 Respostas to “A concepção da dimensão data num sistema de BI”

  1. PedroCGD Says:

    Ola Lito,
    Muito obrigado pela referência ao meu artigo. É uma honra enorme! Cumprimentos e força com o trabalho magnifico que tem feito neste blog.
    Pedro Perfeito
    MVP, CBIP


Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: