Flavia de Paiva Barbosa
Propaganda
a BACHARELADO EM SISTEMAS DE INFORMAÇÃO Trabalho de Conclusão de Curso Projeto: Transformação de Dados para Data Warehouse Alunos: Flavia de Paiva Barbosa Karla Maiana da Cunha Oliveira 2002010943 UC04007941 Professor: Cândido Salgado 2007 1 Flavia de Paiva Barbosa Karla Maiana da Cunha Oliveira TDDW Transformação de Dados para Data Warehouse Monografia submetida ao XXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXX Universidade Católica de Brasília para obtenção do Grau XXXXXXXXXXXXXXXXXXXXXXX. Brasília 2007 2 TERMO DE APROVAÇÃO Monografia defendida e aprovada como requisito parcial para obtenção do grau de Bacharel em Sistemas de Informação, defendida e aprovada, em XX de XXXX de 2007, pela banca examinadora constituída por: Prof. Cândido Salgado Filho Orientador Prof. Vilson Hartman Examinador Interno Brasília UCB 3 AGRADECIMENTOS “Agradecemos a Deus pela força incessante nos momentos mais difíceis, aos nossos Pais pelo apoio e compreensão infindáveis, aos nossos Amigos por nos proporcionar momentos inesquecíveis ao longo dessa jornada, aos nossos Mestres pelo privilégio do aprendizado e a Nossa determinação e lealdade ao propósito deste Projeto”. 4 Resumo A informação constitui-se como o principal instrumento utilizado pelas Organizações na busca pelo diferencial competitivo. Entretanto, a melhor informação caracteriza-se pelo nível de disponibilidade, acuracidade e direcionamento para o negócio ao qual se aplica. BI (Business Intelligence) é uma metodologia que se baseia na aplicação de tecnologias e conceitos como mecanismo para o alcance de informações acertadas e fundamentais para o processo decisório. Sua edificação comporta a utilização de ferramentas como Data Warehouse, utilizada no armazenamento dos dados padronizados e oriundos de variadas bases de dados, e a utilização do processo que compreende as técnicas de Extração, Transformação e Carga dos dados (Extract, Transform and Load - ETL) focado na proposta de captura, padronização e entrega dos dados. ETL não se restringe apenas a uma etapa no complexo processo de definição estratégica, mas a uma etapa que envolve quase a totalidade do tempo, esforço e despesa para se alcançar o objetivo final desse processo. 5 Abstract Information is main instrument used by organizations in the search for competitive differential. However, the best information is characterized by the level of availability, accuracy and targeting for the business to which it applies. BI (Business Intelligence) is a methodology that is based on the application of technologies and concepts such as mechanism for the reach of information right and fundamental to decision making. His building includes the use of tools such as Data Warehouse, used in the storage of standardized data deriving from varied databases, and the use of the process that includes the techniques of Extraction, Transformation and Load of Data (Extract, Transform and Load-ETL) focalized in the capture, standardization and delivery of data. ETL is not limited to only one step in the complex process of defining strategic, but a step that involves almost all the time, effort and expense to achieve the final goal of this process. 6 SUMÁRIO Resumo ....................................................................................................................................... 5 Abstract ...................................................................................................................................... 6 Lista de Figuras ......................................................................................................................... 9 Capítulo 1 - Introdução ........................................................................................................ 10 1.1 Motivação ............................................................................................................................12 1.2 Problemas Diagnosticados .................................................................................................12 1.3 Surgimento da Necessidade da Pesquisa...........................................................................13 Capítulo 2 - Objetivo da Pesquisa ........................................................................................ 14 2.1 Objetivo Geral .....................................................................................................................14 2.2 Objetivos Específicos ..........................................................................................................14 Capítulo 3 - Proposta da Pesquisa ....................................................................................... 15 3.1 Resultados Esperados .........................................................................................................15 3.2 Restrições da Pesquisa Proposta .......................................................................................15 3.3 Recursos Necessários para Execução do Novo Software.................................................15 3.4 Relação Custo x Benefício ..................................................................................................16 3.5 Áreas Afetadas pela Pesquisa ............................................................................................16 Capítulo 4 - Business Intelligence ....................................................................................... 18 Capítulo 5 - Data Warehouse .............................................................................................. 22 Capítulo 6 - Extração, Transformação e Carga ................................................................. 30 6.1 Staging Area .........................................................................................................................30 6.2 Modelo Dimensional ...........................................................................................................35 6.2.1 Tabela Fato ..........................................................................................................................36 6.2.2 Tabela Fato de Transação ..................................................................................................38 6.2.3 Tabela Fato de Imagem Periódica .....................................................................................38 6.2.4 Tabela Fato de Imagem Acumulada .................................................................................39 6.2.5 Tabela Dimensão .................................................................................................................39 6.3 Documento Lógico ..............................................................................................................41 6.4 Fontes Heterogêneas ...........................................................................................................42 6.5 Detecção de Mudanças .......................................................................................................44 6.6 Qualidade dos Dados ..........................................................................................................46 Capítulo 7 - Ferramenta para Tratamento de Dados ......................................................... 48 7.1 Especificação de Requisitos................................................................................................48 7.1.1 Identificação dos Interessados no Sistema........................................................................49 7.1.2 Principais Necessidades dos Interessados .........................................................................49 7 7.1.3 Requisitos Funcionais .........................................................................................................50 7.1.3.1 Realizar Conexão ................................................................................................................50 7.1.3.2 Visualizar Tabelas...............................................................................................................50 7.1.3.3 Padronizar Dados ...............................................................................................................50 7.1.3.4 Monitorar as execuções efetuadas .....................................................................................50 7.1.3.5 Gerar dados estatísticos e logs de execução ......................................................................51 7.1.3.6 Visualizar todos os projetos departamentais....................................................................51 7.1.3.7 Gerar mapas de execução...................................................................................................51 7.1.4 Requisitos Não-Funcionais .................................................................................................51 7.1.4.1 Segurança ............................................................................................................................51 7.1.5 Restrições do Sistema .........................................................................................................51 7.1.6 Interface Entre os Agentes Externos e o Sistema .............................................................52 7.1.7 Contexto de Utilização do Sistema ....................................................................................52 7.1.8 Procedimento Contra Perdas de Informações .................................................................52 7.1.9 Tratamento de Erros ..........................................................................................................52 7.1.10 Possibilidades de Evolução Futura ......................................... Erro! Indicador não definido. Referências Bibliográficas ...................................................................................................... 54 8 Lista de Figuras Figura 1 - Componentes de um Business Intelligence .......................................................... 20 Figura 2 - A questão da não-volatilidade (Fonte: Inmon, 2002) .......................................... 23 Figura 3 - A questão da variação em relação ao tempo (Fonte: Inmon, 2002) .................... 23 Figura 4 - Estrutura do Data Warehouse (Fonte: Inmon, 2005).......................................... 24 Figura 5 - Granularidade (Fonte: Inmon, 2002) ................................................................... 25 Figura 6 - A questão da integração (Fonte: Inmon, 2002) ................................................... 28 Figura 7 - Áreas de preparação (Fonte: Kimball, 2004) ....................................................... 30 Figura 8 - Integração de fontes de dados heterogêneas (Fonte: Kimball, 2004).................. 34 Figura 9 - Tabela fato (Fonte: Kimball, 2002)....................................................................... 36 Figura 10 - Tabela dimensão (Fonte: Kimball, 2002) ........................................................... 40 9 Capítulo 1 - Introdução A competitividade, característica do ambiente empresarial e que define as regras do processo decisório nas organizações, é o principal combustível na reavaliação dos dados que influenciam direta e indiretamente o ambiente de negócio das empresas. Sendo assim, é preciso inovar e estar atento a todos os fatores que possam causar queda na produtividade e no crescimento da organização. Surge então a necessidade de integrar os dados internos e externos ao âmbito empresarial, no intuito de gerar informações consistentes e que sirvam como base para a tomada de decisões estratégicas. A Inteligência do Negócio (Business Intelligence – BI) surge em meados dos anos 80 e compreende: a aplicação de tecnologias e conceitos em processos empresariais com o objetivo de extrair informações acertadas e fundamentais para a tomada de decisão, favorecendo o aumento da vantagem competitiva. Esta atividade realizada pelo BI se contempla com o estreitamento da comunicação entre os setores departamentais, com as atividades e dados coordenados, além de permitir à organização responder mais rapidamente às mudanças que ocorrem no mercado. No entanto, para que este auxílio ocorra de forma eficaz o BI se estrutura basicamente em três pilares: Processo Decisório, Armazém de dados (Data Warehouse - DW) e Extração, Transformação e Carga (Extract, Transform and Load - ETL). [Kimball, 2002] A tecnologia de Data Warehouse é responsável por integrar bases de dados dando suporte para a atuação da equipe estratégica. As organizações, em sua grande maioria, possuem bancos de dados departamentais e independentes e isso facilita a duplicidade e inconsistência das informações. Um exemplo dessa divergência pode ocorrer ao se levantar dados que foquem um mesmo propósito, mas expostos por equipes distintas: os valores 10 identificados por cada uma certamente apresentarão conteúdos divergentes. A faixa de diferenciação reportada nestes dados provocaria dúvidas na Diretoria ao tomar uma decisão que afetaria o rumo do seu negócio. Sem uma unificação e integração dos dados o processo decisório passaria a ser feito de forma intuitiva e empírica. [Inmon, 1997] Segundo Gupta (1997), Data Warehouse é um ambiente estruturado, extensível, projetado para a análise de dados não voláteis, lógica e fisicamente transformados, provenientes de diversas aplicações, alinhados com a estrutura da empresa, atualizados e sumarizados para análise rápida. A principal característica de um DW é formar um histórico com as informações relevantes que passam pela organização com o intuito de se fazer projeções no negócio baseados em fatos que ocorreram no ambiente interno e externo à empresa ao longo de um determinado tempo. Para isso, antes de sua formação, estes dados são capturados de diversas fontes e posteriormente transformados, de modo a eliminar inconsistências e ambigüidades, evitando que nada seja desconsiderado por uma simples questão de sincronização em seu formato. Esta descrição é conceitualmente conhecida como ETL. Ao formar este armazém de dados, os usuários finais poderão ter acesso dinâmico a estas informações a partir de ferramentas OLAP (On-Line Analytic Processing). Estas aplicações extraem os dados do DW e constroem relatórios que respondem a suas perguntas gerenciais. ETL compreende o estabelecimento de conexões com fontes de dados operacionais. Essas conexões possuem características particulares, de acordo com cada fabricante. Cada fonte deve receber um tratamento especial de modo a possibilitar que a organização utilize dados provenientes das suas diversas fontes. Após essa etapa, os dados lidos passam por uma fase de limpeza, tratamento e conversão para que sejam adequados às regras de negócio e possam ser armazenados na base DW. Para que se tenha um retorno sobre o que ocorreu na fase de ETL, relatórios devem ser apresentados com percentuais de leitura, transformações 11 bem sucedidas e cargas na base de dados do DW. Essa visão levará ao Administrador de dados respostas para o aperfeiçoamento dos padrões e conseqüente aumento do grau de confiabilidade dos dados primários. [Kimball, 2004] Diante desta necessidade de dados de qualidade a Administração de Dados revela então uma dependência do processo de ETL. Com isso, cada vez mais as empresas vêm buscando o domínio sobre todos os padrões e formas utilizadas nos dados dos sistemas, pois isso minimiza problemas no processo de extração de dados. 1.1 Motivação A crescente necessidade de aprimorar os meios de auxílio à tomada de decisão dentro das empresas possibilitou o desenvolvimento de tecnologias que visam integrar as diferentes fontes de informação, de modo a assegurar a acuracidade destes dados. O ETL tem papel fundamental dentro desse processo, pois engloba a extração dos dados transacionais, a transformação e carga em uma base corporativa e analítica. É importante que durante a etapa ETL exista uma preocupação quanto à qualidade e confiabilidade dos dados para que seja evitada a duplicidade de informações e inconsistências no DW. A tomada de decisão possui papel fundamental para a permanência de uma organização no mercado e precisa ser baseada em informações confiáveis, seguras e coerentes com os seus objetivos. 1.2 Problemas Diagnosticados • Necessidade da apresentação de dados estatísticos referentes às cargas efetuadas – tempo de execução, número de registros lidos com sucesso, não lidos, transformados, gravados com sucesso e rejeitados; 12 • Dificuldades com a má administração dos dados propiciam a ocorrência de inconsistências e duplicidades, uma vez que um mesmo dado pode aparecer em mais de uma base com nomes e formatos diferentes; • Dificuldades em se conseguir uma conversão de dados sincronizada com todos os bancos e com um alto nível de qualidade e segurança; • Complexidade em estabelecer conexões com todos os bancos de dados transacionais existentes, devido a diferenças em suas estruturas e gerenciamentos; • Dificuldades em se identificar as reais alterações ocorridas na base de dados transacional, forçando a leitura de todos os registros. 1.3 Surgimento da Necessidade da Pesquisa Tendo em vista as falhas encontradas e a importância da etapa de ETL, torna-se evidente a necessidade de construção de uma ferramenta que evite ou minimize os impactos negativos nas fases seguintes do BI. 13 Capítulo 2 - Objetivo da Pesquisa 2.1 Objetivo Geral Detalhar o processo de ETL e produzir uma ferramenta que contemple algumas das funcionalidades executadas nessa etapa. 2.2 Objetivos Específicos • Possibilitar que a administração dos dados seja executada de maneira eficiente, auxiliada pela criação de metadados e padronização dos tipos dos campos; • Possibilitar a conversão automática dos tipos de dados quando repositórios com definições diferentes forem utilizados; • Possibilitar o estabelecimento de conexão com os diversos bancos de dados; • Possibilitar a identificação dos registros que realmente deverão ser carregados, evitando as leituras de toda a base; • Possibilitar a geração de dados estatísticos e logs de execução. 14 Capítulo 3 - Proposta da Pesquisa 3.1 Resultados Esperados Obter ao final da pesquisa, uma ferramenta ETL que possibilite uma eficiente administração e permita que seja alcançado um alto nível de qualidade dos dados. 3.2 Restrições da Pesquisa Proposta Apesar da citação inicial, não será detalhado o ponto que faz referência à possibilidade de identificação dos registros que realmente deverão ser carregados, o que evitaria a leitura de toda a base. Além disso, não será esmiuçado o ponto que faz referência a geração de dados estatísticos e logs de execução. 3.3 Recursos Necessários para Execução do Novo Software • Descrição do Hardware Ideal o Cliente: Processador AMD Turion 1.6 GHz ou Intel Core 2 Duo 1.6 GHz, Memória RAM 2 GB, HD 120 GB. o Servidor: Processador Opteron Dual Core 2.6 GHz, 4 GB RAM, 4 HDs Ultra SCSI, 300 GB com possibilidade de expansão. • Descrição do Hardware Mínimo o Cliente: Processador AMD Sempron 1.6 GHz ou Intel 1.3 GHz, Memória RAM 512 MB, HD 80 GB. o Servidor: Processador AMD Turion 1.6 GHz ou Intel Core 2 Duo 1.6 GHz, Memória RAM 2 GB, HD 120 GB. • Descrição do Software o Sistema operacional Unix ou Windows de 32 bits, Máquina Virtual Java 5. 15 • Configuração da Rede o LAN ou Ethernet, 100 mbps. • Configuração do Banco de Dados o O banco de dados estará presente no Servidor. Banco: MySQL 5. • Pessoal Exigido o Um Gerente de Projeto, dois Analistas/ Programadores. 3.4 Relação Custo x Benefício Pesquisa economicamente viável, demandando gastos referentes aos recursos materiais e humanos para o desenvolvimento e manutenção. O custo ao final do Projeto é justificado pelo benefício fornecido pela utilização da ferramenta que proporcionará melhor utilização e armazenamento de dados de importância fundamental para a tomada de decisão. 3.5 Áreas Afetadas pela Pesquisa Dentre as diversas áreas que se encontram envolvidas no foco desta Pesquisa, podemos ressaltar: - Setor Estratégico: Responsável por analisar as informações que foram capturadas, tratadas e entregues numa base unificada. Passará a analisar dados provindos de uma fonte especialmente tratada e padronizada; - Administração de Dados: Responsável pelo aperfeiçoamento dos padrões utilizados e busca por uma maior confiabilidade dos dados. Passará a analisar os resultados obtidos do processo de transformação e padronização e verificarão se esse processo deverá sofrer ou não alteração; 16 - Administração de ETL: Responsável pelo manuseio da ferramenta que realizará o processo de Extração, Transformação e Carga. Passará a analisar se as funcionalidades da ferramenta condizem com o que definido; - Administração de Banco de Dados: Responsável pelo gerenciamento dos bancos de onde os dados serão provindos. Passará a disponibilizar os mecanismos de acesso a estes bancos para possibilitar a extração destes pela ferramenta de ETL. 17 Capítulo 4 - Business Intelligence O termo Business Intelligence (BI) foi cunhado pelo grupo Gartner, no final dos anos 80 e definido como “um processo que engloba o acesso e exploração da informação, a análise destas informações, bem como o desenvolvimento de conhecimento e compreensão, o que leva a uma melhor tomada de decisão”. Business Intelligence pode ser entendido também como um conceito que envolve a Inteligência Competitiva (CI), a Gerência de Conhecimento (KMS) e a IBI (Internet Business Intelligence), pesquisa e análise de mercado, relacionados à nova era da Economia da Informação, dedicada a captura de dados, informações e conhecimentos que permitem as organizações competirem com maior eficiência no contexto atual, é um conjunto de ferramentas utilizado para manipular uma massa de dados operacional em busca de informações essenciais para o negócio [Mello, 2006]. Mais detalhadamente, CI é um processo sistemático que visa descobrir as forças que regem os negócios, reduzir o risco e conduzir o tomador de decisão a agir antecipadamente, bem como proteger o conhecimento gerado. KMS teve sua discussão emergida nos anos 90 e objetiva gerenciar o conhecimento acumulado dos recursos humanos da empresa a fim de transformá-los em ativos da empresa. Ela cria condições para que o conhecimento seja criado, socializado, externalizado dentro da empresa, transformando-o de tácito em explícito. IBI consiste na incorporação da web no conceito de BI, e na utilização deste meio como troca de informações dentro e fora das empresas. Dentre as vantagens remetidas ao uso do BI podem-se ressaltar aquelas que estão relacionadas com o acesso a informação de qualidade que permite que as empresas conheçam melhor a sua realidade, quer seja interna, quer seja voltada para o exterior, permitindo-lhes obter importantes indicadores que irão melhorar o desempenho da sua atuação e a inovação tão necessária ao seu crescimento. Mais especificamente, o BI é um conceito que permite: 18 • Alinhar projetos de tecnologia com as metas estabelecidas pelas empresas na busca pelo máximo retorno do investimento; • Ampliar a compreensão das tendências dos negócios, ao propiciar mais consistência no momento de decisão de estratégias e ações; • Permitir uma análise de impacto sobre rumos financeiros e organizacionais, com o objetivo de criar mudanças nas iniciativas gerenciais; • Facilitar a identificação de riscos e gerar segurança para migração de estratégias, criando maior efetividade nas implementações dos projetos; • Permitir um planejamento corporativo mais amplo, ao substituir 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 e todos aqueles que possam, ao usá-la, agregar mais valor; • 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; Torna-se imprescindível ressaltar que Business Intelligence é um conceito em constante evolução que deve estar sempre alinhado aos interesses da empresa e caminhar em direção ao alcance das metas estabelecidas. No entanto, para que a utilização do BI tenha como premissa a corretitude e com isso, um alcance mais efetivo do que se deseja com o seu uso, dois pontos importantes na infra-estrutura tecnológica do processo de BI devem ser assegurados [Almeida, Ishikawa, Reinschmidt & Roeber, 1999]: 19 • Sistemas de Front-End: SAD (Sistemas de Apoio a Decisão. Ex: ERP – Enterprise Resource Planning; CRM – Customer Relationship Management), EIS (Executive Information System), Enterprise Reporting (Relatórios Corporativos), Dashboards (Painéis de Controle) e ferramentas de consulta analítica OLAP (On-line Analytical Processing); • Sistemas de Back-End: Armazém de dados (Data Warehouse), Data Mart, ferramentas de dados (Data Mining) e ferramentas de tratamento de dados ETL (Extraction Transformation and Load). Figura 1 - Componentes de um Business Intelligence O processo de ETL, como alicerce para o BI, tem como importância garantir a edificação de uma base de dados consistente para que possa ser evitada uma possível visão dissonante sobre a mesma informação. 20 As ferramentas de ETL possibilitam cinco operações principais: A primeira delas refere-se à extração dos dados que podem estar em fontes internas (sistemas transacionais, bancos de dados etc.) ou externas (em sistemas fora da empresa). Em seguida, é preciso fazer a limpeza dos dados. Nessa etapa, são corrigidas algumas imperfeições contidas na base de dados transacional, com o objetivo de fornecer ao usuário dados concisos, não redundantes e com qualidade, permitindo uma análise baseada nos valores mais próximos dos reais. A terceira operação refere-se ao processo de transformação do dado. Normalmente, os dados vêm de vários sistemas diferentes e por isso se faz necessário padronizar os diferentes formatos em um só. A quarta etapa diz respeito ao processo de carga do DW, que em geral é feito a partir de um banco de dados temporário, no qual os dados armazenados já passaram pela limpeza e integração. E, finalmente, há a etapa de atualização dos dados (Refresh). Estes dados serão posteriormente utilizados para o auxílio de tomada de decisão. 21 Capítulo 5 - Data Warehouse Data Warehouse é o conjunto de dados baseado em assuntos, integrado, não-volátil, e variável em relação ao tempo, de apoio às decisões gerenciais [Inmon, 1997]. A primeira característica do DW ressaltada neste conceito: ser baseado em assunto, assim o é por ter a responsabilidade de retornar informações referentes ao negócio da empresa. No entanto, antes que estas informações possam estar disponibilizadas é necessário que haja uma integração entre os dados das diferentes aplicações existentes na organização. O papel da integração é padronizar os dados existentes, obrigando que a inserção ocorra com dados num formato uniforme. A característica de não-volatilidade do Data Warehouse referese ao fato de que, como se constitui em um histórico, não ocorrem atualizações nos dados em seu ambiente, o seu propósito é armazenar as suas diferentes versões para se estabelecer projeções ou simulações de cenários. Como dito anteriormente, o DW também é variável em relação ao tempo, isso se manifesta da seguinte maneira [Inmon, 1997]: O horizonte de tempo válido para o Data Warehouse é significativamente maior do que o dos sistemas operacionais. Um horizonte de tempo de 60 a 90 dias é o normal para os sistemas operacionais; um horizonte de tempo de 5 a 10 anos de dados é o normal para o Data Warehouse. Bancos de Dados operacionais contêm dados de “valor corrente” – dados cuja exatidão é válida para o momento de acesso. Assim sendo, dados de valor corrente podem ser atualizados. Dados existentes no Data Warehouse não passam de uma série sofisticada de instantâneos, capturados num determinado momento. A estrutura de chaves dos dados operacionais pode conter, ou não, elementos de tempo, como ano, mês, dia. A estrutura de chave do Data Warehouse sempre contém algum elemento de tempo. 22 Não-volatilidade Data warehouse Operacional inserir alterar deletar acesso deletar inserir carga acesso alterar Manipulação dos dados registro por registro Carga em massa/ acesso dos dados Figura Figura 22 -- A A questão questão da da não-volatilidade não-volatilidade (Fonte: (Fonte: Inmon, Inmon, 2002) 2002) Variação em relação ao tempo Data warehouse Operacional • • • horizonte de tempo – atual até 60-90 dias alteração de registros estrutura de chaves pode/não pode conter elemento de tempo • • • horizonte de tempo – 5-10 anos imagens sofisticadas de dados estrutura de chaves contém elemento de tempo Figura 3 - A questão da variação em relação ao tempo (Fonte: Inmon, 2002) A estrutura do Data Warehouse é caracterizada pela diferenciação dos dados em: [Inmon, 1997] • Dados em nível de detalhe mais antigo: Neste caso os dados armazenados possuem um tempo de alocação mais significativo, como por exemplo, 10 (dez) anos. • Dados em nível de detalhe corrente: Neste caso os dados armazenados possuem um tempo de alocação corrente e uma probabilidade maior de ter realizada a sua consulta. 23 • Dados em nível levemente resumido, referentes apenas a um departamento ou pequena parte da organização: Neste caso os dados são armazenados em DataMarts. • Dados em nível altamente resumido: Neste caso os dados armazenados permitem uma maior possibilidade de consultas. [Inmon, 1997] Dados que apresentam alta probabilidade de acesso e baixo volume de armazenamento reside em um meio rápido e relativamente caro. Dados que apresentam baixa probabilidade de acesso e grande volume de armazenamento residem em um meio menos dispendioso e de acesso mais lento. Vendas mensais por linha de produtos 1981-1992 Altamente resumido M M e e tt aa dd aa d od o Vendas semanais por linha de subprodutos 1984-1992 Levemente resumido (datamart) Detalhes atuais Transformação operacional Detalhes antigos Detalhes das vendas 1990-1991 Detalhes das vendas 1984-1989 Figura 4 - Estrutura do Data Warehouse (Fonte: Inmon, 2005) Esta diferenciação dos dados quanto ao seu detalhamento se traduz como Granularidade. Quanto mais alto o nível de granularidade, menos detalhe contido em seus dados. Quanto menor o nível de granularidade, mais detalhe contido em seus dados. Essa característica do Data Warehouse também definirá a sua proporção (tamanho). No exemplo da figura 4, observa-se que no lado esquerdo ocorre o aparecimento de um baixo de nível de granularidade: cada chamada telefônica é registrada em detalhe, ou seja, 24 ao final de um mês cada cliente possui em média 200 registros que acarretam, juntos, um valor próximo a 40 mil bytes. No lado direito o nível de granularidade é alto: para cada mês, há um registro para cada cliente, e este registro necessita de aproximadamente 200 bytes. Percebe-se então, uma discrepância no detalhamento nos dois casos. [Inmon, 1997] Granularidade Alto nível de detalhe EXEMPLO: Os detalhes de cada chamada telefônica por cliente por mês 40.000 bytes por mês 200 registros por mês 01 atividaderec. 02 data 02 tempo 02 para quem 02 chamada completada 02 tempo completado 02 longa distância 02 celular 02 taxa especial ................... ................... Baixo nível de detalhe EXEMPLO: O resumo das chamadas telefônicas por cliente por mês 200 bytes 1 registro por mês M 01 atividaderec. 02 mês e t 02 chamadasAcum a 02 duraçãoMedia d 02 longaDistanciaAcum a d 02 InterrupçãoAcum . . . . . . . . .o. . . . . . . . . . Figura 5 - Granularidade (Fonte: Inmon, 2002) A enorme quantidade de dados de detalhe corrente que será atingido pelo Data Warehouse será inevitável, e a dificuldade em gerenciar essa gama de dados será proporcional ao seu tamanho. No entanto, para facilitar esta atividade a técnica de Particionamento de Dados é utilizada. Particionar significa distribuir os dados em unidades físicas menores com a finalidade de aumentar a flexibilidade no gerenciamento dos dados e melhorar o desempenho das consultas realizadas. 25 Quando os dados residem em unidades físicas de tamanho maior, entre outras coisas, eles não podem ser [Inmon, 1997]: • Reestruturados facilmente; • Indexados livremente; • Pesquisados sequencialmente, se necessário; • Reorganizados facilmente; • Recuperados facilmente; • Monitorados facilmente. Existem diversas formas de se organizarem a estruturação dos dados no Data Warehouse. Dentre as mais utilizadas encontram-se [Inmon, 1997]: • Dados Cumulativos Simples: Neste caso os dados são transportados do ambiente operacional para o DW e depois são compactados em forma de registros. Esta compactação terá como regra a forma como o DW foi organizado. As transações são resumidas por dia. • Dados de Resumo Rotativo: Neste caso os dados são transportados do ambiente operacional para o DW e a forma como eles são organizados ocorre da seguinte maneira: Nos primeiros sete dias da semana, a atividade é resumida em sete posições diárias. Depois estas sete posições são acumuladas e colocadas na primeira posição semanal. E assim, mensalmente e anualmente. Existem algumas vantagens e desvantagens na utilização destas duas formas de estruturação dos dados [Inmon, 1997]: Dados de Resumo Rotativo: • Bem compactado; • Alguma perda de detalhe; • Quanto mais antigos os dados se tornam, menos detalhes são mantidos. 26 Dados Cumulativos Simples: • Grande demanda por armazenamento; • Nenhuma perda de detalhe; • Muito processamento para qualquer tratamento de dados. O Data Warehouse é formado pelos dados oriundos dos sistemas operacionais existentes na organização. No entanto, a captura destes dados constitui uma tarefa nada simples, pois é necessário que algumas medidas sejam tomadas já que estes sistemas muitas vezes não estão integrados. A falta de integração é identificada, por exemplo, quando mesmos dados são rotulados de maneiras diferentes ou então quando os mesmos dados existem em locais distintos. Um exemplo simples da falta de integração é o fato de os dados não poderem ser codificados de forma coerente, como exemplificado na codificação de gênero. Em uma aplicação, o gênero é codificado como “m/f”. Em outra, ele é codificado como “0/1”. À medida que os dados passam para o Data Warehouse, os diferentes valores precisam ser corretamente decodificados e recodificados com o valor apropriado. [Inmon, 1997] A transformação de campos é outra questão de integração. O mesmo campo existe em quatro aplicações com quatro nomes diferentes. Para que os dados sejam passados corretamente para o Data Warehouse é necessário que ocorra um rastreamento comparativo entre os diferentes campos existentes e os campos do DW. [Inmon, 1997] Para que a utilização do DW ocorra de forma eficiente e eficaz é necessário que haja uma aplicação de esforço anterior a sua implementação, pois é preciso que os dados contidos nele estejam integrados. 27 Figura 6 - A questão da integração (Fonte: Inmon, 2002) Uma das partes mais complexas do processo de edificação do Data Warehouse é o processo de preparação dos dados que serão inseridos. Nesta fase, algumas atividades são essenciais para que ao final haja sucesso. Dentre ela temos [Inmon, 1996]: • A extração de dados do ambiente operacional para o ambiente de Data Warehouse demanda uma mudança na tecnologia; • A seleção de dados do ambiente operacional pode ser muito complexa; • Os dados são reformatados; • Os dados passam por uma limpeza; • Acesso a diferentes fontes de dados; 28 • A eficiência na escolha dos dados de entrada para a extração torna-se freqüentemente uma questão importante; • A alteração de nomes de elementos de dados durante a passagem do ambiente operacional para o ambiente de Data Warehouse deve ser registrada; • A conversão do formato dos dados precisa ser feita; Data Warehousing é, então, o processo de captura dos dados dos sistemas legados e transacionais e a transformação deles em informação organizada, em um formato amigável, para incentivar a análise de dados e para suportar a tomada de decisão baseada em fatos do negócio. O processo ETL envolve transformar dados de seu formato original para uma base dimensional e compreende pelo menos 70 por cento do tempo, do esforço, e da despesa da maioria dos projetos de Data Warehouse. [Kimball, 2004] 29 Capítulo 6 - Extração, Transformação e Carga 6.1 Staging Area Por trás do DW existe uma área conhecida como área de preparação (staging area). Staging neste contexto significa a escrita em disco. Esta prática torna-se recomendável, uma vez que os dados passam por diversas transformações até serem disponibilizados ao usuário final. Caso ocorra algum erro durante o processamento, o armazenamento mantido no decorrer das fases principais do ETL poderá ser retomado do ponto onde parou, ao invés de precisar refazer todo o processo. Grande Porte Extrair Produção Código Limpar Transformar Entregar Extração Área de Preparação Limpeza Área de Preparação Transformação Área de Preparação Entrega Área de Preparação (estruturas de dados geral) (estruturas de dados geral) (estruturas de dados geral) (tabela dimensão) Usuário Final Aplicações Operações: Agendamento, Tratamento de Exceção, Recuperação, Reiniciar, Checar Qualidade, Versão, Suporte Figura 7 - Áreas de preparação (Fonte: Kimball, 2004) A decisão de armazenar dados em uma área física de preparação ao invés de executar o processamento em memória é finalmente a escolha do arquiteto de ETL. A habilidade de desenvolver processos eficientes de ETL é em parte dependente de poder balancear a entrada e saída (I/O) física e processamento em memória. [Kimball, 2004] Armazenar dados durante o processamento varia dependendo do ambiente e exigências do negócio. Algumas razões devem ser consideradas ao se armazenarem dados antes que eles sejam carregados no DW [Kimball, 2004]: • Capacidade de recuperação; 30 • Reserva (Backup); • Auditoria. O armazenamento de dados durante o processamento também é utilizado para suportar funcionalidades que necessitem de históricos. De acordo com essa possibilidade, as tabelas de preparação podem ser classificadas como: Persistentes, que armazenam histórico dos processamentos, ou Transientes, que não armazenam histórico. Independentemente da persistência dos dados na área de preparação, algumas regras deverão ser seguidas para que um projeto de Data Warehouse obtenha sucesso [Kimball, 2004]: • A área de preparação deve ser conhecida apenas pela equipe de ETL. Não é uma área projetada para apresentação. Não há nenhum índice ou agregação que suporte solicitações de consulta. • Não são permitidos acessos de usuários à área de preparação. Dados na área de preparação devem ser considerados como em um local de construção. O acesso por usuários não autorizados pode causar danos à qualidade dos dados, reduzindo a integridade do DW. • Os relatórios não podem alcançar dados da área de preparação. A área de preparação dos dados é um local de trabalho. As tabelas são adicionadas, excluídas, ou modificadas pela equipe de ETL sem notificar aos usuários. • Somente os processos de ETL podem escrever e ler da área de preparação. Cada DW requer séries de dados que não têm uma fonte exterior convencional. Caso o DW não encontre os dados de que necessita em bases transacionais é possível que ele recupere esses dados na área de preparação. Uma aplicação pode ser criada de modo a gerar esses dados e armazená-los utilizando um ETL na área de preparação para servir de entrada aos demais processos. 31 A plataforma exata para armazenar as tabelas da área de preparação depende de muitas variáveis, incluindo padrões e práticas corporativas. O uso de arquivos flat sobre tabelas da base de dados para parcelas do processo de ETL é mais prático quando a finalidade fundamental do processo é uma das seguintes [Kimball, 2004]: • Preparação dos dados da fonte para proteção e recuperação. • Seleção dos dados. • Filtragem. • Substituição de séries de caracteres. • Agregação • Referenciar fontes de dados. Os dados de preparação podem opcionalmente ser armazenados em tabelas relacionais, o que apresenta diversas vantagens [Kimball, 2004]: • Metadados aparentes. Um dos inconvenientes principais de usar arquivos flat é que faltam metadados aparentes. Armazenando dados em uma tabela relacional, o Sistema de Gerenciamento de Banco de Dados (DataBase Management System – SGBD) mantém metadados técnicos automaticamente, e os metadados do negócio podem facilmente ser unidos à tabela. Informação tal como nomes da coluna, tipos de dados e comprimentos, e a cardinalidade são inerentes ao sistema da base de dados. As descrições de negócio da tabela e da coluna são elementos adicionados geralmente aos catálogos de dados do SGBD. • Habilidades relacionais. Recebendo dados de sistemas não-relacionais faz sentido preparar os dados em um ambiente normalizado antes de transformar em um modelo dimensional. 32 • Abrir o repositório. Uma vez que os dados são colocados em um SGBD, os dados podem facilmente ser alcançados por uma ferramenta SQL. O acesso aos dados é crucial durante os testes e exames de garantia de qualidade. • Suporte a administração de dados. Em muitos ambientes corporativos, o grupo do DBA está responsável somente por dados dentro do SGBD. Alocação de espaço, backup e recuperação, arquivamento, e segurança são tarefas que a equipe de ETL deverá coordenar quando a área de preparação não estiver em um SGBD. • Interfaces SQL. Muitas vezes será necessário manipular dados e obtê-los no formato correto utilizando SQL Tabelas Independentes recebem esse nome porque não dependem de nenhuma outra tabela na base de dados. No ambiente transacional, estas tabelas são conhecidas como órfãs por não possuírem relacionamentos com outras tabelas. São as principais candidatas a armazenar os dados na área de preparação, fora de uma base de dados relacional. [Kimball, 2004] Bases de dados transacionais são projetadas para armazenar dados, enquanto bases dimensionais são projetadas para prover dados. A área de preparação deve ser planejada de modo a atender essas duas necessidades. O principal motivo para a criação de uma tabela de preparação é coletar dados de modo que eles possam ser manipulados utilizando SQL. Na maioria dos casos, principalmente em pequenos projetos de Data Warehouse, as tabelas independentes são exatamente o que a área de preparação precisa e não são necessariamente normalizadas. A área de preparação pode ser considerada um repositório central dos dados que eventualmente são carregados no DW. Entretanto, defini-la como um repositório central de todos os dados da empresa é um engano que pode fazer com que os arquitetos acreditem que a área inteira deva ser normalizada. Raramente a área de preparação é modelada atendendo à 33 terceira forma normal. A normalização só faz sentido em casos onde dados são recuperados de diversas fontes, de modo a evitar redundâncias e reforçar a integridade. A normalização da área de preparação não deve ser assumida como regra. Uma das razões para a existência de uma área destinada à preparação é integrar os dados não-relacionais. Integrar fontes de dados heterogêneas é um desafio que os colaboradores de ETL enfrentam constantemente, enquanto o DW expande seu escopo. Em projetos empresariais de Data Warehouse a maioria das fontes de dados são não-relacionais. Ou relacionais, mas que não possuem relacionamentos com as demais fontes. Dentre as nãorelacionais incluem arquivos COBOL, VSAM, arquivos texto, planilhas eletrônicas, entre outros. Sistemas de Fontes Heterogêneas Sistema HR (Oracle) Sistema Financeiro (DB2) Fichas de Arquivos “Textos” Sistema de Inventário (VSAM) Conexões Especializadas Fornecidas como Parte de Ferramentas Típicas ETL Conexão Nativa do SQL*Net Conexão Nativa do DB2 Conexão Nativa do ASCII/ EBCDIC Extração Limpeza Transformação e Processos de Carga Banco de Dados Conexão Nativa do MainFrame VSAM Armazenamento Área de Preparação Fichas de Arquivos no Sistema de Arquivo Esquema de Preparação no DBMS Figura 8 - Integração de fontes de dados heterogêneas (Fonte: Kimball, 2004) 34 A integração de fontes de dados não-relacionais necessita de uma maior atenção quanto à garantia da integridade dos dados. Fontes de dados não-relacionais não reforçam a integridade, são essencialmente uma coleção de tabelas independentes. 6.2 Modelo Dimensional A modelagem dimensional é um nome novo para uma velha tecnologia de estruturar bases de dados simples e compreensíveis. Desde os anos 70, organizações de TI, consultores, usuários finais, e fornecedores têm se direcionado a uma simples estrutura dimensional que seja compatível com a necessidade humana fundamental de simplicidade. Um modelo de dados que começa simples tem chances de permanecer simples ao final do projeto. Enquanto que um modelo que começa complicado certamente será excessivamente complicado ao final do projeto, tendo o processamento lento e sendo rejeitado pelos usuários de negócio. A modelagem dimensional é completamente diferente da modelagem na terceira forma normal, que é uma técnica de projeto que procura remover a redundância dos dados (os dados são divididos em várias entidades distintas, e cada uma se torna uma tabela no modelo relacional). Tanto na terceira forma normal, quanto na modelagem dimensional, os modelos podem ser representados por Diagramas de Entidade Relacional, pois ambos consistem em tabelas relacionais combinadas. A diferença chave ente eles é o seu grau de normalização. [Kimbal, 2002] A modelagem normalizada auxilia no desempenho do processamento operacional, pois uma inserção, ou alteração, acessa o banco de dados em apenas um ponto. No entanto, se tornam muito complicados para atender as solicitações no Data Warehouse. A utilização de modelagem normalizada na área de apresentação do Data Warehouse destrói todo o propósito de um Data Warehouse intuitivo e com alta performance no retorno de dados. 35 A modelagem dimensional dirige-se ao problema de esquemas demasiadamente complexos na área de apresentação. Um modelo dimensional contém a mesma informação que um modelo normalizado, mas empacota os dados em um formato onde o objetivo do projeto é o entendimento do usuário, desempenho das pesquisas, e elasticidade quanto a mudanças. [Kimball, 2002] 6.2.1 Tabela Fato A tabela fato é a tabela principal do modelo dimensional, onde as medidas numéricas de execução do negócio são armazenadas. O termo fato é usado para representar uma medida do negócio. Podemos imaginar um supermercado, onde produtos são vendidos e são anotadas as quantidades vendidas e a soma das vendas por dia, produto e loja. A medida é obtida pela interseção entre todas as dimensões (dia, produto e loja). Essa lista de dimensões define a granularidade da tabela fato e apresenta o escopo da medida. [Kimball, 2002] Tabela Diária da Fato Vendas Data Chave (FK) Produto Chave (FK) Estoque Chave (FK) Quantidade Vendida Total de Vendas - Dollar Figura 9 - Tabela fato (Fonte: Kimball, 2002) As tabelas fato mais úteis são numéricas e aditivas. A aditividade é crucial, pois as aplicações de Data Warehouse quase nunca recuperam apenas uma linha de uma tabela fato. Essencialmente elas recuperam centenas, milhares, até milhões de linhas de uma vez, e a melhor coisa a se fazer com tantas linhas é agrupá-las. Fatos semi-aditivas podem ser adicionadas apenas para algumas dimensões, e fatos não-aditivas simplesmente não podem 36 ser adicionadas. Com fatos não-aditivas é necessária a utilização de contagens ou cálculos de média para resumir as linhas. É teoricamente possível para uma fato medida ser textual; No entanto, a condição aparece raramente. Na maioria dos casos, uma medida textual é uma descrição de alguma coisa e é desenhada para uma lista de valores distintos. O desenhista deve se esforçar para colocar as medidas textuais nas dimensões, pois assim podem ser correlacionadas mais efetivamente com os outros atributos de dimensão e consumirão muito menos espaço. Informação textual redundante não deve ser armazenada em tabelas fato. A menos que o texto seja único para cada linha da tabela, ele faz parte da tabela de dimensão. Uma verdadeira fato texto é rara em um Data Warehouse por causa do conteúdo imprevisível, o que torna a análise quase impossível. [Kimball, 2002] Tabelas fato usualmente constituem 90% ou mais do espaço total consumido por uma base de dados dimensional. Tendem a ser profundas em termos de número de linhas, mas estreitas quanto ao número de colunas [Kimball, 2002]. Todas as tabelas fato possuem duas ou mais chaves estrangeiras, que se associam às chaves primárias das tabelas de dimensão. Por exemplo, a chave de produto na tabela fato sempre será compatível com a chave de um produto específico na tabela dimensão Produto. As tabelas satisfazem a sua integridade relacional quando todas as chaves na tabela fato são compatíveis com suas respectivas chaves primárias nas tabelas dimensão correspondentes. O acesso à tabela fato se dá através das dimensões ligadas a ela. A tabela fato geralmente possui sua própria chave primária, constituída de um subconjunto de chaves estrangeiras. Essa chave é frequentemente chamada de chave composta ou concatenada. Toda tabela fato em um modelo dimensional possui uma chave composta, e reciprocamente, toda tabela que possui uma chave composta é uma tabela fato. 37 Ou seja, em um modelo dimensional toda tabela que expresse relacionamento de muitos para muitos deve ser uma tabela fato. Todas as outras são tabelas de dimensão. [Kimball, 2002] Existem três tipos fundamentais de tabelas fato: Transação, Imagem Periódica e Imagem Acumulada. Frequentemente é necessária a utilização de duas tabelas fato complementares para se obter um retrato completo do negócio. 6.2.2 Tabela Fato de Transação A principal visão das operações de negócio está no nível individual da transação. Essas tabelas fato representam um evento que ocorreu em um momento específico no tempo. Uma linha existe na tabela fato para um determinado consumidor ou produto apenas se um evento de transação ocorreu. Ao contrário, um determinado consumidor ou produto está associado a múltiplas linhas na tabela fato, pois estão envolvidos em mais de uma transação. O dado da transação muitas vezes é construído facilmente em uma estrutura dimensional. O dado de baixo nível é o mais evidente dado dimensional, que apóia análises que não podem ser feitas em dados resumidos. Dados transacionais permitem uma análise extremamente detalhada. [Kimball, 2002] 6.2.3 Tabela Fato de Imagem Periódica Imagens periódicas são necessárias para acompanhar o desempenho cumulativo do negócio em determinado ponto. Ao contrário da tabela fato transacional, onde é armazenada uma linha para cada ocorrência de evento, com a imagem periódica é possível obter um retrato da atividade ao final do dia, semana, mês, e um novo retrato ao final do próximo período. As imagens periódicas são armazenadas consecutivamente na tabela fato. A tabela fato de imagem periódica frequentemente é o único lugar onde se pode obter facilmente uma visão pontual e previsível das medidas de desempenho chaves do negócio. 38 Imagens periódicas tipicamente são mais complexas que transações individuais. Em algumas situações é possível mudá-las de transações individuais para imagens diárias simplesmente através da soma das transações. Neste caso, a imagem periódica representa uma agregação da atividade transacional que ocorreu durante um período de tempo. [Kimball, 2002] 6.2.4 Tabela Fato de Imagem Acumulada O terceiro tipo de tabela fato é o de imagem acumulada. Apesar de não serem muito comuns como as anteriores, imagens acumuladas podem ser bastante intuitivas. Representam uma indeterminada amplitude do tempo, abrangendo a vida completa de uma transação. Ao contrário dos demais tipos, as linhas de uma tabela fato de imagem acumulada podem sofrer atualizações. Imagens acumuladas quase sempre possuem múltiplos marcos de tempo, representando os eventos mais importantes que podem ser previstos ou fases que ocorreram durante toda a vida da transação. Uma coluna adicional de data pode existir, indicando quando a linha da imagem foi alterada pela última vez. Caso estas datas não sejam conhecidas no momento da primeira carga da linha é preciso utilizar datas chaves substitutas que possibilitem uma identificação. [Kimball, 2002] 6.2.5 Tabela Dimensão Tabelas de dimensão são companheiras essenciais de uma tabela fato. São elas que contêm os descritores textuais do negócio. Em um modelo dimensional bem desenhado as tabelas de dimensão possuem muitas colunas ou atributos, que descrevem as linhas na tabela. Tendem a ser relativamente superficiais em termos de número de linhas (menos que 1 milhão), mas possuem muitas colunas. Cada dimensão é definida por sua única chave 39 primária, que serve como base para a integridade referencial com qualquer tabela fato com a qual esteja combinada. [Kimball, 2002] Atributos da dimensão servem como a principal origem de restrições em solicitações, agrupamentos e rótulos de relatórios. Representam uma função vital em um Data Warehouse: são a chave para a construção de um Data Warehouse utilizável e compreensível. O poder do Data Warehouse é diretamente proporcional à qualidade dos atributos da dimensão. Os melhores atributos são textuais e distintos, podem consistir preferencialmente de palavras reais, ou abreviações. Atributos típicos para uma dimensão Produto podem incluir uma pequena descrição (10 a 15 caracteres), uma descrição longa (30 a 50 caracteres), um nome de marca, um nome de categoria, tipo de empacotamento, tamanho, e outras numerosas características do Produto. Embora o tamanho seja provavelmente numérico, ele continua sendo um atributo de dimensão, pois se comporta mais como uma descrição textual do que como uma medida numérica. Tamanho é um descritor distinto e constante de um produto específico. [Kimball, 2002] Tabela Dimensão Produto Produto Chave (PK) Descrição do Produto Número SKU (Chave natural) Descrição Tipo Descrição Categoria Descrição Departamento Descrição Tipo Pacote Tamanho Pacote Descrição Conteúdo da fato Total de Vendas – Dollar Descrição Conteúdo Fato Descrição Tipo Regime Peso Unidade de Peso e Medidas Tipo Armazenamento Tipo da Vida Útil Largura da Prateleira Altura da Prateleira Prateleira do Departamento ... e muito mais Figura 10 - Tabela dimensão (Fonte: Kimball, 2002) 40 As tabelas de dimensão muitas vezes representam relacionamentos dentro do negócio. São tipicamente não-normalizadas e usualmente pequenas (menos que 10% de todo o requisito de armazenamento de dados). Visto que as tabelas de dimensão são geometricamente menores que as tabelas fato, melhorar a eficiência de armazenamento, através da normalização, na prática não exerce impacto no tamanho total da base de dados. [Kimball, 2002] 6.3 Documento Lógico Antes de construir os sistemas de extração é necessário documentar os relacionamentos entre os campos da fonte original e os campos de destino, nas tabelas de apresentação do DW. O modelo lógico de dados é que descreve tais relacionamentos. O processo ETL deve ser definido logicamente e documentado. A documentação é elaborada pelo Arquiteto de DW e utilizada pela equipe de ETL para produzir as tarefas físicas. O modelo lógico é a base de metadados eventualmente concedida aos testadores da garantia de qualidade e também aos usuários finais, com o intuito de descrever exatamente o que é feito entre os sistemas fontes e o DW. O documento lógico contém a definição das fontes de dados por toda a empresa, o modelo de dados do DW e as manipulações exigidas para transformar os dados de seu formato original ao de destino. Revela muitos requisitos ocultos que em outro momento podem ter sido omitidos. Seu objetivo principal é fornecer ao desenvolvedor ETL uma imagem exata do que é esperado do processo ETL. Algumas ferramentas de ETL e modelagem de dados capturam diretamente a informação do projeto de dados lógico, que só pode existir caso os sistemas da fonte estejam identificados e analisados. A equipe de ETL deve entender inteiramente o modelo físico do Data Warehouse e a equipe de desenvolvimento deve possuir um perfeito entendimento de como as dimensões, 41 fatos e outras tabelas, no modelo dimensional, trabalham juntas. É interessante ter certeza de que os cálculos levantados estão corretos antes da codificação dos algoritmos no processo ETL. 6.4 Fontes Heterogêneas Cada fonte de dados possui um conjunto distinto de características que precisam ser gerenciadas de modo a extrair efetivamente os dados para o processo ETL. Conforme a empresa se desenvolve passa a adquirir, ou herdar, diversos sistemas computacionais com a função de auxiliar na administração dos negócios e o processo ETL precisa integrar efetivamente esses diferentes sistemas. As prováveis fontes de dados, que suportarão a tomada de decisões, devem ser identificadas. Os dados nos sistemas fontes devem ser examinados quanto à qualidade, integridade e adaptação ao propósito. Qualquer anomalia detectada deve ser documentada e esforços devem ser feitos, com o intuito de aplicar regras de negócio apropriadas para corrigir os dados antes que sejam carregados no DW. Uma vez que as fontes tenham sido preparadas e o modelo das tabelas finais tenha sido compreendido, é possível dar inicio ao processo de extração, transformação e carga para o DW. Uma vez determinada a fonte originária dos dados a etapa seguinte é analisar os sistemas da fonte para começar uma compreensão melhor de seu índice. Esta compreensão é realizada normalmente adquirindo os diagramas de entidade relacional (ER) para os sistemas selecionados, se forem baseados na tecnologia relacional. Os identificadores únicos descrevem as colunas que representam unicamente uma linha em uma tabela. De um ponto de vista da integridade referencial, um identificador único é a chave primária para uma tabela. Na maioria das vezes, a chave primária é artificial, e embora seja única de um ponto de vista de ETL, não é bastante informação para determinar se a fileira é única. Em cada tabela transacional corretamente projetada, além da chave primária, há pelo menos uma chave 42 natural. A chave natural é o que os usuários do negócio usam para descrever unicamente a linha. Compreender como as tabelas são relacionadas é vital para assegurar a exatidão em associações de tabelas ao recuperar dados. Compreendidos todos os atributos, de todas as entidades, de todos os sistemas sujeitos a considerações, o próximo objetivo é desenhar o algoritmo adequado para permitir que as entidades dos diferentes sistemas sejam associadas. Às vezes o algoritmo adequado é tão simples quanto identificar a chave primária das várias tabelas. Mas em muitos casos, diferentes sistemas não compartilham chaves primárias. Com a aprovação da lógica adequada, é possível estabelecer o registro sobrevivente quando colisões de dados ocorrerem no processo de ETL. Atribuir regras de negócio para atributos que não são chaves é especialmente importante quando os atributos existem em diversos sistemas, mas não no fonte. Nesses casos, a documentação e a publicação dos metadados são cruciais para impedir dúvidas quanto a integridade do DW. A tarefa final do processo de integração é carregar fisicamente a dimensão adequada. Cada fonte de dados pode estar em um SGBD diferente e também em uma plataforma diferente. As bases de dados e os sistemas operacionais, especialmente legados e proprietários, podem requerer linguagens de procedimento diferentes para a comunicação com seus dados. Mesmo se não houver nenhuma limitação técnica, os departamentos ou os subsistemas geralmente possuem uma língua padrão que permite interagir com seus dados. Quando a linguagem específica está além do domínio do conjunto de ferramentas de ETL ou experiência se torna obrigatória, o ideal é solicitar ao proprietário da fonte que os dados sejam extraidos para um arquivo. A Conectividade Aberta de Banco de Dados (Oppen Database Connectivity - ODBC) foi criada para permitir a usuários acessar bases de dados das aplicações Windows. A intenção 43 original para ODBC era fazer aplicações portáteis, significando que se a base de dados subjacente de uma aplicação mudasse não seria necessária recodificação e compilação para acomodar a mudança. Bastaria simplesmente mudar o driver ODBC, que é transparente à aplicação. Para que o processo de ETL utilize dados através de ODBC, duas camadas são adicionadas entre o sistema de ETL e a base de dados subjacente. O gerente ODBC é um programa que aceita o SQL da aplicação de ETL e o distribui ao driver ODBC apropriado. Mantém também a conexão entre a aplicação e o driver ODBC. O driver ODBC traduz o SQL ODBC para o SQL nativo da base de dados subjacente. 6.5 Detecção de Mudanças Durante a carga inicial, capturar mudanças ao conteúdo dos dados da fonte é sem importância caso a extração compreenda toda a fonte de dados ou uma porção em um ponto predeterminado do tempo. Uma vez que a carga inicial está completa, a habilidade de capturar mudanças nos dados no sistema fonte transforma-se imediatamente em prioridade número um. Capturar as mudanças nos dados está longe de ser uma tarefa trivial. Deve se planejar a estratégia para capturar mudanças incrementais na fonte de dados no início do projeto. A manutenção do conteúdo dos dados é dependente do processo incremental da carga. Há diversas maneiras de capturar mudanças aos dados da fonte, e todas são eficazes em seus ambientes apropriados. Na maioria dos casos, o sistema fonte contém colunas de auditoria. As colunas de auditoria são adicionadas à extremidade de cada tabela para armazenar a data e hora em que um registro foi adicionado ou modificado. As colunas de auditoria são povoadas geralmente através dos disparadores da base de dados, automaticamente como os registros são introduzidos ou atualizados. É necessário analisar e 44 testar cada uma das colunas para assegurar de que são fontes de confiança para indicar dados alterados. Existem vários métodos para implementar a utilização de colunas de auditoria para capturar mudanças nos dados. Todos os métodos têm o mesmo objetivo lógico: comparar a data e hora da última modificação de cada registro com a máxima data e hora que existiu durante a carga precedente e pegar todos os que são maiores. Uma solução eficaz é utilizar as colunas de auditoria no sistema fonte. Essencialmente, o processo seleciona a data e a hora máximas das colunas da data de criação e da última modificação. Algumas colunas de última modificação são atualizadas no momento da inserção e de cada mudança no registro. Outras são preenchidas com nulos no momento da inserção e atualizados somente com mudanças, após o registro ter sido introduzido. Nos casos onde as linhas na tabela fato são inseridas mas nunca atualizadas, pode-se simplesmente selecionar registros do sistema fonte onde a data e hora de criação são maiores do que a data e hora máximas da carga precedente e ignorar a coluna da data de última modificação. Desde que as tabelas fato e as tabelas de dimensão podem originar de várias tabelas e sistemas diferentes, e desde que as tabelas fato consistem somente de chaves extrangeiras e métricas, as datas de auditoria não são armazenadas diretamente na tabela fato. Selecionar todas as linhas onde a data de criação ou de modificação seja igual a SYSDATE-1, significando obter todos os registros do dia anterior, é um engano. Carregar registros baseando-se puramente no tempo é um erro e um processo não confiável. As cargas baseadas em tempo duplicam linhas ao serem reiniciadas de falhas no meio do processo. Isto significa que intervenções manuais e limpezas são requeridas caso o processo falhe por qualquer razão. O método da eliminação preserva exatamente uma cópia de cada extração precedente na área de preparação para um uso futuro. Durante a execução seguinte, o processo faz uma 45 cópia da tabelas fonte inteira na área de preparação e faz uma comparação com os dados retidos do último processo. Somente as diferenças são enviadas ao armazém de dados. Embora não seja a técnica mais eficiente, o processo de eliminação é mais confiável de todas as técnicas incrementais para capturar dados atualizados. Por fazer uma comparação linha a linha, procurando mudanças, é virtualmente impossível perder algum dado. Esta técnica tem também a vantagem de detectar linhas excluídas da fonte. 6.6 Qualidade dos Dados As definições dos dados do ETL atravessam um processo de evolução. Conforme as anomalias são descobertas devem ser documentadas e discutidas com a área de negócios, que pode ditar como devem ser tratadas. Todas as transformações que vierem destas reuniões têm que ser documentadas, corretamente aprovadas, e assinadas. A forma mais direta de integração dos dados é a implementação de dimensões adequadas. No DW, dimensões conformadas são o projeto coeso que unifica os muito diferentes sistemas de dados espalhados por toda a empresa. As dimensões e as fatos conformadas são cruciais ao sucesso do projeto. Algumas anomalias mais comuns deverão ser tratadas. Uma delas é a ocorrência de valores nulos, que podem destruir todo o processo de ETL e representam grande risco quando em colunas de chaves estrangeiras. A associação de duas ou mais tabelas, baseada em uma coluna que contenha valores nulos poderá acarretar a perda de dados. Caso existam valores nulos em colunas requeridas pelo negócio, regras de tratamento específicas deverão ser definidas. Sempre que possível, valores padrões deverão ser criados para substituir valores nulos ao carregar o DW. As datas são elementos muito peculiares porque são os únicos elementos lógicos que podem ser representados em vários formatos, literalmente contendo valores diferentes, mas com o mesmo significado. Felizmente, a maioria dos sistemas de base de dados suporta aos 46 vários formatos utilizados para a representação, mas armazenam as datas em um único formato padrão. Existem ainda situações onde datas são armazenadas em campos de texto, especialmente em aplicações de legado, e nesses casos as variações possíveis de formatos de data são ilimitadas. Problemas podem ocorrer se o sistema não fizer o controle da entrada de tais dados no banco. Limpeza e conformidade são as etapas principais onde o sistema de ETL agrega valor. As outras etapas de extração e disponibilização são obviamente necessárias, mas somente movem e formatam os dados. Limpar e dar conformidade realmente provoca mudanças nos dados e fornecem a orientação se os dados podem ser usados para as finalidades pretendidas. São etapas que geram poderosos metadados, um diagnóstico de o que está errado nos sistemas fonte. Finalmente, os dados sujos podem ser reparados apenas com uma mudança na maneira com que os sistemas fonte coletam seus dados. Dados precisos precisam ser corretos, não ambíguos, consistentes, e completos. O processo de limpeza dos dados deve ser completo quanto à detecção, correção, e documentação da qualidade da informação que publica à comunidade de negócio. Usuários finais querem enxergar o DW como uma fonte informações confiáveis. Corrigir problemas de qualidade o mais perto da fonte possível é, naturalmente, a única maneira estratégica de melhorar os recursos da informação da organização. O armazém dos dados deve expor defeitos e empregar atenção aos sistemas e às práticas de negócio que ferem a qualidade dos dados da organização. Essas revelações dirigem finalmente o processo de reengenharia do negócio, onde os sistemas fonte e os procedimentos da entrada de dados são melhorados. 47 Capítulo 7 - Ferramenta para Tratamento de Dados O resultado do projeto de pesquisa será uma Ferramenta com o objetivo de auxiliar no desenvolvimento de fluxos de execução de ETL. A ferramenta compreenderá todo o processo, desde a extração dos dados, passando pela sua transformação até a carga no DW, procurando atender aos Requisitos Funcionais e Não-Funcionais propostos nesta documentação. Os requisitos funcionais representarão as diversas funções que os clientes e usuários necessitam que a ferramenta execute. Sendo assim, serão destacados os processos de realização de conexão com diferentes bancos de dados, padronização dos tipos de dados contidos nas tabelas de origem, monitoramento das execuções, com a geração de dados estatísticos e logs, visualização dos projetos departamentais e geração de mapas de execução. Os requisitos não-funcionais compreendem os atributos e as qualidades globais da ferramenta. Uma das preocupações, neste caso, será garantir a segurança do acesso aos dados resultantes do processo ETL. Os dados das conexões com as bases de dados serão armazenadas em uma tabela própria da ferramenta, com usuários e senhas. Essas entidades chamadas conexões serão utilizadas todas as vezes que for necessário estabelecer uma conexão com um banco de dados. As tabelas fontes e as tabelas de destino serão recuperadas utilizando-se destas entidades. Durante os processos de extração e transformação serão utilizados arquivos temporários, de modo que as transformações possam ser efetuadas sem afetar as tabelas de origem dos dados. 7.1 Tecnologias utilizadas A ferramenta será desenvolvida em plataforma distribuída (cliente-servidor) utilizando-se a linguagem Java 5.0, RMI (Remote Method Invocation) e aplicando-se o padrão 48 de desenvolvimento DAO (Data Access Object) para gerenciamento de acesso às bases de dados. Para o desenvolvimento utilizando o RMI fez-se necessária a aplicação de um plugin destinado a produção de softwares comerciais e pesquisa. Este se encontra disponível no endereço < http://www.genady.net/rmi/v20/index.html>. As licenças são disponibilizadas de acordo com o propósito do projeto. No caso de pesquisa para fins acadêmicos, a licença é gratuita por um prazo de 4 (quatro) meses. 7.2 Especificação de Requisitos 7.2.1 Identificação dos Interessados no Sistema A equipe responsável pelo processo de Extração, Tratamento e Carga dos dados (Administradores) terá acesso irrestrito às funcionalidades do sistema. 7.2.2 Principais Necessidades dos Interessados Os Administradores possuem as seguintes necessidades: – Realizar conexão com as diferentes bases de dados; – Visualizar graficamente as tabelas trabalhadas; – Converter os dados das bases para tipos padrões; – Visualizar todos os projetos departamentais; – Gerar mapas de execução; – Monitorar as execuções efetuadas; – Gerar dados estatísticos e logs de execução. 49 7.2.3 Requisitos Funcionais Todas as funcionalidades deverão permitir acesso multiusuário às mesmas, ou seja, várias pessoas poderão executar simultaneamente operações do mesmo tipo, mas a partir de máquinas cliente distintas. 7.2.3.1 Realizar Conexão O sistema deverá permitir que os administradores possam realizar conexão com diferentes bases de dados, dependendo da escolha feita por aqueles. 7.2.3.2 Visualizar Tabelas O sistema deverá permitir que os administradores visualizem as tabelas utilizadas no processo. Estas tabelas deverão estar relacionadas a projetos específicos. 7.2.3.3 Padronizar Dados O sistema deverá ser capaz de padronizar todos os dados vindos das bases, convertendo aqueles de mesma categoria para tipos únicos. Esta padronização é pré-definida pelos administradores. 7.2.3.4 Monitorar as execuções efetuadas O sistema deverá disponibilizar uma tela onde o administrador poderá verificar se as execuções foram bem sucedidas ou não, nessa tela poderá ter acesso aos dados estatísticos e logs de execução de cada um dos mapas. 50 7.2.3.5 Gerar dados estatísticos e logs de execução O sistema deverá disponibilizar uma tela onde o administrador poderá visualizar o ocorrido no processamento por meio de dados estatísticos e logs de execução, facilitando o controle e verificação do desempenho do sistema na execução do ETL. 7.2.3.6 Visualizar todos os projetos departamentais O sistema deverá disponibilizar ao administrador uma tela onde será possível a visualização de todos os projetos departamentais criados na forma de estrutura de árvore, assim a visualização ajudará na criação e geração dos mapas de execução. 7.2.3.7 Gerar mapas de execução O sistema deverá disponibilizar uma tela onde o administrador poderá montar seus respectivos mapas de execução na seqüência de funcionamento, utilizando a visualização dos projetos departamentais, poderão existir um ou mais mapas executando em paralelo ou em seqüência. 7.2.4 Requisitos Não-Funcionais 7.2.4.1 Segurança Somente os administradores que possuírem login e senha poderão ter acesso ao sistema. 7.2.5 Restrições do Sistema Não há. 51 7.2.6 Interface Entre os Agentes Externos e o Sistema A interface gráfica entre os agentes e o sistema será baseada em telas gráficas, orientadas por ícones, caixas de seleção e menus do tipo “Pull-Down”. Deverá ter a capacidade de ser visualizada em estações Risc/Unix ou em PCs/Windows, com acessos ao equipamento central através da Intranet ou Extranet. 7.2.7 Contexto de Utilização do Sistema O Sistema será utilizado somente pelos administradores. Eles trabalharão em máquinas pessoais e cada atualização no sistema deverá ser feita mediante a escolha prévia dos Projetos. Estas atualizações deverão estar disponíveis logo após sua realização, portanto seu processamento deverá ser on-line. 7.2.8 Procedimento Contra Perdas de Informações Deverá ser realizado um backup dos dados do Sistema diariamente a fim prevenir a perda dos dados registrados durante cada dia de trabalho. 7.2.9 Tratamento de Erros Todos os erros ocorridos serão encaminhados por e-mail ao responsável da área de TI pelo acompanhamento e monitoramento do sistema. 52 Conclusão Diante das atividades propostas conseguiu-se atingir pontos positivos e negativos, conseqüências das dificuldades características de cada funcionalidade do sistema e de cada referência da pesquisa. No que diz respeito à possibilidade de padronização de tipos de campos, característica da etapa de Transformação do ETL foi utilizado o reconhecimento destes campos nas tabelas do banco específico e atrelou a eles a padronização determinada previamente. A realização de conexão com diferentes bancos foi obtida com êxito. A Extração dos dados, outra etapa do ETL, exige que para a formação futura da base de Data Warehouse faz-se necessário que haja a captura dos dados existentes nas bases e que foram determinadas como cruciais para o auxílio à tomada de decisão. No entanto, com relação ao último e penúltimo item, propostos nos objetivos específicos, sendo eles, respectivamente, a possibilidade de identificação de registros que realmente deveriam ser carregados e a possibilidade de geração de logs e dados estatísticos, não se obteve sucesso devido a fatores de tempo e complexidade. O sistema será desenvolvido seguindo as premissas de um correto funcionamento. No entanto, todas as fases implementadas poderão sofrer aperfeiçoamento para uma melhor adequação aos tipos de projetos e necessidades de inúmeras empresas que vierem a utilizar tal ferramenta. 53 Referências Bibliográficas BARBIERI, Carlos. BI – Business Intelligence. Axcel Books, 2001. 424 p. GUPTA, Vivek R. An Introduction to Data Warehousing. Disponível em: <http://www.system-services.com/DataWarehousing.asp> . Acesso em: 11/03/2007. INMON, William H. Building the data warehouse. 4. ed. Estados Unidos da América: Wiley, 2005. 543 p. INMON, William H. Como construir o data warehouse. Rio de Janeiro: Campus, 1997. 388 p. KIMBALL, Ralph; CASERTA, Joe. The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data. Estados Unidos da América: Wiley, 2004. 491 p. KIMBALL, Ralph; ROSS, Margy. The Data Warehouse Toolkit: The Complete Guide for Dimensional Modeling. 2. ed. Estados Unidos da América: Wiley, 2002. 421 p. 54
