Slowly Changing Dimensions (parte 1)

By:
Posted: October 27, 2019
Category: Forum BI , Qlik , QlikSense , QlikView
Comments: 1


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:



  • Type 0: Retain original

  • Type 1: Overwrite

  • Type 2: Add new row

  • Type 3: Add new attribute

  • Type 4: Add a mini-dimension

  • Type 5: Add a mini-dimension and Type 1 outrigger

  • Type 6: Add Type 1 attributes to Type 2 dimension

  • Type 7: Dual Type 1 and Type 2 Dimensions





Type 0 - Retain original


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.





Type 1 - Overwrite


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:



  • Data de nascimento - assume-se que a data de nascimento não muda, se houver alguma alteração é uma correção à data inserida inicialmente

  • Morada - se não houver análises geográficas









Pseudo código de implementação de SCD tipo 1:



  1. Ler a nova tabela dimensão

  2. Ler a tabela dimensão do histórico onde a primary key não exista na tabela lida no passo 1

  3. Concatenar as duas tabelas

  4. Atualizar a dimensão no histórico


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




Type 2 - Add new Row


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:



  • Uma nova primary key, a business key irá repetir-se por cada nova atualização

  • Data de início do registo

  • Data de fim do registo

  • Flag a mencionar qual o último registo


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:



  1. Ler a nova tabela dimensão

  2. Comparar as diferenças da nova tabela com a tabela dimensão em histórico

  3. Obter as linhas novas e as linhas com alterações da nova tabela

  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

  8. Concatenar o resto do historico

  9. Atualizar a dimensão no histórico


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




Type 3 - Add new attribute


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:



  1. Ler a nova tabela dimensão

  2. Left join com o histórico

  3. Obter as linhas histórico que não existem no passo 2

  4. Concatenar o passo 2 e 3

  5. Atualizar a dimensão no histórico


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)


Gostou do post? Compartilhe!

Related Posts

One thought on “Slowly Changing Dimensions (parte 1)”

  1. Pingback: Slowly Changing Dimensions (parte 2) – BM Consulting

Comments are closed.