350 likes | 477 Views
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. Índice. Data Warehousing Data Warehouse BDOs vs DWs : BDOs DWs OLAP Cubos OLAP
E N D
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
Í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
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.
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.
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.
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.
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.
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.
Exemplo de queriesad-hocem excel Figura 2 – Exemplo de queriesad-hoc em excel.
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.
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.
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.
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”.
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.
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.
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.
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.
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.
Limpeza de dados • Ferramentas de migração dos dados. • Ferramentas de data scrubbing. • Ferramentas de audição dos dados.
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.
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.
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.
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.
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).
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.
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.
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.
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.
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.
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.
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”).