Prévia do material em texto
PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Scheila Mello PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Exemplo de uma modelagem comparando o OLTP com OLAP PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Exemplo de uma modelagem comparando o OLTP com OLAP Ex: A área de negócios precisa saber quantos cadastros de clientes solteiros foram realizados em Janeiro/2017. Então precisamos saber quais são os dados de Estado Civil dos clientes, estes deverão estar na tabela analítica. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Exemplo de uma modelagem comparando o OLTP com OLAP Os dados da tabela FATO são, única e exclusivamente, fatos reais e verdadeiros, sobre a análise que precisa ser feita. A tabela FATO deve receber somente os campos chaves das tabelas dimensões, criando o relacionamento entre elas. No formato PK (chave primária) e FK (chave estrangeira) que já estamos acostumados a encontrar no ambiente OLTP. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Exemplo de uma modelagem comparando o OLTP com OLAP Estes dados inseridos dentro da FATO podem ser detalhados, fazendo uma busca na tabela DIMENSÃO correspondente àquela informação. Lembrando que os dados na tabela FATO podem estar desnormalizados, se necessário. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Exemplo de uma modelagem comparando o OLTP com OLAP A tabela Dimensão armazena os dados detalhados sobre aquele grupo específico. A tabela Dimensão de cliente possui os dados relacionados à clientes. A Dimensão de pagamentos, possui os dados referentes à pagamentos. A Dimensão de pedidos, possui dados referentes à pedidos. Lembrando...Elas não se relacionam entre si. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Modelagem em OLTP (E-R) http://www.diegonogare.net/wp-content/uploads/image_266.png PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Modelagem MMD http://www.diegonogare.net/wp-content/uploads/image_267.png PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Exercício: Montar modelagem multidimensional do modelo relacional abaixo, segundo as especificações descritas. Modelo relacional: contém tabelas de informações de faturamentos de notas fiscais em que temos lojas, notas, localidades, produtos, valor, quantidade e impostos. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Tabela Fato: “VENDAS” e que armazenarão informações como vendas, quantidades e impostos de cada produto. Granularidade (sugestão): A granularidade utilizada será: - Tempo (dia, semana, quinzena, mês); - Venda (produto, loja, cidade, UF); - Quantidade (produto, loja, cidade, UF); - Imposto ICMS (produto, loja, cidade, UF); PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Dimensões: As tabelas dimensões foram escolhidas seguindo a necessidade do projeto em que além da venda, quantidade e imposto dos produtos necessitam de informações de cadastro das lojas com seus endereços, cadastro dos itens de produtos e o tempo em várias formas (dia, semana, mês, ...). PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Modelo MMD sugerido PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Um Cubo com dois Dices e dois Slices. Slices: •Venda total de cada loja •Imposto total de cada loja Dices: • Vendas de cada loja no mês de junho/2010 e julho/2010 • Imposto de cada loja no mês de junho/2010 e julho/2010. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Documentação Métricas: Estão na tabela fato “vendas” e foram nomeadas e criadas da seguinte forma: quant_unit decimal(18,2) NOT NULL (informação de quantidade vendida do produto); valor_unit decimal(18,2) NOT NULL (informação de valor unitário do produto); pct_icms_unit decimal(18,2) NOT NULL (taxa percentual de ICMS no momento da venda do produto); PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Documentação Chaves Estrangeiras: Estão na tabela fato “vendas” e foram nomeadas e relacionadas nas seguintes tabelas: cod_tempo int NOT NULL esta relacionada a coluna id_tempo da tabela tempo; cod_produto int NOT NULL esta relacionada a coluna id_produto da tabela produto; cod_loja int NOT NULL esta relacionada a coluna id_loja da tabela loja; PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Documentação Chaves Primárias: id_tempo int NOT NULL esta na tabela tempo; id_produto int NOT NULL esta na tabela produto; id_loja int NOT NULL esta na tabela loja; cod_tempo int NOT NULL é chave composta e esta na tabela vendas vendas; cod_produto int NOT NULL é chave composta e esta na tabela vendas vendas; cod_loja int NOT NULL é chave composta e esta na tabela vendas vendas. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Benefícios da Modelagem Multidimensional Alinhar projetos de tecnologia com as metas estabelecidas pelas empresas na busca do máximo retorno do investimento; Ampliar a compreensão das tendências dos negócios, propiciando melhor consistência no momento de decisão de estratégias e ações; Facilitar a identificação de riscos e gerar segurança para migração de estratégias, criando maior efetividade nas implementações dos projetos; PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Benefícios da Modelagem Multidimensional Permitir um planejamento corporativo mais amplo, substituindo soluções de menor alcance por resultados integrados pela informação consistente; Gerar, facilitar o acesso e distribuir informação de modo mais abrangente para obter envolvimento de todos os níveis da empresa; PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Benefícios da Modelagem Multidimensional Ligar e consolidar dados de diferentes sistemas de modo a oferecer uma visão comum do desempenho da empresa; Automatizar tarefas eliminando os erros ao colocar as pessoas no fim dos processos; Oferecer dados estratégicos para análise com um mínimo de atraso em relação a uma transação ou evento dentro da empresa. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Benefícios da Modelagem Multidimensional A Modelagem Multidimensional ajuda as empresas a tomarem decisões estratégicas e rápidas. Transformar dados em informações é preciso ações específicas dos gestores. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Como Transformar os Dados Modelados em Dados para Carga na Base Analítica? PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Utilizando Extração, Transformação e Carga PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que significa ETL (Extração, Transformação e Carga) “E” Extração (Extract) é a coleta de dados dos sistemas de origem (também chamados Data Sources ou sistemas operacionais), extraindo-os e transferindo-os para a base analítica. O ETL pode operar independente dos sistemas operacionais. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que significa ETL (Extração, Transformação e Carga) “T” Transformação (Transform) Transformar,Limpar e Modificar a informação. Principais atividades: Limpar os dados; Garantir qualidade dos dados; Descartar dados inválidos; Padronização. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que significa ETL (Extração, Transformação e Carga) “T” Transformação (Transform) Os dados a serem carregados devem possuir qualidade e para garantir a qualidade dos dados devemos observar o seguinte: Unicidade: Evitar duplicações de informação; Precisão: Os dados não devem perder suas características originais assim que são carregados no DW; PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que significa ETL (Extração, Transformação e Carga) “T” Transformação (Transform) Completude: Não deve gerar dados parciais dos dados importantes para a análise. Consistência: Os dados devem ser coerentes com os dados das dimensões, devem ter forma homogênea para a carga no DW. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que significa ETL (Extração, Transformação e Carga) “T” Transformação (Transform) Enquanto busca-se a homogeneidade dos dados podem ocorrer dois tipos de conflitos: Semânticos: São aqueles que envolvem a palavra ou o nome associado às estruturas de modelagem, como ter o mesmo nome para entidades distintas. Estruturais: São os que dizem respeito à própria estrutura da modelagem e os mais típicos são: PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que significa ETL (Extração, Transformação e Carga) “T” Transformação (Transform) Diferenças de unidades: Quando as unidades utilizadas diferem, embora forneçam a mesma informação. Como uma distância em metros ou em quilômetros; Diferenças de precisão: Quando a precisão escolhida varia de um ambiente para outro. Se um valor monetário é armazenado com número de casas decimais diferente do que foi extraído; PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que significa ETL (Extração, Transformação e Carga) “T” Transformação (Transform) Diferenças em códigos ou expressões: quando o código utilizado difere um do outro. EX: quando o sexo é definido com código M(masculino) e F(feminino) ou (1) masculino e (2) feminino; Diferenças de abstração: quando a forma de estruturar uma mesma informação segue critérios diferentes. EX: um endereço está armazenado em uma coluna ou em várias separado em nome da rua, número, complemento, etc. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Nessa etapa os dados são limpos e formatados, as diferenças de tipos de dados são tratadas. O controle de erros de carga é feito nesse momento. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que significa ETL (Extração, Transformação e Carga) “T” Transformação (Transform) O processo de qualidade dos dados precisa atender 4 características principais: Ser completo; Ser rápido; Ser corretivo; Ser transparente. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que significa ETL (Extração, Transformação e Carga) “T” Transformação (Transform) Itens a serem tratados na padronização: Padronizar os dados das colunas das dimensões; Padronizar e garantir regras de indicadores; Fazer duplicação, quando necessário; Garantir regras de negócios para colunas, como endereço por exemplo; Padronizar métricas, tipos de dados, etc. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que significa ETL (Extração, Transformação e Carga) “C” Carga (Load) Após a Transformação pode-se iniciar o processo de Carga, nesse momento são carregados os dados das Dimensões e da tabela Fato. Itens a serem tratados na Carga: Realizar a carga das dimensões e suas variações; Realizar a carga das tabelas Fatos e suas variações; Realizar derivações, fatos agregados e outros objetos da camada dimensional de dados. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que significa ETL (Extração, Transformação e Carga) “C” Carga (Load) Cuidados que devemos ter neste processo: Desligamento de índices e referências de integridade (isso pode prejudicar a validade dos dados, pois não são validados no momento da inserção). Saber que alguns dados podem não ser carregados no momento da carga e assim devemos verificar os motivos para que possamos solucionar o problema. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Nessa etapa listamos e estudamos todos os processos que são responsáveis por extrair e capturar os dados dos sistemas legados. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que significa ETL (Extração, Transformação e Carga) “C” Carga (Load) Após as dimensões estarem corretamente carregadas, já é possível iniciar a carga da tabela Fato, direcionando quais regras serão utilizadas como, por exemplo, filtros do que será inserido ou somas a serem realizadas, provocando o aparecimento de regras que passaram despercebidas no início da modelagem. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que significa ETL (Extração, Transformação e Carga) “C” Carga (Load) A tabela Fato demanda cuidados na sua carga, como o uso das chaves artificiais das dimensões para que se tenha uma integridade referencial, controle de valores nulos obtidos no momento da transação para que não gerem a falta de integridade referencial como datas que, estando nulas, invalidarão o histórico do fato. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que significa ETL (Extração, Transformação e Carga) Kimball dividiu o processo de ETL em 34 componentes. Os componentes são agrupados em 4 categorias (E, T, L e M) conforme a figura a seguir: PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Temos uma camada que podemos utilizar antes da Carga na base de dados Analítica. Que camada é essa? Operational Sata Store PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que é ODS (Operational Sata Store)? É normal que a carga inicial seja feita para que todos os dados do sistema origem sejam transferidos para uma área da base analítica chamada de Stage e posteriormente passada para a área chamada ODS. Após isso as demais cargas serão incrementais, como diz Kimball é a mais eficiente e carrega somente os registros que foram alterados ou inseridos na base origem. http://projetoseti.com.br/o-que-e-business-intelligence-parte-4-etl/ PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que é ODS (Operational Sata Store)? A construção de um ODS é facultativa, entretanto, ajuda em muito a diminuir os esforços de construção da base analítica. Todo o esforço de integração entre os sistemas transacionais da empresa seriam depositados no ODS e a carga da base seria simplificada demaneira incomensurável. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O que é ODS (Operational Sata Store)? Para trabalharmos com ODS precisamos da Stage Area. Por que precisamos da Stage Area? PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE A Stage Area é necessária porque simplesmente temos os dados, ou seja, informações de diversas origens diferentes e de diversas plataformas desde uma alta plataforma, a necessidade de utilização da Stage Area para integrar todos os tipos de dados em um único formato. Colocamos esses dados em um repositório que chamamos de Stage Area. Dados integrados PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Por que precisamos da Stage Area? A Stage Area garante a existência única de dados, eliminando a possibilidade de termos dados repetidos ou similares ou até mesmo com valores diferentes na base, dados não sincronizados e dados “sujos”. A Stage Area deve ser usada também para “limpar” os dados, é o único lugar que serve para determinar os valores que vêm efetivamente de sistemas legados. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Por que precisamos da Stage Area? Resumindo: a Stage Area é uma camada intermediária entre a extração dos dados legados até a carga na base analítica, isto é muito utilizado quando estamos extraindo um volume enorme de dados, esta camada auxilia na limpeza destes dados, ou seja, temos que carregar somente aquilo que interessa aos gestores e também dados confiáveis. Com o volume de dados que temos hoje oriundos de várias fontes de dados externas isto é bem utilizado. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O Processo de ETL PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O processo ETL O ETL é um processo para extrair dados de um sistema de Bases de Dados (BD), sendo esses dados processados, modificados, e posteriormente inseridos numa outra BD. Estudos relatam que o ETL e as ferramentas de limpeza de dados consomem um terço do orçamento num projeto de BI, podendo, no que respeita ao tempo de desenvolvimento de um projeto de BI, chegar a consumir 80% desse valor. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O processo ETL O processo deve ser bem planejado para evitar transtornos futuros e até mesmo para que não ocasione, em casos extremos, a interrupção dos sistemas operacionais da empresa. Dessa forma, a base analítica terá informações tratadas, com qualidade e grande valor para apoiar as decisões organizacionais. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O processo ETL Um bom ETL deve ter escalabilidade e ser de manutenção bem fácil. Devemos analisar a janela de operação, pois como trabalha com grandes volumes de dados não é em qualquer momento que ele poderá ser executado. Devemos também analisar a periodicidade de execução. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O processo ETL Não é visto pela área usuária, por isso muitas vezes não se dá a ele a sua devida importância. Mas tudo depende dele! Sem o ETL não existe projeto BI. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O processo de ETL pode ser representado pela imagem abaixo: http://igti.com.br/blog/wp-content/uploads/2017/08/image-1.png PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE A camada inferior representa o armazenamento dos dados que são utilizados em todo o processo. No lado esquerdo podemos observar os dados “originais” provenientes, na maioria dos casos, de BD ou, então, de ficheiros com formatos heterogêneos, por exemplo, de texto. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O processo ETL Importante: alterações nos dados não afetam as fontes originais, mas sim, os dados no momento de extração para o repositório da base analítica. Os ajustes são modelados de acordo com as necessidades da modelagem, atendendo assim às restrições que são necessárias para esse modelo. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O processo ETL Para iniciar o processo de ETL no que tange aos dados a serem carregados temos que prestar atenção em alguns pontos: Comparar o número de registros entre os dados das fontes e o número de registros a serem carregados; Comparar valores únicos de determinados atributos entre as fontes e os dados a serem carregados; Procurar fazer um bom esquema de dados para perceber as limitações dos valores atribuídos; PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O processo ETL Procurar validar os conteúdos de cada atributo, ou seja, não permitir que por razões de codificação o limite de caracteres entre cada esquema relacional (fonte e destino) não resulta na falha do fluxo de dados; Assegurar que os dados são transformados corretamente de acordo com as regras de negócio especificadas; Criar testes, os mais diversos possíveis para antever algumas situações consequentes; PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE O processo ETL Validar o processamento correto de campos no ETL tais como chaves estrangeiras;. Verificar sempre se os tipos de dados presentes na base analítica são os que se tinham planejado; Procurar e testar a integridade referencial entre as tabelas. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Trabalho preliminar ao ETL O projeto de arquitetura deve ter sido esboçado; O projeto lógico deve estar terminado; O mapeamento de fonte-para-destino para todos os elementos de dados, deve estar pronto ou quase no fim; O projeto físico e o trabalho de implementação devem estar bem adiantados; PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Trabalho preliminar ao ETL Obtenha toda informação relevante, incluindo o processamento que a extração terá de acrescentar nos sistemas de transação (alteração de sistema e degradação de performance); Teste algumas alternativas chaves; Faz sentido manter o processo de transformação no sistema fonte, no sistema destino (base analítica) ou em sua própria plataforma? PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Trabalho preliminar ao ETL Que ferramentas estão disponíveis em cada ambiente e quão efetivas elas são? Monte o ambiente de desenvolvimento, incluindo diretórios, convenções de nomes (Dese, Teste, Produção, etc..); Em caso de ser necessário desenvolver/implementar aplicações, verifique o ambiente já disponível. PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Mudança na tecnologia: Os dados são transferidos de BDs já conhecidos para uma nova tecnologia de SGBD; Na seleção de dadosdo ambiente operacional muitas vezes vários campos de um sistema transacional, vão compor um único campo no DW; Dificilmente há o modelo de dados dos sistemas antigos, e se existem não estão documentados; Os dados são reformatados (ex: dd/mm/aaaa p/ aaaa/mm); Problemas que encontramos no ETL PROJETO E IMPLEMENTAÇÃO DE DATAWAREHOUSE Várias origens para um mesmo dado: conflitos estruturais, de conteúdo e de formato nos dados; Dados “faltantes”, dados com “erros”; Não conformidade dos dados com as regras; Diversos formatos de dados (xls, pdf, xml, etc.); Incompatibilidade entre ambientes operacionais diferentes. Problemas que encontramos no ETL