Modelagem dimensional no Amazon Redshift | Amazon Web Services

Amazon RedShift é um data warehouse em nuvem totalmente gerenciado e em escala de petabytes que é usado por dezenas de milhares de clientes para processar exabytes de dados todos os dias para potencializar sua carga de trabalho analítica. Você pode estruturar seus dados, medir processos de negócios e obter insights valiosos rapidamente usando um modelo dimensional. O Amazon Redshift fornece recursos integrados para acelerar o processo de modelagem, orquestração e relatórios de um modelo dimensional.

Neste post, discutimos como implementar um modelo dimensional, especificamente o metodologia Kimball. Discutimos a implementação de dimensões e fatos no Amazon Redshift. Mostramos como executar extração, transformação e carregamento (ELT), um processo de integração focado em obter os dados brutos de um data lake em uma camada de preparação para executar a modelagem. No geral, a postagem fornecerá uma compreensão clara de como usar a modelagem dimensional no Amazon Redshift.

Visão geral da solução

O diagrama a seguir ilustra a arquitetura da solução.

Nas seções a seguir, primeiro discutimos e demonstramos os principais aspectos do modelo dimensional. Depois disso, criamos um data mart usando o Amazon Redshift com um modelo de dados dimensional incluindo dimensões e tabelas de fatos. Os dados são carregados e testados usando o CÓPIA comando, os dados nas dimensões são carregados usando o MERGE declaração, e os fatos serão agregados às dimensões de onde os insights são derivados. Agendamos o carregamento das dimensões e fatos usando o Editor de consultas do Amazon Redshift V2. Por último, usamos AmazonQuickSight para obter informações sobre os dados modelados na forma de um painel do QuickSight.

Para esta solução, usamos um conjunto de dados de amostra (normalizado) fornecido pelo Amazon Redshift para vendas de ingressos de eventos. Para esta postagem, reduzimos o conjunto de dados para fins de simplicidade e demonstração. As tabelas a seguir mostram exemplos de dados para vendas de ingressos e locais.

De acordo com Metodologia de modelagem dimensional de Kimball, há quatro etapas principais na criação de um modelo dimensional:

1. Identifique o processo de negócios.
2. Declare a granulação de seus dados.
3. Identificar e implementar as dimensões.
4. Identifique e implemente os fatos.

Além disso, adicionamos uma quinta etapa para fins de demonstração, que é relatar e analisar eventos de negócios.

Pré-requisitos

Para este passo a passo, você deve ter os seguintes pré-requisitos:

Identifique o processo de negócios

Em termos simples, identificar o processo de negócios é identificar um evento mensurável que gera dados dentro de uma organização. Normalmente, as empresas possuem algum tipo de sistema fonte operacional que gera seus dados em seu formato bruto. Este é um bom ponto de partida para identificar várias fontes para um processo de negócios.

O processo de negócio é então persistido como um data mart na forma de dimensões e fatos. Observando nosso conjunto de dados de amostra mencionado anteriormente, podemos ver claramente que o processo de negócios são as vendas feitas para um determinado evento.

Um erro comum cometido é usar os departamentos de uma empresa como processo de negócios. Os dados (processo de negócios) precisam ser integrados em vários departamentos; nesse caso, o marketing pode acessar os dados de vendas. Identificar o processo de negócios correto é crítico - errar nesta etapa pode afetar todo o data mart (pode fazer com que a granulação seja duplicada e métricas incorretas nos relatórios finais).

Declare a granulação dos seus dados

Declarar a granulação é o ato de identificar exclusivamente um registro em sua fonte de dados. A granulação é usada na tabela de fatos para medir com precisão os dados e permitir que você acumule ainda mais. Em nosso exemplo, isso pode ser um item de linha no processo comercial de vendas.

Em nosso caso de uso, uma venda pode ser identificada exclusivamente observando o momento da transação em que a venda ocorreu; este será o nível mais atômico.

Identifique e implemente as dimensões

Sua tabela de dimensões descreve sua tabela de fatos e seus atributos. Ao identificar o contexto descritivo de seu processo de negócios, você armazena o texto em uma tabela separada, mantendo em mente a granulação da tabela de fatos. Ao unir a tabela de dimensões à tabela de fatos, deve haver apenas uma única linha associada à tabela de fatos. Em nosso exemplo, usamos a tabela a seguir para ser separada em uma tabela de dimensões; esses campos descrevem os fatos que mediremos.

Ao projetar a estrutura do modelo dimensional (o esquema), você pode criar um estrela or floco de neve esquema. A estrutura deve estar alinhada com o processo de negócios; portanto, um esquema em estrela é mais adequado para nosso exemplo. A figura a seguir mostra nosso Diagrama Entidade Relacionamento (ERD).

Nas seções a seguir, detalhamos as etapas para implementar as dimensões.

Prepare os dados de origem

Antes de podermos criar e carregar a tabela de dimensões, precisamos dos dados de origem. Portanto, preparamos os dados de origem em uma tabela temporária ou temporária. Isto é muitas vezes referido como o camada de preparação, que é a cópia bruta dos dados de origem. Para fazer isso no Amazon Redshift, usamos o comando COPIAR para carregar os dados do bucket S3 público dimensional-modeling-in-amazon-redshift localizado no us-east-1 Região. Observe que o comando COPY usa um Gerenciamento de acesso e identidade da AWS (IAM) função com acesso ao Amazon S3. O papel precisa ser associado ao cluster. Conclua as etapas a seguir para preparar os dados de origem:

1. Criar o venue tabela de origem:

CREATE TABLE public.venue ( venueid bigint, venuename character varying(100), venuecity character varying(30), venuestate character(2), venueseats bigint
) DISTSTYLE AUTO SORTKEY (venueid);

2. Carregue os dados do local:

COPY public.venue
FROM 's3://redshift-blogs/dimensional-modeling-in-amazon-redshift/venue.csv'
IAM_ROLE '<Your IAM role arn>'
DELIMITER ','
REGION 'us-east-1'
IGNOREHEADER 1

3. Criar o sales tabela de origem:

CREATE TABLE public.sales (
    salesid integer,
    venueid character varying(256),
    saletime timestamp without time zone,
    qtysold BIGINT,
    commission numeric(18,2),
    pricepaid numeric(18,2)
) DISTSTYLE AUTO;

4. Carregue os dados da fonte de vendas:

COPY public.sales
FROM 's3://redshift-blogs/dimensional-modeling-in-amazon-redshift/sales.csv'
IAM_ROLE '<Your IAM role arn>'
DELIMITER ','
REGION 'us-east-1'
IGNOREHEADER 1

5. Criar o calendar tabela:

CREATE TABLE public.DimCalendar(
    dateid smallint,
        caldate date,
        day varchar(20),
        week smallint,
        month varchar(20),
        qtr varchar(20),
        year smallint,
        holiday boolean
) DISTSTYLE AUTO
SORTKEY
    (dateid);

6. Carregue os dados do calendário:

COPY public.DimCalendar
FROM 's3://redshift-blogs/dimensional-modeling-in-amazon-redshift/date.csv'
IAM_ROLE '<Your IAM role arn>'
DELIMITER ',' 
REGION 'us-east-1'
IGNOREHEADER 1

Criar a tabela de dimensões

Projetar a tabela de dimensões pode depender de seus requisitos de negócios — por exemplo, você precisa acompanhar as alterações nos dados ao longo do tempo? Há sete tipos de dimensões diferentes. Para o nosso exemplo, usamos Tipo 1 porque não precisamos rastrear mudanças históricas. Para mais informações sobre o tipo 2, consulte Simplifique o carregamento de dados em dimensões de alteração lenta do Tipo 2 no Amazon Redshift. A tabela de dimensões será desnormalizada com uma chave primária, chave substituta e alguns campos adicionados para indicar alterações na tabela. Veja o seguinte código:

create schema SalesMart;

CREATE TABLE SalesMart.DimVenue( 
    "VenueSkey" int IDENTITY(1,1) primary key
    ,"VenueId" VARCHAR NOT NULL
    ,"VenueName" VARCHAR NULL
    ,"VenueCity" VARCHAR NULL
    ,"VenueState" VARCHAR NULL
    ,"VenueSeats" INT NULL
    ,"InsertedDate" DATETIME NOT NULL
    ,"UpdatedDate" DATETIME NOT NULL
) 
diststyle AUTO;

Algumas observações sobre a criação da tabela de dimensões:

• Os nomes dos campos são transformados em nomes amigáveis ​​para os negócios
• Nossa chave primária é VenueID, que usamos para identificar exclusivamente um local no qual a venda ocorreu
• Duas linhas adicionais serão adicionadas, indicando quando um registro foi inserido e atualizado (para rastrear alterações)
• Estamos usando um Estilo de distribuição AUTOMÁTICO para dar ao Amazon Redshift a responsabilidade de escolher e ajustar o estilo de distribuição

Outro fator importante a considerar na modelagem dimensional é o uso de chaves substitutas. As chaves substitutas são chaves artificiais usadas na modelagem dimensional para identificar exclusivamente cada registro em uma tabela de dimensões. Eles são normalmente gerados como um número inteiro sequencial e não têm nenhum significado no domínio de negócios. Elas oferecem vários benefícios, como garantir a exclusividade e melhorar o desempenho nas junções, porque normalmente são menores que as chaves naturais e, como chaves substitutas, não mudam com o tempo. Isso nos permite ser consistentes e juntar fatos e dimensões com mais facilidade.

No Amazon Redshift, as chaves substitutas geralmente são criadas usando a palavra-chave IDENTITY. Por exemplo, a instrução CREATE anterior cria uma tabela de dimensão com um VenueSkey Chave substituta. O VenueSkey coluna é preenchida automaticamente com valores exclusivos conforme novas linhas são adicionadas à tabela. Esta coluna pode então ser usada para juntar a tabela do local à FactSaleTransactions tabela.

Algumas dicas para projetar chaves substitutas:

• Use um tipo de dados pequeno e de largura fixa para a chave substituta. Isso melhorará o desempenho e reduzirá o espaço de armazenamento.
• Use a palavra-chave IDENTITY ou gere a chave substituta usando um valor sequencial ou GUID. Isso garantirá que a chave substituta seja exclusiva e não possa ser alterada.

Carregue a tabela dim usando MERGE

Existem inúmeras maneiras de carregar sua mesa dim. Certos fatores precisam ser considerados, por exemplo, desempenho, volume de dados e talvez tempos de carregamento do SLA. Com o MERGE declaração, executamos um upsert sem precisar especificar vários comandos de inserção e atualização. Você pode configurar o MERGE declaração em um procedimento armazenado para preencher os dados. Em seguida, você agenda o procedimento armazenado para ser executado programaticamente por meio do editor de consultas, que demonstramos mais adiante na postagem. O código a seguir cria um procedimento armazenado chamado SalesMart.DimVenueLoad:

CREATE OR REPLACE PROCEDURE SalesMart.DimVenueLoad()
AS $$
BEGIN
MERGE INTO SalesMart.DimVenue USING public.venue as MergeSource
ON SalesMart.DimVenue.VenueId = MergeSource.VenueId
WHEN MATCHED
THEN
UPDATE
SET VenueName = ISNULL(MergeSource.VenueName, 'Unknown')
, VenueCity = ISNULL(MergeSource.VenueCity, 'Unknown')
, VenueState = ISNULL(MergeSource.VenueState, 'Unknown')
, VenueSeats = ISNULL(MergeSource.VenueSeats, -1)
, UpdatedDate = GETDATE()
WHEN NOT MATCHED
THEN
INSERT (
VenueId
, VenueName
, VenueCity
, VenueState
, VenueSeats
, UpdatedDate
, InsertedDate
)
VALUES (
ISNULL(MergeSource.VenueId, -1)
, ISNULL(MergeSource.VenueName, 'Unknown')
, ISNULL(MergeSource.VenueCity, 'Unknown')
, ISNULL(MergeSource.VenueState, 'Unknown')
, ISNULL(MergeSource.VenueSeats, -1)
, ISNULL(GETDATE() , '1900-01-01')
, ISNULL(GETDATE() , '1900-01-01')
);
END;
$$
LANGUAGE plpgsql;

Algumas notas sobre o carregamento da dimensão:

• Quando um registro for inserido pela primeira vez, a data inserida e a data atualizada serão preenchidas. Quando algum valor muda, os dados são atualizados e a data atualizada reflete a data em que foi alterada. A data inserida permanece.
• Como os dados serão usados ​​por usuários corporativos, precisamos substituir os valores NULL, se houver, por valores mais apropriados aos negócios.

Identifique e implemente os fatos

Agora que declaramos nosso grão como o evento de uma venda que ocorreu em um horário específico, nossa tabela de fatos armazenará os fatos numéricos para nosso processo de negócios.

Identificamos os seguintes fatos numéricos para medir:

• Quantidade de ingressos vendidos por venda
• comissão pela venda

Implementando o fato

Tem três tipos de tabelas de fatos (tabela de fatos de transações, tabela de fatos de instantâneos periódicos e tabela de fatos de instantâneos acumulativos). Cada um oferece uma visão diferente do processo de negócios. Para nosso exemplo, usamos uma tabela de fatos de transação. Conclua as seguintes etapas:

1. Criar a tabela de fatos

CREATE TABLE SalesMart.FactSaleTransactions( 
    CalendarDate date NOT NULL
    ,SaleTransactionTime DATETIME NOT NULL
    ,VenueSkey INT NOT NULL
    ,QuantitySold BIGINT NOT NULL
    ,SaleComission NUMERIC NOT NULL
    ,InsertedDate DATETIME DEFAULT GETDATE()
) diststyle AUTO;

Uma data inserida com um valor padrão é adicionada, indicando se e quando um registro foi carregado. Você pode usar isso ao recarregar a tabela de fatos para remover os dados já carregados para evitar duplicatas.

O carregamento da tabela de fatos consiste em uma simples instrução de inserção que une suas dimensões associadas. Nós nos juntamos a partir do DimVenue tabela que foi criada, que descreve nossos fatos. É uma prática recomendada, mas é opcional ter data do calendário dimensões, que permitem ao usuário final navegar na tabela de fatos. Os dados podem ser carregados quando houver uma nova venda ou diariamente; é aqui que a data inserida ou a data de carregamento são úteis.

Carregamos a tabela de fatos usando um procedimento armazenado e usamos um parâmetro de data.

2. Crie o procedimento armazenado com o seguinte código. Para manter a mesma integridade de dados que aplicamos no carregamento da dimensão, substituímos os valores NULL, se houver, por valores mais apropriados aos negócios:

create or replace procedure SalesMart.FactSaleTransactionsLoad(loadate datetime)
language plpgsql
as
    $$
begin
--------------------------------------------------------------------
/*** Delete records loaded for the day, should there be any ***/
--------------------------------------------------------------------
Delete from SalesMart.FactSaleTransactions
where cast(InsertedDate as date) = CAST(loadate as date);
RAISE INFO 'Deleted rows for load date: %', loadate;
--------------------------------------------------------------------
/*** Insert records ***/
--------------------------------------------------------------------
INSERT INTO SalesMart.FactSaleTransactions (
CalendarDate    
,SaleTransactionTime    
,VenueSkey  
,QuantitySold  
,Salecomission
)
SELECT DISTINCT
    ISNULL(c.caldate, '1900-01-01') as CalendarDate
    ,ISNULL(a.saletime, '1900-01-01') as SaleTransactionTime
    ,ISNULL(b.VenueSkey, -1) as VenueSkey
    ,ISNULL(a.qtysold, 0) as QuantitySold
    ,ISNULL(a.commission, 0) as SaleComission
FROM
    public.sales as a
 
LEFT JOIN SalesMart.DimVenue as b
on a.venueid = b.venueid
 
LEFT JOIN public.DimCalendar as c
on to_char(a.saletime,'YYYYMMDD') = to_char(c.caldate,'YYYYMMDD');
--Optional filter, should you want to load only the latest data from source
--where cast(a.saletime as date) = cast(loadate as date);
  
end;
$$;

3. Carregue os dados chamando o procedimento com o seguinte comando:

call SalesMart.FactSaleTransactionsLoad(getdate())

Agendar o carregamento de dados

Agora podemos automatizar o processo de modelagem agendando os procedimentos armazenados no Amazon Redshift Query Editor V2. Conclua as seguintes etapas:

1. Primeiro chamamos o dimension load e depois que o dimension load é executado com sucesso, o fact load começa:

BEGIN;
----Insert Dim Loads
call SalesMart.DimVenueLoad(); ----Insert Fact Loads. They will only run if the DimLoad is successful
call SalesMart.FactSaleTransactionsLoad(getdate());
END;

Se o carregamento da dimensão falhar, o carregamento de fatos não será executado. Isso garante consistência nos dados porque não queremos carregar a tabela de fatos com dimensões desatualizadas.

2. Para agendar a carga, escolha Programação do dia no Editor de consultas V2.

3. Agendamos a consulta para ser executada todos os dias às 5h.
4. Opcionalmente, você pode adicionar notificações de falha ativando Serviço de notificação simples da Amazon (Amazon SNS) notificações.

Relate e analise os dados no Amazon Quicksight

O QuickSight é um serviço de business intelligence que facilita a entrega de insights. Como um serviço totalmente gerenciado, o QuickSight permite que você crie e publique facilmente painéis interativos que podem ser acessados ​​de qualquer dispositivo e incorporados em seus aplicativos, portais e sites.

Usamos nosso data mart para apresentar visualmente os fatos na forma de um painel. Para começar e configurar o QuickSight, consulte Criando um conjunto de dados usando um banco de dados que não é descoberto automaticamente.

Depois de criar sua fonte de dados no QuickSight, juntamos os dados modelados (data mart) com base em nossa chave substituta skey. Usamos esse conjunto de dados para visualizar o data mart.

Nosso painel final conterá os insights do data mart e responderá a perguntas críticas de negócios, como comissão total por local e datas com as vendas mais altas. A captura de tela a seguir mostra o produto final do data mart.

limpar

Para evitar cobranças futuras, exclua todos os recursos que você criou como parte desta postagem.

Conclusão

Agora implementamos com sucesso um data mart usando nosso DimVenue, DimCalendar e FactSaleTransactions tabelas. Nosso armazém não está completo; à medida que podemos expandir o data mart com mais fatos e implementar mais marts, e conforme o processo de negócios e os requisitos crescem com o tempo, o data warehouse também crescerá. Nesta postagem, fornecemos uma visão completa sobre como entender e implementar a modelagem dimensional no Amazon Redshift.

Comece com o seu Amazon RedShift modelo dimensional hoje.

Sobre os autores

Bernard Verster é um engenheiro de nuvem experiente com anos de experiência na criação de modelos de dados escaláveis ​​e eficientes, definindo estratégias de integração de dados e garantindo governança e segurança de dados. Ele é apaixonado pelo uso de dados para gerar insights, ao mesmo tempo em que se alinha aos requisitos e objetivos de negócios.

Abhishek Pan é um especialista em WWSO SA-Analytics que trabalha com clientes do setor público da AWS na Índia. Ele interage com os clientes para definir a estratégia baseada em dados, fornecer sessões de aprofundamento em casos de uso analítico e projetar aplicativos analíticos escaláveis ​​e de alto desempenho. Ele tem 12 anos de experiência e é apaixonado por bancos de dados, análises e AI/ML. Ele é um viajante ávido e tenta capturar o mundo através das lentes de sua câmera.

• Conteúdo com tecnologia de SEO e distribuição de relações públicas. Seja amplificado hoje.
• PlatoData.Network Gerativa Vertical Ai. Capacite-se. Acesse aqui.
• PlatoAiStream. Inteligência Web3. Conhecimento Amplificado. Acesse aqui.
• PlatãoESG. Automotivo / EVs, Carbono Tecnologia Limpa, Energia, Ambiente, Solar, Gestão de resíduos. Acesse aqui.
• BlockOffsets. Modernizando a Propriedade de Compensação Ambiental. Acesse aqui.
• Fonte: https://aws.amazon.com/blogs/big-data/dimensional-modeling-in-amazon-redshift/