Migração para computação distribuída em Business Intelligence

FACULDADE DE ENGENHARIA DA UNIVERSIDADE DO PORTO Migração para computação distribuída em Business Intelligence Filipa Ivars de Sousa e Silva Mestrado Integrado em Engenharia Informática e Computação Orientador: Gabriel David Co-orientador: João Moreira 2 de Março de 2018 Migração para computação distribuída em Business Intelligence Filipa Ivars de Sousa e Silva Mestrado Integrado em Engenharia Informática e Computação Aprovado em provas públicas pelo Júri: Presidente: A. Miguel Monteiro Arguente: Fátima Rodrigues Vogal: Gabriel David 2 de Março de 2018 Resumo Oe-commerce é um negócio emergente e, com o seu crescimento, também aumenta o volume de dados armazenados. A Farfetch é uma empresa de inovação tecnológica desta mesma área que atua no mercado internacional e já conta com uma vasta audiência. Vende no seu canal online artigos de moda das mais conceituadas boutiques de vestuário e acessórios espalhadas pelo mundo. A par do crescimento da empresa, surgem limitações no armazenamento de dados e dificuldades em manter um sistema que permita autonomia na consulta desta informação por parte de profissionais que desempenham tarefas de análise e interpretação de dados. É, então, necessário procurar respostas a estes problemas sustentadas em soluções atualmente disponíveis que permitam gerir este grande volume de dados de forma eficiente e que sejam aplicados princípios de business intelligence edata-warehousing para a sua organização e interpretação. A Farfetch está a implementar uma resposta para estes problemas e, nesta dissertação, pretende-se fazer um estudo das soluções potenciais, compará-las e perceber qual, ou quais, serão as mais adequadas e como deverá ser feita a sua transição. Procura-se que a resposta obtida aumente a capacidade e escalabilidade do armazenamento de uma grande quantidade de dados, aumente a eficiência e diminua o tempo de resposta dos pedidos ao armazém de dados, preveja uma maior autonomia de outros profissionais, como gestores e analistas de dados, na consulta de informação e, finalmente, pretende-se prever/analisar o seu desempenho a longo prazo. i ii Abstract E-commerce is an emerging business and, with its growth, it also increases the volume of stored data. Farfetch is a technological innovator company in the same area that operates in the international market and already has a large audience. It sells on its online channel fashion articles from the most renowned fashion boutiques and accessories scattered around the world. Along with the growth of the company, there are limitations in data storage and difficulties in making and maintaining a system that may allow for autonomy in the consultation of this information by professionals who perform tasks of analysis and interpretation of data. At the same time, it is necessary to look for answers to these problems based on currently available solutions that can efficiently manage this large volume of data and apply business intelligence and datawarehousing principles to its organization and interpretation. Farfetch has implemented a response to these problems, and in this dissertation, we intend to study these solutions and compare them to understand which, or which ones, are the most appropriate and how their transition should be done. It is sought that the response obtained increases the capacity and scalability of the storage of a large amount of data, increase the efficiency and reduce the response time of the requests to the data-warehouse, foresee a greater autonomy of other professionals, such as managers and analysts in the information query, and finally, it is intended to predict / analyze its long-term performance. iii iv “Hello, World” Brian Kernighan v LISTA DE FIGURAS 4.13 Cenário 5 - camada de armazenamento: Azure Blob Storage; camada de processamento: Azure SQL Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . 64 4.14 Cenário 6 - camada de armazenamento: Google Cloud Storage; camada de processamento: Google Big Query . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 4.15 Cenário 7 - camada de armazenamento: Apache Hadoop; camada de processamento: Apache Hive & Cloudera Impala . . . . . . . . . . . . . . . . . . . . . . 64 4.16 Cenário 8 - camada de armazenamento: Apache Hadoop; camada de processamento:Presto.................................... 64 4.17 Cenário 9 - camada de armazenamento: Apache Hadoop; camada de processamento: Azure SQL Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . 65 4.18 Cenário 10 - camada de armazenamento: Google Big Query; camada de processamento:ApacheHadoop.............................. 65 4.19 Cenário 11 - camada de armazenamento: Amazon S3; camada de processamento: AmazonAthena................................... 65 4.20 Gráfico da média de resultados dos testes da admissão sobre a velocidade processamento dos cenários 1, 2, 3, 5 e 6 . . . . . . . . . . . . . . . . . . . . . . . . . 68 4.21 Gráfico da velocidade de processamento do teste VUS . . . . . . . . . . . . . . 69 4.22 Gráfico da velocidade de processamento do teste LP_D . . . . . . . . . . . . . . 69 4.23 Hierarquias representadas por Denorm_ds_fv_fa (esquerda) e Denorm_fv_fa (direita)......................................... 70 4.24 Relações entre as tabelas DimSessao,FactualVistas eFactualAcoes obtidas da hierarquia Denorm_ds_fv_fa ............................ 70 4.25 Estrutura da tabela desnormalizada Denorm_ds_fv_fa . . . . . . . . . . . . . . . 71 4.26 Estrutura da tabela desnormalizada Denorm_fv_fva . . . . . . . . . . . . . . . . 71 4.27 Fluxos de dados de Microsoft SQL Server para Google BigQuery . . . . . . . . 76 4.28 Fluxo detalhado de dados de Microsoft SQL Server para Google BigQuery, através dePolyBase..................................... 77 4.29 Fluxo de dados durante a migração . . . . . . . . . . . . . . . . . . . . . . . . . 78 4.30 Fluxo de dados depois da migração . . . . . . . . . . . . . . . . . . . . . . . . . 78 C.1 Plano de execução da consulta ds_fv ao modelo normalizado . . . . . . . . . . . 120 C.2 Plano de execução da consulta ds_fv ao modelo desnormalizado . . . . . . . . . 120 C.3 Plano de execução da consulta ds_fv_fa ao modelo normalizado . . . . . . . . . 120 C.4 Plano de execução da consulta ds_fv_fa ao modelo desnormalizado . . . . . . . 120 C.5 Plano de execução da consulta ds_fa ao modelo normalizado . . . . . . . . . . . 121 C.6 Plano de execução da consulta ds_fa ao modelo desnormalizado . . . . . . . . . 121 C.7 Plano de execução da consulta fv_fa ao modelo normalizado . . . . . . . . . . . 121 C.8 Plano de execução da consulta fv_fa ao modelo desnormalizado . . . . . . . . . 121 xii Lista de Tabelas 2.1 Bases de dados operacionais vs. Armazéns de dados [SR09] ........... 6 2.2 Factos e dimensões [Cal12]............................. 9 2.3 Slowly Changing Dimension do tipo 1: antes da atualização . . . . . . . . . . . . 15 2.4 Slowly Changing Dimension do tipo 1: depois da atualização . . . . . . . . . . . 15 2.5 Slowly Changing Dimension dotipo2 ....................... 16 2.6 Slowly Changing Dimension dotipo3 ....................... 16 2.7 Tabela da dimensão produto - DimProduto ..................... 20 2.8 Modelo transacional vs. Modelo multidimensional [KR02] ............ 26 2.9 Vantagens e desvantagens das técnicas mais comuns de migração para um sistema cloud [Gar11] [Med18]............................... 37 2.10 MPP vs. MapReduce [Fly17]............................ 46 4.1 Características comuns de algumas tecnologias de armazenamento e processamento de dados a abordar [Goo17] [Mic17a] [Mic17b] .............. 55 4.2 Características comuns de algumas tecnologias de Business Intelligence a abordar [Loo17] [Qli17] [Tab17] .............................. 56 4.3 Informação de preços para Google BigQuery [Goo18b].............. 58 4.4 Informação de preços para Azure SQL DW otimizado para elasticidade [Azu18] . 58 4.5 Informação de capacidade das tecnologias a analisar . . . . . . . . . . . . . . . . 59 4.6 Média dos resultados dos testes de admissão . . . . . . . . . . . . . . . . . . . . 66 4.7 Média dos resultados dos testes contextualizados . . . . . . . . . . . . . . . . . 68 4.8 Média dos resultados dos testes contextualizados . . . . . . . . . . . . . . . . . 72 4.9 Análise a outros dados dos resultados dos testes às tabelas normalizadas e desnormalizadas ...................................... 72 4.10 Cumprimento dos objetivos a atingir com a migração . . . . . . . . . . . . . . . 86 4.11 Resposta às questões propostas . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 C.1 Resultados do teste de admissão 1 . . . . . . . . . . . . . . . . . . . . . . . . . 109 C.2 Resultados do teste de admissão 2 . . . . . . . . . . . . . . . . . . . . . . . . . 110 C.3 Resultados do teste de admissão 3 . . . . . . . . . . . . . . . . . . . . . . . . . 110 C.4 Resultados do teste VerticalUserSession ...................... 111 C.5 Resultados do teste ListingPage_Dashboard .................... 111 C.6 Resultados dos testes às tabelas normalizadas e desnormalizadas . . . . . . . . . 112 C.7 Tabela com as conversões de SQL Server para Hadoop e para BigQuery . . . . . 122 xiii LISTA DE TABELAS xiv Abreviaturas e Símbolos 3NF Terceira Forma Normal (do inglês, 3rd Normal Form) 6NF Sexta Forma Normal (do inglês, 6th Normal Form) ACID Atomicidade, Consistência, Isolamento e Durabilidade AD Armazém de Dados (do inglês Data Warehouse) ou Armazenamento de Dados (do inglês Data Warehousing), dependendo do contexto API Application Programming Interface BI Business Intelligence CDH Cloudera Distribution Including Apache Hadoop DB2 Um SGDBR produzido pela IBM DBI Interface de Bases de Dados (do inglês, Database Interface) DDL Data Definition Language, em português, linguagem de definição de dados DML Data Manipulation Language, em português, linguagem de manipulação de dados DSS Decision Support Systems DWU Data warehouse units ERD Diagrama entidade-relação (do inglês, Entity-relationship diagrams) ETL Extração, Transformação e Carregamento (do inglês, Extract, Transform and Load) GB Gigabyte HDFS Hadoop Distributed File System JSON JavaScript Object Notation MPP Massively Parallel Processing OLTP Online Transaction Processing OLAP Online Analytical Processing PaaS Platform as a Service PB Petabyte RH Recursos humanos SaaS Software as a Service SGBD Sistema de Gestão de Bases de Dados (do inglês Database Management System - DBMS) SGBDR Sistema de Gestão de Bases de Dados Relacionais (do inglês, Relational Database Management System - RDBMS SK Surrogate key TB Terabyte TI Tecnologias de Informação (do inglês, IT - Information Technology) UTC Coordinated Universal Time xv Capítulo 1 Introdução Com a evolução da Internet, as empresas detetam a oportunidade de exploração do seu negócio num ambiente digital. Este ambiente traz-lhes grande visibilidade tornando-se acessíveis a partir de qualquer parte do mundo. A sua internacionalização traz, no entanto, certos constrangimentos que exigem atenção. 1.1 Contexto/Enquadramento A Farfetch é um exemplo representativo do mercado mundial do e-commerce (em português, comércio online). Apresenta-se como uma empresa tecnológica que suporta uma plataforma de venda online de artigos de moda e já conta com uma vasta audiência. Com o seu crescimento, o seu volume de dados aumenta rapidamente e, consequentemente, também as suas limitações de armazenamento e de autonomia nas consultas de informação por parte dos profissionais de análise de dados. No âmbito da procura da solução para os problemas enumerados acima, e especificados na secção seguinte, serão utilizados conceitos e técnicas derivados, maioritariamente, das áreas de Business Intelligence (BI) e Armazéns de Dados (AD). Um Armazém de Dados é uma ferramenta usada por muitas empresas para obter e manter uma vantagem competitiva. Recolhido a partir de fontes de dados internas e externas, a organização de um AD moderno visa facilitar o acesso aos dados. É, portanto, um conjunto de dados orientado por assunto, integrado, catalogado temporalmente e não volátil, que suporta os gestores no processo de tomada de decisão [Inm96]. Assim que a informação é montada, é usado um tipo de software 1 Introdução de consulta assistida para recuperar dados do armazém, o quais são analisados, manipulados e relatados [Moe00]. O termo Business Intelligence refere-se a tecnologias, aplicações e práticas para a coleta, integração, análise e apresentação de informação de um negócio. O seu propósito é apoiar uma melhor tomada de decisões empresariais. Essencialmente, os sistemas de Business Intelligence são Sistemas de Apoio à Decisão (do inglês, DSS - Decision Support Systems) orientado a dados. [OLA17] O BI integra a atividade de exploração do AD, incluindo consultas predefinidas, consultas ad-hoc e elaboração de relatórios, que normalmente permitem a monitorização da evolução ocorrida nos principais indicadores de negócio ao longo da vida da organização. [SR09] Ao longo do trabalho presencial na empresa, houve foco em analisar as suas soluções atuais, indicadores de desempenho perante diferentes objetivos e perspetivas, elaborar propostas para os seus problemas, quer relativamente ao planeamento da base de dados, como à distribuição dos dados e poder computacional, testá-las e compará-las umas com as outras e com o modelo em vigor, elaborar um plano de migração para a(s) nova(s) tecnologia(s) e comparar os resultados com estudos semelhantes. Numa tentativa de selecionar os dados a analisar, consideramos ao longo de todo o trabalho os dados resultantes da análise do clickstream da loja online da empresa. 1.2 Problema Mas o que fazem as empresas com toda esta informação? Qual o seu objetivo? Os dados armazenados podem servir vários propósitos e um deles é análise para suportar a tomada de decisões de gestão. Para isto, os analistas de dados ou gestores fazem consultas às bases de dados. No entanto, quando esta informação existe em grandes quantidades e acomodada nas bases de dados relacionais convencionais, estes profissionais ficam sem autonomia na execução das suas consultas pois são sistemas pouco intuitivos para pessoas sem os conhecimentos necessários na área da informática. O conceito de Armazém de Dados surgiu para responder a estes problemas, aliado ao Business Intelligence. Com esta combinação, procura-se alcançar autonomia da consulta de informação e, assim, uma aproximação ao chamado "self-service BI". Em suma, com o problema do grande volume de dados, temos questões de escalabilidade, e com a modelação do armazenamento de informação, temos o obstáculo do alcance de detalhe e autonomia que podemos oferecer aos gestores e analistas de dados nas suas consultas. E, ainda, devemos considerar a questão da rapidez com que se consegue disponibilizar os dados armazenados para 2 Introdução consulta pois, se os mesmos não ficarem prontos a ser consultados no momento devido, as decisões de que depende a gestão da empresa poderão ser comprometidas, assim como a continuidade do negócio. Procuramos neste documento perceber as possíveis soluções que existem atualmente para a resolução destes problemas e estudar a sua aplicabilidade no contexto da Farfetch. No final, teremos uma proposta com esse resultado, assim como, as metodologias que serão usadas para a migração de um sistema de base de dados centralizado para um sistema de base de dados distribuído através de técnicas do AD e do BI. 1.3 Motivação e Objetivos Numa tentativa de responder a estes problemas, a empresa implementou uma solução que iremos analisar ao longo de todo o documento. Esta será analisada e comparada com outras abordagens que existam atualmente no mercado dando ênfase a métricas de desempenho, custos e funcionalidade/usabilidade. Iremos abordar outros conceitos que podem auxiliar a construção da solução para os problemas da empresa, tais como a arquitetura do Armazém de Dados, bases de dados distribuídos, bases de dados em cloud, paralelismo, armazenamento colunar de dados, desnormalização, entre outros. O cenário que pretendemos analisar é composto pelas camadas de armazenamento e processamento no AD, assim como a cama de visualização referente às ferramentas Business Intelligence. Não só pretendemos analisar este cenário para o caso concreto da migração, como também para o caso em que o novo ambiente é utilizado para as tarefas rotineiras da empresa, em concreto, a manutenção/atualização do AD e as consultas sobre os dados, tentando sempre aplicar as boas práticas para a solução que iremos abordar. Para além da introdução, esta dissertação tem mais quatro capítulos. No capítulo 2, é descrito o estado da arte e são apresentados trabalhos relacionados. No capítulo 3, abordamos a problemática desta dissertação e levantamos algumas questões que serão respondidas ao longo do capítulo seguinte, o 4, em que apresentamos o procedimento utilizado para implementar a solução. Este documento termina com o capítulo 5, que apresenta as conclusões da dissertação. 3 Introdução 4 Capítulo 2 Revisão Bibliográfica 2.1 Introdução Este capítulo pretende dar a conhecer o estudo realizado para obter o conhecimento necessário à resolução do problema descrito no início deste documento. Está dividido em cinco partes. Na segunda, a 2.2, temos a revisão bibliográfica onde pretendemos dar a conhecer conceitos importantes sobre armazenamento de dados, envolvendo alguns conceitos gerais, chaves primárias, externas e surrogate keys, modelos dimensionais e transacionais, enquadramento do Business Intelligence no Armazém de Dados, "dimensões factuais", desnormalização e, finalmente, algumas técnicas de migração mais utilizadas para sistemas em cloud. A parte 2.3 consiste na abordagem de armazenamento colunar vs. orientado a linhas. Na secção 2.4, exploramos o uso do paralelismo no processamento de dados. E, por fim, na quinta parte (2.5) apresentamos um pequeno resumo retirado do estado da arte apresentado. 2.2 Armazenamento de Dados Um Armazém de Dados (AD) é uma base de dados que é mantida de uma forma autónoma em relação às bases de dados operacionais da organização. Toda a informação armazenada num AD é etiquetada temporalmente e deve permitir armazenar dados de múltiplos anos. Os utilizadores finais, analistas de informação, podem executar consultas complexas sobre o AD, libertando as bases de dados operacionais para as tarefas que ditaram a sua implementação, isto é, o registo eficiente e consistente das transações que representam os factos da operação da organização, o que 5 Revisão Bibliográfica Figura 2.7: Esquema em floco de neve contextualizado granularidade grossa. A granularidade deve ser tão fina quanto possível de forma a [KR02]: • Não perder informação • Obter um desenho mais robusto –relativamente a transações futuras não previstas –relativamente ao acrescento de novos elementos de dados • Simplificar e facilitar a análise de indicadores relevantes ao negócio A granularidade dos factos condiciona a forma como o modelo dimensional acompanha as solicitações que surgem à medida que os requisitos do negócio sofrem alterações ou aparecem novos pedidos dos utilizadores. O grão da tabela de factos tem um forte impacto no espaço ocupado em disco rígido pelos data marts (ver A.1): quanto mais fino é o grão, maior é a capacidade de armazenamento necessária. A multiplicação de tabelas de factos e de dimesões, para além de ter custos acrescidos na velocidade de processamento dos dados, tem igualmente reflexos negativos sobre a área de ETL, que se traduzem em procedimentos complexos com custos elevados de exploração e manutenção [Cal12]. A granularidade das dimensões não pode ser mais fina que a dos factos. Por exemplo, se os factos são mensais, a dimensão do tempo não pode ser diária pois haveria muitos valores possíveis. No entanto, pode ser mais grossa sem existir contradição como, por exemplo, no uso da marca para a dimensão produto em vez da referência correta [KR02]. 12 Revisão Bibliográfica Figura 2.8: Esquema em floco de neve contextualizado em detalhe No exemplo da figura 2.5, a granularidade da tabela de factos é cada linha representar uma vista da página na plataforma da empresa. 2.2.1.7 Factos Os factos estão associados a acontecimentos. Estes acontecimentos são valores numéricos que representam determinada métrica ou medida do processo de negócio [SR09]. Os factos mais úteis são numéricos e aditivos, como, por exemplo, o valor das vendas em euros. A aditividade é crucial porque as aplicações de BI raramente devolvem uma só linha de factos. Em vez disso, devolvem centenas, milhares ou mesmo milhões de linhas ao mesmo tempo, e o mais 13 Revisão Bibliográfica útil a fazer com tantas linhas é agregá-las e, sendo a soma a operação mais usual da agregação, podemos dizer que as somamos [KR13]. Já vimos que os factos podem ser aditivos, mas também existem semiaditivos e mesmo nãoaditivos. Os factos semiaditivos podem apenas ser agregados por algumas das dimensões consideradas no modelo de dados e não podem ser por outras como, por exemplo, pelo tempo em que não faz sentido somar o saldo de uma conta. Os factos não-aditivos, como preços unitários de produto, não podem ser agregados por nenhuma das dimensões consideradas no modelo. Então, temos de usar contagens e médias caso contrário, resta-nos imprimir as linhas de factos uma de cada vez, o que é impraticável para tabelas de factos com muitas linhas [KR13]. 2.2.1.8 Dimensões As dimensões contextualizam os factos medidos e registados nas tabelas de factos. As dimensões são homogéneas, dado que armazenam apenas um único tipo de entidades e, relativamente, independentes entre si. Uma dimensão é considerada conforme quando obedece simultaneamente às seguintes condições [Cal12]: • Abrange mais do que um processo de negócio; • Possui um conjunto completo de atributos, cada um deles com uma denominação completa que espelhe os dados que armazena. Deve ainda [KR02]: • Significar o mesmo em todas as estrelas; • Ter uma chave de utilizador bem definida; • Conter dados tratados, ie, limpos e consolidados; • Ter interfaces e conteúdos consistentes; • Ter interpretação consistente dos atributos e agregações; • Assegurar que a chave anónima do AD é definida de forma a que: –seja diferente da chave de produção –evite colisões de chaves –permita criar novos registos 14 Revisão Bibliográfica 2.2.1.9 Alterações nas dimensões Uma vez que um AD é um repositório de leitura de dados e que a operação de escrita de dados está reservada ao ETL, não é possível ao utilizador fazer alterações aos valores armazenados nas diversas dimensões com exceção à alteração de dados por duas razões: [SR09] • Erros • Alteração natural no sistema transacional As alterações só podem ser realizadas no momento de atualização do AD. No entanto, é necessário definir qual será a estratégia a adotar [Cal12].Quando as alterações às dimensões são pontuais, estas designam-se dimensões de alterações lentas ou slowly changing dimensions (ver A.4). Existem três técnicas para a atualização dos valores existentes nas dimensões, em que o primeiro e o segundo métodos são os mais utilizados [Cal12]: • Escrever por cima - Alterações do tipo 1: simplesmente escreve por cima do valor existente e perde-se o rasto ao valor anterior não havendo registo histórico da alteração. No nosso caso, na dimensão DimPagina (representa páginas do website), se imaginarmos o cenário de atualizar o URL da página, não haveria utilidade em guardar na base de dados o URL antigo. Assim, atualizamos o URL e podemos utilizar a coluna AtualizaData para guardar a data de atualização destes dados (ver tabelas 2.3 e2.4). A coluna InsereData guarda a data em que a linha foi inserida. SK_Pagina URL InsereData AtualizaData 282895 https://www.farfetch.com/cx/christmas 2000-01-01 00:00:00.000 NULL Tabela 2.3: Slowly Changing Dimension do tipo 1: antes da atualização SK_Pagina URL InsereData AtualizaData 282895 https://www.farfetch.com/christmas/ pass-the-parcel.aspx 2000-01-01 00:00:00.000 2017-11-09 05:20:37.943 Tabela 2.4: Slowly Changing Dimension do tipo 1: depois da atualização • Inserir um novo registo na dimensão - Alterações do tipo 2: mantém um historial completo dos valores dos atributos; quando deteta uma modificação, desencadeia, na área de estágio do repositório de dados, um processo que acrescenta uma nova linha à dimensão em que já consta o novo valor do atributo alterado. Este método implica ainda a adição de dois atributos na dimensão para registar os instantes do início e do fim da validade. Na dimensão DimUtilizador, onde os dados dos utilizadores estão presentes, se um utilizador pretender, por exemplo, alterar o seu nome, interessa à empresa guardar o nome anterior 15 Revisão Bibliográfica para consultas relacionadas com ações anteriores como, por exemplo, compras, para efeitos de faturação. Então, adiciona-se uma nova entrada na tabela com os dados atualizados, mantendo noutra linha os dados anteriores e indicado quando houve a alteração dos dados na ExcluiData da linha anterior. Esta data será a mesma dada pela coluna InsereData da nova linha (ver tabela 2.5). SK_Utilizador Name InsereData ExcluiData 219219 Ehrica 2016-07-30 00:41:15.973 2017-02-11 02:48:00.460 219400 Erica 2017-02-11 02:48:00.460 NULL Tabela 2.5: Slowly Changing Dimension do tipo 2 • Prever atributos adicionais nas dimensões - Alterações do tipo 3: obriga a que haja uma alteração na estrutura física das tabelas, é apropriado quando se quer dar ao utilizador a hipótese de analisar os dados sob o prisma de vários cenários alternativos; apesar de se ter verificado uma alteração, é possível, de um ponto de vista lógico e de análise, agir como se essa mesma alteração nunca tivesse sido realizada. No cenário em que um utilizador quer alterar a sua região na sessão, imaginemos o exemplo em que o browser deteta a sua região como sendo China, então a região da escolha do utilizador por defeito é também China. Mas, se o utilizador decidir alterar a sua região para os Estados Unidos, a coluna UtilizadorSubGrupoFim será preenchida com United States enquanto UtilizadorSubGrupoInicio eGeoSubGrupo (região detetada pelo browser) mantém o valor China (ver tabela 2.6). SK_Sessao GeoSubGrupo UtilizadorSubGrupoInicio UtilizadorSubGrupoFim 74913736 China China United States Tabela 2.6: Slowly Changing Dimension do tipo 3 2.2.1.10 Dimensão degenerada Por vezes, as dimensões são definidas sem conteúdo para além da sua chave natural (e da chave anónima, criada sistematicamente para todas as dimensões). Estas chaves naturais são por vezes representadas diretamente nas tabelas de factos, evitando-se a criação das respetivas dimensões, e são por isso designadas dimensões degeneradas. A dimensão degenerada é colocada na tabela de factos com o conhecimento explícito de que não há dimensões associadas. Estas dimensões são mais comuns na transação e na acumulação instantânea das tabelas de factos [KR13]. 16 Revisão Bibliográfica Por exemplo, quando um utilizador está a navegar por um website, as páginas por onde navega têm um carrinho de compras, uma cookie e um país associados. Apesar de nenhuma destas três dimensões terem mais atributos para além das suas chaves naturais, estas chaves são dados importantes para a interpretação de toda a página. Assim, estas chaves naturais são incluídas na tabela de factos de página. Na figura 2.10 podemos ver na tabela de factos três elementos: CarrinhoID,CookieID ePaisID. São as três dimensões degeneradas que, numa primeira abordagem seriam três dimensões distintas apenas contendo as suas chaves naturais, como podemos ver na figura 2.9 Figura 2.9: Esquema em estrela com três dimensões contendo unicamente a sua chave primária Figura 2.10: Esquema em estrela com três dimensões degeneradas presentes na tabela de facto 2.2.1.11 Dimensão Lixo Os processos de negócio transacionais produzem tipicamente indicadores variados e de baixa cardinalidade, como por exemplo indicadores boleanos de "sim ou não". Se mantivermos todos esse indicadores na tabela de factos, não só se torna necessário construir várias pequenas tabelas de dimensão, como o volume de dados armazenados na tabela de factos aumenta consideravelmente, correndo o risco de provocar problemas de desempenho e de gestão [1ke17]. Adimensão lixo (do inglês, junk dimension) é a solução para este problema permitindo criar uma única dimensão que combina esses indicadores como seus atributos, reduzindo o tamanho da tabela de factos. Esta dimensão, frequentemente rotulada como uma dimensão de perfil transacional num esquema, não precisa de ser o produto cartesiano de todos os valores possíveis dos atributos, mas deve conter somente a combinação de valores que realmente ocorrem nos dados 17 Revisão Bibliográfica fonte. [KR13]. É útil porque fornece informação adicional sobre um acontecimento. Na figura 2.11 podemos ver presentes na tabela de factos quatro elementos: PagEntrada,PagSaida,seBot enovoRegistado, onde está representado se a página foi a primeira página da sessão, se foi a última da sessão, se a página está a ser visualizada numa sessão detetada como de um utilizador bot e se esta página está a ser visualizada na sessão em que o utilizador se registou no website, respetivamente. Estes dados são do tipo "Sim"ou "Não", ie, boleanos. Então, foi criada uma dimensão lixo DimLixoVistas que contém estes elementos como atributos, reduzindo, assim, o tamanho da tabela de factos, como podemos ver na figura 2.12. Figura 2.11: Esquema em estrela com quatro elementos de baixa cardinalidade Figura 2.12: Esquema em estrela com uma dimensão lixo 2.2.1.12 Hierarquias A definição de hierarquia diz-nos que esta é representada numa relação pai-filho em que o filho tem só um pai, isto é, numa relação de um-para-muitos e, consequentemente, uma estrutura em árvore [MZ08]. Uma árvore representa uma relação de hierarquia se esta for interpretada como um conjunto de níveis como relações de muitos-para-um entre eles. Numa base de dados relacional, os diferentes níveis de uma hierarquia podem ser armazenados numa só tabela, como num esquema em estrela, ou então em tabelas separadas, como num esquema em floco de neve. Podemos ver dois exemplos destes nas figuras 2.13 e2.14 em que, nesta última, DimCidade,DimRegiao,DimPais eDimContinente representam cidade, região, país e continente, respetivamente, e entre elas existe uma relação de um-para-muitos (pela ordem apresentada) e, estando este esquema numa forma mais normalizada, i.e., mais aproximada de um esquema em floco de neve, podem ser comprimidas numa só dimensão chamada DimGeografia (ver 2.13) em que os campos relativos a cidade, região, país e continente estão incluídos nessa dimensão como atributos [Ger18]. 18 Revisão Bibliográfica Figura 2.13: Hierarquia entre factos e dimensões em modelo desnormalizado Figura 2.14: Hierarquia entre factos e dimensões em modelo normalizado Quando se comprime tabelas numa só tabela como vimos da figura 2.14 para 2.13, está-se a aplicar uma técnica de desnormalização que veremos mais à frente (2.2.7.1) com base nas hierarquias. Este conceito de hierarquia é utilizado como base para análise da informação através de drilling down com o objetivo de obter mais detalhe ou do rolling up para resultados mais sumariados, como podemos verificar na figura 2.15 (ver 2.2.1.15). 2.2.1.13 Hierarquias nas dimensões A definição de hierarquias entre os atributos das dimensões é uma outra forma de restrição, ou associação, lógica no modelo dimensional. As tabelas de dimensões representam por vezes estas relações de hierarquia e a informação que dela resulta é armazenada de forma redundante com o objetivo de facilitar o uso e aumentar o desempenho de consultas. Considerando o exemplo da tabela 2.7 e da figura 2.15, deve-se, portanto, evitar normalizar os dados armazenando apenas o código da marca na dimensão produto e criar uma tabela separada para a marca e, analogamente, o mesmo se aplica à categoria. Esta é, portanto, uma técnica de desnormalização. 19 Revisão Bibliográfica Esta desnormalização não tem impacto relevante no tamanho geral da base de dados pois as dimensões são, tipicamente, geometricamente mais pequenas do que as tabelas de factos e também são, geralmente, altamente desnormalizadas com relações muitos-para-um numa única dimensão. Nestes casos, é sempre preferível optar por simplicidade e acessibilidade ao invés de espaço [KR13]. DimProduto Nome do Produto Marca Categoria vestido sem alças Ana Sousa Vestuário camisola às riscas Michael Kors Vestuário meias fantasia Gucci Acessórios clutch clássica dourada Valentino Malas Tabela 2.7: Tabela da dimensão produto - DimProduto Um exemplo de hierarquia nas tabelas de dimensão pode ser encontrado na dimensão DimProduto na qual estão presentes os atributos Nome,Marca eCategoria, entre outros. Neste exemplo é de destacar a relação hierárquica representada na figura 2.15 em que Categoria está hierarquicamente acima de Marca que está, por sua vez, acima de Produto. Desta forma, para cada linha da dimensão do produto (DimProduto) deve-se armazenar a marca e a categoria associadas, como podemos ver na figura 2.7. Figura 2.15: Diagrama da hierarquia dentro da dimensão DimProduto 2.2.1.14 Múltiplas Hierarquias nas Dimensões É comum haver casos em que hierarquias separadas coexistem na mesma dimensão como simples atributos desta. Para isso, é necessário que cada atributo destes seja de valor único face à chave primária da dimensão. As hierarquias podem ser de tamanho fixo ou irregular [KR13]. 20 Revisão Bibliográfica Uma hierarquia com profundidade fixa tem o número de níveis constante e é modelado e populado com um atributo na dimensão para cada nível. É um conjunto de relações de muitos-para-um. Este tipo de hierarquia é o mais fácil de compreender e navegar e permite rapidez e previsibilidade no desempenho de consultas [KR13]. Quando uma hierarquia não é representada numa série de relações de muitos-para-um ou o número de níveis varia, não deve ser considerada uma hierarquia fixa, mas antes uma hierarquia de profundidade ligeiramente irregular. Temos como exemplo a localização geográfica cujo número de níveis não é fixo (usualmente, de três a seis níveis) mas o seu alcance em profundidade é pequeno. Ao invés de utilizar mecanismos complexos para hierarquias que variam de forma imprevisível, podemos forçar a profundidade e encaixar este tipo de hierarquias num modelo com profundidade fixa e com atributos separados na dimensão para um número máximo de níveis e só então popular os valores destes atributos [KR13]. As hierarquias altamente irregulares são encontradas tipicamente em estruturas organizacionais desequilibradas ou com profundidade indeterminada e, por esta razão, são as mais difíceis de modelar e consultar. Uma boa alternativa é, numa base de dados relacional, modelar uma hierarquia irregular com a chamada "tabela-ponte"(bridge table). Esta tabela-ponte contém uma linha por cada caminho possível na hierarquia irregular e permite todas as formas de cruzamento da hierarquia usando SQL em vez de usar extensões de linguagens especiais [KR13]. Temos ainda o caso de hierarquias irregulares com atributos pathstring cuja base assenta em evitar utilizar tabelas-ponte implementando um atributo pathstring na dimensão. Para cada linha na dimensão, o atributo pathstring contém uma string de texto altamente codificada contendo a descrição completa do caminho do nó supremo de uma hierarquia até ao nó descrito pela linha da dimensão. Esta abordagem pode ser vulnerável a alterações estruturais na hierarquia irregular [KR13]. 2.2.1.15 Esploração de um AD O OLAP (ver secção 2.2.1.2) permite criar cubos para analisar a informação através de diferentes perspetivas simultaneamente, sendo esta a tecnologia mais comum para explorar um AD. Estes cubos permitem analisar os factos disponíveis, na tabela de factos, pelas diferentes dimensões consideradas na modelação realizada [SR09]. Portanto, os servidores OLAP permitem a análise multidimensional dos dados a partir de um qualquer repositório de dados. Estes servidores podem ser [SR09]: •ROLAP (Relational OLAP): operam como intermediário entre uma base de dados relacional e as ferramentas de frontend que funcionam como cliente para análise de dados. Utili21 Revisão Bibliográfica Figura 2.19: Arquitetura tecnológica de um sistema AD/BI 2.2.5 Armazém de Dados Distribuído À medida que uma empresa vai crescendo, também o seu volume de dados aumenta. Assim que um AD é implementado, a procura pelo acesso aos dados cresce em proporção direta ao sucesso do AD. No entanto, os dados organizados e prontos a ser acedidos parecem produzir mais dados e, por vezes, além dos limites de um AD regular. Ao invés de utilizar um sistema central de grandes dimensões, algumas organizações optam por servidores de grupos de trabalho em que fornecem os serviços básicos de processamento de dados para um departamento [Moe00]. 2.2.5.1 Contexto Enquanto os AD continuam a crescer além dos limites de um único sistema de computação, é necessário encontrar uma forma de conectar com sucesso todo o hardware participante e todos os utilizadores. Uma intranet organizacional é o veículo ideal para uma conectividade generalizada dentro de uma empresa. Este método de acesso aproveita as tecnologias da Internet para fornecer dados em toda a organização a partir de qualquer fonte interna conectada, independentemente da plataforma ou local. 28 Revisão Bibliográfica Transitar de um modelo baseado em desktops para um modelo centralizado na rede é revolucionário para qualquer organização. Instalar e manter ferramentas fáceis de usar na área de trabalho de alguns utilizadores, é possível para um projeto-piloto - esta abordagem é comummente designada de fat-client (ver B.2). No entanto, instalar software em cada área de trabalho de uma empresa rapidamente se torna incomportável, assim como a sua manutenção. Estas instalações e manutenções representam enormes custos para uma empresa. Tornando os browsers uma ferramenta padrão para todos os desktops empresariais e instalando apenas software habilitado para a Web, a organização pode avançar para um modelo thin-client com uma fração do custo de gestão de sistemas da abordagem fat-client. A abordagem de centralização na rede elimina a necessidade de serviços em cada computador, economizando tempo e dinheiro, além de simplificar o deployment e a manutenção da aplicação. Foi inicialmente pensado que a recolha de todos os dados para um único reservatório relacional iria simplificar o acesso aos dados. E, utilizando um conjunto de ferramentas de serviços relacionais, como linguagens de definição de dados (DDL), linguagens de manipulação de dados (DML), verificadores de integridade referencial, monitores de restrição e linguagem de consulta estruturada (SQL), os profissionais esperavam proporcionar acesso aberto a todos, incluindo a comunidade de utilizadores. Mesmo que esta abordagem não pudesse excluir completamente os programadores do caminho de acesso para todos os utilizadores, pensava-se que, pelo menos, aceleraria o processo de consulta. Como efeito colateral do processamento centralizado, os proprietários perderam o controlo dos seus repositórios de dados e aperceberam-se da complexidade da manutenção de dados pelo sistema centralizado. Embora, de forma geral, a organização tenha beneficiado desses reservatórios de dados, concluiu-se que esta não seria a solução ideal. Surgiram dificuldades na manutenção da consistência entre os sistemas central e local, e houve problemas constantes com a transferência de dados entre os sistemas locais. Então a pressão começou a crescer para a descentralização formal dos repositórios de dados e das suas funções, mantendo a integração e talvez algum controlo centralizado. [Moe00] 2.2.5.2 Razões para aderir à distribuição do AD A Lei de Moore foi enunciada por Gordon Moore, co-fundador da Intel Corp., que declarou que "a cada dezoito meses, a capacidade de computação duplica e os preços são reduzidos a metade". Entre todas as razões técnicas para a distribuição de dados, realçamos as seguintes [Moe00]: •Preocupações com a segurança: a distribuição pode tornar a segurança mais simples; a forma mais segura de proteger os dados é negando o seu acesso; se os dados a proteger 29 Revisão Bibliográfica forem armazenados num servidor departamental, o acesso a este pode ser limitado negando solicitações de informações com origem fora do departamento. •Planeamento da capacidade: aumentar a capacidade de um sistema centralizado é uma tarefa difícil, muitas vezes exigindo a substituição do processador central e tempo de inatividade para todo o sistema de computação.; num ambiente distribuído, um novo servidor adicionado à rede, geralmente, resolve problemas de capacidade sem qualquer interrupção do serviço. •Problemas de recuperação: para fins de recuperação de desastres e backup, ter dados e processamento em vários locais é o desejável pois, se um processador falhar, outro pode assumir o seu trabalho evitando interrupções. 2.2.5.3 Vantagens A descentralização de dados traz vários benefícios para um empresa. Realçamos os seguintes [Moe00]: • simplifica a evolução dos sistemas, permitindo uma melhor resposta aos requisitos dos utilizadores • permite autonomia local, devolvendo o controlo dos dados aos utilizadores • fornece uma arquitetura simples do sistema, tolerante a falhas e flexível, podendo levar a uma poupança de custos para a empresa • garante bom desempenho 2.2.5.4 Desvantagens Existem problemas tecnológicos nesta abordagem que pedem a atenção dos desenvolvedores do sistema, tais como [Moe00]: • garantir que o acesso e processamento entre computadores locais sejam realizados de uma forma eficiente • distribuição do processamento entre nós • distribuição dos dados para um melhor benefício em torno dos vários locais/ nós de uma rede • controlo do acesso aos dados ligados pela rede • apoio à recuperação de problemas de forma eficiente e segura 30 Revisão Bibliográfica • controlo do processamento das transações para evitar interferências E ainda podemos destacar alguns problemas relativos ao planeamento [Moe00]: • estimar a capacidade de sistemas distribuídos • previsão do tráfego em sistemas distribuídos • projetar para maximizar a alocação de dados, objetos e programas • minimizar a concorrência por recursos entre nós Embora seja importante reconhecer e resolver estas dificuldades, os benefícios, tanto técnicos como de negócio, de processamento distribuído têm uma clara vantagem para projetos de grande porte, como no caso de Armazéns de Dados [Moe00]. 2.2.5.5 Requisitos No entanto, a instalação de redes de comunicações para ligar sistemas informáticos não dá por si só uma resposta completa aos sistemas distribuidos. Programadores e utilizadores que pretendam fazer uma consulta usando duas ou mais bases de dados em rede devem: • conhecer a localização dos dados a ser recuperados • dividir a consulta em bits que podem ser endereçados em nós individuais • resolver, sempre que necessário, quaisquer inconsistências entre tipos de dados, formatos ou unidades armazenados em vários locais. • organizar os resultados de cada consulta a ser acumulada no sistema de origem • extrair os detalhes necessários E, ainda, se considerarmos que os dados devem ser mantidos atualizados e consistentes e que as falhas nas ligações de comunicação devem ser detetadas e corrigidas, os serviços de suporte são necessários [Moe00]. 2.2.6 Dimensões Factuais Muitas vezes a identificação de uma entidade como facto ou dimensão é mais complicada do que seria num contexto comum como o que temos vindo a analisar ao longo deste documento. O grande crescimento de ocorrências de tabelas que se comportam, tanto como de factos, como de dimensões levou a que fosse criada uma página no site do Kimball Group [Mun11] dedicada a este tópico. Na página são dados alguns exemplos como numa companhia de seguros, se a unidade de 31 Revisão Bibliográfica processamento de sinistros quiser analisar e relatar as suas reclamações em aberto, as reclamações tanto se comportam como acontecimentos (factos), como contextos de análise de acontecimentos (dimensões). Uma tabela com uma linha por processo (uma linha por reclamação) assemelha-se a uma dimensão. Mas se tentarmos fazer a distinção pela entidade (reclamação) e o processo (resolução de sinistros) torna-se mais claro. A indecisão mantém-se porque precisamos de uma tabela de factos para medir o processo, assim como de quaisquer processos que descrevam os atributos da entidade a ser medida. A solução proposta pelo Kimball Group é, face à indecisão, reconhecer que um evento num negócio representado numa tabela de factos é, na realidade, um processo de longa duração ou um ciclo de vida pois os utilizadores da consultas ao AD estão frequentemente mais interessados em ver o estado atual do processo. Um exemplo encontrado ao longo deste estudo encontra-se no documento na figura 4.3. No diagrama apresentado, a tabela DimSessao é um exemplo de dimensão factual precisamente porque, visto que representa uma sessão, a sua identificação torna-se ambígua por podermos interpretá-la como um acontecimento e também como um contexto de análise de uma ação numa página. 2.2.7 Desnormalização Atualmente, as consultas ao AD envolvem um conjunto de junções e agregações. Desta forma, a normalização poderá não ser benéfica à medida que aumenta a necessidade de aplicar estas operações nas relações entre tabelas nas consultas [MZ08]. A desnormalização aplicada às dimensões suporta os objetivos da modelação dimensional de simplicidade e rapidez [KR13]. Mas uma desvantagem da desnormalização é fornecer um baixo grau de suporte para eventuais atualizações se estas forem realizadas com alguma frequência. No conceito de AD, aplica-se esta baixa necessidade de modificar e/ou atualizar dados, logo a desnormalização será mais indicada para estes casos do que para uma base de dados operacional, onde variadas operações de atualização de dados são aplicadas com frequência [Mul97]. 2.2.7.1 Técnicas de desnormalização Existem variadas formas de construir relações desnormalizadas para uma base dados, como vermos de seguida [Mul97]: 32 Revisão Bibliográfica •Tabelas pré-juntas (Pre-joined Tables) Se for necessário a uma aplicação aplicar regularmente uma junção a duas ou mais tabelas mas o custo desta operação é impraticável, deve ser considerada a criação de tabelas de dados previamente juntas (tabelas pré-juntas). Estas tabelas devem: –Não conter colunas redundantes, correspondendo ao critério de junção –Conter apenas as colunas necessárias para servir a aplicação –Serem periodicamente criadas através de SQL para aplicar a junção das tabelas normalizadas Desta forma, o custo da junção será aplicado apenas uma vez no momento da criação das tabelas pré-juntas, e pode ainda implicar um aumento na eficiência da consulta a estas tabelas pois esta não fica sujeita ao processo de junção. •Tabelas-relatório (Report Tables) O desenvolvimento de um relatório utilizando unicamente SQL ou QMF (ver B.6) é impraticável pois estes relatórios requerem uma formatação especial ou ainda mesmo alguma manipulação de dados. No caso de existir a necessidade deste tipo de relatórios que devam ser vistos num ambiente online, deve-se considerar criar uma tabela que represente o relatório. •Tabelas espelhadas (Mirror Tables) Se uma aplicação tiver uma grande atividade, pode ser necessário dividir o seu processamento em dois ou mais componentes distintos. Isto requer a criação de tabelas duplicadas, ou espelhadas. Considerando o caso de uma aplicação ter duas operações sobre uma tabela: de processamento de apoio à decisão e de atualização de dados, então poderá acontecer uma incoesão dos dados resultando em time-outs edead-locks no processamento de apoio à decisão se este estiver a ocorrer ao mesmo tempo da atualização. A solução vem com as tabelas espelhadas na qual poderia existir um conjunto de tabelas em primeiro plano para o tráfego de produção e outro conjunto de tabelas poderia existir em segundo plano para relatórios de apoio à decisão. Deve também existir um mecanismo que migre periodicamente os dados de primeiro plano para tabelas do segundo plano para manter os dados atualizados. Convém também considerar que as necessidade de acesso ao ambiente de produção são muito diferentes das de acesso ao ambiente de apoio à decisão e, para esse caso, existem definições aplicáveis individualmente a estas tabelas tais como indexação e agregações. •Tabelas divididas (Split tables) Se pedaços individuais de uma tabela normalizada são acedidos por diferentes grupos de utilizadores ou aplicações, então deve ser considerada a divisão da tabela em duas ou mais 33 Revisão Bibliográfica tabelas desnormalizadas : uma para cada grupo. A tabela original pode também ser mantida se ainda assim existirem processos que a consultem por inteiro. Neste caso as tabelas divididas devem ser vistas como um caso especial de uma tabela espelhada. Se, pelo contrário, não existir a necessidade de manter a tabela original, então pode ser fornecida uma vista com as junções das tabelas. As tabelas podem ser dividas de duas formas: verticalmente e horizontalmente. A primeira corta a tabela por colunas de forma a um grupo de colunas poder ser incluído numa tabela e as colunas restantes, noutra. A divisão horizontal, por outro lado, corta a tabela por linhas de forma a que as linhas sejam classificadas em grupos que podem ser incluídos individualmente em tabelas separadas. •Tabelas combinadas (Combined tables) Se existirem tabelas que se relacionem através de uma relação de um-para-um, podem ser combinadas numa só tabela. Esta técnica pode até mesmo ser aplicada a tabelas com relação de um-para-muitos, mas o processo de atualização de dados poderá tornar-se demasiado complexo devido ao aumento de dados redundantes. No caso de ambas as tabelas terem muitas colunas em comum, dever-se-á considerar usar a técnica de "tabelas pré-juntas"em vez desta, pois esta técnica junta as colunas de ambas as tabelas a combinar exceto a coluna referente ao critério de junção podendo, de outra forma, originar muitos dados repetidos. •Dados redundantes (Redundant Data) Se for comum uma ou mais colunas serem acedidas através de uma consulta que acede a outra tabela ou a várias, então deve ser considerado transportar essas colunas para estas tabelas como dados redundantes. Desta forma consegue-se evitar as junções de colunas e aumenta-se a rapidez de consulta. As colunas que podem ser transportadas como dados redundantes devem ter as seguintes características: –Apenas algumas colunas são necessárias para a redundância –As colunas devem ser estáveis e a sua atualização não deve ser frequente –As colunas devem ser usadas, ou por um grande número de utilizadores, ou então por utilizadores de grande importância •Grupos repetidos (Repeating Groups) Quando os grupos repetidos são normalizados, são implementados em linhas distintas em vez de em colunas distintas, resultando num maior uso de DASD (ver B.7) e menor eficiência no retorno de resultados por haver mais linhas na tabela e mais linhas a ler pelas consultas. A desnormalização pode ser feita armazenando os dados em colunas distintas, no entanto 34 Revisão Bibliográfica esta técnica pode custar flexibilidade. Assim os grupos repetidos são atributos de uma relação não normalizada que seriam convertidos em tuplos individuais se na forma normalizada. Por exemplo, se considerarmos o mesmo valor de um dado atributo ao longo de 5 anos, temos 5 linhas distintas. Usando os grupos repetidos, temos estes valores na mesma linha utilizando 5 colunas distintas. Deve ser aplicada se os seguintes critérios se verificarem: –Os dados são raramente, ou nunca, agregados, comparados ou aplicados uma média na mesma linha –Os dados têm, estatísticamente, um bom comportamento –Os dados têm um número estável de ocorrências –Os dados são normalmente acedidos de forma coletiva –Os dados têm um padrão previsível de inserção e eliminação •Dados derivados (Derivable Data) Se o custo de obtenção de dados usando fórmulas complicadas é impraticável, então deve-se considerar armazenar estes dados obtidos numa coluna, em vez de os calcular. No entanto, quando os dados subjacentes se alteram, é imperativo que estes dados obtidos armazenados sejam atualizados senão pode ocorrer inconsistência de dados •Hierarquias (Hierarchies) Uma hierarquia é uma estrutura que é fácil de manter utilizando uma base de dados relacional, mas é complicada para obter dados de forma eficiente. Por esta razão, é comum as aplicações que se suportam sobre hierarquias terem tabelas desnormalizadas para acelerar a obtenção de dados. Os detalhes a este tipo de desnormalização já foram visto anteriormente em 2.2.1.13. 2.2.7.2 Desnormalização em Google BigQuery A desnormalização é uma das boas práticas apresentadas pelo Google BigQuery não só pela baixa frequência de alterações dos dados armazenados como também por se apresentar como uma ferramenta de baixo custo em armazenamento. Baseia-se em duas técnicas de desnormalização que abordámos anteriormente em 2.2.7.1 chamadas de tabelas pré-juntas (pre-joined tables) - permitindo, desta forma, trocar recursos de computação por espaço de armazenamento sendo o armazenamento, para este caso, economicamente viável e com um bom desempenho - e desnormalização por hierarquias. De forma a facilitar o uso desta técnica, o BigQuery fornece as estruturas de dados chamadas 35 Revisão Bibliográfica de nested erepeated, isto é, dados aninhados e repetidos. É possível usar dados aninhados ou aninhados e repetidos tanto no sistema interativo do BigQuery como utilizando um ficheiro com o esquema em formato JSON. Do ponto de vista do carregamento de dados, é possível fazê-lo através de ficheiros que suportem esquemas baseados em objetos tais como JSON,Avro e ficheiros de backup de Cloud Datastore. Nos casos em que temos duas tabelas com uma relação de um-para-muitos, sem que seja necessariamente parte de uma hierarquia, podemos simplesmente juntar a segunda tabela à primeira usando uma chave para identificar esta relação. Do ponto de vista estrutural, é análogo ao conceito de um array de arrays ou, até mesmo, de um objeto ou estrutura de uma linguagem orientada a objetos. Neste caso, falamos de campos aninhados e repetidos (nested and repeated fields). Nos casos em que duas tabelas se relacionam através de uma relação de um-para-um, o procedimento é semelhante apenas com a diferença de apenas se aninhar um "objeto", logo temos apenas um array dentro de (ou aninhado em) outro array. Desta forma, falamos apenas de campos aninhados (nested fields), visto que a repetição da relação antes abordada não se aplica. Podemos ver o exemplo que iremos analisar neste documento na secção 4.10. 2.2.8 Técnicas de migração Existem cerca de cinco técnicas de migração para sistema de cloud comummente utilizadas pelas empresas quando pretendem migrar o serviço que utilizam de suporte à(s) sua(s) base(s) de dados [Gar11]: •realojar (rehost) é a técnica mais comum de migração para um sistema em cloud; trata-se de replicar a aplicação atual num ambiente de cloud sem que seja redesenhada; é usualmente chamada de lift-and-shift. •regerir (refactor) lançar aplicações numa infraestrutura em cloud fornecida do tipo PaaS. •rever (revise /replatform) modificar ou extender a base do código existente para suportar os requisitos de modernização da missão e, depois, utilizar a opção realojar ou refactor para lançar para a cloud; geralmente chamado de lift-tinker-and-shift. •recriar (rebuild) recriar a solução em PaaS, descartar o código da aplicação existente e rearquitetar a aplicação. 36 Revisão Bibliográfica •substituir (replace) descartar uma ou mais aplicações existentes e usar o software comercial entregue como serviço - SaaS. técnicas vantagens desvantagens realojar rapidez características de cloud em falta; dependência regerir retrocompatibilidade; familiaridade capacidades em falta; risco transitivo; dependência rever otimização da aplicação despesas extra em RH recriar acesso a funcionalidades inovadoras dependência substituir evita despesas extra em RH semântica de dados inconsistente; problemas de acesso aos dados; dependência Tabela 2.9: Vantagens e desvantagens das técnicas mais comuns de migração para um sistema cloud [Gar11] [Med18] A análise entre vantagens e desvantagens está presente na tabela 2.9 e a análise ainda entre tempo/custos e benefícios da migração para a cloud está representada na figura 2.20. Figura 2.20: Análise entre tempo/custo e benefícios das técnicas de migração para cloud [Mor16] 2.3 Column-stores eRow-stores Existem duas formas comuns de mapear uma tabela de bases de dados relacionais de duas dimensões para uma interface de armazenamento unidimensional: armazenar a tabela linha a linha (roworiented) ou coluna a coluna (column-oriented). O método mais comum é ainda row-oriented pois 37 Revisão Bibliográfica Os programas escritos segundo este estilo funcional são automaticamente paralelizados e executados num grande conjunto de máquinas. O sistema run-time trata dos detalhes de particionamento dos dados de input, agendamento do processamento do programa ao longo de um conjunto de máquinas, lida com as fallhas das máquinas e gere a comunicação entre máquinas [DG08]. Na sua modelação, a desnormalização é usual para atributos acedidos com muita frequência e para evitar joins de tabelas muito grandes [Con15]. 2.4.2.1 Vantagens Conseguimos perceber as seguintes vantagens da utilização de um sistema MapReduce [Tut17]: •fácil de utilizar, já que esconde os detalhes da paralelização, tolerância a falhas, otimização local e balanceamento de carga; •flexibilidade, traduzindo-se na capacidade de facilmente expressar uma grande variedade de problemas •escalabilidade, pois tem a capacidade de escalar para grandes agrupamentos de máquinas podendo compreender milhares •solução económica para empresas que precisam de armazenar dados com um volume crescente •rapidez de processamento, visto que as ferramentas utilizadas no processamento de dados estão presentes em cada máquina •segurança pois utiliza os serviços de segurança HDFS e HBase, permitindo acesso apenas aos utilizadores com autenticação •processamento paralelo dividindo as tarefas de forma a que a sua execução seja em paralelo •natureza resiliente, pois podem ser utilizadas execuções para reduzir o impacto de máquinas mais lentas, assim como falhas das máquinas e perda de dados e, ainda, utiliza otimizações com o objetivo de reduzir o volume de dados enviados pela rede, minimizando o problema de limitações da largura de banda de rede. 2.4.2.2 Desvantagens Compilamos as seguintes principais fraquezas de um sistema MapReduce [Pay14]: •não-seletivo porque precisa de explorar todo o input de forma a processar o mapeamento 44 Revisão Bibliográfica •processamento redundante realizando processamentos semelhantes ao longo de diferentes tarefas sobre os mesmos dados, não havendo a possibilidade de fazer um reaproveitamento dos resultados de tarefas anteriores •sem fim antecipado quando a tarefa de mapeamento precisa de explorar todos os dados de input para poder prosseguir para a tarefa de redução (reduce) •sem iteração, pois os programadores precisam de redigir uma sequência de tarefas e coordenar a sua execução de forma a implementar um processo iterativo, assim, os dados necessitam de ser recarregados e reprocessados a cada iteração •falta de processamento interativo e em tempo real quando já existem muitas aplicações que requerem tempos de resposta curtos, análises interativas e análises online. 2.4.3 MPP vs. MapReduce Na tabela 2.10 são apresentados os principais aspetos em que ambos os sistemas se distinguem. 2.5 Conclusões No decorrer deste capítulo vimos alguns conceitos e questões pertinentes do nosso problema. Com este estudo, podemos concluir que dificilmente existirá uma resposta certa para toda a variedade de necessidades e motivações de todas as empresas, e ainda que esta deva abranger, não só a escolha das tecnologias a utilizar, como também a estrutura do Armazém de Dados (assim como a sua modelação) e do sistema de Business Intelligence. No próximo capítulo, iremos pôr em prática procedimentos na realização de tarefas usuais nas soluções atuais da empresa, utilizar métodos de teste para comparar indicadores de desempenho entre as diferentes possíveis soluções para o problema através de simulações e, ainda, perceber quais os melhores procedimentos para a migração de um sistema de bases de dados tradicional para aquele que iremos propor. 45 Revisão Bibliográfica MPP MapReduce Desempenho Componentes de otimização e distribuição Melhor desempenho Obedece às propriedades ACID (atomicidade, consistência, isolamento e durabilidade) garantindo que as transações da base de dados sejam processadas de forma precisa com segurança Não aplica a conformidade ACID por defeito Escalabilidade Necessidade de hardware altamente especializado tornando a escalabilidade difícil e mais dispendiosa Pode ser lançado para servidores comuns económicos permitindo que os clusters de nós cresçam conforme a necessidade Deployment e Manutenção Deployment e manutenção fáceis Manutenção e deployment mais complexos podendo representar custos adicionais no recurso a especialistas Restrições Consegue lidar com dados desestruturados sem necessidade de preprocessamento Linguagem SQL Java O uso de SQL faz com que seja mais fácil de utilizar e económico pela poupança de recursos Compatível com as ferramentas existentes usuais por estas serem baseadas em SQL Alternativas menos comuns devem ser procuradas podendo significar um custo adicional de recursos Tabela 2.10: MPP vs. MapReduce [Fly17] 46 Capítulo 3 Problema A empresa depara-se com quatro grandes problemas ao nível do Armazém de Dados. Estas dificuldades são: a limitação da sua tecnologia atual - Microsoft SQL Server 2016 - na capacidade de armazenamento de um grande volume de dados, no desempenho das consultas a esta significante quantidade de dados, na necessidade de uma maior autonomia e usabilidade nas consultas, e ainda no custo que uma tecnologia que corresponda à solução dos problemas anteriores possa implicar. 3.1 Limitações atuais no contexto da empresa Atualmente, não só existem limitações na capacidade de armazenamento dos dados, como também já são notadas dificuldades na capacidade de conseguir processar a quantidade de dados necessária às consultas que suportam a tomada de decisões da empresa. Um dos segmentos de dados que mais reflete o grande volume de que estamos a tratar é o clickstream, armazenando todos os dados referentes às interações dos utilizadores com a plataforma web do portal da Farfetch e com a sua aplicação móvel. Já vimos que as consultas estão com limitações na quantidade de dados a processar, mas também o seu tempo de resposta se está a tornar impraticável nas tarefas rotineiras de Business Intelligence. Oself-service BI traz autonomia ao uso do AD permitindo aos analistas e gestores aceder ao AD através de consultas, sem necessidade de formação em SQL ou na arquitetura do modelo de dados utilizado. Mas este conceito ainda é considerado inalcançável na totalidade da sua definição, o que não implica que esforços não devam ser aplicados no seu sentido. Pelo contrário, qualquer aproximação que se obtenha reflete-se numa considerável melhoria na utilização do AD 47 Problema e no suporte de decisões de negócio. A empresa não apresenta limitação concreta nos custos destinados à aplicação numa nova tecnologia, ou tecnologias, que respondam aos problemas apresentados. No entanto, é importante considerar a solução mais proveitosa analisando o tradeoff entre custos e benefícios. Ao longo da implementação da solução foram surgindo obstáculos, tais como falta de documentação da arquitetura do Armazém de Dados, o AD encontrava-se com algumas naturais inconsistências de dados resultantes do seu uso, atualizações e correções ao longo do tempo, e disponibilidade de uma grande variedade de tecnologias para aplicar a nossa análise. 3.2 Introdução à solução A solução passa por encontrar uma tecnologia, ou um conjunto destas, cujas capacidades e funcionalidades estejam de acordo com as necessidades da empresa. Para tal, é preciso aplicar um manual de procedimentos de migração que passa por definir alguns objetivos alcançáveis com esta migração de tecnologias, identificar um pequeno projeto que possa ser apresentado como amostra aos nossos testes, que no nosso caso será o clickstream, permitindo-nos tomar conclusões e, finalmente, criar uma prova de conceito aplicando o processo de migração à nossa amostra. 3.3 Migração 3.3.1 Objetivos a atingir A empresa definiu alguns objetivos mensuráveis a atingir com a migração que iremos propor: • no armazenamento e processamento: 1. aumentar rapidez para, pelo menos, 125% 2. aumentar a sua capacidade para, pelo menos, 1 PB 3. ser capaz de executar consultas sobre um grande volume de dados 4. aguentar, no mínimo, 50 consultas em paralelo 5. não deve exceder cerca do dobro dos custos com a tecnologia atual (prioridade menor) • na visualização de dados: 1. conseguir ficar mais próximo do self-service BI 48 Problema 2. bom aproveitamento da API da solução de armazenamento e processamento 3. não aumentar mais do que 10% do tempo de resposta às consultas 3.3.2 Questões a responder Ao longo do estudo deste documento foram levantadas algumas questões, às quais iremos apresentar resposta no próximo capítulo: • O modelo atual de dados deve manter-se ou deve ser atualizado para um sistema distribuído? • Poderá a concorrência vir a ser um problema, e que medidas tomar para minimizar o seu impacto? • Qual a opção a tomar, esquema em estrela ou desnormalização, ou ambos? • As operações de junção nas consultas devem ser limitadas? • De que forma deverá ser realizado o carregamento de dados para um sistema distribuído? • Haverá limitações na atualização de dados? 3.4 Resumo Este capítulo deixa-nos perceber quais os objetivos concretos para uma empresa representativa de um mercado procurar soluções que acompanhem o crescimento massivo de informação. No próximo capítulo iremos tentar dar resposta às necessidades exploradas neste, à forma como esta resposta se adequa à problemática explorada e ainda qual o método proposto para a sua implementação. 49 Problema 50 Capítulo 4 Implementação 4.1 Procedimentos Ao longo de 4 meses, foi dada a oportunidade de realizar o estudo para a concretização das análises presentes neste documento no ambiente empresarial da Farfetch. Para tal, foram adotados os seguintes pontos: 1. Pesquisar tecnologias que respondam às questões propostas 2. Estudar o papel das tecnologias na arquitetura do sistema 3. Selecionar tecnologias a estudar 4. Conhecer a arquitetura do Armazém de Dados 5. Estabelecer cenários de utilização para as tecnologias selecionadas 6. Selecionar cenários de utilização a estudar 7. Realizar testes de admissão às tecnologias 8. Análise de resultados 9. Realizar testes contextualizados 10. Análise de resultados 11. Realizar testes aos modelos normalizado e desnormalizado 12. Análise de resultados 13. Pesquisar várias técnicas de implementação da solução 14. Criar manual de procedimentos para a migração 51 Implementação 15. Validar resultados com trabalhos relacionados Cada ponto enumerado acima será abordado em maior detalhe nas secções que se seguem neste capítulo e, no final do mesmo, será dado um resumo de todo o procedimento adotado, assim como as conclusões retiradas. 4.2 Tecnologias Foram estudadas algumas das tecnologias mais usuais de Armazéns de Dados e de BI. De seguida, são apresentadas algumas características. 4.2.1 Tecnologias de Armazéns de Dados Segundo a fonte [G2C17], as seguintes tecnologias relacionadas com a criação e gestão de um AD, são as três mais procuradas: 1. Amazon Redshif é um AD com funções de armazenamento e análise de dados, com MPP totalmente gerido, baseado em SQL, orientado por colunas, com escalabilidade customizável , tolerante a falhas, com backups e restauros automáticos e as suas consultas podem alcançar os petabytes [ws17]; 2. SAP Business Warehouse pertence à camada de armazenamento de dados, baseado em bases de dados relacionais e, arquiteturalmente, assenta sobre bases de dados DB2, Oracle e SAP HANA [SAP17b]; 3. IBM PureData System for Analytics é uma ferramenta de análise e armazenamento de dados com MPP, pode escalar até aos petabytes, não é gerido e não tem serviço de cloud [IBM17a]. As tecnologias analisadas em conjunto com a empresa foram as seguintes: •Google Big Query é um serviço de armazenamento de dados e análise de dados em grande escala utilizando uma tecnologia cloud sem servidores [Goo17]. Tem extensões disponíveis tais como [Goo17] –Google Cloud Storage, –Google Cloud Datastore, –Google Data Studio e –Google Analytics Premium Foi encontrada a seguinte lista de tecnologias compatíveis com o Google BigQuery: 52 Implementação –SQL –Cloud Dataflow –Spark –Hadoop E ainda conta com as seguintes parcerias, entre outras: –Looker –Tableau –Qlik –Talend –Google Analytics –SnapLogic •Microsoft Azure SQL Data Warehouse é um serviço de armazenamento e processamento de dados com sistema de cloud e com escalonamento horizontal e vertical independentes e customizáveis, entre outras características [Mic17a]. Tem ainda as seguintes integrações [Mic17a] –Polybase –Azure Data Lake Store •SQL Server é um serviço de armazenamento e análise de dados com um sistema de processamento adaptativo de consultas e encriptação de dados em uso, em trânsito e em pausa [Mic17b]. É compatível com: –R –Python –serviços de cloud •Amazon S3 é um serviço de armazenamento de dados com possibilidade de armazenamento de ser um sistema híbrido com cloud [Ama17a]. Conta com as seguintes integrações: –AWS CloudTrail –Amazon Macie –Amazon Athena 53 Implementação Fazendo um exercício de comparação entre Google BigQuery e Microsoft Azure SQL DW, do ponto de vista de custos, Azure DW é cerca de 22 vezes mais caro do que BigQuery, apesar do custo em BigQuery ser apenas sobre o armazenamento. Para que estas duas tecnologias tivessem o mesmo custo mensal, teria de ser necessário fazer consultas sobre 2 111.49 PB de dados em BigQuery (por mês) - cerca de duas mil vezes o limite de armazenamento total numa base de dados em Azure DW 4.5. Comparando BigQuery com SQL Server, este último tem um custo de cerca de 43 vezes superior, sem considerar o custo das consultas em BigQuery. Considerando as consultas, para que BigQuery se aproxime de SQL Server, teriam de ser consultados ser de 4 292.50 GB de dados - cerca de 8 192 vezes superior à capacidade máxima de uma base de dados em SQL Server 4.5. 4.5 Arquitetura interna no Armazém de Dados Com o estudo da arquitetura interna do AD, foi possível concluir três elementos principais, sendo dois destes tabelas de factos e um é uma dimensão que tem também um comportamento de tabela de factos. Vamos então chamar a este último elemento "dimensão factual"(ver 2.2.6). • FactualVistas: representa uma página do site; granularidade: uma linha é uma página; • FactualAcoes: representa uma ação do utilizador; granularidade: uma linha é uma ação; • DimSessao: representa uma sessão do utilizador; granularidade: uma linha é uma sessão; Estes elementos relacionam-se com outros elementos como veremos de seguida, mas também se relacionam entre eles da forma representada na figura 4.3. A partir do esquema desta figura podemos deduzir as relações da figura 4.4. Figura 4.3: Relação entre DimSessao, FactualVistas e FactualAcoes Figura 4.4: Relações deduzidas entre DimSessao, FactualVistas e FactualAcoes Estas relações serão importantes para a secção 4.10 em que vamos perceber a aplicação da desnormalização neste estudo. 60 Implementação Dado que a documentação disponível sobre esta arquitetura foi muito limitada, foi necessário estudar cada elemento de forma a poder ser possível retirar conclusões. A arquitetura obtida deste estudo pode ser representada pelos esquemas das figuras 4.5,4.6 e4.7. Também é importante perceber qual a relação entre estes três esquemas como, por exemplo, as dimensões que partilham, na figura 4.8. Ao longo deste estudo, foi também possível obter informações interessantes sobre a composição e o que representam as dimensões que vimos nas figuras mencionadas acima. Figura 4.5: Esquema em estrela da DimSessao Figura 4.6: Esquema em estrela da FactualVistas 61 Implementação Figura 4.7: Esquema em estrela da FactualAcoes Figura 4.8: Combinação dos três esquemas em estrela da DimSessao, FactualVistas e FactualAcoes 4.6 Pesquisa de cenários de utilização para as tecnologias selecionadas De acordo com as tecnologias estudadas na secção 4.3, foram criados onze cenários de arquiteturas possíveis referentes apenas ao processo de consulta de dados, como podemos ver nas imagens seguintes: 62 Implementação Figura 4.9: Cenário 1 - camadas de armazenamento e processamento: Azure SQL Data Warehouse Figura 4.10: Cenário 2 - camadas de armazenamento e processamento: Google Big Query Figura 4.11: Cenário 3 - camadas de armazenamento e processamento: Microsoft SQL Server Figura 4.12: Cenário 4 - camadas de armazenamento e processamento: Amazon Redshift 63 Implementação Figura 4.13: Cenário 5 - camada de armazenamento: Azure Blob Storage; camada de processamento: Azure SQL Data Warehouse Figura 4.14: Cenário 6 - camada de armazenamento: Google Cloud Storage; camada de processamento: Google Big Query Figura 4.15: Cenário 7 - camada de armazenamento: Apache Hadoop; camada de processamento: Apache Hive & Cloudera Impala Figura 4.16: Cenário 8 - camada de armazenamento: Apache Hadoop; camada de processamento: Presto 64 Implementação Figura 4.17: Cenário 9 - camada de armazenamento: Apache Hadoop; camada de processamento: Azure SQL Data Warehouse Figura 4.18: Cenário 10 - camada de armazenamento: Google Big Query; camada de processamento: Apache Hadoop Figura 4.19: Cenário 11 - camada de armazenamento: Amazon S3; camada de processamento: Amazon Athena 65 Implementação 4.7 Seleção cenários de utilização Devido a questões relacionadas com a disponibilidade das tecnologias para construir um ambiente de testes e de complexidade da sua construção, em comparação com a possível utilidade das suas conclusões para a empresa, foram selecionadas os seguintes cenários dos apresentados acima: 1, 2, 3, 5 e 6. 4.8 Testes de admissão Na secção anterior foram selecionados cinco cenários de teste para as tecnologias que indicamos, o que é um elevado número para o tempo disponível. Assim, decidimos criar o que chamamos de "testes de admissão"que irão descartar as tecnologias que se destacam negativamente numa fase inicial precedente aos testes que selecionamos baseados em consultas que a empresa costuma realizar. Aplicamos os testes de admissão com o principal propósito de comparar o desempenho entre as versões normal e cloud das tecnologias que tinham estas duas vertentes: Google Big Query e Azure SQL Data Warehouse. Os testes devolveram as seguintes conclusões: Cenário 3 Cenário 1 Cenário 5 Cenário 2 Cenário 6 SQL Server Azure SQL DW Azure Blob BigQuery BigQuery Cloud linhas teste 1 00:15:19 00:00:32 00:19:01 00:00:09 00:01:15 1691 teste 2 00:07:04 00:00:49 00:19:33 00:00:07 00:00:54 8950 teste 3 00:29:43 00:04:18 00:26:22 00:00:13 00:01:25 17468 Tabela 4.6: Média dos resultados dos testes de admissão Os resultados da tabela 4.6 foram obtidos pela média de 10 repetições de cada teste. Os resultados individuais que deram origem a este estudo estão em anexo na secção C.2. Verificamos que, enquanto no cenário 2 (4.10) foram processados 495 MB, 3.71 GB e 16 GB, respetivamente a cada teste, no cenário 6 (4.14) foram processados 184 GB, 184 GB e 1.09 TB, respetivamente. Isto deve-se à forma como os dados são acedidos: no caso do Google Blob Storage, os dados são lidos a partir de um ficheiro do tipo CSV (ou semelhante), e por isso todos os dados são lidos para serem consultados; enquanto no caso do Google Big Query, os dados são acedidos por coluna pois os dados são lidos de tabelas column-stored (ver 2.3.1). Não só a quantidade de dados processada é afetada, como também o tempo de processamento, como consequência. 66 Implementação Com este estudo foi possível concluir que, se compararmos o desempenho relativo ao tempo de processamento, obtemos: • teste 1: –no cenário 5 demora cerca de 35.98 vezes mais do que no cenário 1 –no cenário 6 demora cerca de 8.30 vezes mais do que no cenário 2 • teste 2: –no cenário 5 demora cerca de 24.05 vezes mais do que no cenário 1 –no cenário 6 demora cerca de 7.94 vezes mais do que no cenário 2 • teste 3: –no cenário 5 demora cerca de 6.13 vezes mais do que no cenário 1 –no cenário 6 demora cerca de 6.37 vezes mais do que no cenário 2 Em média, a utilização do cenário 6 é 22.05 vezes mais lenta do que do cenário 2 e a utilização do cenário 5 é 7.54 vezes mais lenta do que do cenário 1. Desta forma, pude excluir os cenários 6 e 5 dos testes posteriores pois, por serem mais complexos do que estes, iria ser muito custoso e sem ganhos do ponto de vista de conclusões a retirar visto que estes dois cenários têm claramente um menor desempenho do que os outros dois. Ficámos então com os cenários 1, 2 e 3. Apesar desta análise nos permitir excluir os cenários que utilizam os sistemas de Google Cloud Storage e Azure Blob Storage, esta opção é viável em casos específicos como, por exemplo, em armazenamento de dados cujo acesso por consulta seja esporádico, i.e., provavelmente para dados mais antigos, como anos anteriores. Isto porque o armazenamento nestes cenários representa um menor custo monetário do que nos cenários 1 e 2 e a raridade das consultas permite compensar o seu custo em oposição ao custo de armazenamento. Estes sistemas são comummente denominados de armazenamento de longa duração. Podemos ainda verificar que o cenário 3 com SQL Server é o segundo mais lento de todos os analisados. Conclui-se que SQL Server é 9.22 vezes mais lento do que o cenário 1, 1.25 vezes mais rápido do que o cenário 5, 107.42 vezes mais lento do que o cenário 2 e 14.61 vezes mais lento do que o cenário 6. As instruções em SQL utilizadas para estes testes estão em anexo em C.1. 67 Implementação Figura 4.20: Gráfico da média de resultados dos testes da admissão sobre a velocidade processamento dos cenários 1, 2, 3, 5 e 6 4.9 Testes sobre consultas contextualizadas Os testes de admissão que vimos na secção anterior foram testes muito simples e com pouco tempo de processamento e, apesar de também os testes de admissão serem contextualizados dentro das necessidades da empresa, os testes que vamos ver a seguir são consultas agendadas periodicamente sobre o AD no sentido suportar algumas decisões de negócio. O primeiro teste, chamado VerticalUserSession (VUS) consiste em duas consultas, ambas com o objetivo de analisar indicadores relativos à sessão de um dado utilizador, aos dados de localização do utilizador e ainda à forma como este acede à plataforma (versão móvel através do browser -smartphone ou tablet -, versão de computador através do browser e versão da aplicação móvel); sendo a diferença entre elas o foco onde incidem os dados sobre os quais estão a consultar, em que a primeira é para o portal do site através do browser, em ambas as versões, e a segunda, mais direcionada para acessos através da aplicação móvel. O segundo teste chama-se ListingPage_Dashboard (LP_D) e serve de apoio a um dashboard fornecendo dados de interações dos utilizadores com as páginas de listagem de produtos. SQL Server Azure SQL DW Google BigQuery linhas teste VUS 00:14:43 00:11:31 00:01:33 676 951 teste LP_D 00:37:54 00:22:05 00:02:13 3 131 905 Tabela 4.7: Média dos resultados dos testes contextualizados Na tabela 4.7, podemos ver que, no teste VUS, BigQuery é 9.5 vezes mais rápido do que SQL Server e é 7.4 vezes mais rápido do que Azure DW; no teste LP_D, BigQuery é 17 vezes mais rápido do que SQL Server e é 9.9 vezes mais rápido do que Azure DW. Portanto, em ambos os casos, o BigQuery demonstrou um considerável menor tempo de processamento. 68 Implementação Figura 4.21: Gráfico da velocidade de processamento do teste VUS O teste VUS processa cerca de 1.11 GB de dados e o teste LP_D, 120 GB. Assim, como o BigQuery é a única tecnologia destas que estamos a analisar que apresenta um custo por consulta, podemos afirmar que a consulta VUS custa cerca de $0.02 e a consulta LP_D custa cerca de $0.60, em BigQuery. Nestes testes já podemos verificar que não existe uma relação exata de proporcionalidade direta entre quantidade de dados processados e tempo de processamento, embora alguma relação mais ténue entre estes possa, ainda assim, existir. Figura 4.22: Gráfico da velocidade de processamento do teste LP_D 4.10 Testes aos modelos normalizado e desnormalizado Na secção 2.2.7.2 já vimos que BigQuery suporta um tipo de desnormalização que dão o nome de nested and repeated fields. Aplicamos esta desnormalização ligando as tabelas FactualVistas eFactualAcoes (i.e., as tabelas relativas às páginas do portal online e às ações dos utilizadores, respetivamente) à tabela DimSessao (i.e., à tabela das sessões dos utilizadores) e ainda associando a tabela FactualAcoes àFactualVistas. Iremos interpretar estes dois cenários como duas tabelas desnormalizadas diferentes: Denorm_ds_fv_fa eDenorm_fv_fa, respetivamente. 69 Implementação Figura 4.27: Fluxos de dados de Microsoft SQL Server para Google BigQuery 4.11.2 Reflexão sobre a usabilidade depois da migração Com a decisão pelo cenário 2 e 6, sendo o primeiro destinado para armazenamento ativo (cenário primário) e o segundo para armazenamento de longa duração, e o Looker como ferramenta de BI, podemos analisar ativamente as funcionalidades que estas ferramentas oferecem. Vimos que o sistema de custos do BigQuery inclui um custo associado às consultas, mais especificamente à quantidade de dados processados nas consultas. Para os utilizadores poderem ter o controlo destes custos, têm acesso à informação do custo da consulta no Web UI do BigQuery antes de esta ser executada para que tenham a oportunidade de reestruturar a sua consulta. Com o Looker, os dados de custos de consulta são igualmente fornecidos aos utilizadores assim como espaço para estes possam definir limites de acordo com esta informação. O Looker comunica com o BigQuery na sua linguagem nativa. Desta forma, não apresenta limitações na interação com as tabelas desnormalizadas (nested and repeated tables), garantindo a capacidade de as consultar. Assim como na escrita de funções definidas pelo utilizador. Looker pratica uma tentativa grande de proximidade do conceito de self-service BI, dando a possibilidade de escrever em SQL as consultas sobre os dados em BigQuery, tanto em formato normalizado como desnormalizado. BigQuery torna a tarefa de carregamento de dados para tabelas particionadas e a sua criação numa tarefa fácil. Mas temos de ter atenção às limitações de 2 500 partições por tabela, 2 000 atualizações de partição por tabela, por dia e 50 atualizações de partição a cada 10 segundos. Com estas limitações, se fizéssemos, por exemplo, as partições por dia a toda uma tabela que contém dados desde há 3 anos, teríamos 1 095 partições nessa tabela e só nos restavam 2 anos e 175 dias. Há, no entanto, uma solução que passa por dividir uma tabela em várias tabelas por anos e, desta forma, teremos, no máximo, 365 partições em cada uma destas tabelas. A linguagem LookML (pertencente ao Looker) tem o propósito de lidar com dimensões, agregações, cálculos e relações numa 76 Implementação base de dados em SQL [LDS18a]. Desta forma, o Looker tenta tirar o máximo partido de certas funcionalidades particulares, tais como as tabelas particionadas. Com a opção de consultar o plano de execução das consultas (ver C.6.7), o BigQuery dá-nos a conhecer as razões para possíveis demoras ou necessidades de otimização das consultas. Podemos ver em que partes do processamento o BigQuery despendeu mais tempo, se na escrita ou leitura dos dados, tarefas de computação ou em espera. O sistema de guardar os resultados das consultas em cache, vai mais além do convencional ao armazenar também resultados intermédios. Não só podemos poupar custos e tempo em consultas que já executamos antes, como também em consultas relacionadas. As tabelas derivadas do Looker permitem ter um grande controlo sobre caching e transformação automática. O BigQuery também permite ingestão de dados através da API ou do Google Cloud Dataflow. Existem, no entanto, algumas limitações que podem resultar em obstáculos tais como tipos de dados com algumas incompatibilidades, tais como não suportar fusos-horários, e o SQL utilizado é pouco convencional. Outras limitações foram já abordadas neste documento (ver 4.5). 4.12 Migração Agora que já temos os pontos, que Vladimir Stoyak apontou como importantes abordar antes da migração, bem definidos, podemos avançar para os detalhes da migração. 4.12.1 Extrair dados de Microsoft SQL Server Inicialmente, devido à familiarização da empresa com os produtos Microsoft, a ideia seria utilizar PolyBase para extrair os dados para Hadoop e, posteriormente, o fluxo de dados seguia para Google Cloud Storage e, então, para BigQuery (ver 4.28). No entanto, PolyBase mostrou-se limitado e pouco maduro quando foi apercebido o limite de 32 KB por linha na extração de dados o que, na verdade, é bastante redutor quando no situamos num cenário de volume de dados massivo. Figura 4.28: Fluxo detalhado de dados de Microsoft SQL Server para Google BigQuery, através de PolyBase Passamos portanto para outra opção para extrair dados, Apache Sqoop (ver B.8). Utilizamos o Sqoop para extrair os dados de SQL Server para Hadoop através de ficheiros Parquet para então os dados serem novamente encaminhados para Google Cloud Storage através de ficheiros Avro. 77 Implementação Os dados que não estiverem destinados a armazenamento de longa duração, são enviados para BigQuery (ver figura 4.29). O objetivo é, assim que a migração esteja concluída, o fluxo fazer-se apenas a partir de Hadoop como podemos ver na figura 4.30. A forma mais usual de extrair os Figura 4.29: Fluxo de dados durante a migração Figura 4.30: Fluxo de dados depois da migração dados do Microsoft SQL Server é utilizando consultas através de instruções SELECT com filtros, ordenações e limitações dos dados. Esta foi, na realidade o método adotado mas houve problemas com os tipos de dados, conforme antecipado. BigQuery suporta apenas os tipos Array, Boolean, Byte, Date, Datetime, Float, Integer, Record, String e Timestamp. Foi necessário, portanto, realizar algumas conversões,como podemos ver na tabela C.7, e ter especial atenção às questões de aproximações de números, para os casos de float ereal vs.decimal, e ainda à falta de fusos horários em que, no timestamp esta informação é retida sendo interpretada como fuso-horário UTC e no datetime a informação é perdida. Foi preciso ter atenção também a palavras reservadas, caracteres reservados e ainda dados sensíveis, isto é, cujo conteúdo seja de grande importância para as condições de privacidade da empresa. Os caracteres reservados são os seguintes: (espaço),%,#,.,?. O Apache Airflow [Air18] é utilizado nesta arquitetura como orquestrador das tarefas inerentes a este workflow presente nas figuras 4.29 e4.30. 4.12.2 Carregar dados para Google BigQuery Segundo um guia publicado do Google Cloud Storage ([Goo18a]), existem três métodos comuns para carregar dados para BigQuery: • através de Google Cloud Storage • através de fontes de dados legíveis • inserindo registos individuais através de streaming inserts Ao carregarmos os dados, podemos fornecer o esquema da tabela ou partição ou, até mesmo, utilizar a funcionalidade de deteção automática do esquema do BigQuery. 78 Implementação 4.12.3 Manter os dados atualizados O método ideal, e o adotado, seria criar um script que reconheça registos novos e atualizados na base de dados fonte. Utilizando um campo como uma chave auto-incremental que funcionará como marcador para o script saber onde retomar a atualização. 4.12.4 Formatos de dados suportados Devemos optar por um formato de ingestão dados. Os suportados são os seguintes: • Google Cloud Storage –CSV –JSON (apenas delimitado por linha) –Avro –Google Cloud Datastore backups • fontes de dados legíveis –CSV –Avro –JSON (apenas delimitado por linha) Como pudemos ver nas figuras 4.29 e4.30, optámos pelo Avro. Deve-se realçar que os dados em formato Avro tendem a ser mais rápidos de carregar porque podem ser lidos em paralelo, mesmo quando os blocos de dados estão comprimidos. Suporta dados achatados e no formato desnormalizado de nested and repeated fields. Se os dados tiverem novas linhas embutidas, Avro permite um carregamento mais rápido. E é o formato preferido para carregamento de dados comprimidos, que são os mais problemáticos visto que os descomprimidos podem ser lidos em paralelo. Existem ainda limitações externas a ter um consideração, como por exemplo se a fonte de dados só permite a extração dos mesmos num formato específico, e a codificação de dados suportada pelo BigQuery. 4.12.5 Técnica aplicada para a migração Apesar de não termos achado necessidade de aplicar alterações significativas à estrutura das tabelas que migramos, foram necessárias alterações aos tipos de dados e melhorias derivadas da modernização trazida pelo BigQuery como, por exemplo, a desnormalização através dos nested 79 Implementação and repeated fields. Assim, não podemos dizer que aplicamos a técnica de realojamento ou liftand-shift, mas antes a técnica lift-tinker-and-shift, isto é, revisão (ver 2.2.8). Na aplicação desta técnica, foi necessário aplicar mais esforços no desenvolvimento mas, em contrapartida, trouxemos maior eficiência e organização para o novo ambiente. Estes melhoramentos e modificações não teriam sido possíveis se a migração tivesse ocorrido através de qualquer uma das tecnologias existentes, como veremos de seguida, que tornam esta migração mais rápida e autónoma mas pouco flexível. 4.12.6 Tecnologias de migração Existem tecnologias que permitem uma migração quase automática, com grande rapidez e sem custos. Aplicam a técnica de lift-and-shift, o que traz limitações quanto ao controlo que temos nas melhorias e alterações que queremos aplicar ao migrar o nosso sistema. São ferramentas que realizam as tarefas de ETL do SQL Server para o Google BigQuery e mantém-no atualizado. As tecnologias mais conhecidas são: • Stitch • Alooma • Skyvia Apesar de tudo, havia duas razões principais para esta migração: liberdade na capacidade de armazenamento e maior rapidez na resposta de consultas. Este último ponto torna-se comprometido nesta tarefa de migração se nenhuma técnica de otimização for aplicada. Por estas razões não utilizamos estas soluções. 4.13 Validar resultados com trabalhos relacionados 4.13.1 Tecnologias Foram encontrados alguns estudos que tentam comparar algumas tecnologias que temos abordado neste documento, embora poucos tenham uma metodologia experimental. •Azure SQL DW vs BigQuery vs Redshift vs SQL-on-Hadoop Neste estudo incluído num white paper da empresa 8K Miles ([8K ]) foram analisados: Amazon Redshift, Google BigQuery, Microsoft Azure SQL DW, Impala, Hive, Presto e Hive. Foram executados três ambientes diferentes referentes à quantidade de dados a processar: 100GB, 1TB e 10TB. Foi concluído que: 80 Implementação –Hive mostrou-se como a tecnologia mais lenta, seguido por Spark; –Hive e Spark não foram consideradas viáveis para um grande volume de dados (1 TB e 10TB), o primeiro por demorar demasiado tempo a processar os dados e o segundo por devolver demasiados erros; –os testes para 10TB só foram bem sucedidos para BigQuery e Redshift; o resto das tecnologias - Azure e SQL-on-Hadoop - , ou devolveram demasiados erros, ou demoraram demasiado tempo; –BigQuery e Redshift estiveram sempre muito próximos, sendo que, nos testes de 10TB, o BigQuery teve um desempenho melhor na maioria das consultas. •BigQuery vs SQL-on-Hadoop Numa análise de benchmark realizada no blog "AtScale"([Kla17]), foi utilizada uma metodologia experimental no sentido de comparar Google BigQuery com algumas tecnologias SQL-on-Hadoop, tais como Hive, Impala, Presto e Spark. Foram idealizados quatro tipos de consulta para diferentes graus de complexidade compostos por um número diferente de junções e operações group by. As conclusões levam-nos a crer que, à medida que a complexidade e a quantidade de dados processados das consultas aumentava, o BigQuery melhorava os seus resultados em relação às outras tecnologias. BigQuery foi considerada a tecnologia mais favorável também porque é muito fácil de carregar os dados e apresenta resultados quanto à concorrência de utilizadores muito bons pois, enquanto o tempo de resposta às consultas em todas as SQL-on-Hadoop aumenta à medida que o número de utilizadores concorrentes aumenta, em BigQuery mantém-se constante, mesmo depois dos 25 utilizadores. •BigQuery vs Redshift O estudo apresentado no blog "Panoply"([Avi16])é já antigo e apresenta algumas desatualizações quanto às funcionalidades e capacidades atuais de ambas as tecnologias. Como conclusões retiramos: –Redshift apresentou melhores resultados do ponto de vista de desempenho, usabilidade e custos na maioria dos casos, especialmente numa perspetiva de escalamento. –O único ponto negativo significativo de Redshift foi a sua complexidade de configuração, necessitando de uma sintonização de baixo nível de hardware e bases de dados virtualizados. Esta característica, apesar de ser um benefício na customização e flexibilidade do produto resultando num ganho no desempenho e custos, torna-se negativo na sua usabilidade e necessidade de alocar esforços por parte dos trabalhadores para esta configuração e pontuais manutenções. 81 Implementação –É de realçar que, apesar de este estudo ter tentado tirar o máximo proveito das funcionalidades do Redshift para aumentar o seu desempenho, houve algumas funcionalidades do BigQuery que poderiam ter sido utilizadas para melhorar o seu desempenho tais como a desnormalização (nested an repeated fiels) e partições de tabelas. •BigQuery vs PostgreSQL vs Redshift Um projeto da Universidade de Washington ([JL13]) produziu um estudo de benchmark entre as tecnologias: Amazon Redshift, Google BigQuery e PostgreSQL. Este estudo foi realizado numa fase muito recente do BigQuery e do Redshift, já que o primeiro foi lançado em maio de 2010 [Wik18b], o segundo foi em fevereiro de 2013 [Wik18a] e o estudo está datado a junho de 2013, isto é, de há cinco anos atrás. Foram realizados até dois testes de TPC-H ([TPC18]) para a sua comparação. Redshift é representado neste estudo por três versões: um nó a utilizar a cache e um nó e oito nós sem utilizar a cache. Os resultados mostram que –Para consultas massivamente paralelizáveis, BigQuery apresenta-se quase constante à medida que a quantidade de dados a processar aumenta, enquanto PostgreSQL e Redshift (em todas as versões) apresentam um aumento no tempo de processamento das consultas; isto deve-se à utilização de servidores proporcional ao tamanho de dados, pelo BigQuery; PostgreSQL apresenta os piores resultados; –Para consultas com maior complexidade relativa à quantidade de junções e de consultas aninhadas (nested) PostgreSQL não se mostrou viável, BigQuery apresentou um crescimento maior com o aumento da quantidade de dados sendo, até mesmo, a tecnologia com pior desempenho a seguir ao PostgreSQL e Redshift revelou melhores resultados para um nó utilizando a cache seguido de 8 nós sem utilizar a cache; –O estudo justifica este último resultado como devendo-se ao facto de BigQuery não ter sido desenhado com o propósito de lidar com uma grande complexidade nas consultas. Para esta tecnologia, é esperado que os dados estejam aninhados. –Para BigQuery, foi concluído que: *é fácil de configurar - não necessita de nenhuma configuração manual *é fácil de fazer as consultas *escala automaticamente de acordo com o tamanho dos datasets *a falta de configuração manual provoca falta de controlo dos recursos contratados consoante as necessidades *tem suporte limitado da linguagem SQL 82 Implementação *não escala bem em consultas complexas envolvendo junções múltiplas e subconsultas –Para Redshift, o estudo concluiu: *é quase completamente compatível com SQL - não houve necessidade de adaptar o SQL de PostgreSQL para Redshift *necessita de configurações avançadas e administração dos clusters em que as consultas incidem •Azure SQL DW vs Netezza vs Redshift vs SQL Server Num blog de Mark White Business Intelligence on Power BI, Azure and SQL Server, encontramos um estudo de fevereiro de 2017 que realiza um benchmark entre Microsoft Azure SQL DW, Netezza (ver A.7), Amazon Redshift e Microsoft SQL Server. Os resultados obtidos foram: –SQL Server teve um desempenho consideravelmente pior do que o resto –Redshift teve o melhor quadro tanto no desempenho-preço, como nas suas características e facilidade de usar –Azure SQL DW apresentou alguns problemas na configuração e nos testes; quando executado abaixo de DWU600, algumas consultas terminam com erros ou demoram mais de 4 horas a ser processadas, o que sugere que Azure DW não suporta bem as condições baixas que oferece; revelou inconsistências no tempo de processamentos das consultas, talvez derivado ao congestionamento ou contenção de recursos; o PolyBase do Azure DW também devolveu muitos erros no carregamento de dados –os testes ao Netezza correram naturalmente com resultados previsíveis e sem necessidade de configurações de grande complexidade; o carregamento de dados ocorreu sem problemas e dentro de um tempo razoável –globalmente SQL Server revelou ser um produto sem grandes capacidades para acompanhar os concorrentes na área de Armazéns de Dados –o Redshift distancia-se dos outros pela positiva porque: *mantém-se online enquanto escala para cima e para baixo *o carregamento de dados é bastante simples *analisa os dados enquanto estão a ser carregados e determina qual o algoritmo de compressão a aplicar *permite que as tabelas de pesquisa estejam atualizadas na sua totalidade e em cada nó de computação provocando um aumento de desempenho de junções relacionais 83 Implementação •Azure SQL DW vs BigQuery vs Redshift Um artigo publicado no sitepoint.com [dA16], tem como objetivo comparar Amazon Redshift, Google BigQuery e Microsoft Azure SQL SW, e realçou os seguintes resultados: –o Redshift mostra-se o líder na variedade de funcionalidades e maturidade, mas por uma distância pequena do BigQuery –para as empresas com grande investimento em produtos Microsoft, o Azure SQL DW continua a ser um forte concorrente, apesar de não apresentar um desempenho e funcionalidades tão boas ou melhores •Tendências dos Armazéns de Dados Quanto à popularidade das tecnologias, foi publicado um estudo na página web do InfoWorld from IDG [Maa17] que analisa as pesquisas relacionadas com Armazéns de Dados e algumas tecnologias. Permitiu-nos tirar as seguintes conclusões: –o termo global "data warehouse"e as suas variantes é o mais popular desta temática, contando com 475 000 pesquisas por mês, apesar de se ter verificado um decréscimo desde 2014 –"cloud data warehouse"apresenta-se como muito menos popular, principalmente entre 2004 e 2008, quando a sua procura foi praticamente nula; mas, a partir de 2008, começa a crescer; em 2016, contou com 300 pesquisas mensais –"Redshift"tem 38 000 pesquisas por mês –"BigQuery"tem 26 700 pesquisas por mês –"Azure SQL DW"tem 13 000 pesquisas por mês 4.13.2 Procedimentos Quanto a trabalhos relacionados nas temáticas abordadas nos procedimentos, foi possível encontrar os seguintes: •Desnormalização por hierarquias Encontramos um artigo da Conferência Internacional de Ciências da Computação Aplicadas ([MZ08]) que trata de técnicas de desnormalização, tendo como principal objetivo analisar a técnica por hierarquias que também abordámos neste documento. Foi aplicado o método experimental e analisados os seus resultados: –o tempo de resposta apresentou um ligeiro aumento do modelo normalizado para o desnormalizado unidimensional 84 Implementação –o tempo de resposta teve um grande redução: 5190 vezes mais rápido na primeira consulta, 11 vezes na segunda e 9.5 vezes na terceira, do modelo normalizado para o desnormalizado multidimensional –esta desnormalização pode melhorar o desempenho das consultas reduzindo o seu tempo de resposta quando a estrutura dos dados envolve muitas junções Então, a desnormalização por hierarquias justifica-se para modelos multidimensionais e para tabelas cujas consultas são maioritariamente compostas por várias junções •Migração para BigQuery Este white paper tem como objetivo enumerar boas práticas e métodos de migração e fornece um guia de migração de Amazon Redhsift para Google BigQuery. 4.14 Conclusões Neste capítulo, conseguimos comprometer-nos com uma solução para o problema apresentado neste documento: Google BigQuery irá ocupar o lugar de ferramenta de armazenamento e processamento de dados (juntamente com Google Cloud Storage) e será complementado com a ferramenta Looker na camada de visualização de resultados e apoio ao Business Intelligence. Os motivos desta decisão são justificados pela rapidez de processamento de consultas que verificamos nos nossos testes, em que BigQuery se revelou significativamente superior, pelos custos da tecnologia que, embora este sistema comtemple custos por consulta, dificilmente se tornará tão ou mais dispendioso quanto às alternativas que analisámos e, finalmente, pela usabilidade e funcionalidade que BigQuery oferece. Quanto ao Looker, a decisão não é tão claramente justificada pois não houve oportunidade de analisar as ferramentas de visualização através de um método experimental mas, pelas funcionalidades que esta tecnologia oferece, o elemento decisivo foi a proximidade que o Looker tenta alcançar do BigQuery tirando um maior proveito da API fornecida e mostrando, assim, uma maior compatibilidade com este. Conseguimos propor e analisar um processo de migração para a tecnologia sugerida. Este processo, apesar de não ter sido elaborado neste documento, ofereceu a oportunidade de rever a nossa solução, validá-la e tomar precauções quanto a alguns obstáculos espectáveis. No capítulo 3listamos alguns objetivos a atingir com a migração e questões a responder com este estudo. Quanto aos objetivos que a empresa procurava atingir com a migração do seu sistema, podemos resumir as conclusões obtidas na tabela 4.10 e, relativamente às questões a responder apresentadas em 3.3.2, tentamos obter respostas ao longo deste estudo sintetizámo-las na tabela 4.11. 85 Conclusões e Trabalho Futuro 92 Referências [1ke17] 1keydata.com. Data Warehousing Concepts — Junk Dimension. Disponível em http://www.1keydata.com/datawarehousing/junk-dimension. html, Junho 2017. [8K ] 8K Miles. Big Data Solution Benchmark. [Aba08] Daniel J. Abadi. Query execution in column-oriented database systems. PhD thesis, Massachusetts Institute of Technology, Cambridge, MA, USA, 2008. ndltd.org (oai:dspace.mit.edu:1721.1/43043). [ABH+13] Daniel Abadi, Peter Boncz, Stavros Harizopoulos, Stratos Idreos e Samuel Madden. The design and implementation of modern column-oriented database systems. Foundations and Trends ® in Databases, 5(3):197–280, 2013. URL: http://dx.doi.org/10.1561/1900000024, doi:10.1561/1900000024. [Air18] Airflow. Apache Airflow (incubating) Documentation. Disponível em https: //airflow.apache.org/, Janeiro 2018. [Ama17a] Amazon. Amazon S3 Product Details. Disponível em https://aws.amazon. com/s3/details/, Dezembro 2017. [Ama17b] Amazon. Amazon S3 Product Details. Disponível em https://aws.amazon. com/s3/details/, Dezembro 2017. [AMH08] Daniel J. Abadi, Samuel R. Madden e Nabil Hachem. Column-stores vs. row-stores: How different are they really? In Proceedings of the 2008 ACM SIGMOD International Conference on Management of Data, SIGMOD ’08, pages 967–980, New York, NY, USA, 2008. ACM. URL: http://doi.acm.org/10.1145/ 1376616.1376712, doi:10.1145/1376616.1376712. [Avi16] Roi Avinoam. Redshift vs. BigQuery: The Full Comparison. Disponível em https://blog.panoply.io/ a-full-comparison-of-redshift-and-bigquery, Julho 2016. [Azu18] Microsoft Azure. SQL Data Warehouse pricing. Disponível em https://azure.microsoft.com/en-us/pricing/details/ sql-data-warehouse/elasticity/, Janeiro 2018. [BID17] BIDW.ORG. What is Surrogate Key. Disponível em http://www.bidw.org/ datawarehousing/what-is-surrogate-key, Novembro 2017. 93 REFERÊNCIAS [Cal12] Carlos Pampulim Caldeira. Data Warehousing: Conceitos e modelos. Edições Sílabo, Lda., Lisboa, 2 edition, 2012. [Cen17] IBM Knowledge Center. Surrogate Keys. Disponível em https://www. ibm.com/support/knowledgecenter/en/SS9UM9_7.6.0/com.ibm. datatools.dimensional.ui.doc/topics/c_dm_surrogatekeys. html, Novembro 2017. [Clo17] Cloudera. Product Details. Disponível em https://aws.amazon.com/ athena/details/, Dezembro 2017. [Con15] Caserta Concepts. Big Data Warehousing — Dimensional Modelling Still Matters. Disponível em https://pt.slideshare.net/CasertaConcepts/ big-data-warehousing-meetup-dimensional-modeling-still-matters, agosto 2015. [dA16] Lucero del Alba. A side-by-side comparison of aws, google cloud and azure. Disponível em https://www.sitepoint.com/ a-side-by-side-comparison-of-aws-google-cloud-and-azure/, Setembro 2016. [Dat17] Datawarehouse4u.info. Data Warehouse info — OLTP vs. OLAP. Disponível em http://datawarehouse4u.info/, Junho 2017. [DG08] Jeffrey Dean e Sanjay Ghemawat. Mapreduce: Simplified data processing on large clusters. Commun. ACM, 51(1):107–113, January 2008. URL: http://doi.acm. org/10.1145/1327452.1327492, doi:10.1145/1327452.1327492. [dwa17] dwarehouse.wordpress.com. Data Warehouse — Introduction to Massively Parallel Processing (MPP) database. Disponível em https://dwarehouse.wordpress.com/2012/12/28/ introduction-to-massively-parallel-processing-mpp-database/, Junho 2017. [eMARK03] D. L. Moody e M. A. R. Kortink. From ER models to dimensional models: Bridging the gap between OLTP and OLAP design. Business Intelligence Journal, 8(3):7–24, 2003. [evo17] evoeftimov. Data Modeling for Big Data Solutions. Disponível em https://evoeftimov.wordpress.com/2015/01/10/ data-modeling-for-big-data-solutions/, junho 2017. [Fly17] FlyData. Introduction to Massively Parallel Processing. Disponível em https://www.flydata.com/blog/ introduction-to-massively-parallel-processing/, Junho 2017. [Fou18] The Apache Software Foundation. Apache Sqoop. Disponível em http:// sqoop.apache.org/, Janeiro 2018. [G2C17] G2Crowd. Best Data Warehouse Software. Disponível em https://www. g2crowd.com/categories/data-warehouse, junho 2017. 94 REFERÊNCIAS [Gar98] Stephen R. Gardner. Building the data warehouse. Communications of the ACM, 41(9):52–60, setembro 1998. [Gar11] Gartner, Inc. Gartner Identifies Five Ways to Migrate Applications to the Cloud, Stamford, Connecticut, U.S.A., Maio 2011. [Ger18] Gerardnico. Dimensional Modeling — Hierarchy. Disponível em https:// gerardnico.com/wiki/olap/dimensional_modeling/hierarchy, Janeiro 2018. [Goo17] Google. Google Cloud Platform: BigQuery. Disponível em https://cloud. google.com/bigquery/, junho 2017. [Goo18a] Google. Introduction to Loading Data into BigQuery. Disponível em https: //cloud.google.com/bigquery/docs/loading-data, Janeiro 2018. [Goo18b] Google. Pricing. Disponível em https://cloud.google.com/bigquery/ pricing, Janeiro 2018. [Goo18c] Google. Quotas & Limits. Disponível em https://cloud.google.com/ bigquery/quotas, Janeiro 2018. [IBM17a] IBM. IBM PureData System for Analytics. Disponível em https://www. ibm.com/ky-en/marketplace/puredata-system-for-analytics, junho 2017. [IBM17b] IBM. IBM QMF. Disponível em https://www.ibm.com/us-en/ marketplace/db2-qmf, Dezembro 2017. [IBM17c] IBM. MapReduce — What is MapReduce? Disponível em https: //www.ibm.com/analytics/us/en/technology/hadoop/mapreduce/ #mapreduce-products, junho 2017. [Inm96] W. H. Inmon. The data warehouse and data mining. Communications of the ACM, 39(11):49–50, novembro 1996. [Inm11] William H. Inmon. Building the data warehouse. John Wiley & Sons, Inc., Indianapolis, 2011. [JED17] Udaya Ranawake John E. Dorband, Josephine Palencia. Commodity computing clusters at goddard space flight center. Online Journal of Space Communication, outubro 2017. [JL13] Adriana Szekeres Jialin Li, Naveen Kr. Sharma. Redshift vs. BigQuery: The Full Comparison. Disponível em https://courses.cs.washington.edu/ courses/cse544/13sp/final-projects/p18-lijl.pdf, Junho 2013. [Kim98] Ralph Kimball. Surrogate Keys. Disponível em http://www.kimballgroup. com/1998/05/surrogate-keys, May 1998. [Kla17] Joshua Klahr. TECH TALK: BI Performance Benchmarks with Google BigQuery. Disponível em http://blog.atscale.com/ bi-benchmarks-with-google-bigquery, Abril 2017. 95 REFERÊNCIAS [KR02] Ralph Kimball e Margy Ross. The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling. John Wiley & Sons, Inc., New York, NY, USA, 2nd edition, 2002. [KR13] Ralph Kimball e Margy Ross. The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling. Wiley Publishing, 3rd edition, 2013. [Kud18] Apache Kudu. Apache kudu overview. Disponível em https://kudu.apache. org/overview.html, Janeiro 2018. [LDS18a] Inc. Looker Data Sciences. Looker - What is LookML? Disponível em https://docs.looker.com/data-modeling/learning-lookml/ what-is-lookml, Janeiro 2018. [LDS18b] Inc. Looker Data Sciences. Looker Analytics on Google BigQuery - A Match Made for the Cloud. Disponível em https://looker.com/solutions/ google-bigquery, Janeiro 2018. [Loo17] Looker. Rethinking Data Analytics: Why Looker? Disponível em https:// looker.com/product/business-intelligence, junho 2017. [Maa17] Gilad David Maayan. Cloud data warehouse: The technology no one knows about. Disponível em https://www. infoworld.com/article/3220480/data-warehousing/ cloud-data-warehouse-the-technology-no-one-knows-about. html, Agosto 2017. [Med18] Stephen Orban Medium. 6 Strategies for Migrating Applications to the Cloud. Disponível em https://medium.com/aws-enterprise-collection/ 6-strategies-for-migrating-applications-to-the-cloud-eb4e85c412b4, Janeiro 2018. [Meu18] Meusalario.pt. Administradores e especialistas de concepção de bases de dados. Disponível em https://www.gartner.com/newsroom/id/1684114, Janeiro 2018. [Mic17a] Microsoft. Microsoft Azure: Data Warehouse SQL. Disponível em https: //azure.microsoft.com/pt-pt/services/sql-data-warehouse/, junho 2017. [Mic17b] Microsoft. SQl Server Overview. Disponível em https://www.microsoft. com/pt-pt/sql-server/sql-server-2016, Dezembro 2017. [Mic18a] Microsoft. Distributing tables in SQL Data Warehouse. Disponível em https://docs.microsoft.com/en-us/azure/sql-data-warehouse/ sql-data-warehouse-tables-distribute, Janeiro 2018. [Mic18b] Microsoft. Maximum Capacity Specifications for SQL Server. Disponível em https://docs.microsoft.com/en-us/sql/sql-server/ maximum-capacity-specifications-for-sql-server, Janeiro 2018. [Mic18c] Microsoft. Pricing calculator. Disponível em https://azure.microsoft. com/en-us/pricing/calculator/, Janeiro 2018. 96 REFERÊNCIAS [Mic18d] Microsoft. SQL Data Warehouse capacity limits. Disponível em https://docs.microsoft.com/en-us/azure/sql-data-warehouse/ sql-data-warehouse-service-capacity-limits, Janeiro 2018. [Moe00] R. A. Moeller. Distributed Data Warehousing Using Web Technology: How to Build a More Cost-effective and Flexible Warehouse. Amacom, 2000. [Mor16] Morpheus. Application and Database Migration Strategies: Don’t Just Migrate – Transform Your Apps and Databases to Portable, Cloud-Ready Status. Disponível em https://www.morpheusdata.com/blog/ 2016-01-06-application-and-database-migration-strategies-don-t-just-migrate-transform-your-apps-and-databases-to-portable-cloud-ready-status, Janeiro 2016. [Mul97] Craig Mullins. Denormalization Guidelines. Disponível em http://tdan.com/ denormalization-guidelines/4142, Junho 1997. [Mun11] Joy Mundy. Kimball Group — Design Tip 140 Is it a Dimension, a Fact, or Both? Disponível em https://www.kimballgroup.com/2011/ 11/design-tip-140-is-it-a-dimension-a-fact-or-both/, Novembro 2011. [MZ08] Su-Cheng Haw Morteza Zaker, Somnuk Phon-Amnuaisuk. Optimizing the data warehouse design by hierarchical denormalizing. Online Journal of Space Communication, 2008. [OLA17] OLAP OLAP.com. Business Intelligence — What is Business Intelligence (BI). Disponível em http://olap.com/learn-bi-olap/olap-bi-definitions/ business-intelligence/, Junho 2017. [Pay14] Amir H. Payberah. Mapreduce and beyond. Disponível em https://www. sics.se/~amir/files/download/dic/mapreduce.pdf, Swedish Institute of Computer Science, abril 2014. [Pre17] Presto. Amazon S3 Product Details. Disponível em https://aws.amazon. com/s3/details/, Dezembro 2017. [Pro17] IT Pro. An Overview of SQL Server High Availability Options. Disponível em http://www.itprotoday.com/ overview-sql-server-high-availability-options, Dezembro 2017. [Qli17] Qlik. SAP Analytics Cloud: SAP Analytics Cloud for BI. Disponível em http: //www.qlik.com/us/products/why-qlik-is-different, junho 2017. [Sal17] Juan Salazar. THE 20 MOST POPULAR BUSINESS INTELLIGENCE TOOLS. Disponível em http://dataconomy.com/2017/02/top-20-bi-tools/, junho 2017. [SAP17a] SAP. SAP Analytics Cloud: SAP Analytics Cloud for BI. Disponível em https://www.sap.com/products/cloud-analytics/features/ business-intelligence.html, junho 2017. 97 REFERÊNCIAS [SAP17b] SAP. SAP Business Warehouse — Run a real-time data warehouse – on-premise or in the cloud – with SAP BW. Disponível em https://www.sap.com/ products/business-warehouse.product-capabilities.html, junho 2017. [SR09] Maribel Yasmina Santos e Isabel Ramos. Business Intelligence: Tecnologias da Informação na Gestão de Conhecimento. FCA, 2 edition, fevereiro 2009. [Sto16] Vladimir Stoyak. Five Things to Know Before Migrating Your Data Warehouse to Google BigQuery. Disponível em https://blog.pythian.com/ five-things-to-know-before-migrating-your-data-warehouse-to-google-bigquery/, Novembro 2016. [Tab17] Tableau. A Tableau simplifica a sua análise de negócios. Disponível em https://www.tableau.com/pt-br/trial/tableau-software# 1XpZGpIh6BZg5TtQ.99, junho 2017. [Tea09] Editorial Team+. Denormalized Data Store. Disponível em http://www. learn.geekinterview.com/data-warehouse/data-management/ denormalized-data-store.html, abril 2009. [TPC18] TPC. TPC-Current Specifications. Disponível em http://www.tpc.org/ tpc_documents_current_versions/current_specifications.asp, Janeiro 2018. [Tut17] Tutorialspoint. MapReduce — Advantages of Hadoop MapReduce Programming. Disponível em http://www.tutorialspoint.com/articles/ advantages-of-hadoop-mapreduce-programming, junho 2017. [Ver16] Sandeep Verma. Apache Hive Or Cloudera Impala? What is Best for me? Disponível em https://www.linkedin.com/pulse/ apache-hive-cloudera-impala-what-best-forme-sandeep-verma/, Fevereiro 2016. [Wik17a] Wikipedia. Apache Hadoop. Disponível em https://en.wikipedia.org/ wiki/Apache_Hadoop, Dezembro 2017. [Wik17b] Wikipedia. Direct-access storage device. Disponível em https:// en.wikipedia.org/wiki/Direct-access_storage_device, Dezembro 2017. [Wik17c] Wikipedia. Fat client. Disponível em https://en.wikipedia.org/wiki/ Fat_client, junho 2017. [Wik17d] Wikipedia. Slowly changing dimension. Disponível em https://en. wikipedia.org/wiki/Slowly_changing_dimension, junho 2017. [Wik18a] Wikipedia. Amazon Redshift. Disponível em https://en.wikipedia.org/ wiki/Amazon_Redshift, Janeiro 2018. [Wik18b] Wikipedia. BigQuery. Disponível em https://en.wikipedia.org/wiki/ BigQuery, Janeiro 2018. 98 REFERÊNCIAS [ws17] Amazon web services. Amazon Redshift — Data warehousing rápido, simples e econômico. Disponível em https://aws.amazon.com/pt/redshift/ ?nc1=f_ls, junho 2017. 99 REFERÊNCIAS 100 Anexo A Armazenamento de Dados - outros conceitos A.1 Data mart Um Data Mart é um subconjunto natural (área, tema, processo) e completo (composto por dados atómicos) do AD global [KR02]. No passado os data marts eram concebidos como uma forma de apresentar dados unicamente agregados, isto é, sobre os quais era aplicada um aritmética qualquer, mas isso produzia aplicações muito rígidas que conseguiam responder apenas a um conjunto muito limitado de questões. Atualmente, são vistos como estruturas muito flexíveis, que de preferência incorporam os dados mais atómicos que se conseguem extrair de um sistemas operacional, e que são apresentados ao utilizador na forma de um esquema em estrela. [Cal12] Não podem ser isolados pois pode levar a perpetuar vistas incompatíveis da empresa e seria muito pior do que perder uma oportunidade de análise profunda da organização [KR02]. A.2 Chaves Artificiais Uma chave artificial (em inglês, surrogate key) é um campo especial que serve de chave primária em cada uma das dimensões. Uma chave artificial é um atributo que identifica inequívocamente cada registo de uma dimensão e é, usualmente, um valor inteiro positivo. A surrogate key é usada como chave externa na tabela de factos para identificar em cada facto qual é o registo da dimensão que lhe corresponde, estabelecendo desse modo uma restrição de integridade referencial entre os dois tipos de componentes do modelo dimensional. A utilização de chaves artificiais protege o AD contra as alterações, normais ou imprevistas, que 101 Anexos da secção de Procedimentos 27 FactualVistas.PortalFF C.1.2 Teste 2 1SELECT 2FactualVistas.seNovaVisita, 3FactualVistas.SubGrupo, 4FactualVistas.GeoSubGrupo, 5FactualVistas.PortalFF, 6COUNT(DISTINCT FactualVistas.SK_Sessao) Sessoes, 7SUM(FactualVistas.Duracao) Duracao, 8SUM(FactualVistas.AcoesUnicas) AcoesUnicas, 9MAX(FactualVistas.TipoCheckout) TipoCheckout 10 FROM FactualVistas 11 LEFT JOIN FactualVistas AS FactualVistas_prox ON 12 FactualVistas_prox.SK_Sessao = FactualVistas.SK_Sessao 13 AND FactualVistas_prox.SK_DataVisualPag = FactualVistas.SK_DataVisualPag 14 AND FactualVistas_prox.ProfPage = FactualVistas.ProfPage + 1 15 AND (FactualVistas_prox.SK_DataVisualPag between 20170717 and 20170724) 16 AND (FactualVistas.PagURL LIKE ’%shopping/women/sale/items.aspx%’ 17 OR FactualVistas.PagURL LIKE ’%shopping/men/sale/items.aspx%’ 18 OR FactualVistas.PagURL LIKE ’%shopping/kids/sale/items.aspx%’ 19 OR FactualVistas.PagURL LIKE ’%shopping/women/items.aspx%’ 20 OR FactualVistas.PagURL LIKE ’%shopping/men/items.aspx%’ 21 OR FactualVistas.PagURL LIKE ’%shopping/kids/items.aspx%’) 22 WHERE (FactualVistas.SK_DataVisualPag between 20170717 and 20170724) 23 GROUP BY 24 FactualVistas.seNovaVisita, 25 FactualVistas.SubGrupo, 26 FactualVistas.GeoSubGrupo, 27 FactualVistas.PortalFF C.1.3 Teste 3 1SELECT 2FactualVistas.seNovaVisita, 3FactualVistas.SubGrupo, 4FactualVistas.GeoSubGrupo, 5FactualVistas.PortalFF, 6COUNT(DISTINCT FactualVistas.SK_Sessao) Sessoes, 7SUM(FactualVistas.Duracao) Duracao, 8SUM(FactualVistas.AcoesUnicas) AcoesUnicas, 108 Anexos da secção de Procedimentos 9MAX(FactualVistas.TipoCheckout) TipoCheckout 10 FROM FactualVistas 11 LEFT JOIN FactualVistas AS FactualVistas_prox ON 12 FactualVistas_prox.SK_Sessao = FactualVistas.SK_Sessao 13 AND FactualVistas_prox.SK_DataVisualPag = FactualVistas.SK_DataVisualPag 14 AND FactualVistas_prox.ProfPage = FactualVistas.ProfPage + 1 15 AND (FactualVistas_prox.SK_DataVisualPag between 20170701 and 20170731) 16 AND (FactualVistas.PagURL LIKE ’%shopping/women/sale/items.aspx%’ 17 OR FactualVistas.PagURL LIKE ’%shopping/men/sale/items.aspx%’ 18 OR FactualVistas.PagURL LIKE ’%shopping/kids/sale/items.aspx%’ 19 OR FactualVistas.PagURL LIKE ’%shopping/women/items.aspx%’ 20 OR FactualVistas.PagURL LIKE ’%shopping/men/items.aspx%’ 21 OR FactualVistas.PagURL LIKE ’%shopping/kids/items.aspx%’) 22 WHERE (FactualVistas.SK_DataVisualPag between 20170701 and 20170731) 23 GROUP BY 24 FactualVistas.seNovaVisita, 25 FactualVistas.SubGrupo, 26 FactualVistas.GeoSubGrupo, 27 FactualVistas.PortalFF C.2 Resultados dos testes de admissão # SQL Server Azure SQL DW Azure Blob BigQuery BigQuery Cloud 1 00:10:47 00:00:03 00:04:07 00:00:12 00:01:24 2 00:00:57 00:00:03 00:03:58 00:00:16 00:01:40 3 00:05:25 00:00:23 00:04:04 00:00:10 00:01:19 4 00:02:45 00:00:30 00:24:38 00:00:01 00:01:05 5 00:10:52 00:00:38 00:25:19 00:00:19 00:01:19 6 00:03:17 00:00:43 00:25:07 00:00:01 00:01:07 7 00:08:54 00:00:37 00:25:18 00:00:09 00:01:06 8 00:07:34 00:00:51 00:26:20 00:00:01 00:01:09 9 01:34:42 00:00:47 00:25:48 00:00:11 00:01:11 10 00:03:17 00:00:47 00:25:27 00:00:10 00:01:07 Tabela C.1: Resultados do teste de admissão 1 109 Anexos da secção de Procedimentos # SQL Server Azure SQL DW Azure Blob BigQuery BigQuery Cloud 1 00:04:49 00:00:06 00:04:31 00:00:06 00:01:04 2 00:06:34 00:00:06 00:04:53 00:00:13 00:01:07 3 00:06:34 00:00:06 00:04:15 00:00:09 00:00:49 4 00:06:29 00:01:03 00:25:47 00:00:01 00:00:52 5 00:06:31 00:01:06 00:25:32 00:00:10 00:01:00 6 00:06:30 00:01:12 00:25:39 00:00:02 00:00:50 7 00:06:30 00:00:53 00:26:07 00:00:09 00:00:47 8 00:06:31 00:01:09 00:27:01 00:00:01 00:00:49 9 00:15:44 00:01:11 00:26:17 00:00:08 00:00:48 10 00:04:24 00:01:16 00:25:32 00:00:09 00:00:54 Tabela C.2: Resultados do teste de admissão 2 # SQL Server Azure SQL DW Azure Blob BigQuery BigQuery Cloud 1 00:33:05 00:18:00 00:33:07 00:00:22 00:01:43 2 00:47:23 00:00:42 00:06:51 00:00:20 00:01:55 3 00:32:48 00:00:18 00:06:40 00:00:12 00:01:22 4 00:33:18 00:03:09 00:30:19 00:00:01 00:01:17 5 00:31:51 00:03:22 00:31:00 00:00:41 00:01:33 6 00:17:14 00:03:47 00:30:43 00:00:04 00:01:25 7 00:36:14 00:03:12 00:30:49 00:00:11 00:01:01 8 00:31:14 00:03:27 00:32:27 00:00:02 00:01:27 9 00:16:18 00:03:38 00:31:09 00:00:10 00:01:13 10 00:17:49 00:03:25 00:30:32 00:00:11 00:01:18 Tabela C.3: Resultados do teste de admissão 3 110 Anexos da secção de Procedimentos C.3 Resultados dos testes contextualizados C.3.1 VerticalUserSession # SQL Server Azure SQL DW BigQuery 1 00:03:10 00:04:52 00:01:29 2 00:06:06 00:24:23 00:01:38 3 00:04:58 00:24:23 00:01:38 4 00:06:25 00:06:02 00:01:46 5 00:07:28 00:07:44 00:01:31 6 00:07:42 00:07:22 00:01:34 7 00:18:08 00:10:08 00:01:34 8 00:38:07 00:06:11 00:01:31 9 00:22:35 00:06:49 00:01:26 10 00:32:34 00:07:27 00:01:32 Tabela C.4: Resultados do teste VerticalUserSession C.3.2 ListingPage_Dashboard # SQL Server Azure SQL DW BigQuery 1 00:24:08 00:15:53 00:02:15 2 00:26:18 00:20:14 00:02:14 3 00:47:18 00:24:41 00:02:00 4 00:35:48 00:22:16 00:02:00 5 00:26:40 00:24:18 00:02:19 6 00:38:34 00:24:41 00:02:30 7 00:29:28 00:22:52 00:02:29 8 00:27:21 00:21:34 00:02:05 9 01:32:56 00:22:49 00:02:00 10 00:30:26 00:21:35 00:02:22 Tabela C.5: Resultados do teste ListingPage_Dashboard 111 Anexos da secção de Procedimentos C.4 Resultados dos testes dos modelos normalizado e desnormalizado ds_fv ds_fa ds_fv_fa fv_fa # N D N D N D N D 1 00:00:12 00:00:07 00:00:13 00:00:05 00:00:26 00:00:05 00:01:30 00:00:06 2 00:00:12 00:00:05 00:00:08 00:00:05 00:00:16 00:00:06 00:01:03 00:00:05 3 00:00:10 00:00:07 00:00:08 00:00:07 00:00:36 00:00:06 00:01:26 00:00:07 4 00:00:11 00:00:06 00:00:09 00:00:05 00:00:23 00:00:08 00:01:31 00:00:07 5 00:00:12 00:00:09 00:00:11 00:00:07 00:00:17 00:00:06 00:01:22 00:00:07 6 00:00:13 00:00:05 00:00:11 00:00:06 00:00:18 00:00:08 00:01:24 00:00:06 7 00:00:11 00:00:07 00:00:11 00:00:07 00:00:20 00:00:08 00:01:21 00:00:06 8 00:00:13 00:00:07 00:00:10 00:00:06 00:00:17 00:00:07 00:01:19 00:00:09 9 00:00:10 00:00:04 00:00:14 00:00:05 00:00:17 00:00:07 00:01:26 00:00:06 10 00:00:10 00:00:07 00:00:09 00:00:05 00:00:17 00:00:07 00:01:16 00:00:06 Tabela C.6: Resultados dos testes às tabelas normalizadas e desnormalizadas C.5 Consultas em SQL dos testes contextualizados Estes testes foram cedidos pela equipa de análise de dados e são exemplos reais e atuais. C.5.1 VerticalUserSession 1SELECT 2<colunas da DimSessao relativas as acoes na sessao>, 3<colunas da DimAgente relativas ao metodo de acesso do utilizador>, 4<colunas da DimFonte relativas a forma como o utilizador entrou na plataforma da empresa>, 5<colunas da DimGeografia relativas ao sitio de onde o utilizador acedeu a plataforma> 6FROM DimSessao 7INNER JOIN DimAgente ON DimSessao.SK_Agente = DimAgente.SK_Agente 8INNER JOIN DimUtilizador ON DimUtilizador.SK_Utilizador = DimSessao.SK_Utilizador 9INNER JOIN DimFonte AS DimFonte ON DimSessao.SK_FonteInicio = DimFonte.SK_Fonte 10 INNER JOIN DimGeografia ON LastSubfolder.SK_Geografia = DimSessao.SK_PaisFim 11 WHERE 12 DimSessao.SK_DataInicio BETWEEN @start_int AND @end_int 13 AND DimAgente.Agente NOT LIKE ’%catchpoint%’ 14 AND DimAgente.Agente NOT LIKE ’%Automation%’ 15 AND DimAgente.Agente NOT LIKE ’%Rigor%’ 112 Anexos da secção de Procedimentos 16 AND DimAgente.Agente NOT LIKE ’%iplabel%’ 17 GROUP BY 18 <colunas dadas> 19 20 21 SELECT 22 <colunas da DimSessao relativas as acoes na sessao>, 23 <colunas da DimUtilizador relativas as informacoes do utilizador>, 24 FROM DimSessaoPorAplicacaoMovel 25 LEFT JOIN DimUtilizador ON DimUtilizador.email=DimSessaoPorAplicacaoMovel.idEmail AND DimUtilizador.ExcluiData IS NOT NULL 26 LEFT JOIN DimPais ON DimPais.CodigoPais=DimSessaoPorAplicacaoMovel.PaisFim 27 WHERE DimSessaoPorAplicacaoMovel.DataParticao BETWEEN @start_int AND @end_int 28 GROUP BY 29 <colunas dadas> C.5.2 ListingPage_Dashboard 1SELECT 2<colunas da DimSessao relativas as acoes na sessao>, 3<colunas da DimAgente relativas ao metodo de acesso do utilizador>, 4<colunas da DimPagina relativas as paginas existentes na plataforma>, 5<colunas da DimAcoes relativas as acoes do utilizador>, 6<colunas da FactualVistas relativas as vistas de paginas visitadas pelo utilizador>, 7<colunas da DimCategoriaProduto relativas as categorias de produtos nas paginas de listagem>, 8<colunas da DimGeografia relativas as caracteristicas geograficas do utilizador>, 9<colunas da DimSessoesDeEncomendas relativas as sessoes em que o utilizador tenha realizado encomendas>, 10 <colunas da FactualEncomendas relativas as encomendas realizadas pelo utilizador >, 11 <colunas da DimFonte relativas a forma como o utilizador entrou na plataforma da empresa> 12 FROM FactualVistas AS fv_prox 13 INNER JOIN dimpagina dp_listagem ON dp_listagem.sk_pagina = fv_prox. sk_ultimaPagListagemVistada 14 INNER JOIN dimpagina dp_prod ON dp_prod.sk_pagina=fv_prox.sk_pagina 15 LEFT JOIN 16 (FactualAcoes fa JOIN dimpagina AS dp_acao ON 17 dp_acao.sk_pagina=fa.sk_pagina AND trackerid IN (36,38,42)) 18 ON fa.sk_Sessao = fv_prox.sk_Sessao AND fa.sk_DataAcao=fv_prox.SK_DataVisualPag 19 and dp_acao.PagTipo=dp_listagem.PagTipo AND dp_acao.PagSubTipo=dp_listagem. PagSubTipo 20 LEFT JOIN DimCategoriaProduto dcp ON dcp.categoriaid=dp_listagem.categoriaid AND dcp.seCategoriaPrincipal=’Yes’ AND.seCategoriaPrincipalAnalytics=’Yes’ 113 Anexos da secção de Procedimentos 21 INNER JOIN DimSessao ds ON ds.SK_Sessao = fv_prox.SK_Sessao AND fv_prox. SK_DataVisualPag = ds.SK_DataInicio 22 INNER JOIN DimGeografia AS dg ON dg.SK_Geografia = ds.SK_PaisFim 23 INNER JOIN DimAgente da ON dus.SK_Agente= da.SK_Agente 24 LEFT JOIN DimSessoesDeEncomendas AS sde ON sde.sk_Sessao = ds.sk_Sessao AND ds. sk_DataInicio = sde.SK_DataVisualPag 25 LEFT JOIN FactualEncomendas AS fe ON fe.orderportalid=sde.orderportalid AND fe. SK_OrderDate_TZGMT=sde.SK_DataVisualPag AND fe.sk_produto=fv_prox.sk_produto 26 INNER JOIN DimFonte df ON df.sk_fonte=ds.sk_fonteFim 27 WHERE 28 fv_prox.SK_DataVisualPag BETWEEN @startdate AND @enddate 29 AND fv_prox.ListagemPosicao>0 AND dp_listagem.PagTipo=’Listagem Pag’ AND dp_listagem.PagSubTipo<>’Produto Pag’ 30 AND RegiaoClienteFF <> ’N/D’ AND dp_listagem.PagSubTipo<>’Others’ AND dp_prod. PagTipo=’Produto Pag’ 31 AND FonteTipo<> ’N/D’ 32 AND da.Agente NOT LIKE ’%catchpoint%’ 33 AND da.Agente NOT LIKE ’%Automation%’ 34 AND da.Agente NOT LIKE ’%Rigor%’ 35 AND ds.seAplicacaoMovel=’No’ 36 AND (NOT EXISTS 37 (SELECT 1AS Expr1 38 FROM DimIP 39 WHERE (IPAddress = ds.IPUtilizadorInicio))) 40 AND (NOT EXISTS 41 (SELECT 1AS Expr1 42 FROM DimIPMalicioso 43 WHERE (IPAddress = ds.IPUtilizadorInicio))) 44 GROUP BY 45 <colunas dadas> C.6 Consultas para os testes das tabelas normalizadas e desnormalizadas C.6.1 Instrução para a extração dos dados para Denorm_ds_fv_fa 1SELECT 2<colunas da DimSessao relevantes para a relacao> 3,ARRAY_AGG(STRUCT( 4<colunas da FactualVistas relavantes para a relacao> 5)) AS Vistas, 6ARRAY_AGG(STRUCT( 7<colunas de FacualAcoes relevantes para a relacao> 8)) AS Acoes 114 Anexos da secção de Procedimentos 9 10 FROM DimSessao AS Sessao 11 12 LEFT JOIN ( 13 SELECT <colunas da FactualVistas relevantes para a relacao> 14 FROM FactualVistas 15 WHERE DataParticao = timestamp(’_DATEPARTITION_’) 16 )FVON ( 17 FV.SK_Sessao = Sessao.SK_Sessao AND 18 FV.DataParticao = Sessao.DataParticao 19 ) 20 21 LEFT JOIN FactualAcoes FA ON ( 22 FV.SK_Sessao = FA.SK_Sessao AND 23 FV.SK_Pagina = FA.SK_Pagina AND 24 FV.DataParticao = FA.DataParticao AND 25 FA.DataAcao BETWEEN FV.DataVisualPag AND FV.DataVisualPag_prox AND 26 FA.DataParticao = timestamp(’_DATEPARTITION_’) 27 ) 28 29 WHERE Sessao.DataParticao = timestamp(’_DATEPARTITION_’) 30 31 GROUP BY 32 <colunas da DimSessao dadas> C.6.2 Instrução para a extração dos dados para Denorm_ds_fv_fa 1SELECT 2TEMP.*, 3ARRAY_AGG(STRUCT( 4<colunas da FacualAcoes relevantes para a relacao> 5)) AS Acoes 6FROM ( 7SELECT 8<colunas da FactualVistas relevantes para a relacao>, 9IFNULL(LEAD(FV.DataVisualPag) OVER(PARTITION BY FV.SK_Sessao, FV. DataParticao ORDER BY FV.DataVisualPag), CURRENT_TIMESTAMP) DataVisualPag_prox, 10 LEAD(FV.PagURL) OVER(PARTITION BY FV.SK_Sessao, FV.DataParticao ORDER BY FV .ProfPage) PagURL_prox, 11 12 <colunas da PC relevantes para a relacao>, 13 <colunas da PI relevantes para a relacao>, 14 <colunas da PF relevantes para a relacao> 15 FROM FactualVistas AS FV 16 LEFT JOIN DimPagina AS PC ON (FV.SK_Pagina = PC.SK_Pagina) 115 Anexos da secção de Procedimentos 17 LEFT JOIN DimPagina AS PI ON (FV.SK_proxPagina = PN.SK_Pagina) 18 LEFT JOIN DimPagina AS PF ON (FV.SK_PagListagemUltimaVisitada = PL.SK_Pagina) 19 20 WHERE FV.DataParticao = timestamp(’_DATEPARTITION_’) 21 22 ) TEMP 23 24 LEFT JOIN FactualAcoes AS FA ON ( 25 TEMP.SK_Sessao = FA.SK_Sessao AND 26 TEMP.SK_Pagina = FA.SK_Pagina AND 27 TEMP.DataParticao = FA.DataParticao AND 28 FA.DataAcao BETWEEN TEMP.DataVisualPag AND TEMP.DataVisualPag_prox AND 29 FA.DataParticao = timestamp(’_DATEPARTITION_’) 30 ) 31 32 GROUP BY 33 <colunas de TEMP dadas> C.6.3 Consulta à relação ds_fv C.6.3.1 Modelo normalizado 1SELECT 2fv._PARTITIONTIME as data, 3COUNT(DISTINCT fv.SK_Pagina) AS vistas 4FROM FactualVistas_2016 AS fv 5LEFT JOIN dimSessao_2016 ds ON 6fv.SK_Sessao = ds.SK_Sessao AND 7fv._PARTITIONTIME = ds._PARTITIONTIME 8WHERE 9ds._PARTITIONTIME BETWEEN timestamp(’2016-01-01’) AND timestamp(’2016-09-30’) AND 10 fv._PARTITIONTIME BETWEEN timestamp(’2016-01-01’) AND timestamp(’2016-09-30’) AND 11 ds.VisitaComAdicaoAoCarrinho =’Yes’ 12 GROUP BY data C.6.3.2 Modelo desnormalizado 1SELECT 2fv.DataParticao as data, 3COUNT(DISTINCT fv.SK_Pagina) AS vistas 4FROM 5Denorm_ds_fv_fa AS ds, 116 Anexos da secção de Procedimentos 6UNNEST(ds.Vista) AS fv 7WHERE 8ds._PARTITIONTIME BETWEEN timestamp(’2016-01-01’) AND timestamp(’2016-09-30’) AND 9fv.DataParticao BETWEEN timestamp(’2016-01-01’) AND timestamp(’2016-09-30’) AND 10 ds.VisitaComAdicaoAoCarrinho =’Yes’ 11 GROUP BY data C.6.4 Consulta à relação ds_fa C.6.4.1 Modelo normalizado 1SELECT 2fa.SK_DataInicio AS data, 3COUNT(DISTINCT ds.sk_Sessao) AS visitas 4FROM FactualAcoes_2016 AS fa 5LEFT JOIN dimSessao_2016 ds ON 6fa.SK_Sessao = ds.SK_Sessao AND 7fa._PARTITIONTIME = ds._PARTITIONTIME 8WHERE 9fa._PARTITIONTIME BETWEEN timestamp(’2016-01-01’) and timestamp(’2016-09-30’) and 10 ds._PARTITIONTIME BETWEEN timestamp(’2016-01-01’) and timestamp(’2016-09-30’) and 11 fa.SK_DataInicio BETWEEN 20160101 AND 20160930 AND 12 fa.trackerid IN (343,376) AND 13 fa.trackervalor LIKE ’2__’ AND 14 dus.VisitaComAdicaoAoCarrinho=’Yes’ 15 GROUP BY data C.6.4.2 Modelo desnormalizado 1SELECT 2fa.DataParticao as data, 3COUNT(DISTINCT ds.SK_Sessao) AS visitas 4FROM 5Denorm_ds_fv_fa AS ds, 6UNNEST(ds.Acoes) AS fa 7WHERE 8ds._PARTITIONTIME BETWEEN timestamp(’2016-01-01’) and timestamp(’2016-09-30’) and 9fa.trackerId IN (343,376) AND 10 fa.trackerValor Like ’2__’ AND 11 dus.VisitaComAdicaoAoCarrinho=’Yes’ 12 GROUP BY data 117