An Overview of Data Warehousing and OLAP Technology

1. An Overview of Data Warehousing and OLAP Technology Tópicos Avançados de Bases de Dados André Valente Rodrigues  110370165 Carlos Filipe Ribeiro Ferreira  060316048

2. Índice • Data Warehousing • Data Warehouse • BDOsvsDWs: • BDOs • DWs • OLAP • Cubos OLAP • Exemplo de queriesad-hoc em excel • Modelação dimensional • Esquema em estrela • Esquemas "Floco de Neve" e Constelações de Fatos • Arquitetura Data Warehousing • Exemplo Prático • Utilidades e Ferramentas BackEnd • Servidores de uma DW • OLAP em BDOs • Desafios • Tipos de Servidores • Metadata e Gestão de uma Warehouse

3. DATA WAREHOUSING • Data warehousing: coleção de tecnologias de suporte à decisão, com o objetivo de permitir ao trabalhador (executivo, gestor, analista, etc.) tomar melhores e mais rápidas decisões. • O mercado de data warehousing explodiu tanto em número de produtos e serviços oferecidos como na adoção destas tecnologias pela indústria. • As tecnologias foram adotadas em imensas indústrias: - fabrico; - venda a retalho; - serviços financeiros; - transportes; - telecomunicações; - saúde.

4. DATA WAREHOUSE • Data warehouse: é uma coleção de dados não volátil, que varia no tempo, integrada e orientada para o utilizador que é usada primeiramente na tomada de decisão organizacional. • Tipicamente mantida separadamente das bases de dados operacionais das organizações. • Uma Data Warehouse(DW) suporta on-line analyticalprocessing (OLAP). • Uma Base de Dados Operacional (BDO) suporta on-line transactionprocessing (OLTP). • Ambos têm requisitos de funcionalidade e de performance diferentes.

5. BDOsvsDWs - BDOs • Aplicações OLTP tipicamente automatizam tarefas como uma nova entrada na base de dados ou uma transação bancária que são tarefas diárias de uma organização. • Estas transações requerem dados atualizados e detalhados. • As bases de dados operacionais tendem a ter entre centenas de megabytes e gigabytes de tamanho. • São desenhadas para refletir as semânticas operacionais de aplicações conhecidas, em particular, para minimizar os conflitos de concorrência.

6. BDOsvsDWs - DWs • Desenhadas para suporte à decisão. • Dados históricos, sumarizados e consolidados são mais importantes que entradas individuais detalhadas. • Como tendem a conter dados consolidados de uma ou mais bases de dados tendem a ser ordens de magnitude maiores que estas. Warehouses de dados empresariais são projetadas para ser de centenas de gigabytes a terabytes em tamanho. • As queries são intensivas por isso o throughput das queries e os tempos de resposta são mais importantes do que o throughputdas transações.

7. OLAP • Para facilitar a visualização, os dados são tipicamente modelados multidimensionalmente. • Muitas vezes estas dimensões/atributos são hierárquicas: - tempo da venda pode ser organizado como uma hierarquia de dia-mês-trimestre-ano; - um produto pode ser organizado como uma hierarquia de produto-categoria-indústria. • É esta a tecnologia que oferece a interface para os utilizadores responsáveis pelas tomadas de decisão. • Operações típicas OLAP incluem: - rollup; - drill-down; - slide_and_dice; - pivot.

8. Cubos OLAP • Apresentação ao utilizador da informação numa visão cúbica, que se trata do output dos servidores OLAP, em que as dimensões e métricas da DW são directamente mapeadas para este. Figura 1 – Data Cube.

9. Exemplo de queriesad-hocem excel Figura 2 – Exemplo de queriesad-hoc em excel.

10. Modelação dimensional • Tabela de fatos • Medidas numéricas de performance; • Granularidade da Tabela de Factos: • Consiste no nível de detalhe da DW em relação às transacções operacionais tendo por medida unidades de tempo. • Evitar os “Zeros” (“o que não aconteceu”); • Muitas Linhas, Poucas Colunas; • 90% do espaço; • 2 ou mais Chaves Estrangeiras (FK); • Chave Primária (composta). Figura 3 – Tabela de fatos de vendas diárias.

11. Modelação dimensional • Tabelas de Dimensões • Descrição Textual do Negócio; • Contêm Atributos; • Poucas Linhas(relativamente); Muitas Colunas; • Chave Primária (PK) – Integridade Referencial com factos; • Respondem aos “por”: • Vendas em €“por mês”, “por” Cliente e “por” “Artigo”. • Hierarquia de Dimensões (sem normalização): • Ex: Produtos->Marcas->Categorias. Figura 4 – Tabela de Dimensões de Produtos.

12. Esquema em estrela • Diagramas ER e técnicas de normalização são populares em ambientes OLTP. • Diagramas ER são inapropriados para suporte à decisão onde a eficiências das queries e no carregamento dos dados são importantes. • Data Warehousesusam o esquema em estrela para representar modelos de dados multidimensionais. Figura 5 – Esquema em estrela.

13. Esquemas "Floco de Neve" e Constelações de Fatos • A hierarquia dimensional é explicitamente representada normalizando as tabelas de dimensões. Figura 6 – Esquema “Floco de Neve”.

14. Esquemas "Floco de Neve" e Constelações de Fatos • Desvantagem: A estrutura desnormalizada das tabelas de dimensões em esquemas estrela pode ser mais apropriado para navegar nas dimensões. • Constelações de fatos: Estruturas mais complexas em que múltiplas tabelas de fatos partilham tabelas dimensionais.

15. Arquitetura Data Warehousing • OperationalSourceSystems(OSS): • Transacções do Negócio; • Performance e Disponibilidade; • Heterogéneos e não integrados. • Data StagingArea: • Storage + processos ETL ; • Extracção de dados a partir dos OSS; • Transformação (“Limpeza “ de dados); • Regra geral, BDs “não Normalizadas”; • Carregamento (Loading) dos vários Data Marts. • Data PresentationArea: • Conjunto de Data Marts; • Estruturas (Modelos) Dimensionais: • Baixa complexidade; • Dados Atómicos. • Factos e Dimensões Partilhados (“Conformed” ); • Cubos OLAP. • Data Access Tools: • Ad-hocqueries– acesso apenas a especialistas. Figura 7 – Arquitetura Data Warehousing. • ETL – ExtractTransformLoad: • Consiste no processo de transformação e passagem (load) dos dados para a DW; • O ETL package, deve ser capaz de analisar o que há de novo na BD operacional, de modo a no momento do varrimento (refresh) actualizar a DW com sucesso, carregar a DW inteira não é aplicável isto só se deve fazer na primeira vez, estes updates à DW são sempre feitos nas horas em que o sistema está menos sobrecarregado.

16. Exemplo Prático • Esta DW pequena, teve origem na BD OLTP (Online TransactionProcessing) proveniente da demonstração do ERP da Primavera. Figura 8 – Exemplo prático de uma pequena DW.

17. Exemplo Prático • Neste caso o que se esta a medir na tabela de factos são os dados relativos às vendas. As vendas são então o somatório das quantidades vendidas e totais ilíquidos agrupados por artigo, entidade data e vendedor, esta agregação é fácil de entender a olhar pela ilustração a baixo. Neste caso TSQL. Figura 9 – Screenshotdos valores retirados das BD e da DW.

18. Detalhes de uma DW • Uma DW tem que ter sempre a dimensão tempo, pois é deste que ela depende para organizar a sua granularidade. • A DW não pode nunca conter valores nulos, regra geral o que se usa é a substituição de nulos. • Slowlychangindimensions, consiste em updates na base da dados operacional. ex: “Produto = garrafa”, “Material = plástico” passar a ser Material = vidro: • Os tratamentos mais comuns são: • De tipo 1 (não é mantido histórico); • De tipo 2 (todo o histórico é mantido); • De tipo 3 (é apenas mantido um histórico recente). • Mapeamento SurrogateKey,Natural Key: • Consiste num sistema de pk-fk interno à DW, para ligar os factos às dimensões. • Os dados da DW nunca são removidos.

19. Limpeza de dados • Ferramentas de migração dos dados. •  Ferramentas de data scrubbing. • Ferramentas de audição dos dados.

20. Utilidades e Ferramentas BackEnd • Load • BatchLoad: • processamento adicional para carregar os dados para uma data warehouse (toda a computação necessária para criar as tabelas derivadas que são guardadas na data warehouse); • monitorizar o estado, suspender, resumir e reiniciar um load (carga) para a base de dados. • Refresh: • Quando fazer refresh e como fazê-lo; • A política de refresh é definida pelo administrador dependendo nas necessidades e tráfego dos utilizadores e pode ser diferente para diferentes fontes.

21. OLAP em Bases de Dados Operacionais • Dado que as BDOs estão desenhadas para suportar os workloads de OLTP, executar queries OLAP complexas resultaria em performance inaceitável. • Suporte à decisão requere dados que podem não estar numa BDO: - Dados históricos; - Dados consolidados de várias fontes heterogéneas. • BDOs não providencionam os modelos multidimensionais de dados e a organização dos dados especial e os métodos de implementação e de acesso que OLAP requere.

22. Servidores de uma Warehouse • Desafios: • Escolher que índices gerar e que vistas materializar. • Usar efetivamente os índices e as vistas para responder aqueries. • Otimização das queries complexas. • Melhorar a eficiência de scans. • Paralelismo tem que ser explorado para reduzir os tempos de resposta a queries.

23. Estruturas de índices e a sua utilização • As operações de reunião e de interseção de índices podem ser usadas para reduzir significativamente o acesso às bases de dados. • Os servidores de Warehouses conseguem utilizar índices bit map que suportam operações sobre índices eficientes. • A natureza dos esquemas em estrela torna a utilização de índices join especialmente atrativa para suporte à decisão. • Índices para suportar pesquisa de texto também são muito úteis.

24. Vistas e a sua utilização • Materializar resumos de dados pode ajudar a acelerar muitas queries comuns(por ex: num ambiente de investimento a grande maioria das queries pode ser baseada na performance do último trimestre ou no ano fiscal actual). • Uma estratégia simples mas muito útil para usar uma view é a utilização de selection ou rollup(ex: query que pede o total de venda das calças de determinada marca do ano. aplica-se a seleção para a marca de calças e de seguida faz-se rollup do trimestre para o ano).

25. Transformação de Queries SQL complexas e Processamento Paralelo • Tradução de queries SQL com nestedsubqueriesem queries SQL de um único bloco. • Redução de número de invocações de innersubqueriesutilizando técnicas semi-join. • Os maiores vendedores de DBMSs oferecem particionamento de dados e tecnologia de processamento de queries paralela.

26. Arquiteturas de Servidores para processamento de Queries • Servidores SQL especializados: Providencionam uma linguagem de queries e de suporte ao processamento de queries SQL avançada sobre esquemas em estrema e floco de neve em ambientes read-only. • Servidores ROLAP: Servidores intermediários que se localizam entre um servidor backendrelacional (onde os dados da warehouse estão guardados) e as ferramentas frontend to cliente. • Servidores MOLAP: Suportam a visão multidimensional de dados através de um motor de armazenamento multidimensional.

27. Servidores ROLAP • Data Warehouses são implementadas em servidores relacionais OLAP (ROLAP). • Estes servidores assumem que: - os dados estão guardados em BDs relacionais: • - suportam extensões para MySQL; • - possuem métodos especiais de acesso e de implementação que implementem eficientemente o modelo de dados e as operações multidimensionais.

28. Servidores ROLAP • Eles estendem os servidores relacionais tradicionais com middleware especializado para eficientemente suportar queries OLAP multidimensionais. • Identificam as vistas que devem ser materializadas. • Refraseiam as queries dos utilizadores para as vistas materializadas. • Geram multi-statement SQL (é uma técnica que permite executar várias declarações SQL como sendo apenas uma) para o servidor backendrelacional. • Providencionam serviços adicionais tais como scheduling de queries e atribuição de recursos. • Desvantagens: Diferenças intrínsecas entre o estilo de querying de OLAP e de SQL pode causar bottleneck de performance para os servidores OLAP.

29. Servidores MOLAP • Suportam diretamente a visão multidimensional dos dados através de um motor multidimensional de armazenamento. Vantagem: Propriedades de indexação excelentes. Desvantagem:Providenciam utilização do armazenamento fraca especialmente quando o conjunto de dados é esparso. • Alguns adotam uma representação de armazenamento de 2º nível para se adaptarem a conjuntos de dados esparsos e usarem compressão extensivamente: - Um conjunto de dados que seja identificado como sendo possivelmente esparso é representando numarray; - A estrutura de indexação tradicional é usada para indexar para estes arrays.

30. Extensões SQL • Família Estendida de Funções Agregadas: Suporte para rank e percentile. • Relatórios de Características: Avaliação de agregações de dados numa janela temporal com pontos de interrupção e totais correntes. • Group-By Múltiplo: Suportam Rollup e Cube. • Comparações: SQL possui deficiências na execução de comparações que são comuns no mundo dos negócios.

31. Metadata e Gestão de uma Warehouse • Diferentes tipos de metadata que tem de ser gerida: - Administrativa - toda a informação necessária para a criação e utilização de uma warehouse; - Negocial - termos e definições de negócios, propriedade dos dados e políticas tarifárias; - Operacional - a linhagem dos dados migrados e transformados, o estado dos dados na warehouse e informação de monitorização. • Pode ser usado um repositório de metadata que é utilizado para guardar e gerir toda a metadata associada à warehouse(ex: “PlatinumRepository” e “PrismDirectory Manager”).

32. Metadata e Gestão de uma Warehouse

33. Perguntas???

34. Obrigado pela atenção!