Desenvolvendo um Data Warehouse na nuvem para SaaS na SalesLoft

Sonny Mohnalkar Blocked Desbloquear Seguir Seguindo 9 de janeiro Foto cedida por: Cazena

Introdução

Para o bem dos leitores que estão ouvindo o termo 'Data Warehouse' pela primeira vez, deixe-me explicar brevemente. Um data warehouse é um armazenamento de dados integrado, não volátil, orientado ao assunto e variante de tempo para revelar tendências, padrões e correlações que fornecem informações valiosas e insights sobre os negócios. O foco deste artigo é mostrar a importância de um data warehouse em nuvem em uma economia orientada por dados. Vamos dar uma olhada rápida em vários conceitos de data warehousing e avaliar dois cenários – dados estruturados e não estruturados para projetar nosso primeiro data warehouse.

A justificativa para um data warehouse

O SalesLoft está experimentando um tsunami de dados com volume e complexidade de dados exponencialmente maiores, com dados distribuídos em vários bancos de dados, aplicativos, domínios ou em várias fontes, como sites, telefones celulares e em diferentes formatos estruturados ou não estruturados. Como uma empresa orientada por dados, monitorar o comportamento do usuário final e avaliar a adoção pelo cliente significa converter esses dados brutos em informações úteis, limpando, transformando e integrando os dados para se tornar uma fonte única de verdade para a organização. Esses dados limpos, transformados e integrados precisam de um único local de armazenamento econômico e econômico, e esse local é o Data Warehouse.

Segundo, para realizar qualquer análise de tendência, precisamos estudar dados entre várias combinações de tempo de ano, trimestre, mês, semana, dia, dia da semana-semana. Nesse caso, para facilitar a tomada de decisões usando nossos dados históricos, a opção mais adequada é projetar um data warehouse.

Por fim, os dados gerados por máquina geralmente têm uma baixa relação sinal-ruído. Ele pode conter dados valiosos, mas também muito "ruído". Isso significa que devemos armazenar tudo para encontrar os bits úteis. É possível armazenar esses dados de maneira econômica e consumir de maneira significativa por meio de análises? Isso torna a nuvem – com sua escalabilidade quase infinita, capacidade de armazenamento externo e integração perfeita – um local perfeito para hospedar o data warehouse.

Analistas de negócios, analistas de dados, cientistas de dados, engenheiros de dados, gerentes e executivos de alto nível se beneficiam do armazenamento de dados por meio de business intelligence (BI) de autoatendimento. É o sonho de todos em uma organização – todas as informações sobre as atividades da organização reunidas em um único lugar, uma fonte de verdade disponível em um painel, com apenas um clique de um botão. Pode parecer um sonho distante, mas como tornar esse sonho uma realidade? Primeiro, temos que planejar nosso sistema de data warehouse. Modelar seu data warehouse é o primeiro passo nessa direção.

Cenário

A plataforma principal da SalesLoft possui milhares de usuários em várias superfícies. Devido à indisponibilidade de dados integrados, gerentes de contas, SDRs, CSMs, ISMs não conseguem ter uma visão de 360 graus de seus clientes enquanto tomam decisões cruciais. Eles também gostariam de explorar dados históricos para identificar conexões interessantes e valiosas. Atualmente, é um processo manual e demorado e não permite compreender as tendências históricas. Assim, somos solicitados a projetar um sistema que possa ajudá-los rapidamente na tomada de decisões e fornecer um retorno sobre o investimento (ROI). Para começar a projetar o data warehouse, precisamos seguir alguns passos.

Desenvolvendo um Data Warehouse na Cloud

As propriedades da tecnologia de nuvem – especialmente escalabilidade, relação custo-eficácia, recursos ilimitados, pontos de integração natural – tornam-no particularmente adequado para data warehousing. Com o aumento exponencial do volume de dados (atividade do usuário e dados gerados por máquina), escolhemos o Amazon Redshift para construir nosso data warehouse. O Redshift oferece as vantagens do armazenamento colunar, o Redshift Spectrum para separar computação dispendiosa do armazenamento e a ingestão e consulta de formatos de dados semi-estruturados e estruturados.

Avaliar uma ferramenta de ETL é um grande desafio. Com uma ampla variedade de opções no mercado, é difícil saber por onde começar. Nós selecionamos três ferramentas ETL diferentes com base nos recursos para executar carga total, carga incremental com captura de dados alterados (CDC), cadeias de processo, agendamento e monitoramento de tarefas, notificação de sucesso / falha de trabalho para citar alguns critérios mais importantes. Com uma interface de desenvolvimento muito intuitiva, o Matillion ETL for Redshift saiu como vencedor.

Por último, mas não menos importante, tivemos uma ferramenta de BI pré-existente baseada na Web, a 'Looker', para conectar ao data warehouse, executar consultas, criar gráficos e painéis. Consideramos o Looker fácil de usar para analistas de dados e usuários corporativos.

A partir daqui, se feito corretamente, sabíamos que nosso data warehouse estaria funcionando em menos de 3 meses. Portanto, a maior parte do tempo a partir de agora foi gasto reunindo requisitos, construindo o pipeline de ETL e configurando o 'Looker' para extrair informações do data warehouse.

As fases de um projeto de data warehouse listadas abaixo são semelhantes às da maioria dos projetos de banco de dados, começando com a identificação de requisitos de negócios e terminando com a execução do pipeline de ETL para criar o data warehouse:

  1. Requisitos de negócios do documento
  2. Projetar o modelo conceitual, lógico e físico (dimensional)
  3. Construir o pipeline de ETL para criar e preencher as tabelas de dimensões e fatos
  4. Visualize através de relatórios e painéis

Requisitos de negócio

A fim de identificar e coletar requisitos, precisamos entrevistar os principais especialistas no assunto e as partes interessadas para saber – que fatores definem o sucesso no negócio? Quais são as questões comerciais mais importantes que precisam ser respondidas por este novo sistema? Como a administração deseja visualizar e analisar seus dados? Entrevistar os especialistas em vários departamentos fornece requisitos de negócios realistas que são necessários para o escopo do projeto.

Vamos primeiro identificar e documentar os requisitos de negócios das principais partes interessadas para examinar dois casos interessantes.

Os executivos de negócios da SalesLoft estão interessados em conhecer o uso diário ativo de sua plataforma. Nossos clientes estão espalhados por vários continentes. Eles gostariam de examinar os dois casos listados abaixo para obter visibilidade dos usuários ativos em todas as superfícies. A suspeita é que um grande segmento de usuários que não interagem diretamente com a plataforma central diariamente pode estar se envolvendo com a extensão regularmente.

Caso 1 para consumir dados estruturados de fontes do banco de dados

Os CSMs e ISMs precisam visualizar com o cronograma histórico o uso diário ativo para a plataforma principal em várias dimensões – equipes, usuários, conta / proprietário de vendas, contas do Salesforce . Gostaríamos de saber o Número de Sessões de Plataforma que um Usuário teve durante um determinado período de tempo para entender com que frequência eles usam a parte da Plataforma do aplicativo. Esses dados estão disponíveis na origem em um formato estruturado.

Caso 2 para consumir dados não estruturados / semiestruturados do armazenamento S3 externo (desacoplamento da computação do armazenamento)

CSMs, os ISMs precisam visualizar com o cronograma histórico o Uso Ativo Diário para nossas extensões Gmail e Salesforce (SFDC) em várias dimensões – Equipes, Usuários, Conta / Proprietário de Vendas, Contas Salesforce . Gostaríamos de saber o Número de Sessões do Gmail e o Número de Sessões do SFDC que um determinado usuário teve durante um determinado período para entender com que frequência eles usam as extensões. Usamos Segmento para enviar os logs relacionados às sessões de extensão dos usuários (dados brutos) para o destino S3 no formato JSON separado por nova linha e compactado, binned by the day no bucket do S3. O espectro permite o consumo de dados externos sem a necessidade de aumentar o tamanho do cluster Redshift. O Redshift Spectrum também nos permite escrever consultas em relação a dados armazenados em um data lake S3, sem ter que carregar dados na computação Redshift, reduzindo assim o tempo de consumo e o custo de armazenamento.

Projetar o modelo conceitual, lógico e físico (dimensional)

A fase de projeto conceitual realiza uma transformação da especificação de requisitos de negócios semiformais em um esquema multidimensional conceitual formalizado. O Modelo Lógico identifica diferentes Entidades e Atributos e Relações entre eles.

A modelagem de dados é o processo de documentar um projeto de sistema de software complexo como um diagrama de fácil compreensão, usando texto e símbolos para representar a maneira pela qual os dados precisam fluir. O diagrama pode ser usado como um modelo para a construção do modelo dimensional. Utilizamos a ferramenta de modelagem de dados Vertabelo para projetar e armazenar artefatos de nossos modelos.

A modelagem dimensional cria um esquema otimizado para alto desempenho. Isso significa menos junções e ajuda na redução da redundância de dados. É por isso que a modelagem dimensional é usada em um data warehouse. Precisamos projetar Modelo Dimensional para atender aos requisitos de negócios que devem ser facilmente extensíveis de acordo com as necessidades futuras e também fornecer resultados de consulta "instantâneos" para analistas.

Vamos rapidamente dar uma olhada em alguns termos essenciais usados no data warehouse e então os derivaremos para nossa necessidade.

Dimensão

Dimensões (tabelas) contêm descrições textuais sobre os assuntos do negócio. Por exemplo, Customer é uma dimensão com atributos como nome e sobrenome, data de nascimento, sexo etc. As principais funções das dimensões são fornecer filtragem, agrupamento e rotulagem em seus dados. Fora de muitos tipos de dimensão, as dimensões que mudam lentamente (SCD) são mais amplamente usadas para capturar mudanças ao longo do tempo e preservar as informações históricas. Cada dimensão deve ter uma chave primária substituta (coluna de valor numérico gerada internamente) que encontra uso adicional como uma chave estrangeira no modelo dimensional. Essa chave primária substituta se comporta como uma chave de dimensão ao carregar a tabela de fatos. É uma prática recomendada nomear a coluna Chave substituta com o sufixo _SK no data warehouse.

As dimensões conformadas são criadas apenas uma vez no data warehouse. Eles fornecem consistência analítica e reduzem os custos de desenvolvimento futuros porque a roda não é recriada repetidamente. Eles podem ser reutilizados em vários modelos nas tabelas de fatos. Uma tabela de fatos contém fatos mensuráveis e chaves estrangeiras para as tabelas de dimensão. Tabelas de fatos são explicadas mais abaixo neste artigo.

Para fornecer consistência de relatórios em todas as áreas de assunto e reduzir os custos de desenvolvimento, optamos por criar dimensões conformadas sempre que apropriado. A dimensão de data é um exemplo perfeito de uma dimensão conformada com o corte e o corte de dados em vários intervalos de tempo reutilizáveis, como ano, trimestre, mês, semana, dia, dia da semana-fim de semana etc.

Uma dimensão de auditoria é uma dimensão com uma finalidade diferente em qualquer modelo. Dimensões de auditoria não são atualizadas como parte dos dados mestre. Em vez disso, elas são as últimas tabelas de dimensões a serem atualizadas durante o processo de ETL. Eles ajudam com a exclusão seletiva de dados das tabelas de fatos sem muita complicação.

Vamos identificar as dimensões relacionadas ao nosso caso:

Usuários, equipes, conta / proprietário de vendas, contas do Salesforce, data, auditoria

A medida

Uma medida também é comumente chamada de fato. Medidas são elementos de dados que podem ser somados, calculados ou manipulados matematicamente. Eles contêm dados quantificáveis, geralmente numéricos, que podem ser agregados. Principalmente, as medidas existem como uma coluna na tabela de fatos. Uma medida também pode ser derivada usando uma ou mais outras medidas.

Vamos identificar as medidas ou fatos relacionados aos casos acima.

  1. NUMBER_OF_PLATFORM_SESSIONS
  2. NUMBER_OF_GMAIL_SESSIONS
  3. NUMBER_OF_SFDC_SESSIONS
  4. NÚMERO DE USUÁRIOS
  5. NUMBER_OF_UNIQUE_SESSIONS
  6. DAILY_ACTIVE_USAGE_PERCENT

Número de usuários é a contagem total de usuários preenchidos com um valor de 1 para cada usuário.

Número de sessões exclusivas indica se um usuário teve uma sessão em QUALQUER um dos pontos de entrada. Por exemplo, em um determinado dia, se um usuário tiver feito login duas vezes usando o Gmail e três vezes usando o Salesforce e uma vez usando a plataforma, o número de sessões exclusivas será considerado apenas 1.

A porcentagem de uso ativo diário em uma fatia de tempo é uma métrica derivada usando a fórmula

(Número de Sessões Únicas / Número de Usuários) * 100

Tabela de fatos

Uma tabela de fatos consiste em fatos de um processo de negócios específico. Dados na tabela de fato são chamados de medidas. As tabelas de fatos possuem relacionamentos de chave estrangeira lógicos com as tabelas de dimensão. A tabela de fatos fornece estatísticas para áreas de assunto divididas pelas dimensões Usuários, Equipes, Proprietário de vendas, Proprietário da conta e Conta do Salesforce.

Um conceito importante para entender aqui é a granularidade . É uma medida do grau de detalhe em uma tabela de fatos. Quanto maior a granularidade , mais profundo é o nível de detalhes. Isso precisa ser discutido com os principais interessados antes de projetar o modelo dimensional. No nosso caso, a granularidade no nível Day fornece a profundidade necessária para relatórios de negócios. As tabelas de fatos podem ser separadas por granularidade e área de assunto. As tabelas de fatos possuem relacionamentos de chave estrangeira com suas respectivas dimensões conformadas .

Vamos identificar quais atributos devem estar lá em nossas Tabelas de fatos.

Tabela de fatos com chaves de dimensão e medidas

Modele seus dados

Depois que a base do projeto das dimensões e medidas estiver completa, agora temos que usar o esquema apropriado para relacionar essas tabelas de dimensões e fatos. Os esquemas populares usados para desenvolver modelos dimensionais são o Esquema em Estrela, o Esquema do Floco de Neve, etc. No entanto, qual esquema se adapta a qual caso de negócio é uma questão de design para outro artigo. O mais simples e mais usado é um esquema Star para desenvolver data marts. O esquema de estrelas lembra uma estrela, com pontos irradiando de um centro. O centro da estrela consiste na tabela de fatos, e os pontos da estrela são as tabelas de dimensão.

Por favor, consulte a figura do Esquema em Estrela abaixo:

Modelo de uso ativo diário

Codificação e Desenvolvimento do Data Warehouse:

Agora que temos a lista de dimensões e fatos (tabelas) e seus atributos (colunas), estamos prontos para começar a desenvolver o código para construir nosso modelo físico passo a passo. É uma prática de nomenclatura padrão prefixar uma tabela de dimensões com DIM_ e uma tabela de fatos com FACT_ em um data warehouse.

Vamos seguir as etapas abaixo para executar o código na instância Redshift.

Passo 1

Crie um banco de dados para seu Data Warehouse, emitindo o comando abaixo.

Passo 2

Criar uma tabela de dimensão de data que criará e preencherá dados de data divididos em vários níveis.

Você pode consultar as etapas detalhadas do artigo http://elliot.land/post/building-a-date-dimension-table-in-redshift

Faça o download do script e execute-o nesse banco de dados para criar e preencher a dimensão de data com valores.

etapa 3

Crie dimensões – usuários, equipes, conta / proprietário de vendas, contas do Salesforce – como dimensões que mudam lentamente . Para detalhes sobre vários tipos de dimensões que mudam lentamente, você pode consultar https://www.datawarehouse4u.info/SCD-Slowly-Changing-Dimensions.html

Abaixo está um código de exemplo para criar uma dimensão do tipo 4 que muda lentamente para os usuários . Nesse método, os dados históricos são rastreados usando Begin_Date e End_Date para cada registro. Cada atributo dessa tabela depende do tempo e todas as alterações históricas são capturadas para análise de tendências.

Da mesma forma, criamos dimensões para Equipes, Conta / Proprietário de Vendas, Contas do Salesforce.

Passo 4

Esta etapa é especificamente para realizar o Caso 2 para consumir dados não estruturados / semiestruturados do armazenamento S3 externo . Precisamos consumir logs desacoplados das sessões do usuário para determinar se um usuário teve uma sessão em um determinado dia por meio do Gmail ou do Salesforce. Esses logs são gzipados, formato JSON separado por nova linha, binned pelo dia no bucket S3. Você pode consultar as políticas IAM da diretriz do Amazon Redshift Spectrum para funções e autorizações necessárias para criar o esquema externo.

Todas as tabelas externas devem ser criadas em um esquema externo, que você cria usando a instrução abaixo.

O Amazon Redshift Spectrum usa tabelas externas para consultar dados armazenados no Amazon S3. Para consumir dados da tabela externa, escrevemos a mesma sintaxe SQL. Tabelas externas são somente leitura. Criamos uma tabela externa em um esquema externo. Para criar tabelas externas, você deve ser o proprietário do esquema externo ou de um superusuário.

Execute o comando abaixo para criar uma partição externa.

A tabela é criada com partições no receivedat . Quando particionamos nossos dados, restringimos a quantidade de dados que o Redshift Spectrum verifica filtrando a chave de partição. Como consultamos os dados da tabela externa por dia, a melhor partição está no tempo receivedat .

Agora, os dados externos do S3 estão prontos para consumo por meio do SQL padrão no pipeline de ETL.

Passo 5

Crie tabelas de fatos para manter todas as suas entradas transacionais com colunas de chave estrangeira apropriadas que se referem à coluna de chave primária (chave substituta) de suas dimensões. Em um data warehouse, a integridade referencial da chave estrangeira é preferencial para ser lógica. Ter chaves externas físicas ativas nas tabelas melhora a qualidade dos dados, mas prejudica o desempenho das operações de inserção, atualização e exclusão. Esse é um dos motivos pelos quais desistimos de chaves estrangeiras em data warehouses e bancos de dados analíticos que não processam dados de maneira transacional (uma linha por vez), mas em massa. Obviamente, o desempenho é um fator crucial no data warehousing e na business intelligence.

Você precisa tomar cuidado ao preencher sua tabela de fatos para se referir a valores de chave primária de dimensões apropriadas. Se nenhuma referência à dimensão puder ser obtida, você poderá preencher com uma chave simulada, como "todos os 9s".

Exemplo:

O SalesLoft possui vários usuários de diferentes equipes que efetuam login diretamente através das extensões Platform ou Gmail e Salesforce. No entanto, suponhamos que, em 1º de junho de 2018, o usuário A pertencente ao Time X tivesse 4 sessões de Plataforma, 2 sessões do Gmail e 3 sessões do Salesforce. Queremos determinar o uso ativo diário .

Antes de preencher sua tabela de fatos, você deve identificar e executar uma pesquisa de valores de coluna de chave de dimensão a partir das respectivas dimensões, como no exemplo acima e preencher colunas de chave estrangeira da tabela de fatos com os valores de chave de dimensão apropriados. Agora vamos definir como nos referiremos aos valores da chave de dimensão de cada dimensão.

Preencha sua tabela de fatos com valores de transação históricos de qualquer dia histórico com valores derivados apropriados de valores de chave de dimensão para cada usuário para cada dia.

Após a execução do script INSERT acima para cada usuário, a tabela de fatos é preenchida com transações para cada dia, conforme exibido abaixo.

Neste cenário da vida real, projetamos o pipeline de ETL com transformações e orquestrações que são programadas para atualizar todas as dimensões e tabelas de fatos todas as noites, para que o data warehouse esteja pronto para análise e geração de relatórios. Agora o data warehouse está pronto para o OLAP Business Intelligence através do Looker.

Como você pode ver, um data warehouse não é uma tarefa fácil e oferece muitos desafios. Além disso, se houver uma mudança no processo comercial ou na política da organização, o data warehouse poderá ter que se reorganizar. Se um data warehouse deve ser bem-sucedido, é um esforço coletivo e contínuo para examinar os dados. Os maiores ganhos do data warehousing ocorrem quando se integra ao modo como as pessoas executam seus trabalhos e o funcionamento da organização.

Sonny é engenheiro de data warehouse da SalesLoft. Ele tem um mestrado da Southern Illinois University. Ele mora em Atlanta com sua esposa e dois filhos.