Bem-vindos a mais um post sobre modelação de dados em Qlik, este post irá descrever os tipos diferentes de Slowly Changing Dimensions. Como o próprio nome indica, Slowly Changing Dimensions (SCD) são dimensões que podem alterar com o passar do tempo, por exemplo: a morada de um cliente.
SCD são atributos mutáveis, naturalmente, não são factos mas é necessário definir uma metodologia para a atualização destes atributos tendo em conta a sua importância para o facto que estamos a medir e a sua periodicidade.
De acordo com estes factores, Kimbal definiu 8 técnicas de atualização destes atributos. Neste blog iremos detalhar todos mas apenas entraremos em detalhe nos 3 mais importantes:
A SCD do tipo 0 diz respeito a dimensões que nunca mudam de valor, tais como as datas ou os identificadores do registo. Esta técnica não requer nenhuma lógica de programação precisa, apenas a inserção de valores numa tabela.
A SCD do tipo 1 consiste em escrever por cima os novos valores, neste sentido esta técnica não guarda histórico. Sempre que um atributo é atualizado, o antigo valor é apagado.
A atualização dos atributos com esta técnica não tem impacto nos factos nem nas ligações do modelo de dados, uma vez que apenas se atuliza o registo.
Esta técnica é fácil de implementação no entanto, pode trazer alguns dissabores. Se no futuro se pretender guardar histórico, já não há como recuperar valores antigos uma vez que foram apagados. Assim como, após a atualização de um atributo com esta técnica, relatórios antigos irão ser atualizados com os novos valores, perdendo-se a imagem à data do facto.
Após uma atualização todas as agregações précalculadas terão que ser atualizadas com os novos valores.
Exemplos de aplicação do tipo 1:
Pseudo código de implementação de SCD tipo 1:
Qlik Script
// 1 - Carregar os dados atuais
Colaboradores:
LOAD
[Colaborador ID],
[Nome],
[Posição],
[Escritório]
FROM [Colaboradores.xlsx]
(ooxml, embedded labels, table is [$(vAno)]);
// 2 - Ler a tabela histórico e 3 - Concatenar
LET vFileExists = if(len(FileSize('Colaboradores.qvd')) > 0, -1, 0);
IF '$(vFileExists)' = '-1' THEN
CONCATENATE (Colaboradores)
LOAD
[Colaborador ID],
[Nome],
[Posição],
[Escritório]
FROM [Colaboradores.qvd] (qvd)
WHERE NOT Exists([Colaborador ID]);
END IF
// 4 - Atualizar a dimensão no histórico
STORE Colaboradores INTO 'Colaboradores.qvd';
A SCD do tipo 2 adiciona uma linha nova por cada atualização aos atributos. Esta técnica permite guardar o histórico dos atributos. De forma a gerir as novas linhas de histórico para o mesmo registo é necessário atualizar a dimensão como novos atributos:
Há sempre forma de otimizar os atributos de administração do Tipo 2, no entante estes 4 são os que recomendamos a fim de facilitar o manuzeamento dos dados no front-end.
Esta técnica ira duplicar a informação da dimensão, portanto teremos que alterar as nossas agregações sobre as dimensões. Por exemplo se quisermos saber quantos clientes temos, teremos que aplicar a cláusula distinct a fim de remover duplicados.
De seguida apresenta-se um exemplo da aplicação do tipo 2:
Pseudo código de implementação de SCD tipo 2:
Qlik Script
// 1 - Carregar os dados atuais
Colaboradores:
LOAD
[Colaborador ID],
[Nome],
[Posição],
[Escritório]
FROM [Colaboradores.xlsx]
(ooxml, embedded labels, table is [$(vAno)]);
LET vFileExists = if(len(FileSize('Colaboradores.qvd')) > 0, -1, 0);
IF '$(vFileExists)' = '-1' THEN
//Histórico não existe
[Colaboradores_Target]:
LOAD
[Colaborador ID],
[Nome],
[Posição],
[Escritório],
Date(MakeDate($(vAno)), 'YYYY/MM/DD') AS [_Start],
Date(MakeDate(9999, 12, 31), 'YYYY/MM/DD') AS [_End],
1 AS [_Active]
RESIDENT [Colaboradores];
DROP TABLE [Colaboradores];
RENAME TABLE [Colaboradores_Target] TO [Colaboradores];
// 8 - Atualizar a dimensão no histórico
STORE [Colaboradores] INTO 'Colaboradores.qvd' (qvd);
ELSE
//Historico existe
[Map_History_Colaboradores_$(vAno)]:
MAPPING LOAD
[Colaborador ID],
Hash256([Nome],[Posição],[Escritório]) AS [Hash]
FROM [Colaboradores.qvd] (qvd)
WHERE [_Active] = 1;
// 2 - Comparar as diferenças da nova tabela com a tabela dimensão em histórico
[Temp_Colaborador]:
LOAD
*,
If(IsNull(HistHash), 1, 0) AS [_Novo],
If(HistHash = CurHash, 1, 0) AS [_Antigo],
If(HistHash <> CurHash and Not IsNull(HistHash), 1, 0) AS [_Atualizado];
LOAD
[Colabordor ID],
[Nome],
[Posição],
[Escritório],
Date(MakeDate($(vYear)), 'YYYY/MM/DD') AS [_Start],
Date(MakeDate(9999, 12, 31), 'YYYY/MM/DD') AS [_End],
1 AS [_Active],
ApplyMap('Map_History_Colaboradores_$(vAno)',
[Employee ID],
Null()) AS [HistHash],
Hash256([Nome],[Posição],[Escritório]) AS [CurHash]
RESIDENT [Colaboradores];
[Temp_Colaborador_Atualizado]:
LOAD
[Colaborador ID] AS [_CID]
RESIDENT [Temp_Colaborador]
WHERE [_Atualizado] = 1;
//3 - Obter as linhas novas e as linhas com alterações da nova tabela
[Colaborador_Target]:
NOCONCATENATE
LOAD
[Colaborador ID],
[Nome],
[Posição],
[Escritório],
[_Start],
[_End],
[_Active]
RESIDENT [Temp_Colaborador]
WHERE [_Novo] = 1 OR [_Atualizado] = 1;
// 4 - Obter o histórico
// 5 - Filtrar do histórico as linhas atualizadas e com a flag activo = sim
// 6 - Alterar a flag para = não e atualizar a data de fim com today()
// 7 - Concatenar ao resultado do passo 3
CONCATENATE ([Colaborador_Target])
LOAD
[Colaborador ID],
[Nome],
[Posição],
[Escritório],
[_Start],
Date(MakeDate($(vAno)) - 1, 'YYYY/MM/DD') AS [_End],
0 AS [_Active]
FROM [Colaborador.qvd] (qvd)
WHERE EXISTS([_CID], [Colaborador ID]) AND [_Active] = 1;
// 8 - Concatenar o resto do historico
CONCATENATE ([Colaborador_Target])
LOAD
[Colaborador ID],
[Nome],
[Posição],
[Escritório],
[_Start],
If(Not Exists([Colaborador ID]) And [_Active] = 1,
Date(MakeDate($(vYear)) - 1, 'YYYY/MM/DD'),
[_End]) AS [_End],
If(Not Exists([Employee ID]) And [_Active] = 1, 0, [_Active]) AS [_Active]
FROM [Colaborador.qvd] (qvd)
WHERE NOT EXISTS([_CID], [Colaborador ID]) OR (EXISTS([_CID], [Colaborador ID]) AND [_Active] = 0);
DROP TABLE [Colaborador];
RENAME TABLE [Colaborador_Target] TO [Colaborador];
//====================================================================
// Cleanup
DROP TABLE [Temp_Colaborador_Changed];
DROP TABLE [Temp_Colaborador];
END IF
A SCD do tipo 3 cria um novo atributo na dimensão preservando o antigo atributo. Esta técnica é útil para quando há poucas atualizações de um atributo e para quando se quer comparar realidades.
Esta técnica é ideal para requisitos típicos de vendas onde é frequente se mudarem as fronteiras de áreas e os diretores de área quererem comparar as novas áreas com as antigas.
Neste sentido o tipo 3 é adequado para atributos que raramente se alteram, mas quando acontece tem um elevado no processo de negócio. Caso o atributo esteja constantemente a ser alterado deve-se optar pelo tipo 2.
Adicionalmente é frequente criar um atributo por ano a fim de ter as realidades de cada ano comparáveis entre si. Após uma atualização todas as agregações précalculadas terão que ser atualizadas com os novos valores.
De seguida apresenta-se um exemplo da aplicação do tipo 3:
Pseudo código de implementação de SCD tipo 3:
Qlik Script
// 1 - Ler a nova tabela dimensão
Colaboradores:
LOAD
[Colaborador ID],
[Nome],
[Posição],
[Escritório]
FROM
[Colaboradores.xlsx]
(ooxml, embedded labels, table is [$(vAno)]);
;
LET vFileExists = if(len(FileSize('Colaboradores.qvd')) > 0, -1, 0);
IF '$(vFileExists)' = '0' THEN
//Histórico não existe
Temp_Colaboradores:
LOAD
[Colaborador ID],
[Nome],
[Posição],
[Escritório],
'N/A' AS [Previous Escritório],
RESIDENT
Colaboradores;
DROP TABLE Colaboradores;
RENAME TABLE Temp_Colaboradores TO Colaboradores;
STORE Colaboradores INTO 'Colaboradores.qvd' (qvd);
ELSE
//Histórico existe
RENAME TABLE Colaboradores TO Temp_Colaboradores;
// 2 - Left join com o histórico
LEFT JOIN (Temp_Colaboradores)
LOAD
[Colaborador ID],
[Nome],
[Posição],
[Escritório],
[Previous Escritório] AS tmpPreviousEscritorio,
FROM
Colaboradores.qvd
(qvd);
// Target table, apply SCD3 logic
Colaboradores:
LOAD
[Colaborador ID],
[Nome],
[Posição],
[Escritório],
if(Escritório = tmpPreviousEscritorio, mpPreviousEscritorio,
if(isNull(tmpPreviousEscritorio), 'N/A', tmpPreviousEscritorio)) AS [Previous Escritorio],
RESIDENT Temp_Colaboradores;
// 3 - Obter as linhas histórico que não existem no passo 2
// 4 - Concatenar o passo 2 e 3
CONCATENATE (Colaboradores)
LOAD
[Colaborador ID],
[Nome],
[Posição],
[Escritório], AS [Previous supplier]
FROM Colaboradores.qvd (qvd)
WHERE NOT Exists([Entity ID]);
DROP TABLE Temp_Colaboradores;
// Store updated history to QVD
STORE Colaboradores INTO 'Colaboradores.qvd' (qvd);
END IF
Continua no próximo post...
Slowly Changing Dimensions (parte 2)
Comments are closed.
Pingback: Slowly Changing Dimensions (parte 2) – BM Consulting